Re: Optimizing Multiply Joins ???

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