Horton

Re: index prefetching

Peter Geoghegan <pg@bowt.ie>

View thread
From: Peter Geoghegan <pg@bowt.ie>
To: Tomas Vondra <tomas@vondra.me>
Cc: Thomas Munro <thomas.munro@gmail.com>, Andres Freund <andres@anarazel.de>, 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-13T21:36:24Z
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 Wed, Aug 13, 2025 at 1:01 PM Tomas Vondra <tomas@vondra.me> wrote:
> This seems rather bizarre, considering the two tables are exactly the
> same, except that in t2 the first column is negative, and the rows are
> fixed-length. Even heap_page_items says the tables are exactly the same.
>
> So why would the index get so different like this?

In the past, when I required *perfectly* deterministic results for
INSERT INTO test_table ... SELECT * FROM source_table bulk inserts
(which was important during the Postgres 12 and 13 nbtree work), I
found it necessary to "set synchronize_seqscans=off". If I was writing
a test such as this, I'd probably do that defensively, even if it
wasn't clear that it mattered. (I'm also in the habit of using
unlogged tables, because VACUUM tends to set their pages all-visible
more reliably than equivalent logged tables, which I notice that
you're also doing here.)

That said, I *think* that the "locally shuffled" heap TID pattern that
we see with "t2"/"idx2" is mostly (perhaps entirely) caused by the way
that you're inverting the indexed column's value when initially
generating "t2". A given range of values such as "1 through to 4"
becomes "-4 through to -1" as their tuples are inserted into t2.
You're effectively inverting the order of the bigint indexed column
"a" -- but you're *not* inverting the order of the imaginary
tie-breaker heap column (it *remains* in ASC heap TID order in "t2").

In general, when doing this sort of analysis, I find it useful to
manually verify that the data that I generated matches my
expectations. Usually a quick check with pageinspect is enough. I'll
just randomly select 2 - 3 leaf pages, and make sure that they all
more or less match my expectations.

-- 
Peter Geoghegan