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 1lvej2-0008MS-8P for pgsql-admin@arkaria.postgresql.org; Tue, 22 Jun 2021 11:37:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lveiz-00034C-R6 for pgsql-admin@arkaria.postgresql.org; Tue, 22 Jun 2021 11:37:49 +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 1lveiz-000341-Fm for pgsql-admin@lists.postgresql.org; Tue, 22 Jun 2021 11:37:49 +0000 Received: from mail-wr1-x436.google.com ([2a00:1450:4864:20::436]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1lveit-0007BH-30 for pgsql-admin@lists.postgresql.org; Tue, 22 Jun 2021 11:37:49 +0000 Received: by mail-wr1-x436.google.com with SMTP id f2so23215630wri.11 for ; Tue, 22 Jun 2021 04:37:42 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=to:from:subject:message-id:date:user-agent:mime-version :content-language; bh=GjpWHM8ehdoJ6iP3TbWpWC2dk30V2zn4s4WctX+lFzg=; b=JciqVPB+KAJAh1Zzc1EFV8pRaa3ZHLTx/bNk/wT7RbpyyLZaghsdD+EtN748eexYVI hk4IS+I2qn5BPUZpA2s4yp6Uau+FBV6ZejnZdS6CTdQI2f0z8L6RkFHixL5tRlRapXqr w+6JfPWhGjEqCVek9K+rLaKr25jo1AHclkU+R0TEQ4kZhymyF6w1efKU4OTtSfYHi4Fu AoHKJ/ZDct+iMgNoQF6KyAXpsmCOUaqYVYPkxiyB5mNEPEZ7ttKU4H59JMTsNQhnm/BN /4moCYLbUdE2Oqo/eqOq6eMf8VoiwL5olISYrT8pWXWzi1yg1h0KmGl0Lk/0UX+G1U7W R9ag== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:to:from:subject:message-id:date:user-agent :mime-version:content-language; bh=GjpWHM8ehdoJ6iP3TbWpWC2dk30V2zn4s4WctX+lFzg=; b=kB1HTaNaomiPW5f079e/2lYPZz4I6rMeVuwraN2nENjmBGI7LIDqCQ0g79fYDbBbav FNsrZ/6TZhaHasHg4lr8eDTlnAp6bNicNGkRivdUMoYqQbK83TsZzFKHjwYmDJbpfdv7 t8pPTz79iB3ttYO+OYM5l+E5tiIOXIKTEuMCoLKBdTWAdHoKKJ5G3t3zfPy1ZctmJ9n1 V+DnyJ/Oeqx8VFSInYnJG3wA2mGEKJLYT8TomBx/RGvPoPcw5OQFPvftynWY77kHY+4J oIc+hVg6h6F7rL/aIQa+uuvut/ieUWKv4H4fSswB4rvOpaJMCTGdYjORPyx9RhY6PWUd nwdQ== X-Gm-Message-State: AOAM532xunppOiCN0kiaIpchYk7AVlvPcSp/ouSt7PNAjAXJRKAUZvAc gLVq47sd7B66HNkEa3kFXSZU/mnFc+fQQw== X-Google-Smtp-Source: ABdhPJxQR/hJ87EShRRjtsKo0u3hVhEvdTDChJfi0TBnYBLzy65qfl0+d9GlWaVdBlCMfC2MITS+Xg== X-Received: by 2002:adf:e443:: with SMTP id t3mr4181316wrm.52.1624361861470; Tue, 22 Jun 2021 04:37:41 -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 k13sm7372365wrp.73.2021.06.22.04.37.39 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 22 Jun 2021 04:37:40 -0700 (PDT) To: pgsql-admin@lists.postgresql.org From: "Radoulov, Dimitre" Subject: Temp files for simple queries Message-ID: <45cc44dd-b830-5df5-1f09-171e9874bbc0@gmail.com> Date: Tue, 22 Jun 2021 13:39:11 +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 Content-Type: multipart/alternative; boundary="------------05845BDF9965DCF2803E53F6" 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. --------------05845BDF9965DCF2803E53F6 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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 --------------05845BDF9965DCF2803E53F6 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

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



--------------05845BDF9965DCF2803E53F6--