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/
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
