Re: Any optimizations to the join code in 7.1?

On Wed, 25 Apr 2001, Tom Lane wrote:

> > 2) The explicit-joins help suggests that manual structuring and
> > experimentation might help -- has anyone written (or could
> > anyone write) anthing about where to start in guessing what
> > join order might be optimal?
>
> The obvious starting point is the plan produced by the planner from an
> unconstrained query. Even if you don't feel like trying to improve it,
> you could cut the time to reproduce the plan quite a bit --- just CROSS
> JOIN a few of the relation pairs that are joined first in the
> unconstrained plan.

In other words, let it do the work, and steal the credit for
ourselves. :-)

Thanks, Tom. I appreciate your answers to my questions.

In other DB systems I've used, some find that for this original query:

SELECT * FROM a, b WHERE a.id=b.id AND b.name = 'foo';

that this version

SELECT * FROM a JOIN b USING (id) WHERE b.name = 'foo';

has slower performance than

SELECT * FROM b JOIN a USING (id) WHERE b.name = 'foo';

because it can reduce b before any join.

Is it safe to assume that this is a valid optimization in PostgreSQL?

If this whole thing were a view, except w/o the WHERE clause, and we were
querying the view w/the b.name WHERE clause, would we still see a
performance boost from the right arrangement? (ie, does our criteria get
pushed down early enough in the joining process?)

TIA,
--
Joel Burton <jburton(at)scw(dot)org>
Director of Information Systems, Support Center of Washington