From: Tom Lane <tgl@sss.pgh.pa.us>
To: French, Martin <frenchm@cromwell.co.uk>
Cc: pgsql-admin@postgresql.org
Subject: Re: Out Of Memory 8.1
Date: Thu, 07 Apr 2011 10:25:54 -0400
Message-ID: <12467.1302186354@sss.pgh.pa.us> (raw)
In-Reply-To: <81976671721DF04B9DCA6ECD87941A40287D35AF@roundway.Cromwell-tools.co.uk>
References: <81976671721DF04B9DCA6ECD87941A40287D35AF@roundway.Cromwell-tools.co.uk>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, frenchm@cromwell.co.uk
Subject: Re: Out Of Memory 8.1
In-Reply-To: <12467.1302186354@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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