Re: index prefetching

On Thu, Aug 7, 2025 at 1:25 AM Thomas Munro <thomas(dot)munro(at)gmail(dot)com> wrote:
> * you could make a stream that pulls leaf pages from higher level
> internal pages on demand (if you want to avoid the flow control
> problems that come from trying to choose a batch size up front before
> you know you'll even need it by using a pull approach), or just notice
> that it looks sequential and install a block range producer, and if
> that doesn't match the next page pointers by the time you get there
> then you destroy it and switch strategies, or something

I was hoping that we wouldn't ever have to teach index scans to
prefetch leaf pages like this. It is pretty complicated, primarily
because it completely breaks with the idea of the scan having to
access pages in some fixed order. (Whereas if we're just prefetching
heap pages, then there is a fixed order, which makes maintaining
prefetch distance relatively straightforward and index AM neutral.)

It's also awkward to make such a scheme work, especially when there's
any uncertainty about how many leaf pages will ultimately be read/how
much work to do speculatively. There might not be that many relevant
leaf pages (level 0 pages) whose block numbers are conveniently
available as prefetchable downlinks/block numbers to the right of the
downlink we use to descend to the first leaf page to be read (our
initial downlink might be positioned towards the end of the relevant
internal page at level 1). I guess we could re-read the internal page
only when prefetching later leaf pages starts to look like a good
idea, but that's another complicated code path to maintain.

--
Peter Geoghegan