Re: index prefetching

On Tue, Aug 19, 2025 at 2:22 PM Peter Geoghegan <pg@bowt.ie> wrote:
> That definitely seems like a problem. I think that you're saying that
> this problem happens because we have extra buffer hits earlier on,
> which is enough to completely change the ramp-up behavior. This seems
> to be all it takes to dramatically decrease the effectiveness of
> prefetching. Does that summary sound correct?

Update: Tomas and I discussed this over IM.

We ultimately concluded that it made the most sense to treat this
issue as a regression against set enable_indexscan_prefetch =
off/master. It was probably made a bit worse by the recent addition of
delaying creating a read stream (to avoid regressing pgbench SELECT)
with io_method=worker, though for me (with io_method=io_uring) it
makes things faster instead.

None of this is business with io_method seems important, since either
way there's a clear regression against set enable_indexscan_prefetch =
off/master. And we don't want those. So ultimately we need to
understand why mo prefetching wins by a not-insignificant margin with
this query.

Also, I just noticed that with a DESC/backwards scan version of Tomas'
query, things are vastly slower. But even then, fully synchronous
buffered I/O is still slightly faster.

--
Peter Geoghegan