MIME-Version: 1.0
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>
In-Reply-To: <433c7359-3c5d-4fe3-a488-93a17f44d0bf@vondra.me>
From: Peter Geoghegan <pg@bowt.ie>
Date: Tue, 17 Feb 2026 13:17:15 -0500
Message-ID: <CAH2-Wzk=01XfUw028eZcuSxKMui8DdtvtDfMWic7WjbyeSBUAg@mail.gmail.com>
Subject: Re: index prefetching
To: Tomas Vondra <tomas@vondra.me>
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>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAH2-Wzk%3D01XfUw028eZcuSxKMui8DdtvtDfMWic7WjbyeSBUAg%40mail.gmail.com>
Precedence: bulk

On Mon, Feb 16, 2026 at 10:44=E2=80=AFAM Tomas Vondra <tomas@vondra.me> wro=
te:
> 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.

--=20
Peter Geoghegan