Thread

Re: index prefetching

Konstantin Knizhnik <knizhnik@garret.ru> — 2025-12-17T17:19:27Z

On 11/12/2025 4:21 AM, Peter Geoghegan wrote:
> On Wed, Dec 10, 2025 at 5:41 PM Peter Geoghegan <pg@bowt.ie> wrote:
>> Attached is v3. This is another revision whose sole purpose is to keep
>> the patch applying cleanly. No real changes compared to v1 to report
>> here, either.
> Attached is v4. Same story again (another bitrot-fix-only revision).
>

I did some small benchmarking and was slightly confused by result.
I run tests at my MacBook with 64Gb RAM. Database is initialized in this 
way:

create table t (pk integer primary key, payload text default repeat('x', 
1000)) with (fillfactor=10);
insert into t values (generate_series(1,10000000))

So it creates table with size 80Gb (160 after vacuum) which doesn't fit 
in RAM.
I used default Postgres configuration and alter the only parameter - 
`effective_io_concurrency`.

File with query for pgbench:

\set pk random(1, 10000000)
select * from t where pk >= :pk order by pk limit N;

where N is 1,10,100

I run pgbench with just one client:
pgbench  -T 60 -n -M prepared -f select.sql postgres

Results with current master are the following:

limit
1             7754
10           1868
100         1047


With applied index prefetching patch results are almost 2 times better 
for small limit (eio=effective_io_concurrency):


limit\eio       0      10        100
1           14260  14240  13909
10           3088    3152    3174
100         1135    1020    1052

but what confuses me is that they do not depend on 
`effective_io_concurrency`.
Moreover with `enable_indexscan_prefetch=off` results are the same.

Also I expected that the best effect of index prefetching should be for 
larger limit (accessing more heap pages). But as you see - it is not true.

May we there is something wrong with my test scenario.
It will be nice to get some information about efficiency of prefetch, 
for example add `pefetch` option to explain: `explain 
(analyze,buffers,prefetch) ...`
I think that in `pgaio_io_wait` we can distinguish IO operations which 
are completed without waiting and can be considered as prefetch hit.
Right now it is hard to understand without debugger whether prefetch is 
perfromed at all.