Feedback-ID: id4a34324:Fastmail
Date: Wed, 3 Sep 2025 14:47:25 -0400
From: Andres Freund <andres@anarazel.de>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Tomas Vondra <tomas@vondra.me>, Thomas Munro <thomas.munro@gmail.com>,
	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>
Subject: Re: index prefetching
Message-ID: <bpdeohyqvltb77viyft4bza4xc4peed3jcoep74d2ih6ynqlke@wbnhcwmq3ril>
References: <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>
 <931afce3-8c86-4c96-9861-0ffa17c6560f@vondra.me>
 <CAH2-Wz=DfvzasnzLv43cu36Q1Ca8Qi70_JjZ7SRbNhDwwgvirg@mail.gmail.com>
 <qdl4fojnbfcnm2k7b4zpvgd6gwzwdgtbl5c7shpimrb76dbyy6@scdnspus3ejh>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <qdl4fojnbfcnm2k7b4zpvgd6gwzwdgtbl5c7shpimrb76dbyy6@scdnspus3ejh>
Archived-At: 
 <https://www.postgresql.org/message-id/bpdeohyqvltb77viyft4bza4xc4peed3jcoep74d2ih6ynqlke%40wbnhcwmq3ril>
Precedence: bulk

Hi,

I spent a fair bit more time analyzing this issue.


On 2025-08-28 21:10:48 -0400, Andres Freund wrote:
> On 2025-08-28 19:57:17 -0400, Peter Geoghegan wrote:
> > On Thu, Aug 28, 2025 at 7:52 PM Tomas Vondra <tomas@vondra.me> wrote:
> > 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.

Indeed the actual AIO subsystem is unrelated, from what I can tell:

I hacked up read_stream.c/bufmgr.c to do readahead even if the buffer is in
shared_buffers. With that, the negative performance impact of doing
enable_indexscan_prefetch=1 is of a similar magnitude even if the table is
already entirely in shared buffers. I.e. actual IO is unrelated.

I compared perf stat -ddd output for enable_indexscan_prefetch=0 with
enable_indexscan_prefetch=1. The only real difference is a substantial (~3x)
increase in branch misses.

I then took a perf profile to see where all those misses are from.


The first souce is:


> 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

The problem mainly seems to be that the branches in the loop at the start of
GetPrivateRefCountEntry() are entirely unpredictable in this workload.  I had
an old patch that tried to make it possible to use SIMD for the search, by
using a separate array for the Buffer ids - with that gcc generates fairly
crappy code, but does make the code branchless.

Here that substantially reduces the overhead of doing prefetching. Afterwards
it's not a meaningful source of misses anymore.


> 3) same issue with the resowner tracking

This one is much harder to address:

a) The "key" we are searching for is much wider (16 bytes), making
   vectorization of the search less helpful

b) because we search up to owner->narr instead of a fixed-length, the compiler
   wouldn't be able to auto-vectorize anyway

c) the branch-misses are partially caused by ResourcOwnerForget() "scrambling"
   the order in the array when forgetting an element


I don't know how to fix this right now.  I nevertheless wanted to see how big
the impact of this is, so I just neutered
ResourceOwner{Remember,Forget}{Buffer,BufferIO} - that's obviously not
correct, but suffices to see that the performance difference reduces
substantially.

But not completely, unfortunately.


> But there's some additional difference in performance I don't yet
> understand...

I still don't think I fully understand why the impact of this is so large. The
branch misses appear to be the only thing differentiating the two cases, but
with resowners neutralized, the remaining difference in branch misses seems
too large - it's not like the sequence of block numbers is more predictable
without prefetching...

The main increase in branch misses is in index_scan_stream_read_next...


Greetings,

Andres Freund