Re: [GENERAL] Except operation

kaiq(at)realtyideas(dot)com wrote:

> how about I'm a sql92 lover? :-)
>
> the idea is to use index. but the engine only use index when it sees
> "where". so, how about add "where w.tid = w.tid" to the first (or
> both) select on the two sides of the except?
>
> not tested. if work, please let us know, thanks.
>
> Kai

That's not the problem. The problem is that the INTERSECT/EXCEPT code
uses the query rewriter to automatically rewrite the query to something
like:

SELECT * FROM webdata w
WHERE (<webdata.fieldlist>) NOT IN (
SELECT * FROM webdata1);

and 'IN' clauses in PostgreSQL can't use indices. The result is always a
sequential scan on the IN values. INTERSECT/EXCEPT should have been
written to rewrite the query using correlated subqueries and the EXISTS
test, as Adriaan suggests below. In fact, one of the possible solutions
for the un-usability of IN clauses is to have PostgreSQL rewrite those as
EXISTS....but no one's done that yet.

Mike

>
> On Thu, 2 Dec 1999, Adriaan Joubert wrote:
>
> > Satyajeet Seth wrote:
> > >
> > > Hi
> > > The query:
> > > select * from webdata except select * from webdata1;
> > > takes abysmally long .How can I optimise it?
> > > The particulars are:
> >
> > You could try
> >
> > select * from webdata w
> > where not exists
> > (select * from webdata1 w1
> > where w1.tid=w.tid
> > ...
> > )
> >
> > If you have the correct indexes on webdata1 this can be quite fast.
> >
> > Adriaan