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.
