Re: Any optimizations to the join code in 7.1?

Joel Burton <jburton(at)scw(dot)org> writes:
> 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?

In general, that'd be a waste of time --- our planner considers the same
set of plans in either case.

However, it could make a difference if the planner thinks that the two
choices (a outer or b outer) have exactly the same cost. In that case
the order you wrote them in will influence which plan actually gets
picked; and if the planner's estimate is wrong --- ie, there really is a
considerable difference in the costs --- then you could see a change in
performance depending on which way you wrote it. That's a pretty
unusual circumstance, maybe, but it just happens that I'm in the middle
of looking at a planning bug wherein exactly this behavior occurs...

> 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?)

Shouldn't make a difference; AFAIK the WHERE clause will get pushed down
as far as possible, independently of whether a view is involved or you
wrote it out the hard way.

regards, tom lane