Re: NOT IN query takes forever

On Tue, 2004-08-03 at 08:05 -0400, Merlin Moncure wrote:
> > Trying to run this query:
> > EXPLAIN ANALYSE
> > select * FROM trans
> > WHERE query_id NOT IN (select query_id FROM query)
> >
> > but it will remain like that forever (cancelled after 30 min).
>
> explain analyze actually runs the query to do timings. Just run explain
> and see what you come up with. More than likely there is a nestloop in
> there which is causing the long query time.
>
> Try bumping up shared buffers some and sort mem as much as you safely
> can.
Thank you, that did it!

With
shared_buffers = 3000 # min 16, at least max_connections*2, 8KB each
sort_mem = 128000 # min 64, size in KB

it takes <3 seconds (my hardware is not server-class).

--
Marius Andreiana
Galuna - Solutii Linux in Romania
http://www.galuna.ro