Horton

Re: index prefetching

Andres Freund <andres@anarazel.de>

View thread
From: Andres Freund <andres@anarazel.de>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Tomas Vondra <tomas@vondra.me>, 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-29T01:10:48Z
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.

Hi,

On 2025-08-28 19:57:17 -0400, Peter Geoghegan wrote:
> On Thu, Aug 28, 2025 at 7:52 PM Tomas Vondra <tomas@vondra.me> wrote:
> > 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.).
> 
> I found it quite hard to apply Thomas' patch. There's actually 3
> patches, with 2 earlier patches needed for earlier in the thread. And,
> there were significant merge conflicts to work around.

Same.  Tomas, could you share what you applied?


> I'm not sure that Thomas'/your patch to ameliorate the problem on the
> read stream side is essential here. Perhaps Andres can just take a
> look at the test case + feature branch, without the extra patches.
> That way he'll be able to see whatever the immediate problem is, which
> might be all we need.

It seems caused to a significant degree by waiting at low queue depths.  If I
comment out the stream->distance-- in read_stream_start_pending_read() the
regression is reduced greatly.

As far as I can tell, after that the process is CPU bound, i.e. IO waits don't
play a role.

I see a variety for increased CPU usage:

1) The private ref count infrastructure in bufmgr.c gets a bit slower once
   more buffers are pinned

2) signalling overhead to the worker - I think we are resetting the latch too
   eagerly, leading to unnecessarily many signals being sent to the IO worker.

3) same issue with the resowner tracking


But there's some additional difference in performance I don't yet
understand...


Greetings,

Andres Freund