Re: index prefetching

Hi,

On 2025-12-18 15:40:59 +0100, Tomas Vondra wrote:
> The technical reason is that batch_getnext() does this:
> 
>   /* Delay initializing stream until reading from scan's second batch */
>   if (priorbatch && !scan->xs_heapfetch->rs && !batchqueue->disabled &&
>       enable_indexscan_prefetch)
>       scan->xs_heapfetch->rs =
>           read_stream_begin_relation(READ_STREAM_DEFAULT, NULL,
>                                      ....);
> 
> which means we only create the read_stream (which is what enables the
> prefetching) only when creating the second batch. And with LIMIT 100 we
> likely read just a single leaf page (=batch) most of the time, which
> means no read_stream and thus no prefetching.

Why is the logic tied to the number of batches, rather the number of items in
batches? It's not hard to come up with scenarios where having to wait for ~100
random pages will be the majority of the queries IO wait... It makes sense to
not initialize readahead if we just fetch an entry or two, but after that?

Greetings,

Andres Freund