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