Re: INNER JOIN ON vs ','+WHERE

On Mon, 25 Jun 2001, Tom Lane wrote:

> "Thalis A. Kalfigopoulos" <thalis(at)cs(dot)pitt(dot)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

>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>