Re: slow query - where not in

Jeremiah Elliott <jelliott(at)artcobell(dot)com> writes:

> here is the query that is killing me:
>
> select shoporder from sodetailtabletrans where shoporder not in(select
> shoporder from soheadertable)
>
> This is just an example query. Any time I use 'where not in(' it takes several
> hours to return a resultset. The postgres version is 7.2.3 although I have
> tried it on my test server which has 7.3 on it and it runs just as slow. The
> server is a fast server 2GHz with a gig of ram. I have tried several
> differant index setups but nothing seems to help.

This should be improved with 7.4, however there are some other things you can
try now.

try

SELECT shoporder
FROM sodetailtabletrans
WHERE NOT EXISTS (
SELECT 1
FROM soheadertable
WHERE shoporder = sodetailtabletrans.shoporder
)

or else try something like

SELECT a.shoporder
FROM sodetailtabletrans as a
LEFT OUTER JOIN soheadertable as b ON (a.shoporder = b.shoporder)
WHERE b.shoporder IS NULL

--
greg