Re: index prefetching

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