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-08-15T17:22:58Z
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-08-15 12:24:40 -0400, Peter Geoghegan wrote:
> 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.

Somewhat random note about I/O waits:

Unfortunately the I/O wait time we measure often massively *over* estimate the
actual I/O time. If I execute the above query with the patch applied, we
actually barely ever wait for I/O to complete, it's all completed by the time
we have to wait for the I/O. What we are measuring is the CPU cost of
*initiating* the I/O.

That's why we are seeing "I/O Timings" > 0 even if we do perfect readahead.

Most of the cost is in the kernel, primarily looking up block locations and
setting up the actual I/O.

Greetings,

Andres Freund