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: Masahiro.Ikeda@nttdata.com, pgsql-hackers@lists.postgresql.org, Masao.Fujii@nttdata.com
Date: 2024-09-25T19:08:21Z
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

Attachments

On Sat, Sep 21, 2024 at 1:44 PM Peter Geoghegan <pg@bowt.ie> wrote:
> Attached is v8. I still haven't worked through any of your feedback,
> Tomas. Again, this revision is just to keep CFBot happy by fixing the
> bit rot on the master branch created by my recent commits.

Attached is v9.

I think that v9-0002-Normalize-nbtree-truncated-high-key-array-behavio.patch
is close to committable. It's basically independent work, which would
be nice to get out of the way soon.

Highlights for v9:

* Fixed a bug affecting scans that use scrollable cursors: v9 splits
the previous SK_BT_NEXTPRIOR sentinel scan key flag into separate
SK_BT_NEXT and SK_BT_PRIOR flags, so it's no longer possible to
confuse "foo"+infinitesimal with "foo"-infinitesimal when the scan's
direction changes at exactly the wrong time.

* Worked through all of Tomas' feedback.

In more detail:

- v9-0001-Show-index-search-count-in-EXPLAIN-ANALYZE.patch has been
taught to divide the total number of index searches by nloop as
required (e.g., for nested loop joins), per Tomas. This doesn't make
much difference, either way, so if that's what people want I'm happy
to oblige.

- Separately, the same EXPLAIN ANALYZE patch now shows "Index
Searches: 0" in cases where the scan node is never executed. (This was
already possible in cases where the scan node was executed, only for
_bt_preprocess_keys to determine that the scan's qual was
contradictory.)

- Various small updates to comments/symbol names, again based on
feedback from Tomas.

- Various small updates to the user sgml docs, again based on feedback
from Tomas.

- I've polished the commit messages for all 3 patches, particularly
the big one (v9-0003-Add-skip-scan-to-nbtree.patch).

- I haven't done anything about fixing any of the known regressions in
v9. I'm not aware that Tomas expects me to fix any regressions
highlighted by his recent testing (only regressions that I've been
aware of before Tomas became involved). Tomas should correct me if I
have that wrong, though.

Obviously, the #1 open item right now remains fixing the known
regressions in cases where skip scan should be attempted, but cannot
possibly help.

Thanks
-- 
Peter Geoghegan