public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tomas Vondra <[email protected]>
To: Andres Freund <[email protected]>
Cc: Thomas Munro <[email protected]>
Cc: Peter Geoghegan <[email protected]>
Cc: Nazir Bilal Yavuz <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Melanie Plageman <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Georgios <[email protected]>
Cc: Konstantin Knizhnik <[email protected]>
Cc: Dilip Kumar <[email protected]>
Subject: Re: index prefetching
Date: Fri, 29 Aug 2025 01:52:29 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <e33gafg4p7iwvo24ytrxuw43nafm5xm3jefpdspnarcbkfurs7@3jbgdiinxem5>
References: <6butbqln6ewi5kuxz3kfv2mwomnlgtate4mb4lpa7gb2l63j4t@stlwbi2dvvev>
<[email protected]>
<y32ow7vfdrrcchxrwgfr5tbwg3ncm4f3rlp65svcbqorbm3tmn@cwvipbplnqyt>
<[email protected]>
<[email protected]>
<onbn3rx35x6k7mfnsmejnebt4nahnii3qnjrac2jzdh3puwo6t@dzjzsx5ppaj7>
<[email protected]>
<5pltwb73d7cynsxo2yb54ygjk7haviatkrx43mnzihc6kkield@ahnstpgof46i>
<CA+hUKGKL3MRvEftAE+kwBuL2PLg2CwUoHEMr=-KSvsWN8pHq9w@mail.gmail.com>
<[email protected]>
<e33gafg4p7iwvo24ytrxuw43nafm5xm3jefpdspnarcbkfurs7@3jbgdiinxem5>
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.g...
(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
Attachments:
[application/sql] repro.sql (653B, 2-repro.sql)
download
view thread (348+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: index prefetching
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox