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-14T19:41:58Z
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-14 15:15:02 -0400, Peter Geoghegan wrote:
> On Thu, Aug 14, 2025 at 2:53 PM Andres Freund <andres@anarazel.de> wrote:
> > I think this is just an indicator of being IO bound.
> 
> Then why does the exact same pair of runs show "I/O Timings: shared
> read=194.629" for the sequential table backwards scan (with total
> execution time 1132.360 ms), versus "I/O Timings: shared read=352.88"
> (with total execution time 697.681 ms) for the random table backwards
> scan?
> 
> Obviously it is hard to believe that the query with shared
> read=194.629 is one that is naturally much more I/O bound than another
> similar query that shows shared read=352.88. What "I/O Timings" shows
> more or less makes sense to me already -- it just doesn't begin to
> explain why *overall query execution* is much slower when scanning
> backwards sequentially.

Hm, that is somewhat curious.

I wonder if there's some wait time that's not being captured by "I/O
Timings". A first thing to do would be to just run strace --summary-only while
running the query, and see if there are syscall wait times that seem too long.

What effective_io_concurrency and io_max_concurrency setting are you using? If
there are no free IO handles that's currently not nicely reported (because
it's unclear how exactly to do so, see comment above pgaio_io_acquire_nb()).


> > Could you show iostat for both cases?
> 
> iostat has lots of options. Can you be more specific?

iostat -xmy /path/to/block/device

I'd like to see the difference in average IO size (rareq-sz), queue depth
(aqu-sz) and completion time (r_await) between the fast and slow cases.

Greetings,

Andres Freund