Horton

Re: Adding skip scan (including MDAM style range skip scan) to nbtree

Peter Geoghegan <pg@bowt.ie>

View thread
From: Peter Geoghegan <pg@bowt.ie>
To: Tomas Vondra <tomas@vondra.me>
Cc: Mark Dilger <mark.dilger@enterprisedb.com>, Heikki Linnakangas <hlinnaka@iki.fi>, pgsql-hackers@lists.postgresql.org, Matthias van de Meent <boekewurm+postgres@gmail.com>
Date: 2025-05-09T18:37: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. nbtree: Always set skipScan flag on rescan.

  2. meson: Build numeric.c with -ftree-vectorize.

  3. Fix "variable not found in subplan target lists" in semijoin de-duplication.

  4. Revert "nbtree: Remove useless row compare arg."

  5. nbtree: Remove useless row compare arg.

  6. Prevent premature nbtree array advancement.

  7. nbtree: tighten up array recheck rules.

  8. Avoid treating nonrequired nbtree keys as required.

  9. Adjust overstrong nbtree skip array assertion.

  10. Make NULL tuple values always advance skip arrays.

  11. Avoid extra index searches through preprocessing.

  12. Improve nbtree skip scan primitive scan scheduling.

  13. Further optimize nbtree search scan key comparisons.

  14. Add nbtree skip scan optimization.

  15. Improve nbtree array primitive scan scheduling.

  16. nbtree: Make BTMaxItemSize into object-like macro.

  17. Show index search count in EXPLAIN ANALYZE, take 2.

  18. Make parallel nbtree index scans use an LWLock.

  19. Show index search count in EXPLAIN ANALYZE.

  20. Avoid nbtree parallel scan currPos confusion.

  21. nbtree: Remove useless 'strat' local variable.

  22. Normalize nbtree truncated high key array behavior.

  23. Refactor handling of nbtree array redundancies.

  24. Fix nbtree pgstats accounting with parallel scans.

  25. Avoid parallel nbtree index scan hangs with SAOPs.

  26. Show Parallel Bitmap Heap Scan worker stats in EXPLAIN ANALYZE

  27. Enhance nbtree ScalarArrayOp execution.

  28. Skip checking of scan keys required for directional scan in B-tree

  29. Instead of using a numberOfRequiredKeys count to distinguish required

On Fri, May 9, 2025 at 2:04 PM Tomas Vondra <tomas@vondra.me> wrote:
> Yes, I'm sure it's doing index only scan

Looks that way, from the pair of flame graphs you sent. Thanks for that.

> did you update "bid" or did
> you leave it as generated by "pgbench -i"?.

I didn't bother with updating, or running VACUUM FULL. I did run
VACUUM ANALYZE, though (can confirm no heap accesses for the
index-only scans).

> In fact, all of the malloc() calls seem to happen in AllocSetAllocLarge,
> which matches the guess that something tripped over allocChunkLimit. Not
> sure what, though.

While there are way too many AllocSetAllocLarge calls here, I don't
think that that can be blamed on the skip scan work. Note that commit
92fe23d9 didn't even touch the BTScanOpaqueData struct. Its size did
change a bit, in other nearby commits, but it was already so large
that I don't think that it could matter here. Besides, you said that
the problem clearly starts in commit 92fe23d9.

The AllocSetAllocLarge calls that I see from gdb look like the slow
ones from your flame graph. They're for the BTScanOpaqueData
allocation, and for the BLCKSZ * 2 used by index-only scans (though
not index scans). These allocations happen once per rescan/index scan.
So, again, too many large allocations take place here, but it doesn't
look like anything that can be attributed to skip scan.

The difference shown by your flame graph is absolutely enormous --
that's *very* surprising to me. btbeginscan and btrescan go from being
microscopic to being very prominent. But skip scan simply didn't touch
either function, at all, directly or indirectly. And neither function
has really changed in any significant way in recent years. So right
now I'm completely stumped.

-- 
Peter Geoghegan