Skip site navigation (1) Skip section navigation (2)

Re: Optimisation of INTERSECT expressions

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Phil Endecott <spam_from_postgresql_lists(at)chezphil(dot)org>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Optimisation of INTERSECT expressions
Date: 2004-03-23 15:14:46
Message-ID: 20040323071350.Y86816@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.


In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2004-03-23 15:47:42
Subject: Re: Optimisation of INTERSECT expressions
Previous:From: Stephan SzaboDate: 2004-03-23 14:50:53
Subject: Re: Optimisation of INTERSECT expressions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group