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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paesold | 2003-03-28 16:53:46 | Re: slow query - where not in |
Previous Message | Bruno Wolff III | 2003-03-28 15:59:33 | Re: slow query - where not in |