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
