[Enhancement]: Stabilize high-NDV GROUP BY planning and VARCHAR shuffle ownership

[XuPeng-SH]

Motivation

The 10M-row Q35 reproduction exposed two independent ownership failures:

1. ANALYZE TABLE could report success without publishing the refreshed optimizer statistics to existing CN sessions, leaving a stale local-aggregate + MergeGroup plan that spilled the complete high-NDV state;
2. once hash shuffle was selected, VARCHAR ownership sampled a few byte positions, so common-prefix/sequential-suffix keys were distributed unevenly.

These require separate fixes. Missing NDV must not be treated as evidence of high NDV: forcing shuffle from an unknown estimate can materially regress low-cardinality workloads and is not part of the solution.

Reproduction evidence

Environment:

• host: 10.222.1.55
• 16 execution workers
• data and spill on NVMe
• 10,000,000 rows and 10,000,000 distinct VARCHAR URL values

Query:

SELECT 1, URL, COUNT(*) AS c
FROM hits
GROUP BY 1, URL
ORDER BY c DESC
LIMIT 10;

Stale statistics/plan state:

• no shuffle: hash(URL);
• downstream MergeGroup owned the global state;
• 10,000,000 rows / 575.89 MiB spilled;
• warm median 10.698s.

Fresh statistics selected hash shuffle and removed MergeGroup/spill, but legacy string ownership remained skewed:

• minimum groups per owner: 363,803;
• maximum groups per owner: 1,013,198;
• max/min ratio: 2.785x.

Required invariants

Statistics publication

• A successful ANALYZE TABLE is a synchronous publication boundary on the affected CN.
• A later ordinary statement on that CN sees the published table statistics without reconnecting or restarting.
• Only plans/statistics that actually depend on the analyzed (account, table) become stale.
• A compile or slow statistics read crossing publication cannot populate the new cache generation with old state.
• Failed, canceled, or missing refresh results do not invalidate usable cache entries.
• Same-table explicit and automatic refreshes cannot publish out of order.
• Process-lifetime metadata and admission state remain bounded.

String shuffle ownership

• Equal logical string keys map to exactly one owner during an execution.
• v32 ownership depends on the complete key and is deterministic across processes and CPU feature sets.
• v31 preserves the exact legacy mapping for mixed-version rollout and rollback.
• Common-prefix, common-suffix, sequential suffix, short/empty, UTF-8, and binary keys have bounded skew at DOP 1/2/8/16.
• The implementation is allocation-free per key; long-key work is explicitly linear in total bytes.
• Planner behavior for unknown NDV is unchanged.

IVF-FLAT boundary

• IVF-FLAT retains its existing full ObjectID ownership hash and does not use the new string-key protocol gate.
• IVF correctness, coverage, and warmed search performance must not regress.

Implementation split

• perf(shuffle): balance complete string-key ownership #27744 — portable complete-key VARCHAR shuffle ownership only;
• fix(frontend): publish ANALYZE stats by table generation #27758 — synchronous, table/account-scoped ANALYZE statistics publication only;
• [Enhancement]: Reuse canonical grouping hashes across shuffle and aggregation #27753 — optional future reuse of a canonical grouping hash across compatible operators/protocol boundaries.

Neither #27744 nor #27758 contains query text, schema name, URL shape, benchmark size, or issue-number conditions.

Latest validation

#27744 ownership

On the existing 10M-row NVMe data, twelve interleaved v31/v32 pairs measured:

ownership	median	owner max/min	max owner memory	max group shard
v31 sampled	0.514s	2.785x	137.53 MiB	454ms
v32 complete	0.544s	1.005x	109.58 MiB	388ms

The balanced mapping lowers maximum owner memory by about 20.3% and the slowest group shard by about 14.5%. The already non-spilling control pays about 5.8% wall-time for reading complete keys; this tradeoff is explicit rather than hidden.

#27758 publication

Final commit bf1608d70e on the same host:

1. patch hits.URL statistics to NDV=1;
2. connection 1732 compiles without hash shuffle;
3. a second connection runs ANALYZE TABLE hits(URL) successfully in 181ms;
4. connection 1732, without reconnect/restart, immediately compiles with shuffle: hash(hits.url);
5. execution has no MergeGroup and no spill.

Version validation is allocation-free and measures about 47ns for one table, 56ns for four tables, and 131–136ns for sixteen tables. A zero-dependency cached plan takes a lock-free ~1.5ns path.

IVF-FLAT

The IVF implementation/ownership path is unchanged. A 50,000-row, 64-list index on the same host returns identical top-10 results and uses Vector Index Scan; the final #27758 binary measured 44.42ms median per 50 warmed searches versus 44.85ms before the change.

Test requirements

• deterministic, orthogonal unit tests with shared setup;
• no sleeps, million-row unit fixtures, or timing-only assertions;
• race repetition for publication/admission/cache boundaries;
• exact base/candidate statement coverage comparison;
• large scaling evidence remains an integration/benchmark check, not a unit test.

Related

• [Bug]: ClickBench Q35 is significantly slower in MatrixOne than DuckDB #27685
• [Tech Request]: refactoring shuffle plan #20560
• [Enhancement]: Add adaptive partition-parallel execution for exact DISTINCT aggregates #27720
• perf(shuffle): balance complete string-key ownership #27744
• [Enhancement]: Reuse canonical grouping hashes across shuffle and aggregation #27753
• fix(frontend): publish ANALYZE stats by table generation #27758

[XuPeng-SH]

Implementation is available in #27744. The PR covers the ANALYZE publication epoch, stale plan/statistics cache admission, conservative unknown-NDV group shuffle, and complete-key VARCHAR ownership. It includes the required deterministic key-shape/DOP matrix, low-cardinality controls, race repetitions, coverage comparison, and the 10M NVMe integration result.

[XuPeng-SH]

Latest #27744 validation on 10.222.1.55 used the existing 10M-row / 10M-NDV data and NVMe state; no data was regenerated. The tested production commit is ab35c60e75 (the current PR head only adds the 32-byte benchmark case after that commit).

The optimized planning/publication path remains a large gain against the reported failure mode: 10.698s median with 575.89 MiB spill becomes about 0.589s with no spill, approximately 18.2x faster.

The complete-key owner hash has an explicit tradeoff. Twenty interleaved v31/v32 queries on the same binary, process, cache, and data measured 0.549s median for legacy sampled ownership and 0.589s for the new batched complete-key ownership (+7.3%). It is therefore not claimed as a universal latency win for already non-spilling string shuffles.

The corresponding EXPLAIN ANALYZE ownership results were:

• owner max/min rows: 2.785x -> 1.0024x;
• max owner memory: 137.53 MiB -> 109.58 MiB (-20.3%);
• max per-owner group time: 467ms -> 412ms (-11.8%);
• no spill in either refreshed-plan control;
• total group memory: 1.55 GiB -> 1.74 GiB because balanced owners reach similar hash-table capacities.

The implementation now batches the full-key hash through hardware AES, produces zero allocations, and has an exactly equivalent software fallback. The real 32-byte shape measures about 2.8ns/key for hashing and 5.7ns/key for hash plus shuffle selection. v31/v32/rollback mappings are protocol-gated and tested; numeric, low-NDV non-shuffle, and non-string paths are unaffected.

The PR body has been updated with the full benefit/cost matrix rather than presenting only the favorable tail-risk result.

[XuPeng-SH]

Follow-up boundary: #27753 now owns the generic, cost-aware reuse of a canonical grouping hash across Shuffle, exchange, Group admission, and compatible spill/repartition stages. #27744 intentionally remains focused on statistics publication and balanced complete-key ownership. Hash reuse must prove full grouping-identity compatibility and a net benefit over carrying an additional 8-byte value; subset-key and unfavorable transport cases retain receiver recomputation.

[XuPeng-SH]

Implementation update (supersedes the earlier combined-PR notes):

• perf(shuffle): balance complete string-key ownership #27744 now contains only complete-key VARCHAR shuffle ownership. The unsafe unknown-NDV forcing, ANALYZE publication code, hardware/software split, and IVF protocol gating were removed.
• fix(frontend): publish ANALYZE stats by table generation #27758 contains only table/account-scoped synchronous ANALYZE publication, stale cache admission/writeback rejection, and bounded same-table refresh ordering.
• IVF-FLAT remains on its existing ObjectID hash. Both PRs include an explicit IVF regression gate.
• [Enhancement]: Reuse canonical grouping hashes across shuffle and aggregation #27753 remains the separate cost-aware hash-reuse follow-up.

The issue body now contains the corrected boundaries, final commits, exact coverage/performance evidence, and 55 reproduction results.