Thread: 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