Poor performance in EXCEPT? - Mailing list pgsql-general

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