Re: Optimisation of INTERSECT expressions

Bruno Wolff III <bruno(at)wolff(dot)to> writes:
> On Tue, Mar 23, 2004 at 11:21:39 -0500,
> Phil Endecott <spam_from_postgresql_lists(at)chezphil(dot)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