public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andres Freund <[email protected]>
To: Peter Geoghegan <[email protected]>
Cc: Tomas Vondra <[email protected]>
Cc: Thomas Munro <[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: Thu, 28 Aug 2025 21:10:48 -0400
Message-ID: <qdl4fojnbfcnm2k7b4zpvgd6gwzwdgtbl5c7shpimrb76dbyy6@scdnspus3ejh> (raw)
In-Reply-To: <CAH2-Wz=DfvzasnzLv43cu36Q1Ca8Qi70_JjZ7SRbNhDwwgvirg@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<onbn3rx35x6k7mfnsmejnebt4nahnii3qnjrac2jzdh3puwo6t@dzjzsx5ppaj7>
<[email protected]>
<5pltwb73d7cynsxo2yb54ygjk7haviatkrx43mnzihc6kkield@ahnstpgof46i>
<CA+hUKGKL3MRvEftAE+kwBuL2PLg2CwUoHEMr=-KSvsWN8pHq9w@mail.gmail.com>
<[email protected]>
<e33gafg4p7iwvo24ytrxuw43nafm5xm3jefpdspnarcbkfurs7@3jbgdiinxem5>
<[email protected]>
<CAH2-Wz=DfvzasnzLv43cu36Q1Ca8Qi70_JjZ7SRbNhDwwgvirg@mail.gmail.com>
Hi,
On 2025-08-28 19:57:17 -0400, Peter Geoghegan wrote:
> On Thu, Aug 28, 2025 at 7:52 PM Tomas Vondra <[email protected]> wrote:
> > 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.).
>
> I found it quite hard to apply Thomas' patch. There's actually 3
> patches, with 2 earlier patches needed for earlier in the thread. And,
> there were significant merge conflicts to work around.
Same. Tomas, could you share what you applied?
> I'm not sure that Thomas'/your patch to ameliorate the problem on the
> read stream side is essential here. Perhaps Andres can just take a
> look at the test case + feature branch, without the extra patches.
> That way he'll be able to see whatever the immediate problem is, which
> might be all we need.
It seems caused to a significant degree by waiting at low queue depths. If I
comment out the stream->distance-- in read_stream_start_pending_read() the
regression is reduced greatly.
As far as I can tell, after that the process is CPU bound, i.e. IO waits don't
play a role.
I see a variety for increased CPU usage:
1) The private ref count infrastructure in bufmgr.c gets a bit slower once
more buffers are pinned
2) signalling overhead to the worker - I think we are resetting the latch too
eagerly, leading to unnecessarily many signals being sent to the IO worker.
3) same issue with the resowner tracking
But there's some additional difference in performance I don't yet
understand...
Greetings,
Andres Freund
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: <qdl4fojnbfcnm2k7b4zpvgd6gwzwdgtbl5c7shpimrb76dbyy6@scdnspus3ejh>
* 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