Feedback-ID: id4a34324:Fastmail
Date: Wed, 13 Aug 2025 10:44:33 -0400
From: Andres Freund <andres@anarazel.de>
To: Tomas Vondra <tomas@vondra.me>
Cc: Peter Geoghegan <pg@bowt.ie>, Thomas Munro <thomas.munro@gmail.com>,
	Nazir Bilal Yavuz <byavuz81@gmail.com>, Robert Haas <robertmhaas@gmail.com>,
	Melanie Plageman <melanieplageman@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
	Georgios <gkokolatos@protonmail.com>,
 Konstantin Knizhnik <knizhnik@garret.ru>,
	Dilip Kumar <dilipbalaut@gmail.com>
Subject: Re: index prefetching
Message-ID: <c7a77pcyc5eynme376wvyojryijtlieyxsu3bvxp4eiy6au6uf@caniulyi4jr5>
References: <f516d66b-5bed-45af-8127-d09b684dad9e@vondra.me>
 <CAH2-WzmdiO4fHA1O06SYUjgMQZG7haysY7Tu5DS5z-CHsv5MLQ@mail.gmail.com>
 <d7bad36a-ac8d-4727-a52b-822621142d64@vondra.me>
 <CAH2-Wz=Y-PsC6_tZOPhHWvPx0geGnrh9VKjUZ-168ezUM_XM2Q@mail.gmail.com>
 <CA+hUKGKMaZLmNQHaa_DZMw9MJJKGegjrqnTY3KOZB-_nvFa3wQ@mail.gmail.com>
 <DC0RJDW1PEAX.NDGIXR6WPM94@bowt.ie>
 <51b5f71b-5f19-4453-91ff-2b9f2a840c58@vondra.me>
 <b739c179-25ca-4371-b528-cf80ea111d94@vondra.me>
 <CAH2-Wzko86NwiENCJGtakJ=fOhWpr-Yz-F+1oxgv2Ku1mvXwvA@mail.gmail.com>
 <6cb6109d-71d6-490c-8056-d8885081b008@vondra.me>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <6cb6109d-71d6-490c-8056-d8885081b008@vondra.me>
Archived-At: 
 <https://www.postgresql.org/message-id/c7a77pcyc5eynme376wvyojryijtlieyxsu3bvxp4eiy6au6uf%40caniulyi4jr5>
Precedence: bulk

Hi,

On 2025-08-13 14:15:37 +0200, Tomas Vondra wrote:
> In fact, I believe this is about io_method. I initially didn't see the
> difference you described, and then I realized I set io_method=sync to
> make it easier to track the block access. And if I change io_method to
> worker, I get different stats, that also change between runs.
>
> With "sync" I always get this (after a restart):
>
>    Buffers: shared hit=7435 read=52801
>
> while with "worker" I get this:
>
>    Buffers: shared hit=4879 read=52801
>    Buffers: shared hit=5151 read=52801
>    Buffers: shared hit=4978 read=52801
>
> So not only it changes run to tun, it also does not add up to 60236.

This is reproducible on master? If so, how?


> I vaguely recall I ran into this some time ago during AIO benchmarking,
> and IIRC it's due to how StartReadBuffersImpl() may behave differently
> depending on I/O started earlier. It only calls PinBufferForBlock() in
> some cases, and PinBufferForBlock() is what updates the hits.

Hm, I don't immediately see an issue there. The only case we don't call
PinBufferForBlock() is if we already have pinned the relevant buffer in a
prior call to StartReadBuffersImpl().


If this happens only with the prefetching patch applied, is is possible that
what happens here is that we occasionally re-request buffers that already in
the process of being read in? That would only happen with a read stream and
io_method != sync (since with sync we won't read ahead). If we have to start
reading in a buffer that's already undergoing IO we wait for the IO to
complete and count that access as a hit:

	/*
	 * Check if we can start IO on the first to-be-read buffer.
	 *
	 * If an I/O is already in progress in another backend, we want to wait
	 * for the outcome: either done, or something went wrong and we will
	 * retry.
	 */
	if (!ReadBuffersCanStartIO(buffers[nblocks_done], false))
	{
...
		/*
		 * Report and track this as a 'hit' for this backend, even though it
		 * must have started out as a miss in PinBufferForBlock(). The other
		 * backend will track this as a 'read'.
		 */
...
		if (persistence == RELPERSISTENCE_TEMP)
			pgBufferUsage.local_blks_hit += 1;
		else
			pgBufferUsage.shared_blks_hit += 1;
...


Greetings,

Andres Freund