Re: index prefetching - Mailing list pgsql-hackers

Hi,

On 2025-07-23 14:50:15 +0200, Tomas Vondra wrote:
> On 7/23/25 02:59, Andres Freund wrote:
> > Hi,
> > 
> > On 2025-07-23 02:50:04 +0200, Tomas Vondra wrote:
> >> But I don't see why would this have any effect on the prefetch distance,
> >> queue depth etc. Or why decreasing INDEX_SCAN_MAX_BATCHES should improve
> >> that. I'd have expected exactly the opposite behavior.
> >>
> >> Could be bug, of course. But it'd be helpful to see the dataset/query.
> > 
> > Pgbench scale 500, with the simpler query from my message.
> > 
> 
> I tried to reproduce this, but I'm not seeing behavior. I'm not sure how
> you monitor the queue depth (presumably iostat?)

Yes, iostat, since I was looking at what the "actually required" lookahead
distance is.

Do you actually get the query to be entirely CPU bound? What amount of IO
waiting do you see EXPLAIN (ANALYZE, TIMING OFF) with track_io_timing=on
report?

Ah - I was using a very high effective_io_concurrency. With a high
effective_io_concurrency value I see a lot of stalls, even at
INDEX_SCAN_MAX_BATCHES = 64. And a lower prefetch distance, which seems
somewhat odd.


FWIW, in my tests I was just evicting lineitem from shared buffers, since I
wanted to test the heap prefetching, without stalls induced by blocking on
index reads. But what I described happens with either.

;SET effective_io_concurrency = 256;SELECT pg_buffercache_evict_relation('pgbench_accounts'); explain (analyze, costs
off,timing off) SELECT max(abalance) FROM (SELECT * FROM pgbench_accounts ORDER BY aid LIMIT 10000000);
 
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                QUERY PLAN                                                │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Aggregate (actual rows=1.00 loops=1)                                                                     │
│   Buffers: shared hit=27369 read=164191                                                                  │
│   I/O Timings: shared read=358.795                                                                       │
│   ->  Limit (actual rows=10000000.00 loops=1)                                                            │
│         Buffers: shared hit=27369 read=164191                                                            │
│         I/O Timings: shared read=358.795                                                                 │
│         ->  Index Scan using pgbench_accounts_pkey on pgbench_accounts (actual rows=10000000.00 loops=1) │
│               Index Searches: 1                                                                          │
│               Prefetch Distance: 256.989                                                                 │
│               Prefetch Stalls: 3                                                                         │
│               Prefetch Resets: 3                                                                         │
│               Buffers: shared hit=27369 read=164191                                                      │
│               I/O Timings: shared read=358.795                                                           │
│ Planning Time: 0.086 ms                                                                                  │
│ Execution Time: 4194.845 ms                                                                              │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────┘

;SET effective_io_concurrency = 512;SELECT pg_buffercache_evict_relation('pgbench_accounts'); explain (analyze, costs
off,timing off) SELECT max(abalance) FROM (SELECT * FROM pgbench_accounts ORDER BY aid LIMIT 10000000);
 
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                QUERY PLAN                                                │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Aggregate (actual rows=1.00 loops=1)                                                                     │
│   Buffers: shared hit=27368 read=164190                                                                  │
│   I/O Timings: shared read=832.515                                                                       │
│   ->  Limit (actual rows=10000000.00 loops=1)                                                            │
│         Buffers: shared hit=27368 read=164190                                                            │
│         I/O Timings: shared read=832.515                                                                 │
│         ->  Index Scan using pgbench_accounts_pkey on pgbench_accounts (actual rows=10000000.00 loops=1) │
│               Index Searches: 1                                                                          │
│               Prefetch Distance: 56.778                                                                  │
│               Prefetch Stalls: 160569                                                                    │
│               Prefetch Resets: 423                                                                       │
│               Buffers: shared hit=27368 read=164190                                                      │
│               I/O Timings: shared read=832.515                                                           │
│ Planning Time: 0.084 ms                                                                                  │
│ Execution Time: 4413.058 ms                                                                              │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Greetings,

Andres Freund