From: MichaelDBA <MichaelDBA@sqlexec.com>
To: Julien Rouhaud <rjuju123@gmail.com>
Cc: Radoulov, Dimitre <cichomitiko@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Temp files for simple queries
Date: Tue, 22 Jun 2021 08:00:37 -0400
Message-ID: <6fab4381-4a47-0b99-43a9-1ff1c97efd39@sqlexec.com> (raw)
In-Reply-To: <20210622115506.o2u4u5krplxxc54k@nol>
References: <45cc44dd-b830-5df5-1f09-171e9874bbc0@gmail.com>
<16aa733d-bcaf-0aa6-f3f0-5206de695e92@gmail.com>
<20210622115506.o2u4u5krplxxc54k@nol>
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.
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: MichaelDBA@sqlexec.com, rjuju123@gmail.com, cichomitiko@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Temp files for simple queries
In-Reply-To: <6fab4381-4a47-0b99-43a9-1ff1c97efd39@sqlexec.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox