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>, Robert Haas <robertmhaas@gmail.com>, Melanie Plageman <melanieplageman@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Georgios <gkokolatos@protonmail.com>, Thomas Munro <thomas.munro@gmail.com>, Konstantin Knizhnik <knizhnik@garret.ru>, Dilip Kumar <dilipbalaut@gmail.com>
Date: 2025-07-16T14:37:42Z
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/16/25 16:29, Peter Geoghegan wrote:
> On Wed, Jul 16, 2025 at 10:20 AM Tomas Vondra <tomas@vondra.me> wrote:
>> The read stream can only return blocks generated by the "next" callback.
>> When we return the block for the last item on a leaf page, we can only
>> return "InvalidBlockNumber" which means "no more blocks in the stream".
>> And once we advance to the next leaf, we say "hey, there's more blocks".
>> Which is what read_stream_reset() does.
>>
>> It's a bit like what rescan does.
> 
> That sounds weird.
> 

What sounds weird? That the read_stream works like a stream of blocks,
or that it can't do "pause" and we use "reset" as a workaround?


>> In an ideal world we'd have a function that'd "pause" the stream,
>> without resetting the distance etc. But we don't have that, and the
>> reset thing was suggested to me as a workaround.
> 
> Does the "complex" patch require a similar workaround? Why or why not?
> 

I think it'll need to do something like that in some cases, when we need
to limit the number of leaf pages kept in memory to something sane.

(a) index-only scans, with most of the tuples all-visible (we don't
prefetch all-visible pages, so finding the next "prefetchable" block may
force reading a lot of leaf pages)

(b) scans on correlated indexes - we skip duplicate block numbers, so
again, we may need to read a lot of leafs to find enough prefetchable
blocks to reach the "distance" (measured in queued blocks)

(c) indexes with "fat" index tuples (but it's less of an issue, because
with one tuple per leaf we still have a clear idea how many leafs we'll
need to read)


regards

-- 
Tomas Vondra