Re: NOT IN query takes forever

Поиск
Список
Период
Сортировка
От Marius Andreiana
Тема Re: NOT IN query takes forever
Дата
Msg-id 1091548963.6915.2.camel@marte.biciclete.ro
обсуждение исходный текст
Ответ на Re: NOT IN query takes forever  ("Merlin Moncure" <merlin.moncure@rcsonline.com>)
Ответы Re: NOT IN query takes forever  (Gaetano Mendola <mendola@bigfoot.com>)
Список pgsql-performance
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


В списке pgsql-performance по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: NOT IN query takes forever
Следующее
От: "Merlin Moncure"
Дата:
Сообщение: Re: NOT IN query takes forever