Horton

Re: index prefetching

Peter Geoghegan <pg@bowt.ie>

View thread
From: Peter Geoghegan <pg@bowt.ie>
To: Andres Freund <andres@anarazel.de>
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>
Date: 2025-11-24T00:03:44Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. aio: io_uring: Trigger async processing for large IOs

  2. read stream: Split decision about look ahead for AIO and combining

  3. read_stream: Only increase read-ahead distance when waiting for IO

  4. read_stream: Prevent distance from decaying too quickly

  5. Reduce ExecSeqScan* code size using pg_assume()

  6. Fix rare bug in read_stream.c's split IO handling.

  7. Fix multiranges to behave more like dependent types.

  8. Add EXPLAIN (MEMORY) to report planner memory consumption

  9. Optimize nbtree backward scan boundary cases.

  10. Increment xactCompletionCount during subtransaction abort.

  11. Add nbtree Valgrind buffer lock checks.

  12. Add nbtree high key "continuescan" optimization.

  13. Reduce pinning and buffer content locking for btree scans.

  14. Teach btree to handle ScalarArrayOpExpr quals natively.

On Fri, Nov 21, 2025 at 6:31 PM Andres Freund <andres@anarazel.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@anarazel.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