MIME-Version: 1.0
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>
In-Reply-To: <5pltwb73d7cynsxo2yb54ygjk7haviatkrx43mnzihc6kkield@ahnstpgof46i>
From: Thomas Munro <thomas.munro@gmail.com>
Date: Fri, 29 Aug 2025 09:50:57 +1200
Message-ID: 
 <CA+hUKGKL3MRvEftAE+kwBuL2PLg2CwUoHEMr=-KSvsWN8pHq9w@mail.gmail.com>
Subject: Re: index prefetching
To: Andres Freund <andres@anarazel.de>
Cc: Tomas Vondra <tomas@vondra.me>, 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>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BhUKGKL3MRvEftAE%2BkwBuL2PLg2CwUoHEMr%3D-KSvsWN8pHq9w%40mail.gmail.com>
Precedence: bulk

On Fri, Aug 29, 2025 at 7:52=E2=80=AFAM 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, bu=
t
> > 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 switchi=
ng 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?

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%3DoR=
AdEXuCnh%2BtGBTWa7uFeFMGgTLAw%2BQ%40mail.gmail.com