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-16T10:12:12Z
Lists: pgsql-hackers 
> How did you do that? Did you increase the number of rows, make the rows
> wider (by increasing the 'repeat' parameter in the script), or something
> else? Did you verify the table really is 1000x larger?
>

I increased the number of rows by 1000. I didn't really check the size of
the table, the time increase suggests that it was right.


> The "10k table row" means repeat('x',10000) when generating data? Oh, I
> see you're using some string_agg(), to make it not compress. But note
> that if it's TOASTed, it become entirely irrelevant for the prefetching
> test because it's in a separate relation.
>

sorry, 10k row table, for the payload I left a note

> [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.


> Unfortunately, you have not included the new script, so we can't try
> reproducing your results.
>

 Let me try to find something not so insane.

128kB shared buffers is a little bit ... insane. I refuse to optimize
> anything for this value, and I don't even call about regressions. Even
> 128MB is not really practical, any serious system caring about
> performance will use tens or hundreds of GBs of shared buffers.
>

I am not going to dispute that

> If the tests I am doing are pointless, should we consider having
> > something in the planner to prevent these scans from using prefetch?
> >
>
> How would you do that? Please explain.
>

I have no idea. But based on what you said I thought you would know. In my
head: "If my test seemed ridiculous to them all, they have some clear
boundaries in their mind that they could write in the planner".


> ... or you could modify the script to simply use sudo.
>
In that case sudo would request a password to the caller, and the caller is
a python script, no interaction there, of course I could do all the steps
manually, but it is more error prone (just my own mistakes are enough).


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.