Andrey Minogin created CALCITE-7903:
---------------------------------------
Summary: Top-down planning of a 4-table join is ~170x slower with
EnumerableMergeJoinRule while the logical search is unchanged
Key: CALCITE-7903
URL: https://issues.apache.org/jira/browse/CALCITE-7903
Project: Calcite
Issue Type: Improvement
Components: core
Affects Versions: 1.42.0
Reporter: Andrey Minogin
Attachments: JoinChain.java
Follow-up to the dev@ thread "Article: why a flame graph attributes Calcite
planner time to the wrong rule" (28 Oct 2026), where Julian suggested logging a
performance issue.
Setup: Frameworks.getPlanner with Programs.standard() and default rules,
Calcite 1.42.0, JDK 21, with calcite.planner.topdown.opt=true and
calcite.enable.join.commute=true. A chain join of 4 declared tables with no
data (same query shape as the reproducer, 4 tables). The only difference
between the two columns is removing EnumerableMergeJoinRule.
||per plan||without merge join||with merge join||
|RelSets|137|137|
|JoinAssociateRule firings|340|340|
|RelSubsets|278|2,953|
|RelNodes in the memo|1,696|77,971|
|EnumerableLimitRule firings|-|61,514|
|planning time|0.4 s|70-80 s|
|best plan cumulative cost|1.21684E11|1.21681E11|
With 3 tables and merge join, planning takes 0.24 s. Without join commute, 4
tables take about 0.5 s.
The logical search is the same: the same RelSets and the same firings of the
join reordering rules. What grows is the number of physical alternatives (46x
more RelNodes), apparently from the collation trait, and the final plan differs
by 0.0025% in cost. With merge join, about 80% of planning time is spent
outside rule firings, and each logical rule firing costs about 80x more than
without it (FilterIntoJoinRule 0.67 ms vs 54 ms per firing), presumably because
every registration works against a much larger memo.
Is this growth expected for 4 tables, or is something enumerating more
collations than it needs to?
Per-rule time and firing counts were measured with TickSnick
([https://ticksnick.com|https://ticksnick.com/]), a sampling profiler with
per-operation labels, placed through RelOptListener. The memo sizes were read
from VolcanoPlanner after each plan. Times are from a 2-core Linux machine; the
ratios are what matters.
Related: CALCITE-7902 (the same property, calcite.enable.join.commute, makes
the non-top-down planner fail on this query).
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
