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