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