Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lvewo-0000LU-1l for pgsql-admin@arkaria.postgresql.org; Tue, 22 Jun 2021 11:52:06 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lvewn-0005uR-2T for pgsql-admin@arkaria.postgresql.org; Tue, 22 Jun 2021 11:52:05 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lvewm-0005uD-P7 for pgsql-admin@lists.postgresql.org; Tue, 22 Jun 2021 11:52:04 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lvewl-0007fE-E3 for pgsql-admin@lists.postgresql.org; Tue, 22 Jun 2021 11:52:04 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id 599D33803B8; Tue, 22 Jun 2021 13:52:01 +0200 (CEST) Received: from rs.plausibolo.de ([IPv6:::1]) by localhost (h2367442.stratoserver.net [IPv6:::1]) (amavisd-new, port 10024) with ESMTP id c7oMkWXPVLHn; Tue, 22 Jun 2021 13:52:01 +0200 (CEST) Received: from tuxedo.local.de (ip-37-201-154-46.hsi13.unitymediagroup.de [37.201.154.46]) by rs.plausibolo.de (Postfix) with ESMTPSA id 0E9FD3802ED; Tue, 22 Jun 2021 13:52:01 +0200 (CEST) Subject: Re: Temp files for simple queries To: pgsql-admin@lists.postgresql.org References: <45cc44dd-b830-5df5-1f09-171e9874bbc0@gmail.com> From: Holger Jakobs Message-ID: <6ff1c796-9d44-933d-28e1-c02d0b3e86be@jakobs.com> Date: Tue, 22 Jun 2021 13:52:00 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.11.0 MIME-Version: 1.0 In-Reply-To: <45cc44dd-b830-5df5-1f09-171e9874bbc0@gmail.com> Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="6hrH6BW27uTjly6ifczlSUoS08ktYzXiP" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --6hrH6BW27uTjly6ifczlSUoS08ktYzXiP Content-Type: multipart/mixed; boundary="hnNoLxuC7eH1Oeal815qXeQtGZWOLCW1c"; protected-headers="v1" From: Holger Jakobs To: pgsql-admin@lists.postgresql.org Message-ID: <6ff1c796-9d44-933d-28e1-c02d0b3e86be@jakobs.com> Subject: Re: Temp files for simple queries References: <45cc44dd-b830-5df5-1f09-171e9874bbc0@gmail.com> In-Reply-To: <45cc44dd-b830-5df5-1f09-171e9874bbc0@gmail.com> --hnNoLxuC7eH1Oeal815qXeQtGZWOLCW1c Content-Type: multipart/alternative; boundary="------------FD8FEC9FEE70DE117A65BD97" Content-Language: de-DE This is a multi-part message in MIME format. --------------FD8FEC9FEE70DE117A65BD97 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: quoted-printable Default settings might be too low. Try to set work_mem higher. If=20 operations like sorting don't fit into work_mem, temporary files are used= =2E Am 22.06.21 um 13:39 schrieb Radoulov, Dimitre: > > 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=20 > 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=20 > disk temp file creation or the reported SQL statement is not the only=20 > one that contributes to it: > > I 2021-06-22T09:12:59.164913Z 2021-06-22 09:12:59.163 UTC [1957798]:=20 > [2-1] db=3D,user=3D STATEMENT: SELECT c1, c2 FROM schema1.t1 = > WHERE c1 >=3D $1 ORDER BY c1 > I 2021-06-22T09:12:59.164379Z 2021-06-22 09:12:59.163 UTC [1957798]:=20 > [1-1] db=3D,user=3D LOG: temporary file: path=20 > "base/pgsql_tmp/pgsql_tmp1957798.0", size 7380992 > > The t1 table (table and column names have been masked for privacy)=20 > occupies only=C2=A0 440 kB and has 160 records. > > > Best regards > Dimitre > > > --=20 Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012 --------------FD8FEC9FEE70DE117A65BD97 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable

Default settings might be too low. Try to set work_mem higher. If operations like sorting don't fit into work_mem, temporary files are used.

Am 22.06.21 um 13:39 schrieb Radoulov,= Dimitre:

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=3D<db>,user=3D<user> STATEMENT:= SELECT c1, c2 FROM schema1.t1 WHERE c1 >=3D $1 ORDER BY c1
I 2021-06-22T09:12:59.164379Z 2021-06-22 09:12:59.163 UTC [1957798]: [1-1] db=3D<db>,user=3D<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=C2=A0 440 kB and has 160 records.


Best regards
Dimitre



--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
--------------FD8FEC9FEE70DE117A65BD97-- --hnNoLxuC7eH1Oeal815qXeQtGZWOLCW1c-- --6hrH6BW27uTjly6ifczlSUoS08ktYzXiP Content-Type: application/pgp-signature; name="OpenPGP_signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="OpenPGP_signature" -----BEGIN PGP SIGNATURE----- wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCYNHO4AUDAAAAAAAKCRCKV5yTsxlhuvgU AKC1MbJy1QabH5Cu5tHhPol0jkBPVQCfZJB6B2XgwRKTtqNlLiWKuXgXu5M= =EM19 -----END PGP SIGNATURE----- --6hrH6BW27uTjly6ifczlSUoS08ktYzXiP--