Horton

Re: index prefetching

Peter Geoghegan <pg@bowt.ie>

View thread
From: "Peter Geoghegan" <pg@bowt.ie>
To: "Peter Geoghegan" <pg@bowt.ie>, "Tomas Vondra" <tomas@vondra.me>, "Andres Freund" <andres@anarazel.de>
Cc: "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-14T03:43:46Z
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 Aug 13, 2025 at 7:50 PM EDT, Peter Geoghegan wrote:
> pg@regression:5432 [2476413]=# EXPLAIN (ANALYZE ,costs off, timing off) SELECT * FROM t WHERE a BETWEEN 16336 AND 49103 ORDER BY a desc;
> ┌─────────────────────────────────────────────────────────────────────┐
> │                             QUERY PLAN                              │
> ├─────────────────────────────────────────────────────────────────────┤
> │ Index Scan Backward using idx on t (actual rows=1048576.00 loops=1) │
> │   Index Cond: ((a >= 16336) AND (a <= 49103))                       │
> │   Index Searches: 1                                                 │
> │   Buffers: shared hit=6082 read=77813                               │
> │   I/O Timings: shared read=324.305                                  │
> │ Planning Time: 0.071 ms                                             │
> │ Execution Time: 616.268 ms                                          │
> └─────────────────────────────────────────────────────────────────────┘
> (7 rows)

> Also possibly worth noting: I'm pretty sure that "shared hit=6082" is wrong.
> Though now it's wrong in the same way with both variants.

Actually, "Buffers:" output _didn't_ have the same problem with the randomized
test case variants. With master + buffered I/O, with the FS cache dropped, and
with the index relation prewarmed, the same query shows the same "Buffers"
details that the patch showed earlier:

┌─────────────────────────────────────────────────────────────────────┐
│                             QUERY PLAN                              │
├─────────────────────────────────────────────────────────────────────┤
│ Index Scan Backward using idx on t (actual rows=1048576.00 loops=1) │
│   Index Cond: ((a >= 16336) AND (a <= 49103))                       │
│   Index Searches: 1                                                 │
│   Buffers: shared hit=6085 read=77813                               │
│   I/O Timings: shared read=10572.441                                │
│ Planning:                                                           │
│   Buffers: shared hit=90 read=23                                    │
│   I/O Timings: shared read=1.212                                    │
│ Planning Time: 1.505 ms                                             │
│ Execution Time: 10711.853 ms                                        │
└─────────────────────────────────────────────────────────────────────┘
(10 rows)

Though it's not particular relevant to the problem at hand, I'll also point out
that with a scan of an index such as this (an index that exhibits "heap
clustering without heap correlation"), prefetching is particularly important.
Here we see a ~17.3x speedup (relative to master + buffered I/O). Nice!

-- 
Peter Geoghegan