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/


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Reply via email to