From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Nicolas Fertig <nfertig@swissnow.ch>
Cc: pgsql-sql@postgresql.org
Subject: Re: OUTER JOIN with filter
Date: Sat, 1 Mar 2003 23:33:00 -0800 (PST)
Message-ID: <20030301232815.Q24208-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <16210.1046588957@sss.pgh.pa.us>
On Sun, 2 Mar 2003, Tom Lane wrote:
> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > On Sat, 1 Mar 2003, Nicolas Fertig wrote:
> >> Can anyone help me with the problem bellow ?
> >>
> >> SELECT TM.some_field, TS.name
> >> FROM table_main TM
> >> LEFT OUTER JOIN (SELECT name FROM table_slave WHERE c1 = 'myvalue') TS
> >> USING(id)
>
> > select tm.some_field, ts.name
> > from table_main tm left outer join table_slave ts
> > on (tm.id=ts.id and ts.c1='myvalue');
>
> 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. :)
> 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...)
I don't see an obvious case where it'd be different either.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone23.bigpanda.com, tgl@sss.pgh.pa.us, nfertig@swissnow.ch
Subject: Re: OUTER JOIN with filter
In-Reply-To: <20030301232815.Q24208-100000@megazone23.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox