Thread: query plan and parenthesis

Hi,
I have following view definition given by '\d' command: SELECT t1.col1
FROM ((table1 t1 JOIN table2 t2 ON ((t1.id = t2.id))) JOIN table3 t3 ON
((t2.some = t3.some)));
Is query planner able to optimize order of joining (t1, t2), t3 or must
join with order described by parenthesis ? This is very important for
joining small (50 rows) and huge (5M rows) tables.

> I have following view definition given by '\d' command: SELECT t1.col1

> FROM ((table1 t1 JOIN table2 t2 ON ((t1.id = t2.id))) JOIN table3 t3
ON
> ((t2.some = t3.some)));
> Is query planner able to optimize order of joining (t1, t2), t3 or
must
> join with order described by parenthesis ? This is very important for
> joining small (50 rows) and huge (5M rows) tables.
>
The postgresql-7.3.2 documentation says:
Finally, a FROM item can be a JOIN clause, which combines two simpler
FROM items.
(Use parentheses if necessary to determine the order of nesting.)
So, I say, anyway if the planner is able to or not, if you know in
advance what order of
nesting is optimal, use parentheses.
On the other hand, after doing a VACUUM, you can always do an EXPLAIN to
see
how the planner will act.
Regards, Christoph

Thanks for answer, but:
I did not want to determine the order of nesting, so I wrote something
like CREATE VIEW myview AS SELECT ... FROM t1 INNER JOIN t2 ON ... INNER
JOIN t3 ON ... ; - without parenthesis
When I read output of '\d myview', I found parenthesis.
I think I must set some appropriate nesting order when I am defining the
view - optimizer are not able to resolve it from internally stored
definition.

regards, jan

Christoph Haller wrote:
>>I have following view definition given by '\d' command: SELECT t1.col1
>
>
>>FROM ((table1 t1 JOIN table2 t2 ON ((t1.id = t2.id))) JOIN table3 t3
>
> ON
>
>>((t2.some = t3.some)));
>>Is query planner able to optimize order of joining (t1, t2), t3 or
>
> must
>
>>join with order described by parenthesis ? This is very important for
>>joining small (50 rows) and huge (5M rows) tables.
>>
>
> The postgresql-7.3.2 documentation says:
> Finally, a FROM item can be a JOIN clause, which combines two simpler
> FROM items.
> (Use parentheses if necessary to determine the order of nesting.)
> So, I say, anyway if the planner is able to or not, if you know in
> advance what order of
> nesting is optimal, use parentheses.
> On the other hand, after doing a VACUUM, you can always do an EXPLAIN to
> see
> how the planner will act.
> Regards, Christoph
>
>

>
> Thanks for answer, but:
> I did not want to determine the order of nesting, so I wrote something

> like CREATE VIEW myview AS SELECT ... FROM t1 INNER JOIN t2 ON ...
INNER
> JOIN t3 ON ... ; - without parenthesis
> When I read output of '\d myview', I found parenthesis.
I see.
> I think I must set some appropriate nesting order when I am defining
the
> view - optimizer are not able to resolve it from internally stored
> definition.
>
Another thought:
There has been recently a discussion on the hackers list about
unnecessary
parenthesesing while storing view definitions. The conclusion was it's
far too
error prone to start something like that. Because indeed there is no win
at all
regarding query execution in defining a view, you may consider skip the
definition
and leave it to the optimizer when preparing the original query.
Regards, Christoph

Jan Poslusny <pajout@gingerall.cz> writes:
> I have following view definition given by '\d' command: SELECT t1.col1
> FROM ((table1 t1 JOIN table2 t2 ON ((t1.id = t2.id))) JOIN table3 t3 ON
> ((t2.some = t3.some)));
> Is query planner able to optimize order of joining (t1, t2), t3 or must
> join with order described by parenthesis ? This is very important for
> joining small (50 rows) and huge (5M rows) tables.

See
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=explicit-joins.html
and the same page updated for 7.4:
http://developer.postgresql.org/docs/postgres/explicit-joins.html

The discussion here seems to have gone off on a tangent: adding or
leaving out explicit parentheses *does not* affect what the planner will
do with a JOIN construct, it only makes it perfectly clear to both you
and the machine what the nesting order is.  If you leave out parentheses
then the joins nest left-to-right, but this is not semantically
different from having written the same thing with parentheses,
eg,  (A JOIN B ...) JOIN C ...

            regards, tom lane