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.