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 are just 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 are 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
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 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 are 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 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?
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?
awaiting impatiently,
thalis
Show quoted text
---------------------------(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