Be sure to understand what the
work_mem setting actually does. The manual:
Sets the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary
disk files. If this value is specified without units, it is taken as
kilobytes. The default value is four megabytes (
4MB). Note that for a
complex query, several sort or hash operations might be running in
parallel; each operation will generally be allowed to use as much
memory as this value specifies before it starts to write data into
temporary files. Also, several running sessions could be doing such
operations concurrently. Therefore, the total memory used could be
many times the value of
work_mem; it is necessary to keep this fact
in mind when choosing the value. Sort operations are used for
DISTINCT, and merge joins. Hash tables are used in hash joins,
hash-based aggregation, result cache nodes and hash-based processing
of IN subqueries.
Hash-based operations are generally more sensitive to memory availability than equivalent sort-based operations. The memory
available for hash tables is computed by multiplying
hash_mem_multiplier. This makes it possible for hash-based
operations to use an amount of memory that exceeds the usual
work_mem base amount.
hash_mem_multiplier was added in Postgres 13.)
If Postgres needs more memory than
work_mem allows for any given operation, it starts spilling to disk. So what you observed is the excess:
I see that the current value is 86000kB, which is much bigger than the size of the temp files I see mentioned in the logs.
work_mem setting (at least) by the size of the observed temporary file and it should go away.
However, don't set
work_mem too high, either, as that might starve out cache and concurrent transactions. One can always set a higher
work_mem locally for exceptionally big operations. See: