Re: Wrong plan for simple join with index on FK

>
>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/