Re: Out Of Memory 8.1

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "French, Martin" <frenchm(at)cromwell(dot)co(dot)uk>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Out Of Memory 8.1
Date: 2011-04-07 14:25:54
Message-ID: 12467.1302186354@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

"French, Martin" <frenchm(at)cromwell(dot)co(dot)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

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message French, Martin 2011-04-07 14:56:05 Re: Out Of Memory 8.1
Previous Message Kevin Grittner 2011-04-07 13:48:55 Re: multiple hot standby streaming replication scenario with "rotating" the primary server