Re: Changing work_mem

rihad wrote:
> > Sorry, I just decreased work_mem back to 256MB, reloaded, and
> > instantly started seeing 82mb temp file creation, not 165mb as was
> > usual with work_mem=512MB.
> >
> > So it indeed was applied immediately.
> > Really weird figures )
>
> Increased work_mem to 768MB and start seeing temp file creation log
> entries 331MB in size.
>
> Bizzare ) It looks like the bigger it gets, the bigger temp files
> are
> created.
>
> Why not decrease it to 64mb then...

Temporary files are created whenever the data is estimated to not
fit into "work_mem". So it is unsurprising that you see bigger
temporary files being created if you increase "work_mem".

Big temporary files will also be created when "work_mem" is small,
but maybe they got lost in the noise of the smaller files.
You should have noticed that fewer files are created when you increase
"work_mem".

Another thing to notice is that the temporary files use another, more
compact format than the data in memory, so you need to increase
"work_mem" to more than X if you want to avoid temporary files
of size X.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com