Обсуждение: Poor performance in EXCEPT?

Hi all,

I'm new to these lists so hopefully I am posting in the right place.

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;

The purchase table has 52370 records in it and the item table has 23540
item in it. purchase.itemid is indexed and so is item.id.

The first query takes ~3 seconds (not including output time) and return
5181 results. The second query takes ~1 seconds (not including output time).

I am able to write a small python program which will get the output from
these two queries and then do the same thing as except should do and it only
takes about 6 seconds to run.


I found a reference to a similar problem at:

http://www.postgresql.org/mhonarc/pgsql-general/1999-07/msg00336.html

However I was unable to work out how to apply this to my sql query.

My question is, what am I doing wrong with my sql query with the except clause
in it? Should it run this slow, am I not indexing the field correctly?

Btw I am using:

[PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by gcc 2.95.2]

Is performance bigger in version 7?

Regards and thanks in advance,

Ben Leslie

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.

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