apache-calcite - 没有足够的规则来生成具有所需属性的节点
问题描述
我想使用方解石火山规划器来优化查询。它不起作用并返回异常:
没有足够的规则来生成具有所需属性的节点:convention=NONE, sort=[]。所有输入都有相关节点,但是成本仍然是无限的。根:rel#18:RelSubset#4.NONE.[]
原始相对:
LogicalJoin(condition=[=($0, $3)], joinType=[inner]): rowcount = 81495.22499999999, 累积成本 = {686245.725 rows, 61452.0 cpu, 0.0 io}, id = 4
LogicalJoin(condition=[=($0, $1)], joinType=[inner]): rowcount = 543301.5, 累计成本 = {604750.5 rows, 61451.0 cpu, 0.0 io}, id = 2
LogicalTableScan(table=[[CALCITE_TEST, TTLA_ONE]]): rowcount = 59.0, 累计成本 = {59.0 rows, 60.0 cpu, 0.0 io}, id = 0
LogicalTableScan(table=[[CALCITE_TEST, TTLR_ONE]]): rowcount = 61390.0, 累积成本 = {61390.0 rows, 61391.0 cpu, 0.0 io}, id = 1
LogicalTableScan(table=[[CALCITE_TEST, EMPTY_T]]): rowcount = 1.0, 累积成本 = {0.0 rows, 1.0 cpu, 0.0 io}, id = 3
这是导致问题的代码:
val rootSchema = CalciteSchema.createRootSchema(true).plus
val schema = rootSchema.add("CALCITE_TEST", new AbstractSchema())
schema.add("TTLA_ONE", TableA())
schema.add("EMPTY_T", TableS())
schema.add("TTLR_ONE", TableR())
val config = Frameworks.newConfigBuilder.defaultSchema(schema).build
val builder = RelBuilder.create(config)
val opTree: RelNode = builder
.scan("TTLA_ONE")
.scan("TTLR_ONE")
.join(JoinRelType.INNER, "X")
.scan("EMPTY_T")
.join(JoinRelType.INNER, "X")
.build()
val rw = new RelWriterImpl(new PrintWriter(System.out, true))
opTree.explain(rw)
println()
val program = HepProgram.builder
.addRuleInstance(FilterJoinRule.FILTER_ON_JOIN).build
val hepPlanner = new HepPlanner(program)
hepPlanner.setRoot(opTree)
hepPlanner.findBestExp.explain(rw)
println()
val cluster = opTree.getCluster
val planner = cluster.getPlanner().asInstanceOf[VolcanoPlanner]
planner.setRoot(opTree)
// add rules
planner.addRule(PruneEmptyRules.PROJECT_INSTANCE)
// add ConverterRule
planner.addRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE)
planner.addRule(EnumerableRules.ENUMERABLE_SORT_RULE)
planner.addRule(EnumerableRules.ENUMERABLE_VALUES_RULE)
planner.addRule(EnumerableRules.ENUMERABLE_PROJECT_RULE)
planner.addRule(EnumerableRules.ENUMERABLE_FILTER_RULE)
planner.addRule(Bindables.BINDABLE_TABLE_SCAN_RULE)
val optimized = planner.findBestExp
optimized.explain(rw)
它产生输出 belove:
4:LogicalJoin(condition=[=($0, $3)], joinType=[inner])
2:LogicalJoin(condition=[=($0, $1)], joinType=[inner])
0:LogicalTableScan(table=[[CALCITE_TEST, TTLA_ONE]])
1:LogicalTableScan(table=[[CALCITE_TEST, TTLR_ONE]])
3:LogicalTableScan(table=[[CALCITE_TEST, EMPTY_T]])
10:LogicalJoin(condition=[=($0, $3)], joinType=[inner])
7:LogicalJoin(condition=[=($0, $1)], joinType=[inner])
0:LogicalTableScan(table=[[CALCITE_TEST, TTLA_ONE]])
1:LogicalTableScan(table=[[CALCITE_TEST, TTLR_ONE]])
3:LogicalTableScan(table=[[CALCITE_TEST, EMPTY_T]])
There are not enough rules to produce a node with desired properties: convention=NONE, sort=[]. All the inputs have relevant nodes, however the cost is still infinite.
Root: rel#18:RelSubset#4.NONE.[]
我在 VolcanoPlanner 上添加了一些规则,可能是什么问题?
解决方案
改变了
val cluster = opTree.getCluster
val planner = cluster.getPlanner().asInstanceOf[VolcanoPlanner]
planner.setRoot(opTree)
至
val cluster = opTree.getCluster
val desiredTraits = cluster.traitSet.replace(EnumerableConvention.INSTANCE)
val planner = cluster.getPlanner.asInstanceOf[VolcanoPlanner]
val newRoot = planner.changeTraits(opTree, desiredTraits)
planner.setRoot(newRoot)
通过介绍
val desiredTraits = cluster.traitSet.replace(EnumerableConvention.INSTANCE)
并创建一个新的根表单desiredTraits
val newRoot = planner.changeTraits(opTree, desiredTraits)
我还在查询中添加了一些预测,但这对于 Volcano 运行良好不是必需的。
这是现在的输出:
6:LogicalProject(X=[$0], X0=[$2])
5:LogicalJoin(condition=[=($0, $2)], joinType=[inner])
3:LogicalProject(X=[$0], X0=[$1])
2:LogicalJoin(condition=[=($0, $1)], joinType=[inner])
0:LogicalTableScan(table=[[CALCITE_TEST, TTLA_ONE]])
1:LogicalTableScan(table=[[CALCITE_TEST, TTLR_ONE]])
4:LogicalTableScan(table=[[CALCITE_TEST, EMPTY_T]])
16:LogicalProject(X=[$0], X0=[$2])
14:LogicalJoin(condition=[=($0, $2)], joinType=[inner])
11:LogicalProject(X=[$0], X0=[$1])
9:LogicalJoin(condition=[=($0, $1)], joinType=[inner])
0:LogicalTableScan(table=[[CALCITE_TEST, TTLA_ONE]])
1:LogicalTableScan(table=[[CALCITE_TEST, TTLR_ONE]])
4:LogicalTableScan(table=[[CALCITE_TEST, EMPTY_T]])
103:EnumerableProject(X=[$2], X0=[$0])
102:EnumerableHashJoin(condition=[=($0, $2)], joinType=[inner])
52:EnumerableTableScan(table=[[CALCITE_TEST, EMPTY_T]])
101:EnumerableProject(X=[$0], X0=[$1])
100:EnumerableMergeJoin(condition=[=($0, $1)], joinType=[inner])
33:EnumerableTableScan(table=[[CALCITE_TEST, TTLA_ONE]])
37:EnumerableTableScan(table=[[CALCITE_TEST, TTLR_ONE]])
推荐阅读
- python - 熊猫在 groupby 之后添加行号
- javascript - 使用角度的 RXJS 嵌套对象进行深度嵌套过滤的建议?
- python - Matplotlib 中 x-ticks 的均匀空间
- json - 在雪花 SQL 中提取 JSON 值
- java - ObjectMapper 将字符串值(json 格式)转换为对象
- google-sheets - 谷歌表中的多个 IFS 给出公式解析错误
- swift - UITableView 上的用户交互阻止进程
- html - 在 HTML 和 CSS 中创建空白
- networking - 如何在ns3中实现采样?
- excel - 在最后一行插入时间的宏