From: Radoulov, Dimitre <cichomitiko@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Temp files for simple queries
Date: Tue, 22 Jun 2021 13:51:55 +0200
Message-ID: <16aa733d-bcaf-0aa6-f3f0-5206de695e92@gmail.com> (raw)
In-Reply-To: <45cc44dd-b830-5df5-1f09-171e9874bbc0@gmail.com>
References: <45cc44dd-b830-5df5-1f09-171e9874bbc0@gmail.com>
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, but - if that's the case - I would
expect that the table would be much bigger than 440kB (size taken using
\dt+).
Regards
Dimitre
On 22/06/2021 13.39, Radoulov, Dimitre wrote:
>
> Hello all,
>
> We have a small Google Cloud SQL PostgreSQL 11 instance.
> The instance is configured with 8G of memory and 4 vCPUs, work_mem is
> 4MB.
>
> I was wondering why the instance uses disk temp files for such a small
> amount of data (see size in the log below)?
>
> I suppose that it's not only the work_mem limit that could trigger
> disk temp file creation or the reported SQL statement is not the only
> one that contributes to it:
>
> I 2021-06-22T09:12:59.164913Z 2021-06-22 09:12:59.163 UTC [1957798]:
> [2-1] db=<db>,user=<user> STATEMENT: SELECT c1, c2 FROM schema1.t1
> WHERE c1 >= $1 ORDER BY c1
> I 2021-06-22T09:12:59.164379Z 2021-06-22 09:12:59.163 UTC [1957798]:
> [1-1] db=<db>,user=<user> LOG: temporary file: path
> "base/pgsql_tmp/pgsql_tmp1957798.0", size 7380992
>
> The t1 table (table and column names have been masked for privacy)
> occupies only 440 kB and has 160 records.
>
>
> Best regards
> Dimitre
>
>
>
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: cichomitiko@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Temp files for simple queries
In-Reply-To: <16aa733d-bcaf-0aa6-f3f0-5206de695e92@gmail.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