Date: Fri, 24 Oct 2025 14:56:12 +0200
From: hubert depesz lubaczewski <depesz@depesz.com>
Sender: depesz@depesz.com
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL General <pgsql-general@lists.postgresql.org>
Subject: Re: Why is this query touching 4gb of buffers?
Message-ID: <aPt3bONUNkgloyrf@depesz.com>
Reply-To: depesz@depesz.com
References: <aPtcnI2BZkeHjRvF@depesz.com>
 <aPtgp4xidEB_QA_H@depesz.com>
 <1754333.1761310446@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: <1754333.1761310446@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/aPt3bONUNkgloyrf%40depesz.com>
Precedence: bulk

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