Re: index prefetching

On Fri, Nov 21, 2025 at 6:31 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> On 2025-11-21 18:14:56 -0500, Peter Geoghegan wrote:
> > On Fri, Nov 21, 2025 at 5:38 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> > > Another benfit is that it helps even more when there multiple queries running
> > > concurrently - the high rate of lock/unlock on the buffer rather badly hurts
> > > scalability.
> >
> > I haven't noticed that effect myself. In fact, it seemed to be the
> > other way around; it looked like it helped most with very low client
> > count workloads.
>
> It's possible that that effect is more visible on larger machines - I did test
> that on a 2x 24cores/48 threads machine. I do see a smaller effect on a
> 2x10c/20t machine.

Update: I find that when I build Postgres with -march=native, I see
performance characteristics that are much more in line with what you
saw when you ran your own experiments (experiments with minimizing the
number of heap buffer locks acquired during index scans). At 1 client
count, there's now only about a 10% increase in throughput for a
pgbench variant that uses the type of range queries that you'd expect
to benefit the most from this work (that was more like 18%-20% without
-march=native). Whereas with 32 clients, it's an ~18% improvement in
throughput (where before it was only around 15% - 16%).

Are you in the habit of using -march=native? I'm not. I assume that
most Postgres users aren't using packages that were built with the
flags that -march=native implies, which is why I largely go with
defaults for my release/benchmarking builds (the only exception is my
use of -fno-omit-frame-pointer).

In case it matters, my workstation uses a Ryzen 9 5950X CPU (which is Zen 3).

--
Peter Geoghegan