Horton

Re: index prefetching

Peter Geoghegan <pg@bowt.ie>

View thread
From: "Peter Geoghegan" <pg@bowt.ie>
To: "Tomas Vondra" <tomas@vondra.me>
Cc: "Andres Freund" <andres@anarazel.de>, "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-15T16:24:40Z
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 Thu Aug 14, 2025 at 7:26 PM EDT, Tomas Vondra wrote:
>> My guess is that once we fix the underlying problem, we'll see
>> improved performance for many different types of queries. Not as big
>> of a benefit as the one that the broken query will get, but still
>> enough to matter.
>> 
>
> Hopefully. Let's see.

Good news here: with Andres' bufmgr patch applied, the similar forwards scan
query does indeed get more than 2x faster.  And I don't mean that it gets
faster on the randomized table -- it actually gets 2x faster with your
original (almost but not quite entirely sequential) table, and your original
query.  This is especially good news because that query seems particularly
likely to be representative of real world user queries.

And so the "backwards scan" aspect of this investigation was always a bit of a
red herring.  The only reason why "backwards-ness" ever even seemed relevant
was that with the backwards scan variant, performance was made so much slower
by the issue that Andres' patch addresses than even my randomized version of
the same query ran quite a bit faster.

More concretely:

Without bufmgr patch
--------------------

┌─────────────────────────────────────────────────────────────┐
│                         QUERY PLAN                          │
├─────────────────────────────────────────────────────────────┤
│ Index Scan using t_pk on t (actual rows=1048576.00 loops=1) │
│   Index Cond: ((a >= 16336) AND (a <= 49103))               │
│   Index Searches: 1                                         │
│   Buffers: shared hit=6572 read=49933                       │
│   I/O Timings: shared read=77.038                           │
│ Planning:                                                   │
│   Buffers: shared hit=50 read=6                             │
│   I/O Timings: shared read=0.570                            │
│ Planning Time: 0.774 ms                                     │
│ Execution Time: 618.585 ms                                  │
└─────────────────────────────────────────────────────────────┘
(10 rows)

With bufmgr patch
-----------------

┌─────────────────────────────────────────────────────────────┐
│                         QUERY PLAN                          │
├─────────────────────────────────────────────────────────────┤
│ Index Scan using t_pk on t (actual rows=1048576.00 loops=1) │
│   Index Cond: ((a >= 16336) AND (a <= 49103))               │
│   Index Searches: 1                                         │
│   Buffers: shared hit=10257 read=49933                      │
│   I/O Timings: shared read=135.825                          │
│ Planning:                                                   │
│   Buffers: shared hit=50 read=6                             │
│   I/O Timings: shared read=0.570                            │
│ Planning Time: 0.767 ms                                     │
│ Execution Time: 279.643 ms                                  │
└─────────────────────────────────────────────────────────────┘
(10 rows)

I _think_ that Andres' patch also fixes the EXPLAIN ANALYZE accounting, so
that "I/O Timings" is actually correct.  That's why EXPLAIN ANALYZE with the
bufmgr patch has much higher "shared read" time, despite overall execution
time being cut in half.

-- 
Peter Geoghegan