Horton

Re: index prefetching

Tomas Vondra <tomas@vondra.me>

View thread
From: Tomas Vondra <tomas@vondra.me>
To: Peter Geoghegan <pg@bowt.ie>, Andres Freund <andres@anarazel.de>
Cc: Nazir Bilal Yavuz <byavuz81@gmail.com>, Thomas Munro <thomas.munro@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-07-23T01:17:19Z
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 7/23/25 02:39, Peter Geoghegan wrote:
> On Tue, Jul 22, 2025 at 8:08 PM Andres Freund <andres@anarazel.de> wrote:
>> My response was specific to Tomas' comment that for many queries, which tend
>> to be more complicated than the toys we are using here, there will be CPU
>> costs in the query.
> 
> Got it. That makes sense.
> 
>>                     cheaper query       expensive query
>> simple readahead    8723.209 ms         10615.232 ms
>> complex readahead   5069.438 ms          8018.347 ms
>>
>> Obviously the CPU overhead in this example didn't completely eliminate the IO
>> bottleneck, but sure reduced the difference.
> 
> That's a reasonable distinction, of course.
> 
>> If your assumption is that real queries are more CPU intensive that the toy
>> stuff above, e.g. due to joins etc, you can see why the really attained IO
>> depth is lower.
> 
> Right.
> 
> Perhaps I was just repeating myself. Tomas seemed to be suggesting
> that cases where we'll actually get a decent and completely worthwhile
> improvement with the complex patch would be naturally rare, due in
> part to these effects with CPU overhead. I don't think that that's
> true at all.

It's entirely possible my mental model is too naive, or my intuition
about the queries is wrong ...

My mental model of how this works is that if I know the amount of time
T1 to process a page, and the amount of time T2 to handle an I/O, then I
can estimate when I should have submitted a read for a page. For example
if T1=1ms and T2=10ms, then I know I should submit an I/O ~10 pages
ahead in order to not have to wait. That's the "minimal" queue depth.

Of course, on high latency "cloud storage" the queue depth needs to
grow, because the time T1 to process a page is likely about the same (if
determined by CPU), but the T2 time for I/O is much higher. So we need
to issue the I/O much sooner.

When I mentioned "complex" queries, I meant queries where processing a
page takes much more time. Because it reads the page, and passes it to
other operators in the query plan, some of which may do CPU stuff, some
will trigger some synchronous I/O, etc. Which means T1 grows, and the
"minimal" queue depth decreases.

Which part of this is not quite right?


-- 
Tomas Vondra