Re: small temp files

On Monday, July 22, 2024, Scott Ribe <scott_ribe(at)elevated-dev(dot)com> wrote:

> > On Jul 22, 2024, at 7:00 AM, Holger Jakobs <holger(at)jakobs(dot)com> wrote:
> >
> > Typically, queries which need a lot of memory (RAM) create temp files if
> work_mem isn't sufficient for some sorting or hash algorithms.
> >
> > Increasing work_mem will help, but small temp files don't create any
> trouble.
> >
> > You can set work_mem within each session, don't set it high globally.
>
> I understand those things--my question is why, with work_mem set to 128MB,
> I would see tiny temp files (7452 is common, as is 102, and I've seen as
> small as 51).
>
>
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.

David J.