[Bug]: ClickBench Q35 is significantly slower in MatrixOne than DuckDB

[daviszhen]

Parent issue

This is a sub-issue of #27665: significant gap in ClickBench query performance between MatrixOne and DuckDB.

Summary

ClickBench Q35 shows a significant MatrixOne performance gap against the DuckDB reference. The values below are elapsed seconds. Each result array is the three measurements emitted by the adapter: [run 1, run 2, run 3]; warm median is the median of the available run-2/run-3 values.

Query

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

Performance comparison

Engine	Run 1	Run 2	Run 3	Warm median
MatrixOne	55.370	54.120	57.060	55.590
DuckDB	15.413	1.164	1.136	1.150

• MatrixOne / DuckDB warm-median ratio: 48.34x
• Warm-median difference: 54.440 seconds
• Raw MatrixOne timing array: [55.370, 54.120, 57.060]
• Raw DuckDB timing array: [15.413, 1.164, 1.136]

Environment and data

MatrixOne measurement:

• Branch: main
• Commit: 249a458ba567b26d1818fb0496b449135cb05ef2
• Hardware: server 129
• OS: CentOS
• Dataset: ClickBench hits, 99,997,497 rows
• MatrixOne raw output: /mnt/fastdata/ClickBench/matrixone/raw_results.md

DuckDB reference:

• Result file: duckdb/results/20260511/c6a.metal.json
• Machine label: c6a.metal
• Result date: 2026-05-11

The two measurements were not collected on the same hardware. The DuckDB value is the latest local c6a.metal reference available; an apples-to-apples rerun on the same server is recommended before assigning a final performance regression magnitude.

Steps to reproduce

1. Follow the reproduction steps in #27665, including the MatrixOne and DuckDB adapter READMEs:
   • MatrixOne ClickBench README
   • DuckDB ClickBench README
2. Use the same hits.tsv.gz dataset for both engines (the adapters support loading the gzip file directly).
3. In the MatrixOne adapter, run ./benchmark.sh after setting MATRIXONE_HOME, MATRIXONE_DATA_FILE, MATRIXONE_HOST, MATRIXONE_PORT, MATRIXONE_USER, and MATRIXONE_PASSWORD as described in [Bug]: significant gap in clickbench query performance between matrixone and duckdb #27665.
4. In the DuckDB adapter, run ./benchmark.sh after setting DUCKDB_BIN_PATH, DUCKDB_DB_PATH, and DUCKDB_DATA_PATH as described in the README.
5. Compare the Q35 entry in queries.sql; run the query three times after loading the data and record elapsed time and whether the query completes successfully.

Notes

• The timing gap is the subject of this sub-issue; query-result correctness should be verified independently.

Expected behavior

On comparable hardware with the same dataset, MatrixOne should not exhibit a large query-level performance gap for this workload. Please investigate the execution plan, operators, memory use, and any spill or connection/timeout behavior relevant to this query.

[XuPeng-SH]

Validation and follow-up work have been decomposed into independently reviewable issues.

Fresh validation

Environment:

• host: 10.222.1.55
• 16 cores
• MatrixOne tree: dfef35f7dd94de507d40dd18082b64e7f4b84e40
• data and spill on NVMe
• 10,000,000 rows / 10,000,000 distinct VARCHAR URL values

The same SQL exhibited two different physical plans.

Plan without hash shuffle

• no shuffle: hash(URL)
• downstream MergeGroup
• MergeGroup time: approximately 9.19 seconds
• spill: 10,000,000 rows / 575.89 MiB
• warm runs: 10.941, 10.500, 10.698 seconds

Plan with hash shuffle

• shuffle: hash(URL)
• no downstream MergeGroup
• no spill
• warm runs: 3.017, 2.936, 2.848 seconds
• owner groups were imbalanced: min 363,803, max 1,013,198, max/min 2.78x
• aggregate output still materialized 10,000,000 rows / approximately 685.59 MiB before bounded TopK

This establishes three separate costs:

1. stale/incomplete statistics visibility can choose the wrong aggregation topology and trigger a global spill;
2. sampled VARCHAR shuffle hashing creates severe owner skew for common-prefix/suffix-varying keys;
3. even the no-spill plan performs avoidable high-NDV hash work and materializes every finalized group before retaining only 10 rows.

Follow-up issues

• [Enhancement]: Stabilize high-NDV GROUP BY planning and VARCHAR shuffle ownership #27728 — stabilize post-ANALYZE planning and full-key VARCHAR shuffle ownership.
• [Enhancement]: Add adaptive partition-parallel execution for exact DISTINCT aggregates #27720 — adaptive partition-parallel execution for high-NDV exact DISTINCT aggregation.
• [Enhancement]: Parallelize grouped-aggregation spill partition finalization #27729 — parallelize generic grouped-aggregation spill partition finalization.
• [Enhancement]: Fuse grouped-aggregate finalization with bounded TopK #27730 — fuse grouped-aggregate finalization with bounded TopK consumption.

#27729 and #27730 are intentionally separate:

• [Enhancement]: Parallelize grouped-aggregation spill partition finalization #27729 applies to generic spilled grouped aggregation, regardless of downstream ordering.
• [Enhancement]: Fuse grouped-aggregate finalization with bounded TopK #27730 applies only when the optimizer proves an exact Aggregate-to-TopK contract.
• They can compose, but have different correctness, ownership, lifecycle, and validation matrices.

Suggested sequencing

1. [Enhancement]: Stabilize high-NDV GROUP BY planning and VARCHAR shuffle ownership #27728 first, because it removes the observed plan/spill instability and fixes owner skew.
2. [Enhancement]: Parallelize grouped-aggregation spill partition finalization #27729 and [Enhancement]: Fuse grouped-aggregate finalization with bounded TopK #27730 may proceed independently after their design documents are reviewed.
3. [Enhancement]: Add adaptive partition-parallel execution for exact DISTINCT aggregates #27720 remains the separate adaptive exact-DISTINCT execution track.

The original full-scale Q35 dataset must be rerun on the same hardware after each relevant change. Unit tests should use small deterministic inputs and injected statistics, spill thresholds, failures, and ownership boundaries; do not add large-data or timing-only unit tests.

[XuPeng-SH]

Update: the generic statistics-publication and group-ownership work tracked by #27728 is implemented in #27744.

Validation on the existing NVMe-backed 10,000,000-row data set on 10.222.1.55:

• stale-plan reproduction: 10.698s median and 575.89 MiB spill;
• candidate after successful ANALYZE publication: 0.538s warm median and no spill;
• 16-owner max/min rows: 2.78x to 1.0061x;
• maximum owner memory: 137.53 MiB to 109.59 MiB;
• count, sum, min, and max result invariants match;
• known NDV=1 remains non-shuffle; missing NDV takes the conservative shuffle path;
• an existing session observes a successful ANALYZE from another session without reconnecting.

The production code contains no query, schema, data-size, string-format, or issue-specific conditions.

[XuPeng-SH]

Added #27753 for the remaining redundant-hash boundary exposed by the controlled #27744 measurements. It is separate from #27729 (parallel spill finalization) and #27730 (Aggregate-to-TopK fusion): #27753 addresses whether a canonical full grouping hash should be computed once and carried/reused across execution boundaries, with explicit negative controls for short keys and narrow remote rows. Suggested order remains #27744 first; #27753 requires design and cost-model review before implementation and does not block the independent spill or TopK work.

[XuPeng-SH]

Implementation/status correction (supersedes the earlier combined #27744 note):

• perf(shuffle): balance complete string-key ownership #27744 now contains only portable complete-key VARCHAR shuffle ownership. It preserves the exact v31 mapping and activates the new mapping at protocol v32.
• fix(frontend): publish ANALYZE stats by table generation #27758 contains only synchronous, table/account-scoped ANALYZE statistics publication and stale cache admission/writeback rejection.
• The missing-NDV conservative shuffle fallback was removed. Unknown NDV remains unknown; it is not evidence that paying for raw-row shuffle is beneficial.
• IVF-FLAT protocol gating was removed. Its existing full ObjectID owner mapping remains unchanged.
• [Enhancement]: Reuse canonical grouping hashes across shuffle and aggregation #27753 remains the separate, design-gated hash-reuse follow-up; [Enhancement]: Parallelize grouped-aggregation spill partition finalization #27729 and [Enhancement]: Fuse grouped-aggregate finalization with bounded TopK #27730 retain their independent spill-finalization and Aggregate-to-TopK scopes.

Final 55 evidence on the existing 10M-row NVMe data:

• fix(frontend): publish ANALYZE stats by table generation #27758 commit bf1608d70e: connection 1732 first compiled the NDV=1 control without shuffle; after another connection completed ANALYZE TABLE hits(URL) in 181ms, the same connection immediately compiled with shuffle: hash(hits.url) without reconnect/restart; execution had no MergeGroup spill.
• perf(shuffle): balance complete string-key ownership #27744 commit 26c0c9147b: v31/v32 owner max/min improved from 2.785x to 1.005x, max owner memory from 137.53 MiB to 109.58 MiB, and max group shard from 454ms to 388ms. The already non-spilling wall-time control changed from 0.514s to 0.544s (+5.8%) because complete hashing reads all key bytes.
• IVF-FLAT: unchanged top-10 results and Vector Index Scan; final fix(frontend): publish ANALYZE stats by table generation #27758 binary measured 44.42ms median per 50 warmed searches versus 44.85ms before the change.

#27728 now holds the detailed corrected invariants, implementation split, coverage matrix, and validation evidence.