pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Meszaros Attila <tilla@draconis.elte.hu>
Cc: pgsql-sql@hub.org
Subject: Re: Optimizing Multiply Joins ???
Date: Thu, 14 Sep 2000 12:21:47 -0400
Message-ID: <14303.968948507@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.3.96.1000914151743.2542A-100000@draconis.csoma.elte.hu>
References: <Pine.LNX.3.96.1000914151743.2542A-100000@draconis.csoma.elte.hu>

Meszaros Attila <tilla@draconis.elte.hu> writes:
>> Actually, as the 7.1 code currently stands, a query that uses explicit
>> JOIN operators like yours does will always be implemented in exactly
>> the given join order, with no searching.  I haven't quite decided if
>> that's a bug or a feature ...

> 	Do you mean a "linear binary tree" like this is executed?

> 	     /\
> 	    /\ f
> 	   /\ e
> 	  /\ d
> 	 /\ c
> 	a  b

If that's what you write, yes.  You can parenthesize the JOIN clauses
any way you like, though, and the 7.1 planner will follow that structure.
For example

SELECT ... FROM (a CROSS JOIN b) CROSS JOIN (c CROSS JOIN d) WHERE ...

is semantically the same as FROM a,b,c,d, but given this JOIN form
the planner will only consider plans that join a to b and join c to d
and finally join those results.  With the "FROM a,b,c,d" form it will
do a search through all possible join orders, same as before.  Also,
you can mix styles:

SELECT ... FROM a, b, c CROSS JOIN d WHERE ...

which forces c and d to be joined first, but lets the planner have its
head about what to do next.

> 	I'm not sure which version of standards allows to bracket joins,
> 	but I know sybase accepts the above form.

SQL92 says

         <joined table> ::=
                <cross join>
              | <qualified join>
              | <left paren> <joined table> <right paren>

so anything that claims to accept SQL92 had better allow parentheses
around JOIN expressions...

			regards, tom lane



view thread (8+ messages)  latest in thread

Message-ID: <14303.968948507@sss.pgh.pa.us>
Permalink:  ../14303.968948507@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/14303.968948507@sss.pgh.pa.us

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, tilla@draconis.elte.hu, pgsql-sql@hub.org
  Subject: Re: Optimizing Multiply Joins ???
  In-Reply-To: <14303.968948507@sss.pgh.pa.us>
  References: <Pine.LNX.3.96.1000914151743.2542A-100000@draconis.csoma.elte.hu> <14303.968948507@sss.pgh.pa.us>

* 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