Re: Out Of Memory 8.1

Thanks for the info Tom.

The table has been analyzed (somewhat repeatedly...), with the stats
target set at various limits.

At the moment default_statistics_target = 50.

I've had work_mem as low as 1MB and as high as 128MB, with little to no
avail.

Setting enable_hashagg = off, does allow the query to complete, but
mashes the next query in the batch with it having to be killed at 8
hours. Setting it off for this query alone causes the next query to do
exactly the same.

The thing that perplexes me is; that on RHEL 5.0 with all the same
settings and data, the query works fine... so it's confusing as to what
can have changed so much to cause this. I know that we had to move to
RHEL 5.4 because of PERC Raid drivers, but I wouldn't have thought that
would've made too much of a difference.

It might also be of interest that "VACCUM ANALYZE VERBOSE" immediately
falls over with out of memory on a request of 960MB (what it's set to
use...) but that "VACUUM FULL ANALYZE VEBOSE" doesn't...

I have a sneaky suspicion that this is to do with the width of the table
(271 columns of mainly text), and the fact that the designer (not me...)
of it, didn't take into consideration how difficult it would be to
process this amount opf data on a row by row data.

cheers

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: 07 April 2011 15:26
To: French, Martin
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] Out Of Memory 8.1

"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

___________________________________________________

This email is intended for the named recipient. The information contained
in it is confidential. You should not copy it for any purposes, nor
disclose its contents to any other party. If you received this email
in error, please notify the sender immediately via email, and delete it from
your computer.

Any views or opinions presented are solely those of the author and do not
necessarily represent those of the company.

PCI Compliancy: Please note, we do not send or wish to receive banking, credit
or debit card information by email or any other form of communication.

Cromwell Tools Limited, PO Box 14, 65 Chartwell Drive
Wigston, Leicester LE18 1AT. Tel 0116 2888000
Registered in England and Wales, Reg No 00986161
VAT GB 115 5713 87 900
__________________________________________________