I'm wondering if bloat could be causing a problem. Perhaps more blocks
are written to disk including a lot of blocks with little or no data due
to bloat.
Julien Rouhaud wrote on 6/22/2021 7:55 AM:
On Tue, Jun 22, 2021 at 01:51:55PM +0200, Radoulov, Dimitre wrote:
Just to clarify: the size of the temp file is 7MB and with work_mem of 4MB
it clearly doesn't fit.
But why an "order by" of a 440kB of data would require 7MB.
One possible reason is that that the number of records in that table varies
significantly during the day,
That would be the logical explanation. You could configure auto_explain to
make sure of that (https://www.postgresql.org/docs/current/auto-explain.html).
but - if that's the case - I would expect that
the table would be much bigger than 440kB (size taken using \dt+).
Not necessarily. autovacuum can truncate the file if all the trailing blocks
are empty (and if it can acquire an exclusive lock fast enough), so if some
client inserts a lot of rows, process them and remove them all, and later on
slowly start to insert new rows you will get that behavior.
