Re: small temp files

Scott Ribe <scott_ribe(at)elevated-dev(dot)com> writes:
>> You expect the smallest temporary file to be 128MB? I.e., if the memory used exceeds work_mem all of it gets put into the temp file at that point? Versus only the amount of data that exceeds work_mem getting pushed out to the temporary file. The overflow only design seems much more reasonable - why write to disk that which fits, and already exists, in memory.

> Well, I don't know of an algorithm which can effectively sort 128MB + 7KB of data using 128MB of RAM and a 7KB file. Same for many of the other operations which use work_mem, so yes, I expected spill over to start with 128MB file and grow it as needed. If I'm wrong and there are operations which can effectively use temp files as adjunct, then that would be the answer to my question. Does anybody know for sure that this is the case?

You would get more specific answers if you provided an example of the
queries that cause this, with EXPLAIN ANALYZE output. But I think a
likely bet is that it's doing a hash join that overruns work_mem.
What will happen is that the join gets divided into batches based on
hash codes, and each batch gets dumped into its own temp files (one
per batch for each side of the join). It would not be too surprising
if some of the batches are small, thanks to the vagaries of hash
values. Certainly they could be less than work_mem, since the only
thing we can say for sure is that the sum of the temp file sizes for
the inner side of the join should exceed work_mem.

regards, tom lane