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 4D1C9D1E934 for ; Tue, 23 Mar 2004 15:14:44 +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 05983-03 for ; Tue, 23 Mar 2004 11:14:46 -0400 (AST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 2C4B2D1E29F for ; Tue, 23 Mar 2004 11:14:42 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 46D05357BA; Tue, 23 Mar 2004 07:14:46 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 455D4357B9; Tue, 23 Mar 2004 07:14:46 -0800 (PST) Date: Tue, 23 Mar 2004 07:14:46 -0800 (PST) From: Stephan Szabo To: Phil Endecott Cc: pgsql-performance@postgresql.org Subject: Re: Optimisation of INTERSECT expressions In-Reply-To: <20040323063650.X85869@megazone.bigpanda.com> Message-ID: <20040323071350.Y86816@megazone.bigpanda.com> References: <20040323141200.34D86D1EC02@svr1.postgresql.org> <20040323063650.X85869@megazone.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/307 X-Sequence-Number: 6163 On Tue, 23 Mar 2004, Stephan Szabo wrote: > 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. Ugh, the example got trimmed out for the * 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.