Re: index prefetching - Mailing list pgsql-hackers

On Mon, Aug 25, 2025 at 2:33 PM Tomas Vondra <tomas@vondra.me> wrote:
> Right. I might have expressed it more clearly, but this is what I meant
> when I said priorbatch is not causing this.

Cool.

> As for priorbatch, I'd still like to know where does the overhead come
> from. I mean, what's the expensive part of creating a read stream? Maybe
> that can be fixed, instead of delaying the creation, etc. Maybe the
> delay could happen within read_stream?

Creating a read stream is probably really cheap. It's nevertheless
expensive enough to make pgbench select about 3.5% slower. I don't
think that there's really an "expensive part" for us to directly
target here.

Separately, it's probably also true that using a read stream to
prefetch 2 or 3 pages ahead when on the first leaf page read isn't
going to pay for itself. There just isn't enough time to spend on
useful foreground work such that we can hide the latency of an I/O
wait, I imagine. But there'll still be added costs to pay from using a
read stream.

Anyway, whether or not this happens in the read stream itself (versus
keeping the current approach of simply deferring its creation) doesn't
seem all that important to me. If we do it that way then we still have
the problem of (eventually) figuring out when and how to tell the read
stream that it's time to really start up now. That'll be the hard
part, most likely -- and it doesn't have much to do with the general
design of the read stream (unlike the problem with your query).

> I'm not saying we should do whatever to meet effective_io_concurrency.
> It just seems a bit strange to ignore it like this, because right now it
> has absolutely no impact on the read stream. If the query gets into the
> "collapsed distance", it'll happen with any effective_io_concurrency.

That makes sense.

> That's weird. Did you see an increase of the prefetch distance? What
> does the EXPLAIN ANALYZE say about that?

Yes, I did. In general I find that your patch from today is very good
at keeping prefetch distance at approximately effective_io_concurrency
-- perhaps even a bit too good. Overall, the details that I now see
seem to match with my (possibly faulty) expectations about what'll
work best: the distance certainly doesn't get stuck at ~2 anymore (it
gets close to effective_io_concurrency for most possible
effective_io_concurrency settings, I find). The "only" problem is that
the new patch doesn't actually fix the regression itself. In fact, it
seems to make it worse.

With enable_indexscan_prefetch = off, the query takes 2794.551 ms on
my system. With enable_indexscan_prefetch = on, and with your patch
from today also applied, it takes 3488.997 ms. This is the case in
spite of the fact that your patch does successfully lower "shared
read=" time by a small amount (in addition to making the distance look
much more sane, at least to me).

For context, without your patch from today (but with the base index
prefetching patch still applied), the same query takes 3162.195 ms. In
spite of "shared read=" time being higher than any other case, and in
spite of the fact that distance gets stuck at ~2/just looks wrong.
(Like I said, the patch seems to actually make the problem worse on my
system.)

--
Peter Geoghegan