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 9208CD1EBEC for ; Tue, 23 Mar 2004 14:50:52 +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 87553-09 for ; Tue, 23 Mar 2004 10:50:53 -0400 (AST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 96D29D1EBC0 for ; Tue, 23 Mar 2004 10:50:49 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 20C973585D; Tue, 23 Mar 2004 06:50:53 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 19C12356CB; Tue, 23 Mar 2004 06:50:53 -0800 (PST) Date: Tue, 23 Mar 2004 06:50:53 -0800 (PST) From: Stephan Szabo To: Phil Endecott Cc: pgsql-performance@postgresql.org Subject: Re: Optimisation of INTERSECT expressions In-Reply-To: <20040323141200.34D86D1EC02@svr1.postgresql.org> Message-ID: <20040323063650.X85869@megazone.bigpanda.com> References: <20040323141200.34D86D1EC02@svr1.postgresql.org> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/306 X-Sequence-Number: 6162 On Tue, 23 Mar 2004, Phil Endecott wrote: > Dear PostgresQL Experts, > > I am trying to get to the bottom of some efficiency problems and hope that > you can help. The difficulty seems to be with INTERSECT expressions. > > I have a query of the form > select A from T where C1 intersect select A from T where C2; > It runs in about 100 ms. > > But it is equivalent to this query > select A from T where C1 and C2; > which runs in less than 10 ms. > > Looking at the output of "explain analyse" on the first query, it seems > that PostgresQL always computes the two sub-expressions and then computes > an explicit intersection on the results. I had hoped that it would notice > that both subexpressions are scanning the same input table T and convert > the expression to the second form. > > Is there a reason why it can't do this transformation? Probably because noone's bothered to try to prove under what conditions it's the same. For example, given a non-unique A, the two queries can give different answers (if say the same two A values match both C1 and C2 in different rows how many output rows does each give? *), also given a non-stable A (for example random) the two queries are not necessarily equivalent.