X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 2F24E476364 for ; Sun, 2 Mar 2003 06:52:09 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 83481-08 for ; Sun, 2 Mar 2003 06:52:08 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id D0FC2476195 for ; Sun, 2 Mar 2003 02:09:22 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h2279H5u016211; Sun, 2 Mar 2003 02:09:17 -0500 (EST) To: Stephan Szabo Cc: Nicolas Fertig , pgsql-sql@postgresql.org Subject: Re: OUTER JOIN with filter In-reply-to: <20030301170535.U21117-100000@megazone23.bigpanda.com> References: <20030301170535.U21117-100000@megazone23.bigpanda.com> Comments: In-reply-to Stephan Szabo message dated "Sat, 01 Mar 2003 17:07:09 -0800" Date: Sun, 02 Mar 2003 02:09:17 -0500 Message-ID: <16210.1046588957@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new X-Archive-Number: 200303/44 X-Sequence-Number: 12084 Stephan Szabo 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 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 concur with the other comments that there's nothing wrong with the given query ... other than such trivial details as it doesn't work ... the sub-select doesn't produce an ID column. So I think Nicolas is giving us an over-censored description of his real problem; but without the omitted details, we're not likely to offer much useful help. regards, tom lane