Re: index prefetching

On Tue, Aug 5, 2025 at 7:31 PM Thomas Munro <thomas.munro@gmail.com> wrote:
> There must be a similar opportunity for parallel index scans.  It has
> that "seize the scan" concept where parallel workers do one-at-a-time
> locked linked list leapfrog.

True. More generally, flexibility to reorder work would be useful there.

The structure of parallel B-tree scans is one where each worker
performs its own "independent" index scan. The workers each only
return tuples from those leaf pages that they themselves manage to
read. That isn't particularly efficient, since we'll usually have to
merge the "independent" index scan tuples together once again using a
GatherMerge.

In principle, we could avoid a GatherMerge by keeping track of the
logical order of leaf pages at some higher level, and outputting
tuples in that same order -- which isn't a million miles from what the
batch interface that Tomas wrote already does. Imagine an enhanced
version of that design where the current read_stream callback wholly
farms out the work of reading leaf pages to parallel workers. Once we
decouple the index page reading from the heap access, we might be able
to invent the idea of "task specialization", where some workers more
or less exclusively read leaf pages, and other workers more or less
exclusively perform related heap accesses.

> Basically, the stuff that we can't fix with "precise" I/O
> streaming as I like to call it, where it might still be interesting to
> think about opportunities to do fuzzier speculative lookahead.  I'll
> start a new thread.

That sounds interesting. I worry that we won't ever be able to get
away without some fallback that behaves roughly like OS readahead.

--
Peter Geoghegan