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-08-15T17:58:19Z
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, Aug 15, 2025 at 1:09 PM Andres Freund <andres@anarazel.de> wrote:
> On 2025-08-15 12:29:25 -0400, Peter Geoghegan wrote:
> > FWIW, this development probably completely changes the results of many
> > (all?) of your benchmark queries. My guess is that with Andres' patch,
> > things will be better across the board. But in any case the numbers
> > that you posted before now must now be considered
> > obsolete/nonrepresentative. Since this is such a huge change.
>
> I'd hope it doesn't improve all benchmark queries - if so the set of
> benchmarks would IMO be too skewed towards cases that access the same heap
> blocks multiple times within the readahead distance.

I don't think that that will be a problem. Up until recently, I had
exactly the opposite complaint about the benchmark queries.

> That's definitely an
> important thing to measure, but it's surely not the only thing to care
> about. For the index workloads the patch doesn't do anything about cases where
> we don't up re-encountering a buffer that we already started IO for.

IMV we need to make a conservative assumption that it might matter for
any query. There have already been numerous examples where we thought
we fully understood a test case, but didn't.

BTW, I just rebooted my workstation, losing various procfs changes
that I'd made when debugging this issue. It now looks like the forward
scan query is actually made about 3x faster by the addition of your
patch (not 2x faster, as reported earlier). It goes from 592.618 ms to
204.966 ms.

-- 
Peter Geoghegan