Re: index prefetching

Peter Geoghegan <pg@bowt.ie>

From: "Peter Geoghegan" <pg@bowt.ie>

To: "Tomas Vondra" <tomas@vondra.me>

Cc: "Andres Freund" <andres@anarazel.de>, "Thomas Munro" <thomas.munro@gmail.com>, "Nazir Bilal Yavuz" <byavuz81@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-08-26T01:08:47Z

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

On Mon Aug 25, 2025 at 10:18 AM EDT, Tomas Vondra wrote:
> The attached patch is a PoC implementing this. The core idea is that if
> we measure "miss probability" for a chunk of requests, we can use that
> to estimate the distance needed to generate e_i_c IOs.

I noticed an assertion failure when the tests run. Looks like something about
the patch breaks the read stream from the point of view of VACUUM:

TRAP: failed Assert("stream->pinned_buffers + stream->pending_read_nblocks <= stream->max_pinned_buffers"), File: "../source/src/backend/storage/aio/read_stream.c", Line: 402, PID: 1238204
[0x55e71f653d29] read_stream_start_pending_read: /mnt/nvme/postgresql/patch/build_meson_dc/../source/src/backend/storage/aio/read_stream.c:401
[0x55e71f6533ad] read_stream_look_ahead: /mnt/nvme/postgresql/patch/build_meson_dc/../source/src/backend/storage/aio/read_stream.c:670
[0x55e71f652e9a] read_stream_next_buffer: /mnt/nvme/postgresql/patch/build_meson_dc/../source/src/backend/storage/aio/read_stream.c:1173
[0x55e71f34cd2b] lazy_scan_heap: /mnt/nvme/postgresql/patch/build_meson_dc/../source/src/backend/access/heap/vacuumlazy.c:1310
[0x55e71f34cd2b] heap_vacuum_rel: /mnt/nvme/postgresql/patch/build_meson_dc/../source/src/backend/access/heap/vacuumlazy.c:839
[0x55e71f49a3f4] table_relation_vacuum: ../source/src/include/access/tableam.h:1670
[0x55e71f49a3f4] vacuum_rel: /mnt/nvme/postgresql/patch/build_meson_dc/../source/src/backend/commands/vacuum.c:2296
[0x55e71f499e8f] vacuum: /mnt/nvme/postgresql/patch/build_meson_dc/../source/src/backend/commands/vacuum.c:636
[0x55e71f49931d] ExecVacuum: /mnt/nvme/postgresql/patch/build_meson_dc/../source/src/backend/commands/vacuum.c:468
[0x55e71f6a69f7] standard_ProcessUtility: /mnt/nvme/postgresql/patch/build_meson_dc/../source/src/backend/tcop/utility.c:862
[0x55e71f6a67d7] ProcessUtility: /mnt/nvme/postgresql/patch/build_meson_dc/../source/src/backend/tcop/utility.c:523
[0x55e71f6a630b] PortalRunUtility: /mnt/nvme/postgresql/patch/build_meson_dc/../source/src/backend/tcop/pquery.c:1153
[0x55e71f6a59b3] PortalRunMulti: /mnt/nvme/postgresql/patch/build_meson_dc/../source/src/backend/tcop/pquery.c:0
[0x55e71f6a52c5] PortalRun: /mnt/nvme/postgresql/patch/build_meson_dc/../source/src/backend/tcop/pquery.c:788
[0x55e71f6a4119] exec_simple_query: /mnt/nvme/postgresql/patch/build_meson_dc/../source/src/backend/tcop/postgres.c:1274
[0x55e71f6a1b84] PostgresMain: /mnt/nvme/postgresql/patch/build_meson_dc/../source/src/backend/tcop/postgres.c:0
[0x55e71f69c078] BackendMain: /mnt/nvme/postgresql/patch/build_meson_dc/../source/src/backend/tcop/backend_startup.c:124
[0x55e71f5e5eda] postmaster_child_launch: /mnt/nvme/postgresql/patch/build_meson_dc/../source/src/backend/postmaster/launch_backend.c:290
[0x55e71f5ea847] BackendStartup: /mnt/nvme/postgresql/patch/build_meson_dc/../source/src/backend/postmaster/postmaster.c:3587
[0x55e71f5ea847] ServerLoop: /mnt/nvme/postgresql/patch/build_meson_dc/../source/src/backend/postmaster/postmaster.c:1702
[0x55e71f5e86d9] PostmasterMain: /mnt/nvme/postgresql/patch/build_meson_dc/../source/src/backend/postmaster/postmaster.c:1400
[0x55e71f51acd9] main: /mnt/nvme/postgresql/patch/build_meson_dc/../source/src/backend/main/main.c:231
[0x7ff312633ca7] __libc_start_call_main: ../sysdeps/nptl/libc_start_call_main.h:58
[0x7ff312633d64] __libc_start_main_impl: ../csu/libc-start.c:360
[0x55e71f2e09a0] [unknown]: [unknown]:0

2025-08-25 21:05:28.915 EDT postmaster[1236725] LOG:  client backend (PID 1238204) was terminated by signal 6: Aborted
2025-08-25 21:05:28.915 EDT postmaster[1236725] DETAIL:  Failed process was running: VACUUM (PARALLEL 0, BUFFER_USAGE_LIMIT 128) test_io_vac_strategy;
2025-08-25 21:05:28.915 EDT postmaster[1236725] LOG:  terminating any other active server processes
2025-08-25 21:05:28.915 EDT postmaster[1236725] LOG:  all server processes terminated; reinitializing

__ 
Peter Geoghegan