Horton

Re: index prefetching

Alexandre Felipe <o.alexandre.felipe@gmail.com>

View thread View on postgr.es
From: Alexandre Felipe <o.alexandre.felipe@gmail.com>
To: Tomas Vondra <tomas@vondra.me>
Cc: Andres Freund <andres@anarazel.de>, 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-02-16T05:30:00Z
Lists: pgsql-hackers

Attachments

Thank you for your comments Tomas and Andres,

I will try to be brief.

## Large table MacOS test

I did a 1000x larger test in MacOS, with the sql script . prefetch had
negligible effect for random and periodic, and made sequential 16% slower
[a].


## Small scale linux test.

I have repeated the tests with a 10k table row on a linux system, this time
I tried using either a SSD or a HDD, with shared_buffers of either 128kB or
128MB, and either psql or python with psycopg. I used a slightly different
table [b].

Prefetch makes...

SDD with low available buffers is significantly slower, except for
sequential reads.
SDD with cold OS reads is significantly faster for random access.
HDD faster, but not reliably (high variance).

I am not questioning the usefulness of the patch, and I know that there is
a log of work already put into it. The reason why I decided to review it is
because I believe this can be one important step forward. But I hope that
it is not hard to agree that these (counter)examples suggest that there are
some edges to be pruned. Where I work, most of the queries will access
at most a few hundred lines and are expected to be perceived as
instantaneous.

If the tests I am doing are pointless, should we consider having something
in the planner to prevent these scans from using prefetch?

Should we introduce centralized coordination for IO? As far as I know this
is an area where we just let each query request what they need and hope for
the best. What happens if we have two sequential scans in different tables?
the disk access could interleave pages of the two scans, falling into a
random access pattern, right?

### Cache control

This is a way to make the script run without sudo in linux, you give
ownership to root, and then you pin this program to the owner
% gcc drop_cache.c -o drop_cache;
% sudo chown root:root drop_cache;
% sudo chmod 4755 drop_cache;

In MacOS  purge in the sudoers[2] temporarily, similar to [3]
user ALL=(ALL) NOPASSWD: /usr/bin/purge
So that I don't need to give sudo privileges to the script (that imports
code that I am not even aware of).


Notes:

[a] I did some profiling with sample [4], and tried to spot functions with
the highest increase or decrease in run time, but I was too confused, no
point in dumping raw logs here.

[b] This time I used a (SELECT string_agg((i*j)::text, '+') FROM
generate_series(1, 50)) instead of repeat('x', 100), just to prevent it
from compressing to nothing when I try larger payloads, and hit the TOAST
thresholds. I removed the primary key `id` because it was annoying to take
20 minutes to insert the data in the large scale test.

References:

[1]
https://www.reddit.com/r/sysadmin/comments/pwiboy/whats_the_purpose_of_chmod_4755_binsh_command/
[2] https://man7.org/linux/man-pages/man5/sudoers.5.html
[3] https://unix.stackexchange.com/a/13058
[4] https://www.unix.com/man_page/osx/1/sample/


Regards,
Alexandre

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.