pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Phil Endecott <spam_from_postgresql_lists@chezphil.org>
To: pgsql-performance@postgresql.org
Subject: Re: Optimisation of INTERSECT expressions
Date: Tue, 23 Mar 2004 11:21:39 -0500
Message-ID: <20040323162823.B4131D1EC40@svr1.postgresql.org> (raw)

I asked:
> select A from T where C1 intersect select A from T where C2;
> select A from T where C1 and C2;
> [why isn't the first optimised into the second?]

Stephan Szabo answered:
> 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.

Tom Lane answered: 
> Another way that the queries are not equivalent is that INTERSECT is
> defined to remove duplicate output rows (much like DISTINCT) whereas
> the AND form of course won't do that.

Thanks!  In my case the attribute A is unique - it is the primary key - and
I hadn't considered the more general case properly.

So I suppose I'll have to find a more sophisticated way to generate my
queries.  Imagine a user interface for a search facility with various
buttons and text entry fields.  At the moment, for each part of the search
that the user has enabled I create a string of SQL.  I then compose them
into a single statement using INTERSECT.  Each sub-query always returns the
same attribute, but to make things complicated they may come from different
tables.  It now seems that I'll have to merge the queries more thoroughly.
 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.


Any thoughts on my other question about empty intersections?

Thanks again for the feedback.

--Phil.



view thread (8+ messages)  latest in thread

Message-ID: <20040323162823.B4131D1EC40@svr1.postgresql.org>
Permalink:  ../20040323162823.B4131D1EC40@svr1.postgresql.org/
Also on:    postgresql.org/message-id/20040323162823.B4131D1EC40@svr1.postgresql.org

 · 

reply

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: spam_from_postgresql_lists@chezphil.org
  Subject: Re: Optimisation of INTERSECT expressions
  In-Reply-To: <20040323162823.B4131D1EC40@svr1.postgresql.org>

* 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