Re: index prefetching
Andres Freund <andres@anarazel.de>
From: Andres Freund <andres@anarazel.de>
To: Tomas Vondra <tomas@vondra.me>
Cc: Peter Geoghegan <pg@bowt.ie>, Nazir Bilal Yavuz <byavuz81@gmail.com>, Thomas Munro <thomas.munro@gmail.com>, Robert Haas <robertmhaas@gmail.com>, Melanie Plageman <melanieplageman@gmail.com>,
PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Georgios <gkokolatos@protonmail.com>,
Konstantin Knizhnik <knizhnik@garret.ru>, Dilip Kumar <dilipbalaut@gmail.com>
Date: 2025-07-23T15:09:18Z
Lists: pgsql-hackers
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
aio: io_uring: Trigger async processing for large IOs
- a9ee66881744 19 (unreleased) landed
-
read stream: Split decision about look ahead for AIO and combining
- 8ca147d582a5 19 (unreleased) landed
-
read_stream: Only increase read-ahead distance when waiting for IO
- f63ca3379025 19 (unreleased) landed
-
read_stream: Prevent distance from decaying too quickly
- 6e36930f9aaf 19 (unreleased) landed
-
Reduce ExecSeqScan* code size using pg_assume()
- b227b0bb4e03 19 (unreleased) cited
-
Fix rare bug in read_stream.c's split IO handling.
- b421223172a2 19 (unreleased) cited
-
Fix multiranges to behave more like dependent types.
- 3e8235ba4f9c 17.0 cited
-
Add EXPLAIN (MEMORY) to report planner memory consumption
- 5de890e3610d 17.0 cited
-
Optimize nbtree backward scan boundary cases.
- c9c0589fda0e 17.0 cited
-
Increment xactCompletionCount during subtransaction abort.
- 90c885cdab8b 14.0 cited
-
Add nbtree Valgrind buffer lock checks.
- 4a70f829d86c 14.0 cited
-
Add nbtree high key "continuescan" optimization.
- 29b64d1de7c7 12.0 cited
-
Reduce pinning and buffer content locking for btree scans.
- 2ed5b87f96d4 9.5.0 cited
-
Teach btree to handle ScalarArrayOpExpr quals natively.
- 9e8da0f75731 9.2.0 cited
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