Message-ID: <76579da6-ca00-4486-8d9f-4032e510edbe@vondra.me>
Date: Tue, 17 Feb 2026 21:55:08 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: index prefetching
To: Peter Geoghegan <pg@bowt.ie>
Cc: Andres Freund <andres@anarazel.de>,
 Alexandre Felipe <o.alexandre.felipe@gmail.com>,
 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>
References: <CAH2-Wzm7-QuDOs6TcqfhhDsGEZCuHtn=D-SriOTnTZ_fiXNBvA@mail.gmail.com>
 <CAH2-WzmH7pVQ0-mYAxb82aWbz29_BiBPq2wV5p7+1o2sRFqDRQ@mail.gmail.com>
 <CAH2-Wz=6a7fGz2rALDX+xiFDuEaGQWpZ49xEaBUDKiPH8gcL+Q@mail.gmail.com>
 <CAH2-WzkehuhxyuA8quc7rRN3EtNXpiKsjPfO8mhb+0Dr2K0Dtg@mail.gmail.com>
 <CAH2-WzmymSyOt5Y2RGbm6cJXg18J_ttfqjdcpodHe6Gp23ConQ@mail.gmail.com>
 <CAH2-Wznv9_KGqHQ1vCW2pkiA6QskBGcx5NC_-UXnD6GEQasvAQ@mail.gmail.com>
 <CAE8JnxN_EwnTLLMWGhvgwaomYZ0ysm7NeogA-BqBd=Rs3S7Oqw@mail.gmail.com>
 <9411f220-007d-4f1e-9c8f-ca8eb09e6788@vondra.me>
 <CAE8JnxNvssQBKJPmVhkeQBk_mEi3deR_ij10+7MAN+6y9WFDrQ@mail.gmail.com>
 <984dcf9e-ada0-4dff-ae58-1f97bc904ccb@vondra.me>
 <7herwtpae3ptqdng3s7tcft4ljkc23fyocp3mbrvc7xyk7s2lk@uq3qbm4blizo>
 <433c7359-3c5d-4fe3-a488-93a17f44d0bf@vondra.me>
 <CAH2-Wzk=01XfUw028eZcuSxKMui8DdtvtDfMWic7WjbyeSBUAg@mail.gmail.com>
Content-Language: en-US
From: Tomas Vondra <tomas@vondra.me>
In-Reply-To: <CAH2-Wzk=01XfUw028eZcuSxKMui8DdtvtDfMWic7WjbyeSBUAg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/76579da6-ca00-4486-8d9f-4032e510edbe%40vondra.me>
Precedence: bulk

On 2/17/26 19:17, Peter Geoghegan wrote:
> On Mon, Feb 16, 2026 at 10:44 AM Tomas Vondra <tomas@vondra.me> wrote:
>> I've described this exact behavior a couple months ago in this very
>> thread. The queue of batches has limited size, the prefetch needs to be
>> paused - at that point read_stream_pause did not exist, so it was done
>> by terminating the stream and then read_stream_reset to "resume" it.
>>
>> That has the unfortunate effect that it resets distance to 1, and so it
>> easily led exactly to the issue you describe. But without the pausing it
>> would work perfectly fine, in many cases.
> 
> Just to be clear: it *used* to work that way, back when we weren't
> using Thomas Munro's built-in pausing mechanism. Because we'd actually
> reset the read stream back then, as a workaround for the lack of any
> built-in pausing mechanism. But we don't do that anymore; recent
> versions only reset in cases where it's strictly necessary, such as on
> a rescan, or when the scan direction changes.
> 
> Recent versions of the patch are just about impossible to make pause.
> Because (for better or worse) we'll yield before ever running out of
> batch slots/before ever pausing. Andres has shown that this can be
> detrimental to our ability to maintain an adequate prefetch distance.
> But that's likely only because pausing hurts when it prevents us from
> aggressively ramping up prefetch distance at the start of the scan.
> 
> Even without yielding, pausing is rare and likely doesn't hurt us
> much. After all, 64 batches is usually plenty.
> 
>> With the resetting, this effect is pretty brutal.
> 
> What resetting? We don't do that anymore? And even back when the patch
> did things that way, we made sure to save the old prefetch distance
> (which was a kludge upon a kludge)?
> 
> Basically I'm confused about why you're now talking about how the
> patch worked months ago, which was always a temporary workaround to
> deal with the lack of a built-in pausing mechanism. All of our current
> problems are due to yielding (not pausing), and/or issues on the read
> stream side.
> 

Yes, the patch no longer resets the stream like this.

I was responding to Andres, explaining that we saw basically the same
issue, with the twist that the impact strongly depends on the distance
value at the moment we hit the particular hit/miss pattern.

We no longer have that issue (with the "read_stream_reset" forcing the
distance collapse). We still have the issue with decaying too early in
some cases, depending on the exact pattern of hits/misses.


Sorry if that wasn't clear.

-- 
Tomas Vondra