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
>