Horton

Re: index prefetching

Tomas Vondra <tomas@vondra.me>

View thread
From: Tomas Vondra <tomas@vondra.me>
To: Peter Geoghegan <pg@bowt.ie>
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-14T23:26:22Z
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 8/15/25 01:05, Peter Geoghegan wrote:
> On Thu, Aug 14, 2025 at 6:24 PM Tomas Vondra <tomas@vondra.me> wrote:
>> FWIW I'm not claiming this explains all odd things we're investigating
>> in this thread, it's more a confirmation that the scan direction may
>> matter if it translates to direction at the device level. I don't think
>> it can explain the strange stuff with the "random" data sets constructed
>> Peter.
> 
> The weird performance characteristics of that one backwards scan are
> now believed to be due to the WaitIO issue that Andres described about
> an hour ago. That issue seems unlikely to only affect backwards
> scans/reverse-sequential heap I/O.
> 

Good. I admit I lost track of which the various regressions may affect
existing plans, and which are specific to the prefetch patch.

> I accept that backwards scans are likely to be significantly slower
> than forwards scans on most/all SSDs. But that in itself doesn't
> explain why the same issue didn't cause the equivalent sequential
> forward scan to also be a lot slower. Actually, it probably *did*
> cause that forwards scan to be *somewhat* slower -- just not by enough
> to immediately jump out at me (not enough to make the forwards scan
> much slower than a scan that does wholly random I/O, which is
> obviously absurd).
> 

True. That's weird.

> My guess is that once we fix the underlying problem, we'll see
> improved performance for many different types of queries. Not as big
> of a benefit as the one that the broken query will get, but still
> enough to matter.
> 

Hopefully. Let's see.


regards

-- 
Tomas Vondra