Message-ID: <9411f220-007d-4f1e-9c8f-ca8eb09e6788@vondra.me>
Date: Sun, 15 Feb 2026 08:57:19 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: index prefetching
To: Alexandre Felipe <o.alexandre.felipe@gmail.com>,
 Peter Geoghegan <pg@bowt.ie>
Cc: Andres Freund <andres@anarazel.de>, Thomas Munro
 <thomas.munro@gmail.com>, Nazir Bilal Yavuz <byavuz81@gmail.com>,
 Robert Haas <robertmhaas@gmail.com>,
 Melanie Plageman <melanieplageman@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Georgios <gkokolatos@protonmail.com>,
 Konstantin Knizhnik <knizhnik@garret.ru>, Dilip Kumar <dilipbalaut@gmail.com>
References: <6d59c277-c440-4d1f-a46e-157958c06a5f@vondra.me>
 <CAH2-WzkHK0++yn3_nmWL2rWzXiu8Qc=2=NPQDG4nfVjvYkRDrQ@mail.gmail.com>
 <CAH2-Wzk9=x=a2TbcqYcX+XXmDHQr5=1v9m4Z_v8a-KwF1Zoz0A@mail.gmail.com>
 <CAH2-WzmYqhacBH161peAWb5eF=Ja7CFAQ+0jSEMq=qnfLVTOOg@mail.gmail.com>
 <CAH2-Wzmm+mXuv_r+eYgcwOKZzNSmbYoEmxi5mdKMUtkmYa_qXw@mail.gmail.com>
 <CAH2-WzkC_EdGB_tdEQr63BiOuJbcvZiV=xO+OJYux4wOaxPD8w@mail.gmail.com>
 <CAH2-Wzn8whv-RE4E5L2WQB_Ha_jy0UReJq1yypu83gDYHeXq5g@mail.gmail.com>
 <CAH2-Wzm7-QuDOs6TcqfhhDsGEZCuHtn=D-SriOTnTZ_fiXNBvA@mail.gmail.com>
 <CAH2-WzmH7pVQ0-mYAxb82aWbz29_BiBPq2wV5p7+1o2sRFqDRQ@mail.gmail.com>
 <CAH2-Wz=6a7fGz2rALDX+xiFDuEaGQWpZ49xEaBUDKiPH8gcL+Q@mail.gmail.com>
 <CAH2-WzkehuhxyuA8quc7rRN3EtNXpiKsjPfO8mhb+0Dr2K0Dtg@mail.gmail.com>
 <CAH2-WzmymSyOt5Y2RGbm6cJXg18J_ttfqjdcpodHe6Gp23ConQ@mail.gmail.com>
 <CAH2-Wznv9_KGqHQ1vCW2pkiA6QskBGcx5NC_-UXnD6GEQasvAQ@mail.gmail.com>
 <CAE8JnxN_EwnTLLMWGhvgwaomYZ0ysm7NeogA-BqBd=Rs3S7Oqw@mail.gmail.com>
Content-Language: en-US
From: Tomas Vondra <tomas@vondra.me>
In-Reply-To: <CAE8JnxN_EwnTLLMWGhvgwaomYZ0ysm7NeogA-BqBd=Rs3S7Oqw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/9411f220-007d-4f1e-9c8f-ca8eb09e6788%40vondra.me>
Precedence: bulk

Hi,

On 2/15/26 01:13, Alexandre Felipe wrote:
> Hi,
> I decided to test this PR.
> 
> I didn't take much time to go through the thread or the code in detail
> yet. But I have my first benchmark results and I would like to share.
> 

I'm quite confused by the scripts you shared, it seems incomplete. The
run_regression.py is meant to call purge_cache.sh (which is missing),
and the run_benchmark tries to call all sorts of missing .sql scripts.

So how do we use that?

> EXPERIMENT
> 
> I tested [CF 4351] v10 - Index Prefetching
> 
> I created a table with 100k rows and
> Sequential is, as guessed, 1,2,3,4 (indexed)
> Periodic is a quasi random (i * jump) % num_rows, where gcd(jump,
> num_rows) = 1, guarantee that there are no repeated entries (indexed)
> Random is a `row_number() over (order by random())` (indexed)
> The payload is a fixed 200 character long string, just to make it more
> realistic.
> 
> For the tests, I disable sorting, sequential scans, index only scans and
> bitmap scans.
> Since buffer cache always has a significant impact on the query
> performance, I shuffled the tests, and tried to adjust for the number of
> buffer hit/read, but later I found that the best way to control that was
> to use a table small enough to be entirely held in cache, and evict the
> buffers.
> 

That seems a bit bizarre. The whole point of index prefetching is better
I/O scheduling (ahead of time), but if you "control" the impact of cache
by making sure everything is cached, that kinda defeats the whole thing.

A table that is just 24MB and fits into buffers is a bit useless. It
means that even with random pattern (which is generally about the best
for prefetching), only about ~1/30 of pages will require I/O. Each page
has ~32 items, but only the first item from each page will incur an I/O.


> * off: buffers are kept in cache
> * pg: buffers evicted from postgres pg_buffercache_evict from
> pg_buffercache extension.
> * os: supported only in  python, I separated the buffer eviction in
> purge_cache as it requires sudo (tested only in MacOS).
> 
> I varied 
>  * max_parallel_workers_per_gather (although I guess it wasn't exploited), 
>  * enable_index_prefetch 
>  * the column used as sorting key and, as a result, the index used.
>  * and buffer eviction mode.
> 
> Running from python with psycopg
> 

On what kind of hardware? How much variance is in the results?


regards

-- 
Tomas Vondra