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 1B819D1EC4C for ; Tue, 23 Mar 2004 16:28:47 +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 44256-08 for ; Tue, 23 Mar 2004 12:28:29 -0400 (AST) Received: from mail231.csoft.net (resin.csoft.net [63.111.22.86]) by svr1.postgresql.org (Postfix) with SMTP id B4131D1EC40 for ; Tue, 23 Mar 2004 12:28:23 -0400 (AST) Received: (qmail 13130 invoked from network); 23 Mar 2004 16:21:39 -0000 Received: from unknown (HELO chezphil.org) (63.111.27.87) by mail231.csoft.net with SMTP; 23 Mar 2004 16:21:39 -0000 Content-Disposition: inline Content-Transfer-Encoding: binary Content-Type: text/plain MIME-Version: 1.0 X-Mailer: MIME::Lite 3.01 (F2.71; A1.17; B2.12; Q2.03) From: "Phil Endecott" To: pgsql-performance@postgresql.org Subject: Re: Optimisation of INTERSECT expressions Date: Tue, 23 Mar 2004 11:21:39 -0500 X-Mailer: Phil's WebMail Message-Id: <20040323162823.B4131D1EC40@svr1.postgresql.org> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/310 X-Sequence-Number: 6166 I asked: > select A from T where C1 intersect select A from T where C2; > select A from T where C1 and C2; > [why isn't the first optimised into the second?] Stephan Szabo answered: > Given a non-unique A, C1 as B>5, c2 as C>5 and the data: > A | B | C > 1 | 6 | 1 > 1 | 1 | 6 > The intersect gives 1 row, the and query gives 0 AFAICS. Tom Lane answered: > Another way that the queries are not equivalent is that INTERSECT is > defined to remove duplicate output rows (much like DISTINCT) whereas > the AND form of course won't do that. Thanks! In my case the attribute A is unique - it is the primary key - and I hadn't considered the more general case properly. So I suppose I'll have to find a more sophisticated way to generate my queries. Imagine a user interface for a search facility with various buttons and text entry fields. At the moment, for each part of the search that the user has enabled I create a string of SQL. I then compose them into a single statement using INTERSECT. Each sub-query always returns the same attribute, but to make things complicated they may come from different tables. It now seems that I'll have to merge the queries more thoroughly. 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. Any thoughts on my other question about empty intersections? Thanks again for the feedback. --Phil.