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