Subject: Re: Temp files for simple queries
To: Julien Rouhaud <rjuju123@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
References: <45cc44dd-b830-5df5-1f09-171e9874bbc0@gmail.com>
 <16aa733d-bcaf-0aa6-f3f0-5206de695e92@gmail.com>
 <20210622115506.o2u4u5krplxxc54k@nol>
From: "Radoulov, Dimitre" <cichomitiko@gmail.com>
Message-ID: <2890331e-aa23-bbef-0150-f1e374d8313b@gmail.com>
Date: Tue, 22 Jun 2021 14:05:57 +0200
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:78.0) Gecko/20100101
 Thunderbird/78.11.0
MIME-Version: 1.0
In-Reply-To: <20210622115506.o2u4u5krplxxc54k@nol>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/2890331e-aa23-bbef-0150-f1e374d8313b%40gmail.com>
Precedence: bulk

Hello Julien,

On 22/06/2021 13.55, Julien Rouhaud wrote:
> 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.

I thought that only a vacuum full could do that, thanks for pointing it out.


Best regards
Dimitre