Re: index prefetching

On Thu, Aug 28, 2025 at 7:01 PM Tomas Vondra <tomas@vondra.me> wrote:
> I'm not sure how to determine what concurrency it "wants". All I know is
> that for "warm" runs [1], the basic index prefetch patch uses distance
> ~2.0 on average, and is ~2x slower than master. And with the patches the
> distance is ~270, and it's 30% slower than master. (IIRC there's about
> 30% misses, so 270 is fairly high. Can't check now, the machine is
> running other tests.)

Is it possible that the increased distance only accidentally
ameliorates the IPC issues that you're seeing with method=worker? I
mentioned already that it makes things a bit slower with io_uring, for
the same test case. I mean, if you use io_uring then things work out
strictly worse with that extra patch...so something doesn't seem
right.

I notice that the test case in question manages to merge plenty of
reads together with other pending reads, within read_stream_look_ahead
(I added something to our working branch that'll show that information
in EXPLAIN ANALYZE). My wild guess is that an increased distance could
interact with that, somewhat masking the IPC problems with
method=worker.

Could that explain it? It seems possible that the distance is already
roughly optimal, without your patch (or Thomas' similar read stream
patch). It may be that we just aren't converging on "no prefetch"
behavior when we ought to, given such a low distance. If this theory
of mine was correct, it would reconcile the big differences we see
between "worker vs io_uring" with your patch + test case.

--
Peter Geoghegan