Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vCHPz-00BZNK-FI for pgsql-general@arkaria.postgresql.org; Fri, 24 Oct 2025 13:01:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1vCHPy-00GTUC-5N for pgsql-general@arkaria.postgresql.org; Fri, 24 Oct 2025 13:01:17 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vCHPx-00GTU4-RP for pgsql-general@lists.postgresql.org; Fri, 24 Oct 2025 13:01:16 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vCHPu-003zl9-1r for pgsql-general@lists.postgresql.org; Fri, 24 Oct 2025 13:01:16 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 59OD1BRM1755688; Fri, 24 Oct 2025 09:01:11 -0400 From: Tom Lane To: depesz@depesz.com cc: PostgreSQL General Subject: Re: Why is this query touching 4gb of buffers? In-reply-to: References: <1754333.1761310446@sss.pgh.pa.us> Comments: In-reply-to hubert depesz lubaczewski message dated "Fri, 24 Oct 2025 14:56:12 +0200" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1755686.1761310871.1@sss.pgh.pa.us> Date: Fri, 24 Oct 2025 09:01:11 -0400 Message-ID: <1755687.1761310871@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk hubert depesz lubaczewski writes: > On Fri, Oct 24, 2025 at 08:54:06AM -0400, Tom Lane wrote: >> The first execution probably had to set hint bits on a whole lot >> of recently-deleted rows. > But why it doesn't happen/help on secondary? IIRC, secondaries aren't authorized to update hint bits for themselves, they have to wait for the primary to do it and then propagate the new data. There might also be some question of what the oldest open transaction is ... regards, tom lane Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vCHPz-00BZNK-FI for pgsql-general@arkaria.postgresql.org; Fri, 24 Oct 2025 13:01:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1vCHPy-00GTUC-5N for pgsql-general@arkaria.postgresql.org; Fri, 24 Oct 2025 13:01:17 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vCHPx-00GTU4-RP for pgsql-general@lists.postgresql.org; Fri, 24 Oct 2025 13:01:16 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vCHPu-003zl9-1r for pgsql-general@lists.postgresql.org; Fri, 24 Oct 2025 13:01:16 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 59OD1BRM1755688; Fri, 24 Oct 2025 09:01:11 -0400 From: Tom Lane To: depesz@depesz.com cc: PostgreSQL General Subject: Re: Why is this query touching 4gb of buffers? In-reply-to: References: <1754333.1761310446@sss.pgh.pa.us> Comments: In-reply-to hubert depesz lubaczewski message dated "Fri, 24 Oct 2025 14:56:12 +0200" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1755686.1761310871.1@sss.pgh.pa.us> Date: Fri, 24 Oct 2025 09:01:11 -0400 Message-ID: <1755687.1761310871@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk hubert depesz lubaczewski writes: > On Fri, Oct 24, 2025 at 08:54:06AM -0400, Tom Lane wrote: >> The first execution probably had to set hint bits on a whole lot >> of recently-deleted rows. > But why it doesn't happen/help on secondary? IIRC, secondaries aren't authorized to update hint bits for themselves, they have to wait for the primary to do it and then propagate the new data. There might also be some question of what the oldest open transaction is ... regards, tom lane