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>, 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-23T00:08:04Z
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-07-22 19:13:23 -0400, Peter Geoghegan wrote:
> On Tue, Jul 22, 2025 at 6:53 PM Andres Freund <andres@anarazel.de> wrote:
> > That may be true with local fast NVMe disks, but won't be true for networked
> > storage like in common clouds. Latencies of 0.3 - 4ms leave a lot of CPU
> > cycles for actual processing of the data.
>
> I don't understand why it wouldn't be a problem for NVMe disks, too.

> Take a range scan on pgbench_accounts_pkey, for example -- something
> like your ORDER BY ... LIMIT N test case, but with pgbench data
> instead of TPC-H data. There are 6 heap blocks per leaf page. As I
> understand it, the simple patch will only be able to see up to 6 heap
> blocks "into the future", at any given time. Why isn't that quite a
> significant drawback, regardless of the underlying storage?

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.

E.g. on my local NVMe SSD I get about 55k IOPS with an iodepth of 6 (that's
without stalls between leaf pages, so not really correct, but it's too much
math for me to compute).  If you have 6 heap blocks referenced per index
block, with 60 tuples on those heap pages and you can get 55k iops with that,
you can fetch 20 million tuples / second. If per-tuple CPU processing takes
longer 10**9/20_000_000 = 50 nanoseconds, you'll not be bottlenecked on
storage.

E.g. for this silly query:
SELECT max(abalance) FROM (SELECT * FROM pgbench_accounts ORDER BY aid LIMIT 10000000);

while also using io_combine_limit=1 (to actually see the achieved IO depth), I
see an achieved IO depth of ~6.3 (complex).

Whereas this even sillier query:
SELECT max(abalance), min(abalance), sum(abalance::numeric), avg(abalance::numeric), avg(aid::numeric), avg(bid::numeric) FROM (SELECT * FROM pgbench_accounts ORDER BY aid LIMIT 10000000);
only achieves an IO depth of ~4.1 (complex).


                    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.

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.



Btw, something with the batching is off with the complex patch.  I was
wondering why I was not seing 100% CPU usage while also not seeing very deep
queues - and I get deeper queues and better times with a lowered
INDEX_SCAN_MAX_BATCHES and worse with a higher one.

Greetings,

Andres Freund