public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tomas Vondra <[email protected]>
To: Peter Geoghegan <[email protected]>
Cc: Andres Freund <[email protected]>
Cc: Alexandre Felipe <[email protected]>
Cc: Thomas Munro <[email protected]>
Cc: Nazir Bilal Yavuz <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Melanie Plageman <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Georgios <[email protected]>
Cc: Konstantin Knizhnik <[email protected]>
Cc: Dilip Kumar <[email protected]>
Subject: Re: index prefetching
Date: Tue, 17 Feb 2026 21:55:08 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAH2-Wzk=01XfUw028eZcuSxKMui8DdtvtDfMWic7WjbyeSBUAg@mail.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>
<[email protected]>
<CAE8JnxNvssQBKJPmVhkeQBk_mEi3deR_ij10+7MAN+6y9WFDrQ@mail.gmail.com>
<[email protected]>
<7herwtpae3ptqdng3s7tcft4ljkc23fyocp3mbrvc7xyk7s2lk@uq3qbm4blizo>
<[email protected]>
<CAH2-Wzk=01XfUw028eZcuSxKMui8DdtvtDfMWic7WjbyeSBUAg@mail.gmail.com>
On 2/17/26 19:17, Peter Geoghegan wrote:
> On Mon, Feb 16, 2026 at 10:44 AM Tomas Vondra <[email protected]> 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
view thread (87+ 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], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: index prefetching
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