Re: slow query - where not in

Jeremiah Elliott <jelliott@artcobell.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