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
