Re: Poor performance in EXCEPT? - Mailing list pgsql-general

From Ben Leslie
Subject Re: Poor performance in EXCEPT?
Date
Msg-id 20000925122426.A9332@bob.sesgroup.net
Whole thread Raw
In response to Re: Poor performance in EXCEPT?  (Stephan Szabo <sszabo@megazone23.bigpanda.com>)
List pgsql-general
On Sun, 24 Sep 2000, Stephan Szabo wrote:

> 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.


Thankyou very much, that works perfectly.

Regards,

Ben Leslie

pgsql-general by date:

Previous
From: "ian"
Date:
Subject: Case Studies for PostgresSQL
Next
From: Jeff MacDonald
Date:
Subject: Re: Case Studies for PostgresSQL