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 1qCqKW-0001Du-2V for pgsql-admin@arkaria.postgresql.org; Fri, 23 Jun 2023 23:36:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qCqKU-00060T-HL for pgsql-admin@arkaria.postgresql.org; Fri, 23 Jun 2023 23:36:38 +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 1qCqKU-00060J-6r for pgsql-admin@lists.postgresql.org; Fri, 23 Jun 2023 23:36:38 +0000 Received: from mail-il1-x12c.google.com ([2607:f8b0:4864:20::12c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qCqKO-0005SU-4L for pgsql-admin@postgresql.org; Fri, 23 Jun 2023 23:36:37 +0000 Received: by mail-il1-x12c.google.com with SMTP id e9e14a558f8ab-341c72be673so4580415ab.1 for ; Fri, 23 Jun 2023 16:36:31 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1687563390; x=1690155390; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:cc:to:from:date:from:to:cc:subject:date:message-id:reply-to; bh=jCgKM4ftjJyQmPtGKXxJjm1gFDbxP23gtfQJ+uteQVo=; b=Lr6wwXOMmXn9WUpu7z7kaZ5BmYI5/HASaUiaYQZb/mJWO9t3UTObfbvSZ5ilIIF/bY 8/hmLsukIM4RKHK1brY/yfk6B1TXzkpWZFCSl0ZcHjWcn9R5FD7BnmnUGkBYa70/gJ6r zt3gg8J+Eo5MaAQf9qvXRqAP2D+z+pvTWM8V4O1IO+iLmci8cplvw+4KEp1Jr14VbEBs c8HS9+B+ln3SRSD8fyNUTrbE5a1ZOjjBa78/W26ke4/WT4NkCaYCf5e3bPBPeMZ6InG0 FTNI39K4fHkZP5tv6sTA9gaJBtbRRW6CiPj+rfCctkiHS0ezh3jcJKufYj4TlQjfslpS HAyg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1687563390; x=1690155390; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:cc:to:from:date:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=jCgKM4ftjJyQmPtGKXxJjm1gFDbxP23gtfQJ+uteQVo=; b=Ti0kC0Icuk1eUmmWdHEO16m+OSaO1UHd6c0MpYGdG9vBUh50g15AsBFD9Z8fq2dZB6 2RPQCQKXzdFD0o1Hx55BzneEx52hNz8tA5QvO56ek0HQFzM1W6w0/cmlT4vXBQH9jucC Ssc58ABGanj+XgSIjX94KsYVORCBZefldjZsupH2BkjQa66BXOMK6H/EQaUfWPwZUcwv sGeCT2IdhK9Kf4GbpcNAZKJAUnzBWMLfMalvONt7dbr/S63BrTxyD0LzpcdeBQmaKONa 9a9GmgH2+G2jXCYJMvGhCEmFQ8NERVIU/YUVfenZI+mUK0QEMYzboCFzQ/pqXE9qlSSV PqGA== X-Gm-Message-State: AC+VfDzVR7X/RhhHIqeEwu0dThCD1HaKo9IgvxOWQjH49EBBSAEOyMMG opGLuypShgM69ncZ7R92cIknNnD6ALA= X-Google-Smtp-Source: ACHHUZ7GLoSZ7QPoRw33gIr2eX88jJfr3KqY0kWYT+/BZ0eKcWHSxg1tFR3b5lbHjl7HaknjAtslsQ== X-Received: by 2002:a92:c802:0:b0:33b:4d0c:e947 with SMTP id v2-20020a92c802000000b0033b4d0ce947mr21617928iln.0.1687563390012; Fri, 23 Jun 2023 16:36:30 -0700 (PDT) Received: from jrouhaud (2001-b011-1005-da44-7496-e387-b5c9-29e5.dynamic-ip6.hinet.net. [2001:b011:1005:da44:7496:e387:b5c9:29e5]) by smtp.gmail.com with ESMTPSA id c17-20020a62e811000000b0064d47cd116esm60365pfi.161.2023.06.23.16.36.28 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Fri, 23 Jun 2023 16:36:29 -0700 (PDT) Date: Sat, 24 Jun 2023 07:36:26 +0800 From: Julien Rouhaud To: Wells Oliver Cc: pgsql-admin Subject: Re: Temp getting written to disk despite being smaller than temp_buffers? Message-ID: <20230623233626.3h2vcgbhlmpzcbh3@jrouhaud> References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi, On Fri, Jun 23, 2023 at 11:20:02AM -0700, Wells Oliver wrote: > Probably reading my logs wrong or misunderstanding something basic but my > temp_buffers is 512MB and I see things in the log like: > > 2023-06-23 18:02:44 UTC:172.31.21.22(40866):woliver@db:[25473]:LOG: > temporary file: path "base/pgsql_tmp/pgsql_tmp25473.135", size 255107072 > > Wondering why 255mb is being written to disk rather than using RAM? temp_buffers is for temporary tables, while those temporary files are used for node operations that consume more than work_mem.