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.00rows=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.00rows=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/