Re: index prefetching

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
49103ORDER 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