[Bug] 4.1.1 版本 FE 规划阶段（Nereids Rewrite） 消耗很多的时间

[linzhisheng]

Search before asking

• I had searched in the issues and found no similar issues.

Version

doris 4.1.1 版本

What's Wrong?

从doris 2.1.8 版本升级到 从doris 4.1.1 版本后相同的SQL 执行时间从 2秒变到了10秒。排查问题发现 doris 4.1.1 版本 FE 规划阶段（Nereids Rewrite）花了8秒多。这个问题随着查询字段的增加进一步恶化 但是 2.1.8版本则没有类似的问题发生。附件是doris 4.1.1 的profile文件和explain 文件

query_result_doris4_慢.csv

profile.txt

What You Expected?

希望能优化FE 规划阶段（Nereids Rewrite）或者是否可以通过修改配置参数解决这个问题

How to Reproduce?

No response

Anything Else?

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[zclllyybb]

Breakwater-GitHub-Analysis-Slot: slot_45846e1babf3
This content is generated by AI for reference only.

Initial triage:

The attached profile supports the reporter's observation: this is dominated by FE planning, specifically the Nereids rewrite phase, not by BE execution. In the profile for doris-4.1.1-rc01-b10073ad9ca, total time is 8sec788ms, with Plan Time: 8sec390ms and Nereids Rewrite Time: 8sec266ms. Other FE phases are much smaller (Parse SQL Time: 23ms, Nereids Analysis Time: 71ms, Nereids Translate Time: 24ms, Schedule Time: 138ms, Wait and Fetch Result Time: 237ms).

The attached CSV is an explain output. The plan is very wide and complex: an outer select *, count(*) over() ... order by ... limit 20 over a large aggregate result, many repeated sum/if/cast expressions, several joins and union/aggregate branches, 19 fragments, 937 instances, and the explain ends with planned with unknown column statistics. The reporter's note that planning worsens as selected fields increase is consistent with rewrite-time amplification over a very wide projection/aggregate plan.

Code-path check on the 4.1.1 tag: NereidsPlanner.rewrite() runs Rewriter.getWholeTreeRewriter(cascadesContext).execute() and then records Nereids Rewrite Time. In this version, that whole-tree rewrite includes join rewrite, set-operation optimization, column pruning/predicate inference, project/join rewrites, aggregate pushdown/eager aggregation, and other heuristic rewrite jobs. Therefore the profile only proves that the RBO rewrite stage is expensive; it does not yet prove which specific rule is responsible.

Current conclusion:

This looks like a real 4.1.1 Nereids rewrite performance regression or at least a high-risk rewrite scalability case for wide aggregation/join/union plans. I would not recommend a blind configuration change yet. nereids_timeout_second would only avoid timeout, not reduce the 8s planning latency, and enable_nereids_planner is marked removed in 4.1.1. A targeted disable_nereids_rules workaround may be possible, but only after identifying the slow rule from trace evidence.

Recommended next checks:

1. Ask the reporter for the complete original SQL text, the 2.1.8 profile/explain for the same SQL, and table DDL/statistics state (SHOW CREATE TABLE, relevant column stats or ANALYZE status).
2. Re-run on 4.1.1 with Nereids tracing enabled, for example set enable_nereids_trace=true; set nereids_trace_event_mode='all';, then attach the trace JSON from the FE nereids_trace_log_dir for the query id. This is needed to identify the exact rewrite rule or topic consuming the 8s.
3. As a mitigation validation, try reducing the projection width: avoid the outer select *, return only required columns, and test whether count(*) over() can be split into a separate count query. This should not be treated as the product fix, but it can confirm that wide projection/window rewrite is the main amplifier.
4. If trace shows a single dominant rewrite rule, test disable_nereids_rules=<RULE_TYPE> only for that rule as a temporary workaround and compare plan time plus result correctness.

Maintainer-side follow-up should focus on reproducing this query shape on 4.1.1/upstream and profiling the Nereids whole-tree rewrite jobs over wide projections and aggregate/join/union branches.

[linzhisheng]

@zclllyybb
1.以下是doris4.1 和 doris2.1执行相同SQL的 profile
2.附件是SQL和SQL中使用到的相关表的建表语句
3.目前从profile可以看出FE规划阶段（Nereids Rewrite）性能差距极大

profile_doris4.txt

profile_doris2.txt

sql_related_create_tables.sql

sql.sql