Horton

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

Tomas Vondra <tomas@vondra.me>

View thread
From: Tomas Vondra <tomas@vondra.me>
To: Peter Geoghegan <pg@bowt.ie>
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-09T16:28:49Z
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 5/9/25 17:55, Peter Geoghegan wrote:
> On Fri, May 9, 2025 at 10:57 AM Tomas Vondra <tomas@vondra.me> wrote:
>> I see the regression even with variants that actually match some rows.
>> For example if I do this:
> 
>> so that the query matches 100 rows, I get the same behavior.
> 
> That's really weird, since the index scans will no longer be cheap.
> And yet whatever the overhead is still seems to be plainly visible. I
> would expect whatever the underlying problem is to be completely
> drowned out once the index scan had to do real work.
> 

Not sure if it matters, but this uses index-only scans, and the pages
are all-visible, so maybe it's not much more expensive.

> I wonder if it could be due to the fact that I added a new support
> function, support function #6/skip support? That would have increased
> the size of things like RelationData.rd_support and
> RelationData.rd_supportinfo.
> 
> Note that "sizeof(FmgrInfo)" is 48 bytes. Prior to skip scan,
> RelationData.rd_supportinfo would have required 48*5=240 bytes. After
> skip scan, it would have required 48*6=288 bytes. Maybe 256 bytes is
> some kind of critical threshold, someplace?
> 

Not sure, I did multiple tests with different queries, and it'd be a bit
strange if this was the only one affected. Not impossible.

The theory about crossing the 256B threshold is interesting. I've been
thinking about ALLOC_CHUNK_LIMIT = 8KB, which is what's making the
BTScanOpaque expensive. But there's also ALLOC_CHUNK_FRACTION, which is
1/4. So maybe there's a context with maxBlockSize=1kB? But I think most
places use the size macros, and ALLOCSET_SMALL_MAXSIZE is 8KB.


regards

-- 
Tomas Vondra