Thread: INNER JOIN ON vs ','+WHERE
I noticed that doing a join with the INNER JOIN ON... syntax gives a different execution plan (for complex queries at least)than when using the ',' syntax with the join conditions in the WHERE clause. Actually the latter proved more efficient:-/ I assume/guess/speculate that the optimizer gets more freedom in the order it'll perform the join when all conditions arejust thrown in the WHERE clause whereas the former case forces it to follow the user provided join order? TIA, thalis
"Thalis A. Kalfigopoulos" <thalis@cs.pitt.edu> writes: > I noticed that doing a join with the INNER JOIN ON... syntax gives a different execution plan (for complex queries at least)than when using the ',' syntax with the join conditions in the WHERE clause. Actually the latter proved more efficient:-/ > I assume/guess/speculate that the optimizer gets more freedom in the order it'll perform the join when all conditions arejust thrown in the WHERE clause whereas the former case forces it to follow the user provided join order? http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/explicit-joins.html regards, tom lane
On Mon, 25 Jun 2001, Tom Lane wrote: > "Thalis A. Kalfigopoulos" <thalis@cs.pitt.edu> writes: > > I noticed that doing a join with the INNER JOIN ON... syntax gives a different execution plan (for complex queries atleast) than when using the ',' syntax with the join conditions in the WHERE clause. Actually the latter proved more efficient:-/ > > I assume/guess/speculate that the optimizer gets more freedom in the order it'll perform the join when all conditionsare just thrown in the WHERE clause whereas the former case forces it to follow the user provided join order? > > http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/explicit-joins.html > > regards, tom lane Amazingly to the point as always. And now question hour: Changing the type of "join" syntax affects the plan-generation time and the execution-time. Would it be logical to EXPLAINthe query once using the 'FROM a,b,c WHERE...' syntax and then assuming that it returns the optimal execution plan,use the join order of this plan to rewrite the query in a 'a INNER JOIN b ON ... INNER JOIN c ON...' fashion so as tosave the plan generation time from then on? Is the plan generation time significant compared to actual execution time when we are talking about large tables even ifthere's lots of them? The question actually is: is there any more time involved to plan generation other than what it takeswhen I do an EXPLAIN on the query? After that, all time is consumed to performing the actual joins, correct? awaiting impatiently, thalis > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
"Thalis A. Kalfigopoulos" <thalis@cs.pitt.edu> writes:
> Changing the type of "join" syntax affects the plan-generation time
> and the execution-time. Would it be logical to EXPLAIN the query once
> using the 'FROM a,b,c WHERE...' syntax and then assuming that it
> returns the optimal execution plan, use the join order of this plan to
> rewrite the query in a 'a INNER JOIN b ON ... INNER JOIN c ON...'
> fashion so as to save the plan generation time from then on?
Yup, that's more or less what the documentation is trying to suggest,
down at the bottom.
> Is the plan generation time significant compared to actual execution
> time when we are talking about large tables even if there's lots of
> them? The question actually is: is there any more time involved to
> plan generation other than what it takes when I do an EXPLAIN on the
> query? After that, all time is consumed to performing the actual
> joins, correct?
EXPLAIN runs the same planner as actual execution does. If EXPLAIN
doesn't take long enough to bother you, then you don't need to worry
about planning time.
regards, tom lane