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>, Robert Haas <robertmhaas@gmail.com>, Melanie Plageman <melanieplageman@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Georgios <gkokolatos@protonmail.com>, Thomas Munro <thomas.munro@gmail.com>, Konstantin Knizhnik <knizhnik@garret.ru>, Dilip Kumar <dilipbalaut@gmail.com>
Date: 2025-07-16T20:54:06Z
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 Wed, Jul 16, 2025 at 3:40 PM Andres Freund <andres@anarazel.de> wrote:
> As a first thing I just wanted to get a feel for the improvements we can get.
> I had a scale 5 tpch already loaded, so I ran a bogus query on that to see.

Cool.

> Test:
>
> Peter's:

To be clear, the "complex" patch is still almost all Tomas' work -- at
least right now. I'd like to do a lot more work on this project,
though.

So far, my main contribution has been debugging advice, and removing
code/simplifying things on the nbtree side.

> I call that a hell of an impressive improvement with either patch - it's
> really really hard to find order of magnitude improvements in anything close
> to realistic cases.

Nice.

> Peter, it'd be rather useful if your patch also had an enable/disable GUC,
> otherwise it's more work to study the performance effects. The
> effective_io_concurrency approach isn't great, because it also affects
> bitmap scans, seqscans etc.

FWIW I took out the GUC because it works by making indexam.c use the
amgettuple interface. The "complex" patch completely gets rid of
btgettuple, whereas the simple patch keeps btgettuple in largely its
current form.

I agree that having such a GUC is important during development, and
will try to add it back soon. It'll have to work in some completely
different way, but that still shouldn't be difficult.

-- 
Peter Geoghegan