Horton

Re: index prefetching

Peter Geoghegan <pg@bowt.ie>

View thread
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-19T22:27:56Z
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 19, 2025 at 2:22 PM Peter Geoghegan <pg@bowt.ie> wrote:
> That definitely seems like a problem. I think that you're saying that
> this problem happens because we have extra buffer hits earlier on,
> which is enough to completely change the ramp-up behavior. This seems
> to be all it takes to dramatically decrease the effectiveness of
> prefetching. Does that summary sound correct?

Update: Tomas and I discussed this over IM.

We ultimately concluded that it made the most sense to treat this
issue as a regression against set enable_indexscan_prefetch =
off/master. It was probably made a bit worse by the recent addition of
delaying creating a read stream (to avoid regressing pgbench SELECT)
with io_method=worker, though for me (with io_method=io_uring) it
makes things faster instead.

None of this is business with io_method seems important, since either
way there's a clear regression against set enable_indexscan_prefetch =
off/master. And we don't want those. So ultimately we need to
understand why mo prefetching wins by a not-insignificant margin with
this query.

Also, I just noticed that with a DESC/backwards scan version of Tomas'
query, things are vastly slower. But even then, fully synchronous
buffered I/O is still slightly faster.

-- 
Peter Geoghegan