Horton

Re: index prefetching

Andres Freund <andres@anarazel.de>

View thread
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>
Date: 2025-11-24T20:48:42Z
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.

Hi,

On 2025-11-23 19:03:44 -0500, Peter Geoghegan wrote:
> 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).

Huh. I wouldn't have expected -march=native to make a huge difference...

> Are you in the habit of using -march=native? I'm not.

I occasionally use it, but not regularly - I do however use -O3, as I found
that to actually improve performance sufficiently in plenty cases. And it's
something that's much more generally applicable than -march=native?.


I don't think the precise gains here, particularly basedon on quick
prototypes, make that much of a difference. There's so much more optimization
potential other than the amortization of locking costs...

Greetings,

Andres Freund