pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
NOT IN query takes forever
10+ messages / 7 participants
[nested] [flat]

* NOT IN query takes forever
@ 2004-08-03 08:49  Marius Andreiana <mandreiana@rdslink.ro>
  0 siblings, 1 reply; 10+ messages in thread

From: Marius Andreiana @ 2004-08-03 08:49 UTC (permalink / raw)
  To: pgsql-performance

Hi

I have 2 tables like this:
CREATE TABLE query (
	query_id 	int not null,
	dat 	varchar(64)  null ,
	sub_acc_id 	int  null ,
	query_ip 	varchar(64)  null ,
	osd_user_type 	varchar(64)  null 
)
;

CREATE TABLE trans (
	transaction_id 	varchar(64)  not null ,
	date 	varchar(64)  null ,
	query_id 	int not  null ,
	sub_acc_id 	int  null ,
	reg_acc_id 	int  null 
)
;

CREATE UNIQUE INDEX query_query_id_idx
ON query (query_id)
;

CREATE INDEX trans_reg_acc_id_idx
ON trans (reg_acc_id)
;

CREATE INDEX trans_query_id_idx
ON trans(query_id)
;
osd=> select count(*) from trans
osd-> ;
 count
--------
 598809
(1 row)
 
osd=>
osd=> select count(*) from query
osd-> ;
 count
--------
 137042
(1 row)

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 is the default:
# - Memory -
 
shared_buffers = 1000           # min 16, at least max_connections*2,
8KB each
#sort_mem = 1024                # min 64, size in KB
#vacuum_mem = 8192              # min 1024, size in KB

Should I adjust something?

Using postgresql 7.4.2, saw in release notes that IN/NOT IN queries are
at least as faster than EXISTS.

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




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: NOT IN query takes forever
@ 2004-08-03 12:05  Merlin Moncure <merlin.moncure@rcsonline.com>
  0 siblings, 3 replies; 10+ messages in thread

From: Merlin Moncure @ 2004-08-03 12:05 UTC (permalink / raw)
  To: Marius Andreiana <mandreiana@rdslink.ro>; +Cc: pgsql-performance

> 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



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: NOT IN query takes forever
@ 2004-08-03 14:03  Stephan Szabo <sszabo@megazone.bigpanda.com>
  parent: Marius Andreiana <mandreiana@rdslink.ro>
  0 siblings, 0 replies; 10+ messages in thread

From: Stephan Szabo @ 2004-08-03 14:03 UTC (permalink / raw)
  To: Marius Andreiana <mandreiana@rdslink.ro>; +Cc: pgsql-performance


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 is the default:
> # - Memory -
>
> shared_buffers = 1000           # min 16, at least max_connections*2,
> 8KB each
> #sort_mem = 1024                # min 64, size in KB
> #vacuum_mem = 8192              # min 1024, size in KB
>
> Should I adjust something?

Probably sort_mem.  It's probably estimating that it can't hash the result
into the 1MB of sort_mem so it's probably falling back to some sort of
nested execution.




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: NOT IN query takes forever
@ 2004-08-03 14:59  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Merlin Moncure <merlin.moncure@rcsonline.com>
  2 siblings, 0 replies; 10+ messages in thread

From: Tom Lane @ 2004-08-03 14:59 UTC (permalink / raw)
  To: Merlin Moncure <merlin.moncure@rcsonline.com>; +Cc: Marius Andreiana <mandreiana@rdslink.ro>; pgsql-performance

"Merlin Moncure" <merlin.moncure@rcsonline.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



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: NOT IN query takes forever
@ 2004-08-03 16:02  Marius Andreiana <mandreiana@rdslink.ro>
  parent: Merlin Moncure <merlin.moncure@rcsonline.com>
  2 siblings, 1 reply; 10+ messages in thread

From: Marius Andreiana @ 2004-08-03 16:02 UTC (permalink / raw)
  To: 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




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: NOT IN query takes forever
@ 2004-08-03 16:10  Merlin Moncure <merlin.moncure@rcsonline.com>
  0 siblings, 1 reply; 10+ messages in thread

From: Merlin Moncure @ 2004-08-03 16:10 UTC (permalink / raw)
  To: Marius Andreiana <mandreiana@rdslink.ro>; +Cc: pgsql-performance

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

Be careful...sort_mem applies to each connection and (IIRC) in some
cases more than once to a connection.  Of all the configuration
parameters, sort_mem (IMO) is the most important and the hardest to get
right.  128k (or 128MB) is awfully high unless you have a ton of memory
(you don't) or you are running in single connection scenarios.  Do some
experimentation by lowering the value until you get a good balance
between potential memory consumption and speed.

Merlin



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: NOT IN query takes forever
@ 2004-08-03 17:28  Gaetano Mendola <mendola@bigfoot.com>
  parent: Marius Andreiana <mandreiana@rdslink.ro>
  0 siblings, 1 reply; 10+ messages in thread

From: Gaetano Mendola @ 2004-08-03 17:28 UTC (permalink / raw)
  To: Marius Andreiana <mandreiana@rdslink.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
















^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: NOT IN query takes forever
@ 2004-08-03 18:26  Scott Marlowe <smarlowe@qwest.net>
  parent: Merlin Moncure <merlin.moncure@rcsonline.com>
  0 siblings, 0 replies; 10+ messages in thread

From: Scott Marlowe @ 2004-08-03 18:26 UTC (permalink / raw)
  To: Merlin Moncure <merlin.moncure@rcsonline.com>; +Cc: Marius Andreiana <mandreiana@rdslink.ro>; pgsql-performance

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		# min 64, size in KB
> > 
> > it takes <3 seconds (my hardware is not server-class).
> 
> Be careful...sort_mem applies to each connection and (IIRC) in some
> cases more than once to a connection.  Of all the configuration
> parameters, sort_mem (IMO) is the most important and the hardest to get
> right.  128k (or 128MB) is awfully high unless you have a ton of memory
> (you don't) or you are running in single connection scenarios.  Do some
> experimentation by lowering the value until you get a good balance
> between potential memory consumption and speed.

Minor nit, sort_mem actually applies to EACH sort individually, so a
query that had to run three sorts could use 3 x sort_mem.

Note that one can set sort_mem per backend connection with set
sort_mem=128000 if need be so as not to use up all the memory with other
backends.




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: NOT IN query takes forever
@ 2004-08-04 01:32  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Merlin Moncure <merlin.moncure@rcsonline.com>
  2 siblings, 0 replies; 10+ messages in thread

From: Christopher Kings-Lynne @ 2004-08-04 01:32 UTC (permalink / raw)
  To: Merlin Moncure <merlin.moncure@rcsonline.com>; +Cc: Marius Andreiana <mandreiana@rdslink.ro>; pgsql-performance

> 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



^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: [PERFORM] NOT IN query takes forever
@ 2004-08-04 05:40  Marius Andreiana <mandreiana@rdslink.ro>
  parent: Gaetano Mendola <mendola@bigfoot.com>
  0 siblings, 0 replies; 10+ messages in thread

From: Marius Andreiana @ 2004-08-04 05:40 UTC (permalink / raw)
  To: pgsql-general <pgsql-general@postgresql.org>

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




^ permalink  raw  reply  [nested|flat] 10+ messages in thread


end of thread, other threads:[~2004-08-04 05:40 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-03 08:49 NOT IN query takes forever Marius Andreiana <mandreiana@rdslink.ro>
2004-08-03 14:03 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-08-03 12:05 Re: NOT IN query takes forever Merlin Moncure <merlin.moncure@rcsonline.com>
2004-08-03 14:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-03 16:02 ` Marius Andreiana <mandreiana@rdslink.ro>
2004-08-03 17:28   ` Gaetano Mendola <mendola@bigfoot.com>
2004-08-04 05:40     ` Marius Andreiana <mandreiana@rdslink.ro>
2004-08-04 01:32 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-08-03 16:10 Re: NOT IN query takes forever Merlin Moncure <merlin.moncure@rcsonline.com>
2004-08-03 18:26 ` Scott Marlowe <smarlowe@qwest.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox