From: | Sidar López Cruz <sidarlopez(at)hotmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | how postgresql request the computer resources |
Date: | 2005-10-27 14:25:23 |
Message-ID: | BAY23-F28F5DD23A4A73C7BFE3CD1CE680@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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/
From | Date | Subject | |
---|---|---|---|
Next Message | PFC | 2005-10-27 14:33:51 | Re: browsing table with 2 million records |
Previous Message | Jan Wieck | 2005-10-27 13:41:26 | Re: Perfomance of views |