Skip site navigation (1) Skip section navigation (2)

Re: slow query - where not in

From: Greg Stark <gsstark(at)mit(dot)edu>
To: jelliott(at)artcobell(dot)com
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: slow query - where not in
Date: 2003-03-28 16:20:29
Message-ID: 87u1dn31ki.fsf@stark.dyndns.tv (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

pgsql-performance by date

Next:From: Michael PaesoldDate: 2003-03-28 16:53:46
Subject: Re: slow query - where not in
Previous:From: Bruno Wolff IIIDate: 2003-03-28 15:59:33
Subject: Re: slow query - where not in

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group