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 1lvevJ-0000I4-Ij for pgsql-admin@arkaria.postgresql.org; Tue, 22 Jun 2021 11:50:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lvevI-0001wG-74 for pgsql-admin@arkaria.postgresql.org; Tue, 22 Jun 2021 11:50:32 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lvevH-0001w8-Ou for pgsql-admin@lists.postgresql.org; Tue, 22 Jun 2021 11:50:31 +0000 Received: from mail-wr1-x42d.google.com ([2a00:1450:4864:20::42d]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1lvevE-0003TR-R6 for pgsql-admin@lists.postgresql.org; Tue, 22 Jun 2021 11:50:30 +0000 Received: by mail-wr1-x42d.google.com with SMTP id y7so23193397wrh.7 for ; Tue, 22 Jun 2021 04:50:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:from:to:references:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=dOX+ubz7wyuDB0qICq8REN7LYoGvtnRWzZEfYTaI7Mg=; b=XqQmnpwPw2vIVXk3B97hbMEvA4kYKLKKmJ3H2hBNqril8re/hDtDY1XWNRbJnVYgaf qVG2exaRa2H/2vnNXP786qR3tVpYZc6CPj/23GhrV0FGQeu7zdeZRbaygUXTEON3rsKB Y6ub60sHy2oegX1ln3Z///H6LNSxtu6Br9+8hCdD+JhtCURYC0xhAlsOO8ukMDAbLoD6 e3WYhQOdGqUz9W0n+siqZAGEM3B+1SdoEYcMWFwcyuvVp7IynttZxwAW9JcW5XoK7Kxs +DF++Zf1NGWxksPrHDP3cCqsd+z06+qdK4vm/7rgHMa20CTQzieSDWvpHf9ifrca/Y4K Iegg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:from:to:references:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=dOX+ubz7wyuDB0qICq8REN7LYoGvtnRWzZEfYTaI7Mg=; b=C/VA/522zttxwx8rqMB4ekMv26xkq5QdbwTYfuMzaZAA7gosxA0J6K0CXFYMwn/G8S +tXjunxAF2SJ0L0EySonfD0tIAr3gAc86+HI8WIdy2ZMF7CrUQW/JJYvSC/ZwGAf0LOg h9QMTdSy13lyqVvPsOr6AJ55E0m8yPyGCLRBMsLzFG9ePNR59+rEj+bvED1KBsNvDobr ODTLfod18BGSntsg3JfEgWjkeNXuL+bWjhWT63yxflKmYOwwVvoXcfj6AQK4tKTkRUY/ eMcVfj6cUEAS3zAwwIECjOBvrCv5nVj6GDUI3mHW3pGD5HYTp/XGNaHf4YwB+AqVYGOW Qs3Q== X-Gm-Message-State: AOAM533YEX427njl2oxWvuH9UbvarYLqUj8Jf0hrVe/One4PK/ChQahN d3jCo0tn9ZVBR+YDy8STqGSFe6sR59rP/Q== X-Google-Smtp-Source: ABdhPJw/Fnl3Gpjp6tknoCU6LpcJiOcntKDwSI/AxnNBAoKE94v9Mzsw3m2c6eGscOPt52BeL6wAbw== X-Received: by 2002:a05:6000:12c7:: with SMTP id l7mr4328692wrx.161.1624362626204; Tue, 22 Jun 2021 04:50:26 -0700 (PDT) Received: from [192.168.1.8] (net-93-66-70-148.cust.vodafonedsl.it. [93.66.70.148]) by smtp.gmail.com with ESMTPSA id l16sm2148007wmq.28.2021.06.22.04.50.23 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 22 Jun 2021 04:50:25 -0700 (PDT) Subject: Re: Temp files for simple queries From: "Radoulov, Dimitre" To: pgsql-admin@lists.postgresql.org References: <45cc44dd-b830-5df5-1f09-171e9874bbc0@gmail.com> Message-ID: <16aa733d-bcaf-0aa6-f3f0-5206de695e92@gmail.com> Date: Tue, 22 Jun 2021 13:51:55 +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: <45cc44dd-b830-5df5-1f09-171e9874bbc0@gmail.com> Content-Type: multipart/alternative; boundary="------------E7F4471745DBC7FB8D8E9746" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------E7F4471745DBC7FB8D8E9746 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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=,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=,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 > > > --------------E7F4471745DBC7FB8D8E9746 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

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



--------------E7F4471745DBC7FB8D8E9746--