how postgresql request the computer resources - Mailing list pgsql-performance

Is there something that tells postgres to take the resorces from computer
(RAM, HDD, SWAP on linux) as it need, not modifying variables on
postgresql.conf and other operating system things?

A days ago i am trying to show that postgres is better than mssql but when
execute a simple query like:

(1)
select count(*) from
(
    select archivo from fotos
        except
    select archivo from archivos
) x;
Aggregate  (cost=182162.83..182162.84 rows=1 width=0) (actual
time=133974.495..133974.498 rows=1 loops=1)
  ->  Subquery Scan x  (cost=173857.98..181830.63 rows=132878 width=0)
(actual time=109148.158..133335.279 rows=169672 loops=1)
        ->  SetOp Except  (cost=173857.98..180501.86 rows=132878 width=58)
(actual time=109148.144..132094.382 rows=169672 loops=1)
              ->  Sort  (cost=173857.98..177179.92 rows=1328775 width=58)
(actual time=109147.656..113870.975 rows=1328775 loops=1)
                    Sort Key: archivo
                    ->  Append  (cost=0.00..38710.50 rows=1328775 width=58)
(actual time=27.062..29891.075 rows=1328775 loops=1)
                          ->  Subquery Scan "*SELECT* 1"
(cost=0.00..17515.62 rows=523431 width=58) (actual time=27.052..9560.719
rows=523431 loops=1)
                                ->  Seq Scan on fotos  (cost=0.00..12281.31
rows=523431 width=58) (actual time=27.038..5390.238 rows=523431 loops=1)
                          ->  Subquery Scan "*SELECT* 2"
(cost=0.00..21194.88 rows=805344 width=58) (actual time=10.803..12117.788
rows=805344 loops=1)
                                ->  Seq Scan on archivos
(cost=0.00..13141.44 rows=805344 width=58) (actual time=10.784..5420.164
rows=805344 loops=1)
Total runtime: 134552.325 ms


(2)
select count(*) from fotos where archivo not in (select archivo from
archivos)
Aggregate  (cost=29398.98..29398.99 rows=1 width=0) (actual
time=26660.565..26660.569 rows=1 loops=1)
  ->  Seq Scan on fotos  (cost=15154.80..28744.69 rows=261716 width=0)
(actual time=13930.060..25859.340 rows=169799 loops=1)
        Filter: (NOT (hashed subplan))
        SubPlan
          ->  Seq Scan on archivos  (cost=0.00..13141.44 rows=805344
width=58) (actual time=0.319..5647.043 rows=805344 loops=1)
Total runtime: 26747.236 ms


(3)
select count(1) from fotos f where not exists (select a.archivo from
archivos a where a.archivo=f.archivo)
Aggregate  (cost=1761354.08..1761354.09 rows=1 width=0) (actual
time=89765.384..89765.387 rows=1 loops=1)
  ->  Seq Scan on fotos f  (cost=0.00..1760699.79 rows=261716 width=0)
(actual time=75.556..88880.234 rows=169799 loops=1)
        Filter: (NOT (subplan))
        SubPlan
          ->  Index Scan using archivos_archivo_idx on archivos a
(cost=0.00..13451.40 rows=4027 width=58) (actual time=0.147..0.147 rows=1
loops=523431)
                Index Cond: ((archivo)::text = ($0)::text)
Total runtime: 89765.714 ms



(4)
SELECT count(*)
FROM fotos f
LEFT JOIN archivos a USING(archivo)
WHERE a.archivo IS NULL
Aggregate  (cost=31798758.40..31798758.41 rows=1 width=0) (actual
time=114267.337..114267.341 rows=1 loops=1)
  ->  Merge Left Join  (cost=154143.73..31772412.02 rows=10538550 width=0)
(actual time=85472.696..113392.399 rows=169799 loops=1)
        Merge Cond: ("outer"."?column2?" = "inner"."?column2?")
        Filter: ("inner".archivo IS NULL)
        ->  Sort  (cost=62001.08..63309.66 rows=523431 width=58) (actual
time=38018.343..39998.201 rows=523431 loops=1)
              Sort Key: (f.archivo)::text
              ->  Seq Scan on fotos f  (cost=0.00..12281.31 rows=523431
width=58) (actual time=0.158..4904.410 rows=523431 loops=1)
        ->  Sort  (cost=92142.65..94156.01 rows=805344 width=58) (actual
time=47453.790..50811.216 rows=805701 loops=1)
              Sort Key: (a.archivo)::text
              ->  Seq Scan on archivos a  (cost=0.00..13141.44 rows=805344
width=58) (actual time=0.206..7160.148 rows=805344 loops=1)
Total runtime: 114893.116 ms




WITH ANY OF THIS QUERIES MSSQL TAKES NOT MUCH OF 7 SECONDS....


PLEASE HELP ME

_________________________________________________________________
Consigue aquí las mejores y mas recientes ofertas de trabajo en América
Latina y USA: http://latam.msn.com/empleos/