pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedControlling join order with parenthesis
2+ messages / 2 participants
[nested] [flat]
* Controlling join order with parenthesis
@ 2010-09-09 12:51 Marc Mamin <M.Mamin@intershop.de>
0 siblings, 1 reply; 2+ messages in thread
From: Marc Mamin @ 2010-09-09 12:51 UTC (permalink / raw)
To: pgsql-sql
Hello,
According to the documentation, I thought it was possible to force given
join orders with parenthesis.
But in this small example, this seems to have no effect;
With the first query, I'd expected to see t3 in the Merge Right Join
but both queries return the same query plan .
I'm missing a point ?
(Postgres 8.3)
create temp table t1(i int);
create temp table t2(i int);
create temp table t3(i int);
select * from
(
( t1 LEFT OUTER JOIN t2 on (t1.i=t2.i) )
LEFT OUTER JOIN t3 on (t2.i=t3.i)
)
select * from
(
t1 LEFT OUTER JOIN
(t2 LEFT OUTER JOIN t3 on (t2.i=t3.i) )
on (t1.i=t2.i)
)
Merge Right Join (cost=506.24..6206.24 rows=345600 width=12)
Merge Cond: (t2.i = t1.i)
-> Merge Left Join (cost=337.49..781.49 rows=28800 width=8)
Merge Cond: (t2.i = t3.i)
-> Sort (cost=168.75..174.75 rows=2400 width=4)
Sort Key: t2.i
-> Seq Scan on t2 (cost=0.00..34.00 rows=2400 width=4)
-> Sort (cost=168.75..174.75 rows=2400 width=4)
Sort Key: t3.i
-> Seq Scan on t3 (cost=0.00..34.00 rows=2400 width=4)
-> Sort (cost=168.75..174.75 rows=2400 width=4)
Sort Key: t1.i
-> Seq Scan on t1 (cost=0.00..34.00 rows=2400 width=4)
best regards,
Marc Mamin
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Controlling join order with parenthesis
@ 2010-09-09 13:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Marc Mamin <M.Mamin@intershop.de>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2010-09-09 13:49 UTC (permalink / raw)
To: Marc Mamin <M.Mamin@intershop.de>; +Cc: pgsql-sql
"Marc Mamin" <M.Mamin@intershop.de> writes:
> According to the documentation, I thought it was possible to force given
> join orders with parenthesis.
You can do that if you dial down join_collapse_limit. Bear in mind that
leaves you *solely* responsible for picking an intelligent join order.
I wouldn't recommend it as a production setting.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-09-09 13:49 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-09-09 12:51 Controlling join order with parenthesis Marc Mamin <M.Mamin@intershop.de>
2010-09-09 13:49 ` Tom Lane <tgl@sss.pgh.pa.us>
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