Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>,
	Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: OUTER JOIN with filter
Date: Mon, 3 Mar 2003 10:30:11 -0800
User-Agent: KMail/1.4.3
Cc: Nicolas Fertig <nfertig@swissnow.ch>, <pgsql-sql@postgresql.org>
References: <20030301232815.Q24208-100000@megazone23.bigpanda.com>
In-Reply-To: <20030301232815.Q24208-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Message-Id: <200303031030.11785.josh@agliodbs.com>
Content-Transfer-Encoding: quoted-printable

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 wi=
th=20
criteria, it pays to try the query both ways.   The way Stephan did it is=
=20
frequently very speedy with small tables but not so speedy with large ones.=
=20=20

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

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

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

> > push down the ts.c1=3D'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=3D'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=
=20
times larger than the main queried table.  The planner should recognize the=
=20
possibility of filtering the records in the joined table before joining in=
=20
order to minimize the join operation.  If that's what you're asking, please=
=20
fix it!

--=20
Josh Berkus
Aglio Database Solutions
San Francisco