Message-ID: <ffcc1472-604e-48cf-afd1-25a245d3df4a@vondra.me>
Date: Fri, 29 Aug 2025 01:00:58 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: index prefetching
To: Thomas Munro <thomas.munro@gmail.com>, Andres Freund <andres@anarazel.de>
Cc: Peter Geoghegan <pg@bowt.ie>, 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: 
 <CAH2-WzkWNtCRTcUajGYrCkp9-+btteAthg21BzxbKV09AJuSrA@mail.gmail.com>
 <CAH2-WzkgkvbN_GqR+pfE7uKwhWxQ6h4jst7Rpjgrt68Vc1=FDA@mail.gmail.com>
 <5v2wuxg65l5e3s6uf373zskcqqoukmraxiucnvgn4t7b5cmeqx@5mhqsurdj6xn>
 <6butbqln6ewi5kuxz3kfv2mwomnlgtate4mb4lpa7gb2l63j4t@stlwbi2dvvev>
 <ce485718-f49e-4cbc-99de-5b935fe98926@vondra.me>
 <y32ow7vfdrrcchxrwgfr5tbwg3ncm4f3rlp65svcbqorbm3tmn@cwvipbplnqyt>
 <0dd33755-cab8-49c8-b1ed-698732577fbb@vondra.me>
 <1c9302da-c834-4773-a527-1c1a7029c5a3@vondra.me>
 <onbn3rx35x6k7mfnsmejnebt4nahnii3qnjrac2jzdh3puwo6t@dzjzsx5ppaj7>
 <6d59c277-c440-4d1f-a46e-157958c06a5f@vondra.me>
 <5pltwb73d7cynsxo2yb54ygjk7haviatkrx43mnzihc6kkield@ahnstpgof46i>
 <CA+hUKGKL3MRvEftAE+kwBuL2PLg2CwUoHEMr=-KSvsWN8pHq9w@mail.gmail.com>
Content-Language: en-US
From: Tomas Vondra <tomas@vondra.me>
In-Reply-To: 
 <CA+hUKGKL3MRvEftAE+kwBuL2PLg2CwUoHEMr=-KSvsWN8pHq9w@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/ffcc1472-604e-48cf-afd1-25a245d3df4a%40vondra.me>
Precedence: bulk


On 8/28/25 23:50, Thomas Munro wrote:
> On Fri, Aug 29, 2025 at 7:52 AM Andres Freund <andres@anarazel.de> wrote:
>> On 2025-08-28 19:08:40 +0200, Tomas Vondra wrote:
>>> From the 2x regression (compared to master) it might seem like that, but
>>> even with the increased distance it's still slower than master (by 25%). So
>>> maybe the "error" is to use AIO in these cases, instead of just switching to
>>> I/O done by the backend.
>>
>> If it's slower at a higher distance, we're missing something.
> 
> Enough io_workers?  What kind of I/O concurrency does it want?  Does
> wait_event show any backends doing synchronous IO?  How many does [1]
> want to run for that test workload and does it help?
> 

I'm not sure how to determine what concurrency it "wants". All I know is
that for "warm" runs [1], the basic index prefetch patch uses distance
~2.0 on average, and is ~2x slower than master. And with the patches the
distance is ~270, and it's 30% slower than master. (IIRC there's about
30% misses, so 270 is fairly high. Can't check now, the machine is
running other tests.)

Not sure about wait events, but I don't think any backends are doing
sychnronous I/O. There's only that one query running, and it's using AIO
(except for the index, which is still read synchronously).

Likewise, I don't think there's insufficient number of workers. I've
tried with 3 and 12 workers, and there's virtually no difference between
those. IIRC when watching "top", I've never seen more than 1 or maybe 2
workers active (using CPU).

[1] https://www.postgresql.org/message-id/attachment/180630/ryzen-warm.pdf

[2]
https://www.postgresql.org/message-id/293a4735-79a4-499c-9a36-870ee9286281%40vondra.me

> FWIW there's a very simple canned latency test in a SQL function in
> the first message in that thread (0005-XXX-read_buffer_loop.patch),
> just on the off-chance that it's useful as a starting point for other
> ideas.  There I was interested in IPC overheads, latch collapsing and
> other effects, so I was deliberately stalling on/evicting a single
> block repeatedly without any readahead distance, so I wasn't letting
> the stream "hide" IPC overheads.
> 
> [1] https://www.postgresql.org/message-id/flat/CA%2BhUKG%2Bm4xV0LMoH2c%3DoRAdEXuCnh%2BtGBTWa7uFeFMGgTLAw%2BQ%40mail.gmail.com

Interesting, I'll give it a try tomorrow. Do you recall if the results
were roughly in line with results of my signal IPC test?


regards

-- 
Tomas Vondra