From: Gavin Sherry <swm@linuxworld.com.au>
To: Anjan Kumar. A. <anjankumar@cse.iitb.ac.in>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Problem with the Planner
Date: Mon, 16 Jan 2006 11:20:16 +1100 (EST)
Message-ID: <Pine.LNX.4.58.0601161119230.30653@linuxworld.com.au> (raw)
In-Reply-To: <Pine.LNX.4.61.0601160500250.13979@nsl-33.cse.iitb.ac.in>
References: <Pine.LNX.4.61.0512111610320.4525@nsl-33.cse.iitb.ac.in>
<17378.1134321553@sss.pgh.pa.us>
<Pine.LNX.4.61.0512121833490.11179@nsl-33.cse.iitb.ac.in>
<Pine.LNX.4.61.0601160500250.13979@nsl-33.cse.iitb.ac.in>
On Mon, 16 Jan 2006, Anjan Kumar. A. wrote:
>
>
>
> Please observe the following queries. Why PostgreSQL is favouring MergeJoin eventhough, it leading to higher execution times 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.520 rows=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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-benchmarks@postgresql.org
Cc: swm@linuxworld.com.au, anjankumar@cse.iitb.ac.in, pgsql-hackers@postgresql.org
Subject: Re: Problem with the Planner
In-Reply-To: <Pine.LNX.4.58.0601161119230.30653@linuxworld.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox