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 14:56:12 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>

On Fri, Oct 24, 2025 at 08:54:06AM -0400, Tom Lane wrote:
> hubert depesz lubaczewski <[email protected]> writes:
> > First run of the query generated:
> > ...
> >    ->  Index Scan using index_some_table_pending on some_table  (cost=0.42..178322.57 rows=611988 width=16) (actual time=27962.567..27962.567 rows=0 loops=1)
> >          Index Cond: (send_at <= '2025-10-23 12:35:48'::timestamp without time zone)
> >          Buffers: shared hit=4624 read=117838 dirtied=486
> 
> > Then, immediately I reran it, without reindex, without analyze, without anything. And I got:
> 
> >    ->  Index Scan using index_some_table_pending on some_table  (cost=0.42..178328.27 rows=612009 width=16) (actual time=0.438..0.438 rows=0 loops=1)
> >          Index Cond: (send_at <= '2025-10-23 12:35:48'::timestamp without time zone)
> >          Buffers: shared hit=424
> 
> > Time is irrelevant, the point is that we are going down from ~120k buffers
> > "touched" to 424 buffers. What is going on?
> 
> 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?

Subsequent runs on secondary still have to "touch" hundreds of thousands
of pages", even if I'll do the run on primary that would reset hint
bits.

So, on primary - reruning the query fixes the "how many pages we touch"
- but on secondary, the number generally doesn't change, at least
withint 15-20 minute window.

Best regards,

depesz

view thread (5+ messages)  latest in thread

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