Horton

Re: index prefetching

Andres Freund <andres@anarazel.de>

View thread View on postgr.es
From: Andres Freund <andres@anarazel.de>
To: Alexandre Felipe <o.alexandre.felipe@gmail.com>
Cc: Tomas Vondra <tomas@vondra.me>, Peter Geoghegan <pg@bowt.ie>, 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: 2026-03-02T19:29:16Z
Lists: pgsql-hackers 
Hi,

On 2026-03-02 18:45:03 +0000, Alexandre Felipe wrote:
> > Unfortunately that has too big a performance penalty for fully cached
> > workloads :(. Doing buffer mapping lookups ahead of the current point is
> not
> > free.
>
> Could you elaborate?

Looking ahead requires pinning those buffers (or you need to do more buffer
lookups, which also isn't free). The way things work right now is that the
infrastructure to maintain buffer pins gets more expensive the more buffers
you pin.  That increased overhead doesnt' matter much if you actually avoid IO
or you have fair bit of work for each buffer (like looking at each tuple), but
if you just are fully cached and just look at a single tuple, it's painful.


CREATE TABLE uncorrelated(id int8 primary key, balance float not null);
INSERT INTO uncorrelated(id, balance) SELECT id, 0 FROM (SELECT generate_series(1, 1000000) AS id) ORDER BY random();
VACUUM (FREEZE, ANALYZE) uncorrelated;

CREATE TABLE correlated(id int8 primary key, balance float not null);
INSERT INTO correlated(id, balance) SELECT id, 0 FROM (SELECT generate_series(1, 1000000) AS id);
VACUUM (FREEZE, ANALYZE) correlated;


SELECT pg_prewarm('uncorrelated');
SET max_parallel_workers_per_gather=0;SET enable_bitmapscan = 0;SET enable_seqscan = 0;
SELECT sum(balance) FROM uncorrelated WHERE id > 1000 and id < 500000;

SELECT pg_prewarm('correlated');
SET max_parallel_workers_per_gather=0;SET enable_bitmapscan = 0;SET enable_seqscan = 0;
SELECT sum(balance) FROM correlated WHERE id > 1000 and id < 500000;


Index prefetching patch:
uncorrelated: 228.936 ms
correlated:   71.684  ms

+ minimum distance 2:
uncorrelated: 244.361 ms
correlated:   71.848 ms

+ minimum distance 4:
uncorrelated: 246.164 ms
correlated:   71.912 ms

+ minimum distance 8:
uncorrelated: 268.686 ms
correlated:   71.880 ms

+ minimum distance 16:
uncorrelated: 310.320 ms
correlated:   71.890 ms

+ minimum distance 32:
uncorrelated: 330.714 ms
correlated:   74.178 ms


There starts to be some overhead of the increased distance even when
correlated, but it's small and starts much later.


We probably can reduce the overhead of buffer tracking, but probably not
enough for it to not be a factor at all.


Possible improvements to refcount tracking:

- increase REFCOUNT_ARRAY_ENTRIES - there's a very significant cliff at 8
  right now, and with vectorized lookup it might not hurt too much to go to 16
  or so

- To make the cliff at REFCOUNT_ARRAY_ENTRIES smaller, replace dynahash with
  simplehash. That should reduce the perf penalty a good bit.


Unfortunately it's not just the refcount tracking, it's also resowner
management that gets more expensive.

Greetings,

Andres Freund

Commits

  1. read stream: Split decision about look ahead for AIO and combining

  2. read_stream: Only increase read-ahead distance when waiting for IO

  3. aio: io_uring: Trigger async processing for large IOs

  4. heapam: Keep buffer pins across index scan resets.

  5. heapam: Track heap block in IndexFetchHeapData.

  6. Move heapam_handler.c index scan code to new file.

  7. Rename heapam_index_fetch_tuple argument for clarity.

  8. Optimize fast-path FK checks with batched index probes

  9. read_stream: Prevent distance from decaying too quickly

  10. read_stream: Issue IO synchronously while in fast path

  11. bufmgr: Return whether WaitReadBuffers() needed to wait

  12. aio: io_uring: Allow IO methods to check if IO completed in the background

  13. bufmgr: Make UnlockReleaseBuffer() more efficient

  14. Add fake LSN support to hash index AM.

  15. Make IndexScanInstrumentation a pointer in executor scan nodes.

  16. Use fake LSNs to improve nbtree dropPin behavior.

  17. Move fake LSN infrastructure out of GiST.

  18. Use simplehash for backend-private buffer pin refcounts.

  19. nbtree: Avoid allocating _bt_search stack.

  20. bufmgr: Fix use of wrong variable in GetPrivateRefCountEntrySlow()

  21. Conditional locking in pgaio_worker_submit_internal

  22. Reduce ExecSeqScan* code size using pg_assume()

  23. Fix rare bug in read_stream.c's split IO handling.

  24. Remove HeapBitmapScan's skip_fetch optimization

  25. Optimize nbtree backwards scans.

  26. Fix multiranges to behave more like dependent types.

  27. Add EXPLAIN (MEMORY) to report planner memory consumption

  28. Optimize nbtree backward scan boundary cases.

  29. Increment xactCompletionCount during subtransaction abort.

  30. Add nbtree Valgrind buffer lock checks.

  31. Add nbtree high key "continuescan" optimization.

  32. Reduce pinning and buffer content locking for btree scans.

  33. Teach btree to handle ScalarArrayOpExpr quals natively.