Re: index prefetching

On Thu Aug 14, 2025 at 7:26 PM EDT, Tomas Vondra wrote:
>> My guess is that once we fix the underlying problem, we'll see
>> improved performance for many different types of queries. Not as big
>> of a benefit as the one that the broken query will get, but still
>> enough to matter.
>>
>
> Hopefully. Let's see.

Good news here: with Andres' bufmgr patch applied, the similar forwards scan
query does indeed get more than 2x faster. And I don't mean that it gets
faster on the randomized table -- it actually gets 2x faster with your
original (almost but not quite entirely sequential) table, and your original
query. This is especially good news because that query seems particularly
likely to be representative of real world user queries.

And so the "backwards scan" aspect of this investigation was always a bit of a
red herring. The only reason why "backwards-ness" ever even seemed relevant
was that with the backwards scan variant, performance was made so much slower
by the issue that Andres' patch addresses than even my randomized version of
the same query ran quite a bit faster.

More concretely:

Without 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=6572 read=49933 │
│ I/O Timings: shared read=77.038 │
│ Planning: │
│ Buffers: shared hit=50 read=6 │
│ I/O Timings: shared read=0.570 │
│ Planning Time: 0.774 ms │
│ Execution Time: 618.585 ms │
└─────────────────────────────────────────────────────────────┘
(10 rows)

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.

--
Peter Geoghegan