Re: Exploring memory usage

On Tue, Dec 27, 2011 at 8:33 AM, Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:
> On Sat, Dec 24, 2011 at 4:22 PM, Michael Smolsky <sitrash(at)email(dot)com> wrote:
>> work_mem = 128MB (tried 257MB, didn't change anything)
>
> This is probably your problem.
>
> Without an EXPLAIN output, I cannot be sure, but 'work_mem' is not the
> total amount of memory a query can use, it's the amount of memory it
> can use for *one* sort/hash/whatever operation. A complex query can
> have many of those, so your machine is probably swapping due to
> excessive memory requirements.
>
> Try *lowering* it. You can do so only for that query, by executing:
>
> set work_mem = '8MB'; <your query>

He can lower it for just that query but honestly, even on a machine
with much more memory I'd never set it as high as he has it. On a
busy machine with 128G RAM the max I ever had it set to was 16M, and
that was high enough I kept a close eye on it (well, nagios did
anway.)