Re: Problem with the Planner

On Mon, 16 Jan 2006, Anjan Kumar. A. wrote:

>
>
>
> Please observe the following queries. Why PostgreSQL is favouring MergeJoin eventhough, it leading to higher
executiontimes than 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
>
--------------------------------------------------------------------------------------------------------------------------------------------
>   Merge Join  (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.520rows=10000 loops=1)
 
>     ->  Sort  (cost=665.09..673.42 rows=3334 width=244) (actual time=9.601..9.646 rows=50 loops=1)
>           Sort Key: t1.unique2
>           ->  Seq Scan on tenk1 t1  (cost=0.00..470.00 rows=3334 width=244) (actual time=0.154..9.140 rows=50
loops=1)
>                 Filter: (unique1 < 50)
>   Total runtime: 41.101 ms
> (8 rows)

Your statistics are way off. The seqscan on tenk1 estimates 3334 rows but
gets only 50. Run ANALYZE and try again.

Thanks,

Gavin