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
