Horton

Re: index prefetching

Peter Geoghegan <pg@bowt.ie>

View thread
From: Peter Geoghegan <pg@bowt.ie>
To: Tomas Vondra <tomas@vondra.me>
Cc: Nazir Bilal Yavuz <byavuz81@gmail.com>, Thomas Munro <thomas.munro@gmail.com>, 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>, Konstantin Knizhnik <knizhnik@garret.ru>, Dilip Kumar <dilipbalaut@gmail.com>
Date: 2025-07-22T20:28:37Z
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 Tue, Jul 22, 2025 at 1:35 PM Peter Geoghegan <pg@bowt.ie> wrote:
> What is the difference between cases like "linear / eic=16 / sync" and
> "linear_1 / eic=16 / sync"?

I figured this out for myself.

> One would imagine that these tests are very similar, based on the fact
> that they have very similar names. But we see very different results
> for each: with the former ("linear") test results, the "complex" patch
> is 2x-4x faster than the "simple" patch. But, with the latter test
> results ("linear_1", and other similar pairs of "linear_N" tests) the
> advantage for the "complex" patch *completely* evaporates. I find that
> very suspicious

Turns out that the "linear" test's table is actually very different to
the "linear_1" test's table (same applies to all of the other
"linear_N" test tables). The query that I posted earlier clearly shows
this when run against the test data [1].

The "linear" test's linear_a_idx index consists of leaf pages that
each point to exactly 21 heap blocks. That is a lot more than the
pgbench_accounts_pkey's 6 blocks. But it's still low enough to see a huge
advantage on Tomas' test -- an index scan like that can be 2x - 4x
faster with the "complex" patch, relative to the "simple" patch. I
would expect an even larger advantage with a similar range query that
ran against pgbench_accounts.

OTOH, the "linear_1" tests's linear_1_a_idx index shows leaf pages
that each have about 300 distinct heap blocks. Since the total number
of heap TIDs is always 366, it's absolutely not surprising that we can
derive little value from the "complex" patch's ability to eagerly read
more than one leaf page at a time -- a scan like that simply isn't going to
benefit from eagerly reading pages (or it'll only see a very small benefit).

In summary, the only test that has any significant ability to
differentiate the "complex" patch from the "simple" patch is the
"linear" test, which is 2x - 4x faster. Everything else seems to be
about equal, which is what I'd expect, given the particulars of the
tests. This even includes the confusingly named "linear_1" and other
"linear_N" tests.

[1] https://github.com/tvondra/iomethod-tests/blob/master/create2.sql

--
Peter Geoghegan