From: | "Pavel Stehule" <pavel(dot)stehule(at)hotmail(dot)com> |
---|---|
To: | kleptog(at)svana(dot)org |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Wrong plan for simple join with index on FK |
Date: | 2006-05-16 10:54:58 |
Message-ID: | BAY20-F598A731D3E4CACE485244F9A00@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
>
>Can we seen an EXPLAIN ANALYZE output to see where the miscalculation
>lies. Is it underestimating the cost of the index scan, or
>overestimating the cost of the hash join.
postgres=> explain analyze select count(*) from f1 join f2 on pk=fk;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=6631.75..6631.76 rows=1 width=0) (actual
time=2433.700..2433.703 rows=1 loops=1)
-> Merge Join (cost=0.00..6281.75 rows=140000 width=0) (actual
time=0.055..1916.815 rows=140000 loops=1)
Merge Cond: (f1.pk = f2.fk)
-> Index Scan using f1_pkey on f1 (cost=0.00..187.00 rows=10000
width=4) (actual time=0.025..45.635 rows=10000 loops=1)
-> Index Scan using xxx on f2 (cost=0.00..4319.77 rows=140000
width=4) (actual time=0.011..812.661 rows=140000 loops=1)
Total runtime: 2433.859 ms
(6 rows)
postgres=> explain analyze select count(*) from f1 join f2 on pk=fk;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=7788.00..7788.01 rows=1 width=0) (actual
time=2216.490..2216.493 rows=1 loops=1)
-> Hash Join (cost=170.00..7438.00 rows=140000 width=0) (actual
time=80.296..1712.505 rows=140000 loops=1)
Hash Cond: (f2.fk = f1.pk)
-> Seq Scan on f2 (cost=0.00..2018.00 rows=140000 width=4)
(actual time=0.031..493.614 rows=140000 loops=1)
-> Hash (cost=145.00..145.00 rows=10000 width=4) (actual
time=80.201..80.201 rows=10000 loops=1)
-> Seq Scan on f1 (cost=0.00..145.00 rows=10000 width=4)
(actual time=0.025..37.587 rows=10000 loops=1)
Total runtime: 2216.730 ms
(7 rows)
Regards
Pavel
_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/
From | Date | Subject | |
---|---|---|---|
Next Message | Gurjeet Singh | 2006-05-16 11:06:36 | Re: [BUGS] BUG #2429: Explain does not report object's schema |
Previous Message | Martijn van Oosterhout | 2006-05-16 10:10:46 | Re: Wrong plan for simple join with index on FK |