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: (view raw, whole thread or download thread mbox)
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.


SELECT shoporder 
  FROM sodetailtabletrans 
       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


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-2017 The PostgreSQL Global Development Group