Re: OUTER JOIN with filter

Tom, Stephan,

> > Offhand I believe that these queries should generate identical plans.
> > They do not at the moment --- the second one generates a worse plan
> > (sorry Stephan ;-)) --- because the planner does not realize it could
>
> I wasn't really sure if it would or not (wrote it without testing on a
> running server), actually I didn't realize it wouldn't push down, and
> figured it'd give pretty much the same plan, but it is less verbose. :)

Yeah.  I've found that when I'm doing a left outer join to a large table with
criteria, it pays to try the query both ways.   The way Stephan did it is
frequently very speedy with small tables but not so speedy with large ones.

For that matter, in a few queries I've found that it pays to force the
planner's hand by repeating some of the clauses in the WHERE clause in the
JOIN as well, as:

SELECT a.x, b.y, c.z
FROM a JOIN b ON (a.id = b.a_id AND b.type = 'm')LEFT OUTER JOIN c ON (b.id = c.b_id)
WHERE b.type = 'm'

This is only useful, of course, when the presense of outer joins forces you
into an explicit join order ( And not always then ) -- otherwise the planner
will generally do a good job given a free hand.

> > push down the ts.c1='myvalue' JOIN condition into the scan of ts, even
> > though the join is OUTER.  But AFAICS it would not change the results to
> > do so; ts rows failing ts.c1='myvalue' will not produce join output
> > anyway, but would allow outer-joined lefthand rows to be produced.  (Can
> > anyone see a hole in that logic?  It's on my to-do list to change it...)

The logic is fine.  The issue comes where the outer joined table is several
times larger than the main queried table.  The planner should recognize the
possibility of filtering the records in the joined table before joining in
order to minimize the join operation.  If that's what you're asking, please
fix it!

--
Josh Berkus
Aglio Database Solutions
San Francisco