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