On 11/12/2025 4:21 AM, Peter Geoghegan wrote:
On Wed, Dec 10, 2025 at 5:41 PM Peter Geoghegan <[email protected]> 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.
