To: "French, Martin" <frenchm@cromwell.co.uk>
cc: pgsql-admin@postgresql.org
Subject: Re: Out Of Memory 8.1 
In-reply-to: 
 <81976671721DF04B9DCA6ECD87941A40287D35AF@roundway.Cromwell-tools.co.uk>
References: 
 <81976671721DF04B9DCA6ECD87941A40287D35AF@roundway.Cromwell-tools.co.uk>
Comments: In-reply-to "French, Martin" <frenchm@cromwell.co.uk>
	message dated "Wed, 06 Apr 2011 23:44:07 +0100"
Date: Thu, 07 Apr 2011 10:25:54 -0400
Message-ID: <12467.1302186354@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"French, Martin" <frenchm@cromwell.co.uk> writes:
> I am having problems with a query on 8.1 running on 
> RHEL 5.4
> work_mem = 98394

> The explain (cannot explain analyze, or Postgres runs out of memory
> again)
> 'HashAggregate  (cost=2731947.55..2731947.57 rows=1 width=38)'
> '  ->  Seq Scan on stkl_rec  (cost=0.00..2731947.54 rows=1 width=38)'
> '        Filter: (((stkl_comp)::text = 'A'::text) AND
> ((stkl_stockno)::text ~
> '^(TK[A-Za-z0-9][0-9]{3}(?:[0-9]{5}(?:[0-9]{3,4})?)?|NSP[0-9]{3}([0-9]{4
> })?|Z[MZ][0-9]{8,9}|LSP[0-9]{7}[A-Za-z0-9]|[A-Z][A-Z&][A-Z][0-9]{7}[A-Z0
> -9])$'::text) AND ((stkl_stockno)::text !~~ 'ZZ%'::text) AND
> ((stkl_stockno)::text ~
> '^([A-z&]{2,3})([0-9][0-9][0-9])([0-9][0-9][0-9][0-9][A-z0-9]{1,3})$'::t
> ext))'

Apparently the number of groups is way more than the planner expects,
and so the hash table grows to exceed available memory.

Kluge fixes: try reducing work_mem to discourage it from using
HashAggregate.  Or you could temporarily turn off enable_hashagg.

A non-kluge fix would involve getting the planner to realize there are a
lot of groups needed.  Have you analyzed the table lately?  Maybe you
need to increase the statistics target for it.

			regards, tom lane