From: Tom Lane <tgl@sss.pgh.pa.us>
To: Bruno Wolff III <bruno@wolff.to>
Cc: Phil Endecott <spam_from_postgresql_lists@chezphil.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimisation of INTERSECT expressions
Date: Tue, 23 Mar 2004 12:21:31 -0500
Message-ID: <21623.1080062491@sss.pgh.pa.us> (raw)
In-Reply-To: <20040323170500.GA25181@wolff.to>
References: <20040323162823.B4131D1EC40@svr1.postgresql.org>
<20040323170500.GA25181@wolff.to>
Bruno Wolff III <bruno@wolff.to> writes:
> On Tue, Mar 23, 2004 at 11:21:39 -0500,
> Phil Endecott <spam_from_postgresql_lists@chezphil.org> wrote:
>> Does anyone have any suggestions about how to do this? I'd like a nice
>> general technique that works for all possible subqueries, as my current
>> composition with INTERSECT does.
> One adjustment you might make is using INTERSECT ALL if you know there
> can't be duplicates. Then time won't be wasted trying to remove duplicates.
Actually, I don't think that will help. UNION ALL is much faster than
UNION, because it doesn't have to match up duplicates, but INTERSECT
and EXCEPT still have to match rows from the inputs in order to find
out if they should emit a row at all. IIRC there will not be any
noticeable speed difference with or without ALL.
AFAICS, what Phil will want to do is
SELECT a FROM table1 WHERE cond11 AND cond12 AND ...
INTERSECT
SELECT a FROM table2 WHERE cond21 AND cond22 AND ...
INTERSECT
...
which is more painful to assemble than his current approach, but it
shouldn't be *that* bad --- you just need to tag each condition with the
table it applies to, and bring together matching tags when you build the
SQL string.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, bruno@wolff.to, spam_from_postgresql_lists@chezphil.org
Subject: Re: Optimisation of INTERSECT expressions
In-Reply-To: <21623.1080062491@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox