Re: index prefetching

On 21/12/2025 7:55 PM, Peter Geoghegan wrote:
> On Wed, Dec 10, 2025 at 9:21 PM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
>> Attached is v4.
> Attached is v5. Changes from v4:
>
> * Simplified and optimized index-only scans, with a particular
> emphasis on avoiding regressions with nested loop joins with an inner
> index-only scan.
>
> There were quite a number of small problems/dead code related to
> index-only scans fixed by this new v5. Overall, I'm quite a bit
> happier with the state of index-only scans, which I'd not paid too
> much attention to before now.
>
> * Added Valgrind instrumentation to the hash index patch, which was
> required to fix some false positives.
>
> The generic indexam_util_batch_unlock routine had Valgrind
> instrumentation in earlier versions, just to keep nbtree's buffer
> locking checks from generating similar false positives. Some time
> later, when I added the hashgetbatch patch, there were new Valgrind
> false positives during hash index scans -- which I missed at first.
> This new v5 revisions adds similar Valgrind checks to hash itself
> (changes that add code that is more or less a direct port of the stuff
> added to nbtree by commit 4a70f829), which fixes the false positives,
> and is independently useful.
>
> The rule for amgetbatch-based index AMs is that they must have similar
> buffer locking instrumentation. That seems like a good thing.
>
> --
> Peter Geoghegan

I the previous mail I shared results of my experiments with different
prefetch distance.
I think that we should start prefetching of heap tuples not from the
second batch, but after some number of proceeded tids.

Attached please find a patch which implements this approach.
And below are updated results:

limit\prefetch    on      off   always  inc    threshold
1                 12074   12765  3146    3282     12394
2                 5912    6198   2463    2438      6124
4                 2919    3047   1334    1964      2910
8                 1554    1496   1166    1409      1588
16                815     775    947     940        600
32                424     403    687     695        478
64                223     208    446     453        358
128               115     106    258     270        232
256               68      53     138     149        131
512               43      27     72      78          71
1024              28      13     38      40          38

Last column is result of prefetch with read_stream_threshold=10.