Re: [PERFORMANCE] work_mem vs temp files issue

decibel <decibel(at)decibel(dot)org> writes:
> On Aug 19, 2009, at 7:45 PM, Jaime Casanova wrote:
>> AFAIUI, work_mem is used for some operations (sort, hash, etc) for
>> avoiding the use of temp files on disk...
>>
>> In a client server i'm monitoring (pg 8.3.7, 32GB of ram) work_mem is
>> set to 8MB, however i'm seeing a lot of temp files (>30000 in 4 hours)
>> with small sizes (ie: 2021520 obviously lower than 8MB). so, why?
>> maybe we use work_mem until we find isn't enough and we send just the
>> difference to a temp file?
>>
>> i'm not thinking in raising work_mem until i understand this well,
>> what's the point if we still create temp files that could fit in
>> work_mem...

> Are you using temp tables? Those end up in pgsql_tmp as well.

Uh, no, they don't.

It might be useful to turn on trace_sort to see if the small files
are coming from sorts. If they're from hashes I'm afraid there's
no handy instrumentation ...

regards, tom lane