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.
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
On Tue, 2004-08-03 at 10:10, Merlin Moncure wrote:
> > > 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
> > 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 s
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 qu
"Merlin Moncure" <[EMAIL PROTECTED]> 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
On Tue, 3 Aug 2004, Marius Andreiana wrote:
> I just vacuum analyse'd the database.
>
> 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).
>
> My postgresql.conf
> 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
