Horton

Re: index prefetching

Tomas Vondra <tomas@vondra.me>

View thread
From: Tomas Vondra <tomas@vondra.me>
To: Andres Freund <andres@anarazel.de>
Cc: Thomas Munro <thomas.munro@gmail.com>, Peter Geoghegan <pg@bowt.ie>, 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-28T23:52:29Z
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.

Attachments

On 8/29/25 01:27, Andres Freund wrote:
> Hi,
> 
> On 2025-08-29 01:00:58 +0200, Tomas Vondra wrote:
>> I'm not sure how to determine what concurrency it "wants". All I know is
>> that for "warm" runs [1], the basic index prefetch patch uses distance
>> ~2.0 on average, and is ~2x slower than master. And with the patches the
>> distance is ~270, and it's 30% slower than master. (IIRC there's about
>> 30% misses, so 270 is fairly high. Can't check now, the machine is
>> running other tests.)
> 
> There got to be something wrong here, I don't see a reason why at any
> meaningful distance it'd be slower.
> 
> What set of patches do I need to repro the issue?
> 

Use this branch:

  https://github.com/tvondra/postgres/commits/index-prefetch-master/

and then Thomas' patch that increases the prefetch distance:


https://www.postgresql.org/message-id/CA%2BhUKGL2PhFyDoqrHefqasOnaXhSg48t1phs3VM8BAdrZqKZkw%40mail.gmail.com

(IIRC there's a trivial conflict in read_stream_reset.).

> And what are the complete set of pieces to load the data?
> https://postgr.es/m/293a4735-79a4-499c-9a36-870ee9286281%40vondra.me
> has the query, but afaict not enough information to infer init.sql
> 

Yeah, I forgot to include that piece, sorry. Here's an init.sql, that
loads the table, it also has the query.

> 
>> Not sure about wait events, but I don't think any backends are doing
>> sychnronous I/O. There's only that one query running, and it's using AIO
>> (except for the index, which is still read synchronously).
>>
>> Likewise, I don't think there's insufficient number of workers. I've
>> tried with 3 and 12 workers, and there's virtually no difference between
>> those. IIRC when watching "top", I've never seen more than 1 or maybe 2
>> workers active (using CPU).
> 
> That doesn't say much - if the they are doing IO, they're not on CPU...
> 

True. But one worker did show up in top, using a fair amount of CPU, so
why wouldn't the others (if they process the same stream)?


regards

-- 
Tomas Vondra