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 14:50:53 |
Message-ID: | 20040323063650.X85869@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2004-03-23 15:14:46 | Re: Optimisation of INTERSECT expressions |
Previous Message | Phil Endecott | 2004-03-23 14:12:01 | Optimisation of INTERSECT expressions |