matrixorigin / matrixorigin/matrixone
[Enhancement]: Stabilize high-NDV GROUP BY planning and VARCHAR shuffle ownership
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
## 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:
```sql
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
- #27744 — portable complete-key VARCHAR shuffle ownership only;
- #27758 — synchronous, table/account-scoped `ANALYZE` statistics publication only;
- #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
- #27685
- #20560
- #27720
- #27744
- #27753
- #27758
Contributor guide
Assessment
This issue has not been assessed yet.