public inbox for [email protected]  

help / color / mirror / Atom feed

From: hubert depesz lubaczewski <[email protected]>
To: Tom Lane <[email protected]>
Cc: PostgreSQL General <[email protected]>
Subject: Re: Why is this query touching 4gb of buffers?
Date: Fri, 24 Oct 2025 15:18:44 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>

On Fri, Oct 24, 2025 at 09:01:11AM -0400, Tom Lane wrote:
> hubert depesz lubaczewski <[email protected]> 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 ...

OK. So tested this idea on yet another "setup".

We have single primary, and two streaming replicas. Before test I ran:
select now() - min(xact_start), now() - pg_last_xact_replay_timestamp() from pg_stat_activity

on both replicas, and got:

    ?column?     │    ?column?
═════════════════╪═════════════════
 00:00:00.003007 │ 00:00:00.003673
(1 row)

and

 ?column? │     ?column?
══════════╪══════════════════
 00:00:00 │ -00:00:00.006129
(1 row)

Then I ran the problematic query on replica 1 (the first one), and got
numbers:

Buffers: shared hit=21107
Execution Time: 18.621 ms

Subsequent runs on the replica showed the same buffers usage, and
similar time.

Then I ran this query twice on primary, and noticed improvement:
Buffers: shared hit=569 read=20927 dirtied=498
Execution Time: 2596.283 ms

and on 2nd run:

Buffers: shared hit=391
Execution Time: 2.015 ms

Awesome. Well, mostly.

Now, I waited ~ 1 minute, checked replication lag, and oldest transactions
replies, where all lags were < 1s, and longest transaction across
primary/secondary1/secondary2 was ~ 4s.

Then I re-ran the query on secondary 1 and got:
Buffers: shared hit=21107
Execution Time: 18.403 ms

Interestingly, on one "setup" running vacuum analyze of the table helped
execution on secondary, but on this one, it doesn't.

Best regards,

depesz

view thread (5+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Why is this query touching 4gb of buffers?
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox