From: Tom Lane <tgl@sss.pgh.pa.us>
To: depesz@depesz.com
cc: PostgreSQL General <pgsql-general@lists.postgresql.org>
Subject: Re: Why is this query touching 4gb of buffers?
In-reply-to: <aPtgp4xidEB_QA_H@depesz.com>
References: <aPtcnI2BZkeHjRvF@depesz.com> <aPtgp4xidEB_QA_H@depesz.com>
Comments: In-reply-to hubert depesz lubaczewski <depesz@depesz.com>
	message dated "Fri, 24 Oct 2025 13:19:03 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1754332.1761310446.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 24 Oct 2025 08:54:06 -0400
Message-ID: <1754333.1761310446@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/1754333.1761310446%40sss.pgh.pa.us>
Precedence: bulk

hubert depesz lubaczewski <depesz@depesz.com> writes:
> First run of the query generated:
> ...
>    ->  Index Scan using index_some_table_pending on some_table  (cost=3D=
0.42..178322.57 rows=3D611988 width=3D16) (actual time=3D27962.567..27962.=
567 rows=3D0 loops=3D1)
>          Index Cond: (send_at <=3D '2025-10-23 12:35:48'::timestamp with=
out time zone)
>          Buffers: shared hit=3D4624 read=3D117838 dirtied=3D486

> 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=3D=
0.42..178328.27 rows=3D612009 width=3D16) (actual time=3D0.438..0.438 rows=
=3D0 loops=3D1)
>          Index Cond: (send_at <=3D '2025-10-23 12:35:48'::timestamp with=
out time zone)
>          Buffers: shared hit=3D424

> Time is irrelevant, the point is that we are going down from ~120k buffe=
rs
> "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.

			regards, tom lane