Re: index prefetching

On 8/12/25 18:53, Tomas Vondra wrote:
> ...
>
> EXPLAIN (ANALYZE, COSTS OFF)
> SELECT * FROM t WHERE a BETWEEN 16336 AND 49103 ORDER BY a ASC;
>
> QUERY PLAN
> ------------------------------------------------------------------------
> Index Scan using idx on t
> (actual time=0.584..433.208 rows=1048576.00 loops=1)
> Index Cond: ((a >= 16336) AND (a <= 49103))
> Index Searches: 1
> Buffers: shared hit=7435 read=50872
> I/O Timings: shared read=332.270
> Planning:
> Buffers: shared hit=78 read=23
> I/O Timings: shared read=2.254
> Planning Time: 3.364 ms
> Execution Time: 463.516 ms
> (10 rows)
>
> EXPLAIN (ANALYZE, COSTS OFF)
> SELECT * FROM t WHERE a BETWEEN 16336 AND 49103 ORDER BY a DESC;
>
> QUERY PLAN
> ------------------------------------------------------------------------
> Index Scan Backward using idx on t
> (actual time=0.566..22002.780 rows=1048576.00 loops=1)
> Index Cond: ((a >= 16336) AND (a <= 49103))
> Index Searches: 1
> Buffers: shared hit=36131 read=50872
> I/O Timings: shared read=21217.995
> Planning:
> Buffers: shared hit=82 read=23
> I/O Timings: shared read=2.375
> Planning Time: 3.478 ms
> Execution Time: 22231.755 ms
> (10 rows)
>
> That's a pretty massive difference ... this is on my laptop, and the
> timing changes quite a bit, but it's always a multiple of the first
> query with forward scan.
>
> I did look into pg_aios, but there's only 8kB requests in both cases. I
> didn't have time to look closer yet.
>

One more detail I just noticed - the DESC scan apparently needs more
buffers (~87k vs. 57k). That probably shouldn't cause such massive
regression, though.

regards

--
Tomas Vondra