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@scw.org>
Director of Information Systems, Support Center of Washington
