X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 561BA475F73 for ; Sun, 2 Mar 2003 00:36:52 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 60023-05 for ; Sun, 2 Mar 2003 00:36:51 -0500 (EST) Received: from wolff.to (wolff.to [66.93.249.74]) by postgresql.org (Postfix) with SMTP id 456FC479E3B for ; Sat, 1 Mar 2003 20:40:19 -0500 (EST) Received: (qmail 17374 invoked by uid 500); 2 Mar 2003 01:54:06 -0000 Date: Sat, 1 Mar 2003 19:54:06 -0600 From: Bruno Wolff III To: Nicolas Fertig Cc: pgsql-sql@postgresql.org Subject: Re: OUTER JOIN with filter Message-ID: <20030302015406.GA17308@wolff.to> Mail-Followup-To: Nicolas Fertig , pgsql-sql@postgresql.org References: <3e61011d$0$1220$5402220f@news.sunrise.ch> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <3e61011d$0$1220$5402220f@news.sunrise.ch> User-Agent: Mutt/1.3.25i X-Virus-Scanned: by amavisd-new X-Archive-Number: 200303/38 X-Sequence-Number: 12078 On Sat, Mar 01, 2003 at 19:53:27 +0100, Nicolas Fertig wrote: > > I want to have all the row in table "table_main" with the value in the table > "table_slave" (value or null if not exist) > > It is possible to have the same result without sub-select in OUTER JOIN > (speed problem on big table) ? Why do you think there will be a speed problem? With proper statistics a good plan for the query below should be possible. As long as both tables have indexes on id a merge join with a filter can be used. If there are only a few rows with c1 = 'myvalue' out of a large number of rows and there is an index on c1, then an alternative plan that does a merge join with the sorted output from the subselect (done with an index scan) might be faster. The planner should be able to choose between these plans as long as has good statistics for the tables. > SELECT TM.some_field, TS.name > FROM table_main TM > LEFT OUTER JOIN (SELECT name FROM table_slave WHERE c1 = 'myvalue') TS > USING(id) Assuming that the above query gives you the results you want, then I expect that it is the most efficient way to write it. You could use a case statement to handle the where c1 = 'myvalue' clause, but doing this is probably going to be slower than doing a join to the subselect.