Horton

Re: index prefetching

Peter Geoghegan <pg@bowt.ie>

View thread
From: Peter Geoghegan <pg@bowt.ie>
To: Tomas Vondra <tomas@vondra.me>
Cc: Nazir Bilal Yavuz <byavuz81@gmail.com>, Thomas Munro <thomas.munro@gmail.com>, Andres Freund <andres@anarazel.de>, 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-12T19:38:06Z
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 →

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

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

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

  4. read_stream: Prevent distance from decaying too quickly

  5. Reduce ExecSeqScan* code size using pg_assume()

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

  7. Fix multiranges to behave more like dependent types.

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

  9. Optimize nbtree backward scan boundary cases.

  10. Increment xactCompletionCount during subtransaction abort.

  11. Add nbtree Valgrind buffer lock checks.

  12. Add nbtree high key "continuescan" optimization.

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

  14. Teach btree to handle ScalarArrayOpExpr quals natively.

On Tue, Aug 12, 2025 at 1:51 PM Tomas Vondra <tomas@vondra.me> wrote:
> One more detail I just noticed - the DESC scan apparently needs more
> buffers (~87k vs. 57k). That probably shouldn't cause such massive
> regression, though.

I can reproduce this.

I wondered if the difference might be attributable to the issue with
posting lists and backwards scans (this index has fairly large posting
lists), which is addressed by this patch of mine:

https://commitfest.postgresql.org/patch/5824/

This makes the difference in buffers read identical between the
forwards and backwards scan case. However, it makes exactly no
difference to the execution time of the backwards scan case -- it's
still way higher.

I imagine that this is down to some linux readahead implementation
detail. Maybe it is more willing to speculatively read ahead when the
scan is mostly in ascending order, compared to when the scan is mostly
in descending order. The performance gap that I see is surprisingly
large, but I agree that it has nothing to do with this prefetching
work/the issue that I saw with backwards scans.

I had imagined that we'd be much less sensitive to these kinds of
differences once we don't need to depend on heuristic-driven OS
readahead. Maybe that was wrong.

-- 
Peter Geoghegan