Content-Type: multipart/mixed; boundary="------------XLDxU09B405aW0AqEOVg0tC0"
Message-ID: <931afce3-8c86-4c96-9861-0ffa17c6560f@vondra.me>
Date: Fri, 29 Aug 2025 01:52:29 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: index prefetching
To: Andres Freund <andres@anarazel.de>
Cc: Thomas Munro <thomas.munro@gmail.com>, 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: <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>
 <ffcc1472-604e-48cf-afd1-25a245d3df4a@vondra.me>
 <e33gafg4p7iwvo24ytrxuw43nafm5xm3jefpdspnarcbkfurs7@3jbgdiinxem5>
Content-Language: en-US
From: Tomas Vondra <tomas@vondra.me>
In-Reply-To: <e33gafg4p7iwvo24ytrxuw43nafm5xm3jefpdspnarcbkfurs7@3jbgdiinxem5>
Archived-At: 
 <https://www.postgresql.org/message-id/931afce3-8c86-4c96-9861-0ffa17c6560f%40vondra.me>
Precedence: bulk

This is a multi-part message in MIME format.
--------------XLDxU09B405aW0AqEOVg0tC0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

On 8/29/25 01:27, Andres Freund wrote:
> Hi,
> 
> On 2025-08-29 01:00:58 +0200, Tomas Vondra wrote:
>> 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.)
> 
> There got to be something wrong here, I don't see a reason why at any
> meaningful distance it'd be slower.
> 
> What set of patches do I need to repro the issue?
> 

Use this branch:

  https://github.com/tvondra/postgres/commits/index-prefetch-master/

and then Thomas' patch that increases the prefetch distance:


https://www.postgresql.org/message-id/CA%2BhUKGL2PhFyDoqrHefqasOnaXhSg48t1phs3VM8BAdrZqKZkw%40mail.gmail.com

(IIRC there's a trivial conflict in read_stream_reset.).

> And what are the complete set of pieces to load the data?
> https://postgr.es/m/293a4735-79a4-499c-9a36-870ee9286281%40vondra.me
> has the query, but afaict not enough information to infer init.sql
> 

Yeah, I forgot to include that piece, sorry. Here's an init.sql, that
loads the table, it also has the query.

> 
>> 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).
> 
> That doesn't say much - if the they are doing IO, they're not on CPU...
> 

True. But one worker did show up in top, using a fair amount of CPU, so
why wouldn't the others (if they process the same stream)?


regards

-- 
Tomas Vondra

--------------XLDxU09B405aW0AqEOVg0tC0
Content-Type: application/sql; name="repro.sql"
Content-Disposition: attachment; filename="repro.sql"
Content-Transfer-Encoding: base64

LS0gYnVpbGQgdGhlIHRhYmxlCnNlbGVjdCBzZXRzZWVkKDApOwpjcmVhdGUgdW5sb2dnZWQg
dGFibGUgdCAoYSBiaWdpbnQsIGIgdGV4dCkgd2l0aCAoZmlsbGZhY3RvciA9IDIwKTsKCmlu
c2VydCBpbnRvIHQKc2VsZWN0IDEgKiBhLCBiIGZyb20gKAogIHNlbGVjdCByLCBhLCBiLCBn
ZW5lcmF0ZV9zZXJpZXMoMCwyLTEpIEFTIHAgZnJvbSAoCiAgICBzZWxlY3Qgcm93X251bWJl
cigpIG92ZXIgKCkgQVMgciwgYSwgYiBmcm9tICgKICAgICAgc2VsZWN0IGkgQVMgYSwgbWQ1
KGk6OnRleHQpIEFTIGIgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMSwgNTAwMDAwMCkgcyhpKSBP
UkRFUiBCWSAoaSArIDE2ICogKHJhbmRvbSgpIC0gMC41KSkKICAgICkgZm9vCiAgKSBiYXIK
KSBiYXogT1JERVIgQlkgKChyICogMiArIHApICsgOCAqIChyYW5kb20oKSAtIDAuNSkpOwoK
Y3JlYXRlIGluZGV4IGlkeCBvbiB0KGEgQVNDKSB3aXRoIChkZWR1cGxpY2F0ZV9pdGVtcz1m
YWxzZSk7Cgp2YWN1dW0gZnJlZXplOwoKYW5hbHl6ZSB0OwoKY2hlY2twb2ludDsKCi0tIHF1
ZXJ5IHRvIHRlc3QKU0VMRUNUICogRlJPTSAoIFNFTEVDVCAqIEZST00gdCBXSEVSRSBhIEJF
VFdFRU4gMTYxNTAgQU5EIDQ1NDA0MzcgT1JERVIgQlkgYSBBU0MpIE9GRlNFVCAxMDAwMDAw
MDAwOwo=

--------------XLDxU09B405aW0AqEOVg0tC0--