Re: index prefetching

From: Konstantin Knizhnik <knizhnik(at)garret(dot)ru>
To: Peter Geoghegan <pg(at)bowt(dot)ie>, Tomas Vondra <tomas(at)vondra(dot)me>
Cc: Andres Freund <andres(at)anarazel(dot)de>, Thomas Munro <thomas(dot)munro(at)gmail(dot)com>, Nazir Bilal Yavuz <byavuz81(at)gmail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Melanie Plageman <melanieplageman(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Georgios <gkokolatos(at)protonmail(dot)com>, Dilip Kumar <dilipbalaut(at)gmail(dot)com>
Subject: Re: index prefetching
Date: 2025-12-17 17:19:27
Message-ID: c76ce1c7-6a05-4459-ba27-953c05bf5c14@garret.ru
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


On 11/12/2025 4:21 AM, Peter Geoghegan wrote:
> On Wed, Dec 10, 2025 at 5:41 PM Peter Geoghegan <pg(at)bowt(dot)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.

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2025-12-17 17:24:29 Re: Updating IPC::Run in CI?
Previous Message Heikki Linnakangas 2025-12-17 17:14:47 Re: Visibility bug in tuple lock