pg.ddx.io pgsql-benchmarks@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with the Planner
2+ messages / 2 participants
[nested] [flat]
* Problem with the Planner
@ 2006-01-15 23:35 Anjan Kumar. A. <anjankumar@cse.iitb.ac.in>
0 siblings, 1 reply; 2+ messages in thread
From: Anjan Kumar. A. @ 2006-01-15 23:35 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org
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)
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
----------------------------------------------------------------------------------------------------------------------
Hash Join (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.846 rows=10000 loops=1)
-> Hash (cost=470.00..470.00 rows=3334 width=244) (actual time=8.378..8.378 rows=0 loops=1)
-> Seq Scan on tenk1 t1 (cost=0.00..470.00 rows=3334 width=244) (actual time=0.135..8.093 rows=50 loops=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
------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..633218.15 rows=166701 width=488) (actual time=0.178..9.389 rows=50 loops=1)
-> Seq Scan on tenk1 t1 (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=1 loops=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
______________________________________________________________
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Problem with the Planner
@ 2006-01-16 00:20 Gavin Sherry <swm@linuxworld.com.au>
parent: Anjan Kumar. A. <anjankumar@cse.iitb.ac.in>
0 siblings, 0 replies; 2+ messages in thread
From: Gavin Sherry @ 2006-01-16 00:20 UTC (permalink / raw)
To: Anjan Kumar. A. <anjankumar@cse.iitb.ac.in>; +Cc: pgsql-hackers@postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-01-16 00:20 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-15 23:35 Problem with the Planner Anjan Kumar. A. <anjankumar@cse.iitb.ac.in>
2006-01-16 00:20 ` Gavin Sherry <swm@linuxworld.com.au>
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