[Bug]: ClickBench Q10 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 Q10 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 RegionID, SUM(AdvEngineID), COUNT(*) AS c, AVG(ResolutionWidth), COUNT(DISTINCT UserID) FROM hits GROUP BY RegionID ORDER BY c DESC LIMIT 10;

Performance comparison

Engine	Run 1	Run 2	Run 3	Warm median
MatrixOne	735.600	751.040	failed (null)	751.040
DuckDB	2.462	0.512	0.483	0.497

• MatrixOne / DuckDB warm-median ratio: 1509.63x
• Warm-median difference: 750.543 seconds
• Raw MatrixOne timing array: [735.600, 751.040, null]
• Raw DuckDB timing array: [2.462, 0.512, 0.483]

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 Q10 entry in queries.sql; run the query three times after loading the data and record elapsed time and whether the query completes successfully.

Notes

• MatrixOne has one failed measurement (ERROR 2013 (HY000): Lost connection to MySQL server during query); the third value is shown as null.

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]

55 机器上的最小复现（2026-08-26）

Q10 是 ClickBench 聚合查询，不是 TPC-H Q10：

SELECT RegionID, SUM(AdvEngineID), COUNT(*) AS c,
       AVG(ResolutionWidth), COUNT(DISTINCT UserID)
FROM hits
GROUP BY RegionID
ORDER BY c DESC
LIMIT 10;

为避免使用 1 亿行原始 ClickBench 数据，在 55（MatrixOne 8.0.30-MatrixOne-v4.0.0-rc4，SQL 端口 6001，单 CN 16 cores）建立了独立的最小投影表。Q10 实际只读取以下 4 列，列式扫描下这可以隔离聚合问题：

CREATE DATABASE issue27672_q10_repro;
CREATE TABLE issue27672_q10_repro.hits (
    RegionID INT NOT NULL,
    AdvEngineID SMALLINT NOT NULL,
    ResolutionWidth SMALLINT NOT NULL,
    UserID BIGINT NOT NULL
);

INSERT INTO issue27672_q10_repro.hits
SELECT mod(result - 1, 100) + 1,
       mod(result, 8),
       800 + mod(result, 1200),
       result
FROM generate_series(1, 1000000) g;

数据特征：1,000,000 行、100 个 RegionID、1,000,000 个 UserID（每个 region 约 10,000 个 distinct user）。

实测热查询耗时：

• 原始 Q10：8.2–9.2 秒（连续运行）
• 去掉 COUNT(DISTINCT UserID)：约 0.013 秒
• 只有 COUNT(DISTINCT UserID)：约 0.02–0.03 秒
• GROUP BY RegionID, COUNT(DISTINCT UserID)：约 0.03 秒
• GROUP BY RegionID, SUM(AdvEngineID), COUNT(DISTINCT UserID)：约 8.2 秒

EXPLAIN ANALYZE 将耗时定位到 Aggregate，而不是扫描、排序或返回结果：

Aggregate: timeConsumed=16405ms inputRows=1000000 outputRows=100 MemorySize=36.34 MiB
Table Scan: timeConsumed=4ms inputRows=1000000 ReadSize=4.23 MiB
Sort/Project: timeConsumed=0ms

对照计划显示，只有 DISTINCT 时会生成 GROUP BY RegionID, UserID 的两阶段聚合；混合普通聚合时则保留单个分组 Aggregate，导致每一行同时维护分组内 DISTINCT 状态。当前 optimizeDistinctAgg 对 AggList 长度大于 1 直接跳过改写，这与 Q10 的形状正好吻合。

因此这个最小数据集已经稳定复现 issue 的慢路径；1 亿行的 751 秒是该路径在更大输入和更高 distinct 基数下的规模放大，不能仅归因于磁盘或连接超时。该结果是同机实测，合成数据不用于宣称与 ClickBench 绝对倍数相同。

[XuPeng-SH]

Update: mixed aggregation slow path fixed by #27693

#27693 (perf: parallelize high-cardinality mixed DISTINCT aggregation) is now merged. It addresses the root cause identified in the minimal reproduction: a mixed aggregate containing mergeable COUNT(DISTINCT ...) was costed only by the final GROUP BY cardinality. With a modest number of final groups, the planner did not distribute ownership of complete groups, leaving expensive exact DISTINCT state merges on the critical path.

The fix preserves exact DISTINCT semantics and changes the topology only when the retained exact DISTINCT state is material relative to the input:

• estimate the COUNT(DISTINCT ...) state cardinality in addition to final group cardinality;
• for eligible, mergeable COUNT(DISTINCT) queries with enough active groups, shuffle by a group key so every logical group has one aggregate owner;
• eliminate the downstream merge of that group's exact DISTINCT state;
• retain the old topology for unsupported DISTINCT forms, inactive grouping-set keys, one physical owner, and cases without sufficient expected benefit.

10 million-row isolated validation on 55

The following is the same reduced Q10 shape used for diagnosis, scaled to 10,000,000 rows on the existing NVMe-backed isolated table: 100 balanced RegionID values and 10,000,000 unique UserID values.

SELECT RegionID, SUM(AdvEngineID), COUNT(*) AS c,
       AVG(ResolutionWidth), COUNT(DISTINCT UserID)
FROM issue27672_q10_repro.hits
GROUP BY RegionID
ORDER BY c DESC
LIMIT 10;

Revision	Run 1	Run 2	Run 3	Mean
Pre-#27693 baseline	98.792 s	101.389 s	102.119 s	100.77 s
#27693 implementation	2.439 s	2.276 s	2.301 s	2.34 s

This is a 43x reduction on the same data and machine. The execution plan uses a 16-core distributed aggregate topology; runtime sampling showed about 11–12 CPU cores in use during the query. The machine also had unrelated mo-service processes running, so this result is intentionally reported as a same-host before/after comparison rather than a clean-host absolute latency claim.

The result confirms that the issue's observed multi-aggregate Q10 regression was caused by aggregate-state ownership/merge topology, rather than the scan, sort, client, or storage path. The prior 1 million-row diagnosis remains a smaller reproducer of the same root cause.

Scope remaining after the fix

This fix obtains parallelism from complete final-group ownership. It is therefore the correct fast path for many balanced groups, but cannot fully parallelize these shapes:

SELECT COUNT(DISTINCT user_id) FROM events;            -- one final group

SELECT region_id, COUNT(DISTINCT user_id)
FROM events
GROUP BY region_id;                                    -- few or skewed groups

Those cases need partitioning by the canonical DISTINCT identity (group keys, DISTINCT arguments), not only by final group key. They also need a bounded-memory path when one group's exact state exceeds memory.

• [Enhancement]: Add adaptive partition-parallel execution for exact DISTINCT aggregates #27720 tracks adaptive partition-parallel exact DISTINCT execution for single/few/hot groups while retaining the perf: parallelize high-cardinality mixed DISTINCT aggregation #27693 fast path where it is cheaper.
• Support bounded distinct-key spill for high-NDV exact DISTINCT aggregates #27698 tracks compatible bounded-memory DISTINCT-key spill.

This issue remains open until the original full ClickBench-scale workload has been re-run on the merged revision under a controlled, comparable environment and the remaining single-hot-group/memory boundaries have been separately covered.