X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 6EF86D1B51F for ; Tue, 23 Mar 2004 17:21:36 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 80376-08 for ; Tue, 23 Mar 2004 13:21:35 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 67381D1EC4A for ; Tue, 23 Mar 2004 13:21:32 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i2NHLVPT021624; Tue, 23 Mar 2004 12:21:31 -0500 (EST) To: Bruno Wolff III Cc: Phil Endecott , pgsql-performance@postgresql.org Subject: Re: Optimisation of INTERSECT expressions In-reply-to: <20040323170500.GA25181@wolff.to> References: <20040323162823.B4131D1EC40@svr1.postgresql.org> <20040323170500.GA25181@wolff.to> Comments: In-reply-to Bruno Wolff III message dated "Tue, 23 Mar 2004 11:05:00 -0600" Date: Tue, 23 Mar 2004 12:21:31 -0500 Message-ID: <21623.1080062491@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/314 X-Sequence-Number: 6170 Bruno Wolff III writes: > On Tue, Mar 23, 2004 at 11:21:39 -0500, > Phil Endecott wrote: >> Does anyone have any suggestions about how to do this? I'd like a nice >> general technique that works for all possible subqueries, as my current >> composition with INTERSECT does. > One adjustment you might make is using INTERSECT ALL if you know there > can't be duplicates. Then time won't be wasted trying to remove duplicates. Actually, I don't think that will help. UNION ALL is much faster than UNION, because it doesn't have to match up duplicates, but INTERSECT and EXCEPT still have to match rows from the inputs in order to find out if they should emit a row at all. IIRC there will not be any noticeable speed difference with or without ALL. AFAICS, what Phil will want to do is SELECT a FROM table1 WHERE cond11 AND cond12 AND ... INTERSECT SELECT a FROM table2 WHERE cond21 AND cond22 AND ... INTERSECT ... which is more painful to assemble than his current approach, but it shouldn't be *that* bad --- you just need to tag each condition with the table it applies to, and bring together matching tags when you build the SQL string. regards, tom lane