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 A9CD4D1B97B for ; Tue, 23 Mar 2004 15:48:12 +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 17496-10 for ; Tue, 23 Mar 2004 11:48:14 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id B9260D1B49B for ; Tue, 23 Mar 2004 11:48:09 -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 i2NFlgJu020716; Tue, 23 Mar 2004 10:47:42 -0500 (EST) To: Stephan Szabo Cc: Phil Endecott , pgsql-performance@postgresql.org Subject: Re: Optimisation of INTERSECT expressions In-reply-to: <20040323071350.Y86816@megazone.bigpanda.com> References: <20040323141200.34D86D1EC02@svr1.postgresql.org> <20040323063650.X85869@megazone.bigpanda.com> <20040323071350.Y86816@megazone.bigpanda.com> Comments: In-reply-to Stephan Szabo message dated "Tue, 23 Mar 2004 07:14:46 -0800" Date: Tue, 23 Mar 2004 10:47:42 -0500 Message-ID: <20715.1080056862@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/308 X-Sequence-Number: 6164 Stephan Szabo writes: > 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. 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. regards, tom lane