Re: index prefetching

On 8/25/25 17:43, Thomas Munro wrote:
> On Tue, Aug 26, 2025 at 2:18 AM Tomas Vondra <tomas(at)vondra(dot)me> wrote:
>> Of course, this can happen even with other hit ratios, there's nothing
>> special about 50%.
>
> Right, that's what this patch was attacking directly, basically only
> giving up when misses are so sparse we can't do anything about it for
> an ordered stream:
>
> https://www.postgresql.org/message-id/CA%2BhUKGL2PhFyDoqrHefqasOnaXhSg48t1phs3VM8BAdrZqKZkw%40mail.gmail.com
>
> aio: Improve read_stream.c look-ahead heuristics C
>
> Previously we would reduce the look-ahead distance by one every time we
> got a cache hit, which sometimes performed poorly with mixed hit/miss
> patterns, especially if it was trapped at one.
>
> Instead, sustain the current distance until we've seen evidence that
> there is no window big enough to span the gap between rare IOs. In
> other words, we now use information from a much larger window to
> estimate the utility of looking far ahead.

Ah, I forgot about this patch.

There's been too many PoC / experimental patches with read_stream
improvements, I'm loosing track of them. I'm ready to do some
evaluation, but it's not clear which ones to evaluate, etc. Could you
maybe consolidate them into a patch series that I could benchmark?

I did give this patch a try with the dataset/query shared in [1], and
the explain looks like this:

QUERY PLAN
---------------------------------------------------------------------
Index Scan using idx on t (actual rows=9048576.00 loops=1)
Index Cond: ((a >= 16150) AND (a <= 4540437))
Index Searches: 1
Prefetch Distance: 271.999
Prefetch Count: 4339129
Prefetch Stalls: 386
Prefetch Skips: 6039906
Prefetch Resets: 0
Stream Ungets: 1331122
Stream Forwarded: 306719
Prefetch Histogram: [2,4) => 10, [4,8) => 2, [8,16) => 2,
[16,32) => 2, [32,64) => 2, [64,128) => 3,
[256,512) => 4339108
Buffers: shared hit=2573920 read=455610
Planning:
Buffers: shared hit=83 read=26
Planning Time: 4.142 ms
Execution Time: 1694.368 ms
(16 rows)

which is pretty good, and pretty much on-par with master (so no
regression, which is good).

It's a bit strange the distance ends up being that high, though. The
explain says:

Prefetch Distance: 271.999

There's ~70% misses on average, so isn't 217 a bit too high? Wouldn't
that cause too many concurrent IOs? Maybe I'm interpreting this wrong,
or maybe the explain stats are not quite right.

For comparison, the patch from [1] ends up with this:

Prefetch Distance: 36.321

In any case, the patch seems to help, and maybe it's a better approach,
I need to take a closer look.

regards

[1]
https://www.postgresql.org/message-id/8f5d66cf-44e9-40e0-8349-d5590ba8efb4%40vondra.me

--
Tomas Vondra