Re: Join efficiency

Russ Brown wrote:
>
> I have always written queries with ordinary joins in this manner:
>
> SELECT * FROM a, b WHERE a.x=b.x;
>
> However I recently saw an laternative syntax:
>
> SELECT * FROM a JOIN b ON a.x=b.x;
>
> Is there any difference between these queries in terms of the speed of
> planning or the quality of the plan untimately used? I'd imagine that
> the second form provides more information that the planner may be able
> to use to make a better plan (or make a good plan more easily), but
> I've never had any problems with the first form.

The first form allows PG to plan however it sees fit. The second will
force the join order to be the same as you specify in the query. This
doesn't matter here, but might with a more complicated query.

With v7.4 and higher, I believe this join forcing is configurable
(join_collapse_limit).

> It also seems to me that the second form is more self-documenting,
> which is something I'm always in favour of.

I tend to prefer the WHERE form, but that might just be me.

--
Richard Huxton
Archonet Ltd