Re: Poor performance in EXCEPT?

On Mon, 25 Sep 2000, Ben Leslie wrote:

> I'm just having a bit of a problem using an sql query with an except
> clause in it.
>
> I have a squery, which I am sure will work, however it takes an
> excessive amount of time (cancelled at ~10-15minutes).
>
> The query is:
>
> bomond=> select distinct itemid
> from purchase p, orders o
> where p.orderid = o.id and date(o.date) > '30/6/00'
> except
> select id from item;

This might be equivalent and faster (basically untested)

select distinct itemid
from purchase p, orders o
where p.orderid=o.id and date(o.date) > '30/6/00'
and not exists (select item.id from item where item.id=itemid);

There are some cases with IN (which exists is effectively done as
internally IIRC) where the performance can be poor.