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-09T14:15:03Z
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 15:59, Peter Geoghegan wrote:
> On Fri, May 9, 2025 at 9:42 AM Peter Geoghegan <pg@bowt.ie> wrote:
>> I'm rather puzzled as to why this happens, then. I expect that nbtree
>> preprocessing will be able to use its usual single index column/index
>> key fast path here -- the "We can short-circuit most of the work if
>> there's just one key" path in _bt_preprocess_keys (and I expect that
>> _bt_num_array_keys() quickly determines that no skip arrays should be
>> added, preventing array preprocessing from ever really starting).
> 
> You've been testing commit 92fe23d9 ("Add nbtree skip scan
> optimization") here, but I think you should test commit 8a510275
> ("Further optimize nbtree search scan key comparisons") instead. The
> former commit's commit message says that there big regressions, that
> the latter commit should go on to fix. Note that these two commits
> were pushed together, as a unit. All of my performance validation work
> was for the patch series as a whole, not for any individual commit.
> 
> I don't actually think that this kind of scan would have been affected
> by those known regressions -- since they don't use array keys. But it
> is definitely true that the queries that you're looking at very much
> rely on the optimization from commit 8a510275 (or its predecessor
> optimization, the "pstate.prechecked" optimization). As I said, my
> performance validation didn't target individual commits.
> 

I initially compared 17 to current master, but after discovering the
regression I bisected to the actual commit. That's how I ended up with
92fe23d93aa. This is how it looks for current master (bc35adee8d7):


  mode       #c    3ba2cdaa454     92fe23d93aa      bc35adee8d7
  -------------------------------------------------------------
  simple      1           2617            1832             1899
              4           8332            6260             6143
             32          11603            7110             7193
  -------------------------------------------------------------
  prepared    1          11113            3646             3655
              4          25379           11375            11342
             32          37319           14097            13911

There's almost no difference between bc35adee8d7 and 92fe23d93aa.


regards

-- 
Tomas Vondra