Problem with the Planner - Mailing list pgsql-hackers

Please observe the following queries. Why PostgreSQL is favouring MergeJoin eventhough, it leading to higher execution
timesthan NestedLoopJoin. Any suggestions to fix this problem.
 


bench=# EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;
                                                   QUERY PLAN
 

--------------------------------------------------------------------------------------------------------------------------------------------
MergeJoin  (cost=665.09..4704.60 rows=166701 width=488) (actual time=10.128..40.843 rows=50 loops=1)   Merge Cond:
("outer".unique2= "inner".unique2)   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.00..1514.00 rows=10000
width=244)(actual time=0.031..20.520 rows=10000 loops=1)   ->  Sort  (cost=665.09..673.42 rows=3334 width=244) (actual
time=9.601..9.646rows=50 loops=1)         Sort Key: t1.unique2         ->  Seq Scan on tenk1 t1  (cost=0.00..470.00
rows=3334width=244) (actual time=0.154..9.140 rows=50 loops=1)               Filter: (unique1 < 50) Total runtime:
41.101ms
 
(8 rows)

bench=# SET enable_mergejoin = off;
SET
bench=# EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;
                                        QUERY PLAN
 
----------------------------------------------------------------------------------------------------------------------
HashJoin  (cost=588.34..11841.35 rows=166701 width=488) (actual time=9.028..70.453 rows=50 loops=1)   Hash Cond:
("outer".unique2= "inner".unique2)   ->  Seq Scan on tenk2 t2  (cost=0.00..445.00 rows=10000 width=244) (actual
time=0.007..11.846rows=10000 loops=1)   ->  Hash  (cost=470.00..470.00 rows=3334 width=244) (actual time=8.378..8.378
rows=0loops=1)         ->  Seq Scan on tenk1 t1  (cost=0.00..470.00 rows=3334 width=244) (actual time=0.135..8.093
rows=50loops=1)               Filter: (unique1 < 50) Total runtime: 70.659 ms
 
(7 rows)

bench=# SET enable_hashjoin = off;
SET
bench=# EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;
                                               QUERY PLAN
 

------------------------------------------------------------------------------------------------------------------------------------
NestedLoop  (cost=0.00..633218.15 rows=166701 width=488) (actual time=0.178..9.389 rows=50 loops=1)   ->  Seq Scan on
tenk1t1  (cost=0.00..470.00 rows=3334 width=244) (actual time=0.135..8.349 rows=50 loops=1)         Filter: (unique1 <
50)  ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.00..189.16 rows=50 width=244) (actual time=0.009..0.011
rows=1loops=50)         Index Cond: ("outer".unique2 = t2.unique2) Total runtime: 9.552 ms
 
(6 rows)


-- 
Regards.

Anjan Kumar A.
MTech2,  Comp Sci.,
www.cse.iitb.ac.in/~anjankumar
______________________________________________________________