This is an automated email from the ASF dual-hosted git repository.
englefly pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 056fffbc6b6 [opt](nereids) add cost penalty for pattern
Aggregation-nestedLoopJoin (#51127)
056fffbc6b6 is described below
commit 056fffbc6b6cbdfb811ae28ca659c9c85921a2d5
Author: minghong <zhoumingh...@selectdb.com>
AuthorDate: Fri May 30 10:34:51 2025 +0800
[opt](nereids) add cost penalty for pattern Aggregation-nestedLoopJoin
(#51127)
### What problem does this PR solve?
lets illustrate this pr by an example
`select count() from bigTable join smallTable on big.B > small.A group
by B, A`
where A is distribute key, but B is not.
before this pr, we choose join order : smallTable join bigTable.
after this pr, we choose bigTable join smallTable.
Join order "smallTable join bigTable" is friendly to aggregation but
unfriendly to join. However, since the estimated number of rows for the
join is very large, the benefits of aggregation outweigh the losses of
the join in the estimation system. Nevertheless, incorrect join order
may lead to catastrophic consequences in actual operation, so we do not
choose this join order.
---
.../org/apache/doris/nereids/cost/CostModelV1.java | 17 ++++-
.../java/org/apache/doris/qe/SessionVariable.java | 3 -
regression-test/data/shape_check/others/nlj.out | Bin 0 -> 481 bytes
.../suites/shape_check/others/nlj.groovy | 73 +++++++++++++++++++++
4 files changed, 89 insertions(+), 4 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/cost/CostModelV1.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/cost/CostModelV1.java
index 09e4d7e6773..5a7023dc749 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/cost/CostModelV1.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/cost/CostModelV1.java
@@ -559,9 +559,24 @@ class CostModelV1 extends PlanVisitor<Cost, PlanContext> {
Preconditions.checkState(context.arity() == 2);
Statistics leftStatistics = context.getChildStatistics(0);
Statistics rightStatistics = context.getChildStatistics(1);
+ /*
+ * nljPenalty:
+ * The row count estimation for nested loop join (NLJ) results often
has significant errors.
+ * When the estimated row count is higher than the actual value, the
cost benefits of subsequent
+ * operators (e.g., aggregation) may be overestimated. This can lead
the optimizer to choose a
+ * plan where a small table joins a large table, severely impacting
the overall SQL execution efficiency.
+ *
+ * For example, if the subsequent operator is an aggregation (AGG) and
the GROUP BY key aligns with
+ * the distribution key of the small table, the optimizer might
prioritize avoiding shuffling the NLJ
+ * results by choosing to join the small table to the large table,
even if this is suboptimal.
+ */
+ double nljPenalty = 1.0;
+ if (leftStatistics.getRowCount() < 10 * rightStatistics.getRowCount())
{
+ nljPenalty = Math.min(leftStatistics.getRowCount(),
rightStatistics.getRowCount());
+ }
return CostV1.of(context.getSessionVariable(),
leftStatistics.getRowCount() * rightStatistics.getRowCount(),
- rightStatistics.getRowCount(),
+ rightStatistics.getRowCount() * nljPenalty,
0);
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java
b/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java
index 3858f7c636a..1846f325913 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/qe/SessionVariable.java
@@ -2663,12 +2663,9 @@ public class SessionVariable implements Serializable,
Writable {
this.enableLocalExchange = random.nextBoolean();
this.enableSharedExchangeSinkBuffer = random.nextBoolean();
this.useSerialExchange = random.nextBoolean();
- // This will cause be dead loop, disable it first
- // this.disableJoinReorder = random.nextBoolean();
this.enableCommonExpPushDownForInvertedIndex = random.nextBoolean();
this.disableStreamPreaggregations = random.nextBoolean();
this.enableShareHashTableForBroadcastJoin = random.nextBoolean();
- this.enableParallelResultSink = random.nextBoolean();
// 4KB = 4 * 1024 bytes
int minBytes = 4 * 1024;
diff --git a/regression-test/data/shape_check/others/nlj.out
b/regression-test/data/shape_check/others/nlj.out
new file mode 100644
index 00000000000..7a4caa03911
Binary files /dev/null and b/regression-test/data/shape_check/others/nlj.out
differ
diff --git a/regression-test/suites/shape_check/others/nlj.groovy
b/regression-test/suites/shape_check/others/nlj.groovy
new file mode 100644
index 00000000000..19a7f25b3e1
--- /dev/null
+++ b/regression-test/suites/shape_check/others/nlj.groovy
@@ -0,0 +1,73 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+suite("nlj") {
+ sql """
+ drop table if exists a;
+ create table a (
+ `id` int,
+ a_date dateV2,
+ x int,
+ y int
+ )
+ DISTRIBUTED BY HASH(id) BUCKETS 8
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ alter table a modify column a_date set stats ('row_count'='1428418116',
'ndv'='498', 'min_value'='2023-12-31', 'max_value'='2025-05-18',
'avg_size'='4');
+
+
+ drop table if exists b;
+ create table b (
+ `id` int,
+ b_date dateV2,
+ )
+ DISTRIBUTED BY HASH(b_date) BUCKETS 8
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+
+
+ alter table b modify column b_date set stats ('row_count'='505',
'ndv'='505', 'min_value'='2025-05-14', 'max_value'='2025-05-15',
'avg_size'='4');
+
+ drop table if exists c;
+ create table c (
+ `id` int,
+ c_date dateV2,
+ )
+ DISTRIBUTED BY HASH(id) BUCKETS 8
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+
+ alter table c modify column c_date set stats ('row_count'='505',
'ndv'='505', 'min_value'='2025-05-14', 'max_value'='2025-05-15',
'avg_size'='4');
+
+ set disable_nereids_rules='PRUNE_EMPTY_PARTITION';
+ set runtime_filter_mode=off;
+ """
+
+ // expected join order : a b
+ // order b-a is good for aggregation, but usually this order is extremely
bad for join.
+ qt_shape """
+ explain shape plan
+ select b_date
+ from a join b on a_date>b_date
+ group by b_date, a_date, a.x
+ """
+}
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
