Re: NOT IN query takes forever

> 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.

Merlin

"Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com> writes:
> Try bumping up shared buffers some and sort mem as much as you safely
> can.

sort_mem is probably the issue here. The only reasonable way to do NOT
IN is with a hash table, and the default setting of sort_mem is probably
too small to support a 137042-element table.

regards, tom lane

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

Marius Andreiana wrote:

> 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

128 MB for sort_mem is too much, consider that in this way each backend can
use 128 MB for sort operations...
Also shared_buffers = 3000 means 24MB that is not balanced with the 128MB
needed for sort...
Try to bump up 128 MB for shared_buffer ( may be you need to instruct your
OS to allow that ammount of shared memory usage ) and 24MB for sort_mem.

Regards
Gaetano Mendola

> 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.

Just use an EXISTS query I suggest.

Chris

On Tue, 2004-08-03 at 19:28 +0200, Gaetano Mendola wrote:
> > With
> > shared_buffers = 3000 # min 16, at least max_connections*2, 8KB each
> > sort_mem = 128000 # min 64, size in KB
>
> 128 MB for sort_mem is too much, consider that in this way each backend can
> use 128 MB for sort operations...
> Also shared_buffers = 3000 means 24MB that is not balanced with the 128MB
> needed for sort...
> Try to bump up 128 MB for shared_buffer ( may be you need to instruct your
> OS to allow that ammount of shared memory usage ) and 24MB for sort_mem.
Thanks for the advice. I increased shmmax to allow shared_buffers to be
128mb and set sort_mem to 24mb.

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