Horton

Re: index prefetching

Andres Freund <andres@anarazel.de>

View thread
From: Andres Freund <andres@anarazel.de>
To: Tomas Vondra <tomas@vondra.me>
Cc: Peter Geoghegan <pg@bowt.ie>, 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-13T23:19:26Z
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 01:11:07 +0200, Tomas Vondra wrote:
> On 8/13/25 23:57, Peter Geoghegan wrote:
> > On Wed, Aug 13, 2025 at 5:19 PM Tomas Vondra <tomas@vondra.me> wrote:
> >> It's also not very surprising this happens with backwards scans more.
> >> The I/O is apparently much slower (due to missing OS prefetch), so we're
> >> much more likely to hit the I/O limits (max_ios and various other limits
> >> in read_stream_start_pending_read).
> > 
> > But there's no OS prefetch with direct I/O. At most, there might be
> > some kind of readahead implemented in the SSD's firmware.
> > 
> 
> Good point, I keep forgetting direct I/O means no OS read-ahead. Not
> sure if there's a good way to determine if the SSD can do something like
> that (and how well). I wonder if there's a way to do backward sequential
> scans in fio ..

In theory, yes, in practice, not quite:
https://github.com/axboe/fio/issues/1963

So right now it only works if you skip over some blocks. For that there rather
significant performance differences on my SSDs. E.g.

andres@awork3:~/src/fio$ fio --directory /srv/fio --size=$((1024*1024*1024)) --name test --bs=4k --rw read:8k --buffered 0 2>&1|grep READ
   READ: bw=179MiB/s (188MB/s), 179MiB/s-179MiB/s (188MB/s-188MB/s), io=341MiB (358MB), run=1907-1907msec
andres@awork3:~/src/fio$ fio --directory /srv/fio --size=$((1024*1024*1024)) --name test --bs=4k --rw read:-8k --buffered 0 2>&1|grep READ
   READ: bw=70.6MiB/s (74.0MB/s), 70.6MiB/s-70.6MiB/s (74.0MB/s-74.0MB/s), io=1024MiB (1074MB), run=14513-14513msec

So on this WD Red SN700 there's a rather substantial performance difference.

On a Samsung 970 PRO I don't see much of a difference. Nor on a ADATA
SX8200PNP.

Greetings,

Andres Freund