You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by en...@apache.org on 2023/06/05 10:56:32 UTC
[doris] branch master updated: [improve](nereids)derive analytics node stats (#20340)
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 92721c84d3 [improve](nereids)derive analytics node stats (#20340)
92721c84d3 is described below
commit 92721c84d36a7d26e04e8e960ee68fe5a0c79157
Author: minghong <en...@gmail.com>
AuthorDate: Mon Jun 5 18:56:20 2023 +0800
[improve](nereids)derive analytics node stats (#20340)
1. derive analytic node stats, add support for rank()
2. filter estimation stats derive updated. update row count of filter column.
3. use ColumnStatistics.orginal to replace ColumnStatistics.orginalNdv, where ColumnStatistics.orginal is the column statisics get from TableScan.
TPCDS 70 on tpcds_sf100 improved from 23sec to 2 sec
This pr has no performance downgrade on other tpcds queries and tpch queries.
---
.../doris/nereids/stats/FilterEstimation.java | 9 ++--
.../apache/doris/nereids/stats/JoinEstimation.java | 4 +-
.../doris/nereids/stats/StatsCalculator.java | 14 +++++
.../apache/doris/statistics/ColumnStatistic.java | 23 +++++---
.../doris/statistics/ColumnStatisticBuilder.java | 14 +++--
.../doris/statistics/StatisticsRepository.java | 2 +-
.../doris/nereids/util/HyperGraphBuilder.java | 2 +-
.../doris/statistics/StatsDeriveResultTest.java | 2 +-
.../nereids_tpcds_shape_sf100_p0/shape/query11.out | 30 +++++------
.../nereids_tpcds_shape_sf100_p0/shape/query4.out | 30 +++++------
.../nereids_tpcds_shape_sf100_p0/shape/query44.out | 62 +++++++++++-----------
.../nereids_tpcds_shape_sf100_p0/shape/query70.out | 58 ++++++++++----------
.../nereids_tpcds_shape_sf100_p0/shape/query74.out | 29 +++++-----
13 files changed, 155 insertions(+), 124 deletions(-)
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/FilterEstimation.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/FilterEstimation.java
index 90ab646146..d7ae653798 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/FilterEstimation.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/FilterEstimation.java
@@ -370,6 +370,7 @@ public class FilterEstimation extends ExpressionVisitor<Statistics, EstimationCo
.setNdv(intersectRange.getDistinctValues());
double sel = leftRange.overlapPercentWith(rightRange);
Statistics updatedStatistics = context.statistics.withSel(sel);
+ leftColumnStatisticBuilder.setCount(updatedStatistics.getRowCount());
updatedStatistics.addColumnStats(leftExpr, leftColumnStatisticBuilder.build());
leftExpr.accept(new ColumnStatsAdjustVisitor(), updatedStatistics);
return updatedStatistics;
@@ -392,15 +393,17 @@ public class FilterEstimation extends ExpressionVisitor<Statistics, EstimationCo
double sel;
double reduceRatio = 0.25;
double bothSideReducedRatio = 0.9;
- if (leftStats.ndv < leftStats.originalNdv * bothSideReducedRatio
- && rightStats.ndv < rightStats.originalNdv * bothSideReducedRatio) {
+ if (!leftStats.rangeChanged() && !rightStats.rangeChanged()
+ && leftStats.ndv < leftStats.getOriginalNdv() * bothSideReducedRatio
+ && rightStats.ndv < rightStats.getOriginalNdv() * bothSideReducedRatio) {
double sel1;
if (leftStats.ndv > rightStats.ndv) {
sel1 = 1 / StatsMathUtil.nonZeroDivisor(leftStats.ndv);
} else {
sel1 = 1 / StatsMathUtil.nonZeroDivisor(rightStats.ndv);
}
- double sel2 = Math.min(rightStats.ndv / rightStats.originalNdv, leftStats.ndv / leftStats.originalNdv);
+ double sel2 = Math.min(rightStats.ndv / rightStats.getOriginalNdv(),
+ leftStats.ndv / leftStats.getOriginalNdv());
sel = sel1 * Math.pow(sel2, reduceRatio);
} else {
sel = 1 / StatsMathUtil.nonZeroDivisor(Math.max(leftStats.ndv, rightStats.ndv));
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/JoinEstimation.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/JoinEstimation.java
index efc5a5a4bc..df36d84a4a 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/JoinEstimation.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/JoinEstimation.java
@@ -193,7 +193,7 @@ public class JoinEstimation {
double rowCount;
if (join.getJoinType().isLeftSemiOrAntiJoin()) {
double semiRowCount = StatsMathUtil.divide(leftStats.getRowCount() * buildColStats.ndv,
- buildColStats.originalNdv);
+ buildColStats.getOriginalNdv());
if (join.getJoinType().isSemiJoin()) {
rowCount = semiRowCount;
} else {
@@ -202,7 +202,7 @@ public class JoinEstimation {
} else {
//right semi or anti
double semiRowCount = StatsMathUtil.divide(rightStats.getRowCount() * probColStats.ndv,
- probColStats.originalNdv);
+ probColStats.getOriginalNdv());
if (join.getJoinType().isSemiJoin()) {
rowCount = semiRowCount;
} else {
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/StatsCalculator.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/StatsCalculator.java
index b976a4196d..8c9ab4dacd 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/StatsCalculator.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/stats/StatsCalculator.java
@@ -30,7 +30,9 @@ import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.NamedExpression;
import org.apache.doris.nereids.trees.expressions.Slot;
import org.apache.doris.nereids.trees.expressions.SlotReference;
+import org.apache.doris.nereids.trees.expressions.WindowExpression;
import org.apache.doris.nereids.trees.expressions.functions.agg.AggregateFunction;
+import org.apache.doris.nereids.trees.expressions.functions.window.Rank;
import org.apache.doris.nereids.trees.plans.Plan;
import org.apache.doris.nereids.trees.plans.algebra.Aggregate;
import org.apache.doris.nereids.trees.plans.algebra.EmptyRelation;
@@ -842,6 +844,18 @@ public class StatsCalculator extends DefaultPlanVisitor<Statistics, Void> {
Map<Expression, ColumnStatistic> childColumnStats = stats.columnStatistics();
Map<Expression, ColumnStatistic> columnStatisticMap = windowOperator.getWindowExpressions().stream()
.map(expr -> {
+ //estimate rank()
+ if (expr instanceof Alias && expr.child(0) instanceof WindowExpression
+ && ((WindowExpression) expr.child(0)).getFunction() instanceof Rank) {
+ ColumnStatisticBuilder colBuilder = new ColumnStatisticBuilder();
+ colBuilder.setNdv(stats.getRowCount())
+ .setOriginal(null)
+ .setCount(stats.getRowCount())
+ .setMinValue(0)
+ .setMaxValue(stats.getRowCount());
+ return Pair.of(expr.toSlot(), colBuilder.build());
+ }
+ //estimate other expressions
ColumnStatistic value = null;
Set<Slot> slots = expr.getInputSlots();
if (slots.isEmpty()) {
diff --git a/fe/fe-core/src/main/java/org/apache/doris/statistics/ColumnStatistic.java b/fe/fe-core/src/main/java/org/apache/doris/statistics/ColumnStatistic.java
index 46dd8fa4a5..9485ecf662 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/statistics/ColumnStatistic.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/statistics/ColumnStatistic.java
@@ -96,7 +96,7 @@ public class ColumnStatistic {
but originalNdv is not. It is used to trace the change of a column's ndv through serials
of sql operators.
*/
- public final double originalNdv;
+ public final ColumnStatistic original;
// For display only.
public final LiteralExpr minExpr;
@@ -105,12 +105,12 @@ public class ColumnStatistic {
// assign value when do stats estimation.
public final Histogram histogram;
- public ColumnStatistic(double count, double ndv, double originalNdv, double avgSizeByte,
+ public ColumnStatistic(double count, double ndv, ColumnStatistic original, double avgSizeByte,
double numNulls, double dataSize, double minValue, double maxValue,
double selectivity, LiteralExpr minExpr, LiteralExpr maxExpr, boolean isUnKnown, Histogram histogram) {
this.count = count;
this.ndv = ndv;
- this.originalNdv = originalNdv;
+ this.original = original;
this.avgSizeByte = avgSizeByte;
this.numNulls = numNulls;
this.dataSize = dataSize;
@@ -165,7 +165,6 @@ public class ColumnStatistic {
columnStatisticBuilder.setMaxValue(Double.MAX_VALUE);
}
columnStatisticBuilder.setSelectivity(1.0);
- columnStatisticBuilder.setOriginalNdv(ndv);
Histogram histogram = Env.getCurrentEnv().getStatisticsCache().getHistogram(tblId, idxId, colName)
.orElse(null);
columnStatisticBuilder.setHistogram(histogram);
@@ -308,7 +307,7 @@ public class ColumnStatistic {
statistic.put("MaxExpr", maxExpr);
statistic.put("IsUnKnown", isUnKnown);
statistic.put("Histogram", Histogram.serializeToJson(histogram));
- statistic.put("OriginalNdv", originalNdv);
+ statistic.put("Original", original);
return statistic;
}
@@ -347,7 +346,7 @@ public class ColumnStatistic {
return new ColumnStatistic(
stat.getDouble("Count"),
stat.getDouble("Ndv"),
- stat.getDouble("OriginalNdv"),
+ null,
stat.getDouble("AvgSizeByte"),
stat.getDouble("NumNulls"),
stat.getDouble("DataSize"),
@@ -368,4 +367,16 @@ public class ColumnStatistic {
public boolean hasHistogram() {
return histogram != null && histogram != Histogram.UNKNOWN;
}
+
+ public double getOriginalNdv() {
+ if (original != null) {
+ return original.ndv;
+ }
+ return ndv;
+ }
+
+ // TODO expanded this function to support more cases, help to compute the change of ndv density
+ public boolean rangeChanged() {
+ return original != null && (minValue != original.minValue || maxValue != original.maxValue);
+ }
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/statistics/ColumnStatisticBuilder.java b/fe/fe-core/src/main/java/org/apache/doris/statistics/ColumnStatisticBuilder.java
index 9c34924b26..60e0bdab85 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/statistics/ColumnStatisticBuilder.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/statistics/ColumnStatisticBuilder.java
@@ -35,7 +35,7 @@ public class ColumnStatisticBuilder {
private Histogram histogram;
- private double originalNdv;
+ private ColumnStatistic original;
public ColumnStatisticBuilder() {
}
@@ -53,7 +53,7 @@ public class ColumnStatisticBuilder {
this.maxExpr = columnStatistic.maxExpr;
this.isUnknown = columnStatistic.isUnKnown;
this.histogram = columnStatistic.histogram;
- this.originalNdv = columnStatistic.originalNdv;
+ this.original = columnStatistic.original;
}
public ColumnStatisticBuilder setCount(double count) {
@@ -66,8 +66,8 @@ public class ColumnStatisticBuilder {
return this;
}
- public ColumnStatisticBuilder setOriginalNdv(double originalNdv) {
- this.originalNdv = originalNdv;
+ public ColumnStatisticBuilder setOriginal(ColumnStatistic original) {
+ this.original = original;
return this;
}
@@ -171,7 +171,11 @@ public class ColumnStatisticBuilder {
public ColumnStatistic build() {
dataSize = Math.max((count - numNulls + 1) * avgSizeByte, 0);
- return new ColumnStatistic(count, ndv, originalNdv, avgSizeByte, numNulls,
+ if (original == null) {
+ original = new ColumnStatistic(count, ndv, null, avgSizeByte, numNulls,
+ dataSize, minValue, maxValue, selectivity, minExpr, maxExpr, isUnknown, histogram);
+ }
+ return new ColumnStatistic(count, ndv, original, avgSizeByte, numNulls,
dataSize, minValue, maxValue, selectivity, minExpr, maxExpr, isUnknown, histogram);
}
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/statistics/StatisticsRepository.java b/fe/fe-core/src/main/java/org/apache/doris/statistics/StatisticsRepository.java
index 63e5d1b634..ced8e1e6a6 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/statistics/StatisticsRepository.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/statistics/StatisticsRepository.java
@@ -275,7 +275,7 @@ public class StatisticsRepository {
if (ndv != null) {
double dNdv = Double.parseDouble(ndv);
builder.setNdv(dNdv);
- builder.setOriginalNdv(dNdv);
+ builder.setOriginal(null);
}
if (nullCount != null) {
builder.setNumNulls(Double.parseDouble(nullCount));
diff --git a/fe/fe-core/src/test/java/org/apache/doris/nereids/util/HyperGraphBuilder.java b/fe/fe-core/src/test/java/org/apache/doris/nereids/util/HyperGraphBuilder.java
index a3223f8281..0458f27613 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/nereids/util/HyperGraphBuilder.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/nereids/util/HyperGraphBuilder.java
@@ -190,7 +190,7 @@ public class HyperGraphBuilder {
int count = rowCounts.get(Integer.parseInt(scanPlan.getTable().getName()));
for (Slot slot : scanPlan.getOutput()) {
slotIdToColumnStats.put(slot,
- new ColumnStatistic(count, count, 0, 0, 0, 0, 0,
+ new ColumnStatistic(count, count, null, 0, 0, 0, 0,
0, 0, null, null, true, null));
}
Statistics stats = new Statistics(count, slotIdToColumnStats);
diff --git a/fe/fe-core/src/test/java/org/apache/doris/statistics/StatsDeriveResultTest.java b/fe/fe-core/src/test/java/org/apache/doris/statistics/StatsDeriveResultTest.java
index beda41a5b9..6c157bece6 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/statistics/StatsDeriveResultTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/statistics/StatsDeriveResultTest.java
@@ -26,7 +26,7 @@ public class StatsDeriveResultTest {
@Test
public void testUpdateRowCountByLimit() {
StatsDeriveResult stats = new StatsDeriveResult(100);
- ColumnStatistic a = new ColumnStatistic(100, 10, 10, 1, 5, 10,
+ ColumnStatistic a = new ColumnStatistic(100, 10, null, 1, 5, 10,
1, 100, 0.5, null, null, false, null);
Id id = new Id(1);
stats.addColumnStats(id, a);
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query11.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query11.out
index cb3a7412d1..b17960f808 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query11.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query11.out
@@ -45,21 +45,21 @@ CteAnchor[cteId= ( CTEId#4=] )
------PhysicalTopN
--------PhysicalProject
----------hashJoin[INNER_JOIN](t_s_firstyear.customer_id = t_w_firstyear.customer_id)(CASE WHEN (year_total > 0.00) THEN (cast(year_total as DECIMALV3(38, 8)) / year_total) ELSE 0.000000 END > CASE WHEN (year_total > 0.00) THEN (cast(year_total as DECIMALV3(38, 8)) / year_total) ELSE 0.000000 END)
-------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id = t_w_secyear.customer_id)
---------------PhysicalProject
-----------------filter((t_w_secyear.dyear = 2002)(t_w_secyear.sale_type = 'w'))
-------------------CteConsumer[cteId= ( CTEId#4=] )
---------------PhysicalDistribute
-----------------hashJoin[INNER_JOIN](t_s_secyear.customer_id = t_s_firstyear.customer_id)
-------------------PhysicalProject
---------------------filter((t_s_secyear.sale_type = 's')(t_s_secyear.dyear = 2002))
-----------------------CteConsumer[cteId= ( CTEId#4=] )
-------------------PhysicalDistribute
+------------PhysicalProject
+--------------filter((t_w_firstyear.year_total > 0.00)(t_w_firstyear.sale_type = 'w')(t_w_firstyear.dyear = 2001))
+----------------CteConsumer[cteId= ( CTEId#4=] )
+------------PhysicalDistribute
+--------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id = t_w_secyear.customer_id)
+----------------PhysicalProject
+------------------filter((t_w_secyear.dyear = 2002)(t_w_secyear.sale_type = 'w'))
+--------------------CteConsumer[cteId= ( CTEId#4=] )
+----------------PhysicalDistribute
+------------------hashJoin[INNER_JOIN](t_s_secyear.customer_id = t_s_firstyear.customer_id)
--------------------PhysicalProject
-----------------------filter((t_s_firstyear.dyear = 2001)(t_s_firstyear.sale_type = 's')(t_s_firstyear.year_total > 0.00))
+----------------------filter((t_s_secyear.sale_type = 's')(t_s_secyear.dyear = 2002))
------------------------CteConsumer[cteId= ( CTEId#4=] )
-------------PhysicalDistribute
---------------PhysicalProject
-----------------filter((t_w_firstyear.year_total > 0.00)(t_w_firstyear.sale_type = 'w')(t_w_firstyear.dyear = 2001))
-------------------CteConsumer[cteId= ( CTEId#4=] )
+--------------------PhysicalDistribute
+----------------------PhysicalProject
+------------------------filter((t_s_firstyear.dyear = 2001)(t_s_firstyear.sale_type = 's')(t_s_firstyear.year_total > 0.00))
+--------------------------CteConsumer[cteId= ( CTEId#4=] )
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query4.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query4.out
index c3e8c94135..4c8dd7e768 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query4.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query4.out
@@ -77,21 +77,21 @@ CteAnchor[cteId= ( CTEId#6=] )
----------------PhysicalDistribute
------------------PhysicalProject
--------------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id = t_c_firstyear.customer_id)(CASE WHEN (year_total > 0.000000) THEN (cast(year_total as DECIMALV3(38, 16)) / year_total) ELSE NULL END > CASE WHEN (year_total > 0.000000) THEN (cast(year_total as DECIMALV3(38, 16)) / year_total) ELSE NULL END)
-----------------------hashJoin[INNER_JOIN](t_s_secyear.customer_id = t_s_firstyear.customer_id)
-------------------------PhysicalProject
---------------------------filter((t_s_secyear.sale_type = 's')(t_s_secyear.dyear = 2000))
-----------------------------CteConsumer[cteId= ( CTEId#6=] )
-------------------------PhysicalDistribute
---------------------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id = t_c_secyear.customer_id)
-----------------------------PhysicalProject
-------------------------------filter((t_c_secyear.sale_type = 'c')(t_c_secyear.dyear = 2000))
---------------------------------CteConsumer[cteId= ( CTEId#6=] )
-----------------------------PhysicalDistribute
+----------------------PhysicalProject
+------------------------filter((t_c_firstyear.year_total > 0.000000)(t_c_firstyear.dyear = 1999)(t_c_firstyear.sale_type = 'c'))
+--------------------------CteConsumer[cteId= ( CTEId#6=] )
+----------------------PhysicalDistribute
+------------------------hashJoin[INNER_JOIN](t_s_secyear.customer_id = t_s_firstyear.customer_id)
+--------------------------PhysicalProject
+----------------------------filter((t_s_secyear.sale_type = 's')(t_s_secyear.dyear = 2000))
+------------------------------CteConsumer[cteId= ( CTEId#6=] )
+--------------------------PhysicalDistribute
+----------------------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id = t_c_secyear.customer_id)
------------------------------PhysicalProject
---------------------------------filter((t_s_firstyear.year_total > 0.000000)(t_s_firstyear.dyear = 1999)(t_s_firstyear.sale_type = 's'))
+--------------------------------filter((t_c_secyear.sale_type = 'c')(t_c_secyear.dyear = 2000))
----------------------------------CteConsumer[cteId= ( CTEId#6=] )
-----------------------PhysicalDistribute
-------------------------PhysicalProject
---------------------------filter((t_c_firstyear.year_total > 0.000000)(t_c_firstyear.dyear = 1999)(t_c_firstyear.sale_type = 'c'))
-----------------------------CteConsumer[cteId= ( CTEId#6=] )
+------------------------------PhysicalDistribute
+--------------------------------PhysicalProject
+----------------------------------filter((t_s_firstyear.year_total > 0.000000)(t_s_firstyear.dyear = 1999)(t_s_firstyear.sale_type = 's'))
+------------------------------------CteConsumer[cteId= ( CTEId#6=] )
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query44.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query44.out
index bebc49746e..d4bfa8d3ae 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query44.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query44.out
@@ -4,11 +4,11 @@ PhysicalTopN
--PhysicalDistribute
----PhysicalTopN
------PhysicalProject
---------hashJoin[INNER_JOIN](asceding.rnk = descending.rnk)
-----------hashJoin[INNER_JOIN](i2.i_item_sk = descending.item_sk)
-------------PhysicalProject
---------------PhysicalOlapScan[item]
-------------PhysicalDistribute
+--------hashJoin[INNER_JOIN](i1.i_item_sk = asceding.item_sk)
+----------PhysicalProject
+------------PhysicalOlapScan[item]
+----------PhysicalDistribute
+------------hashJoin[INNER_JOIN](asceding.rnk = descending.rnk)
--------------PhysicalProject
----------------filter((rnk < 11))
------------------PhysicalWindow
@@ -33,33 +33,33 @@ PhysicalTopN
--------------------------------------------PhysicalProject
----------------------------------------------filter(ss_addr_sk IS NULL(store_sales.ss_store_sk = 146))
------------------------------------------------PhysicalOlapScan[store_sales]
-----------PhysicalDistribute
-------------hashJoin[INNER_JOIN](i1.i_item_sk = asceding.item_sk)
---------------PhysicalProject
-----------------PhysicalOlapScan[item]
--------------PhysicalDistribute
-----------------PhysicalProject
-------------------filter((rnk < 11))
---------------------PhysicalWindow
-----------------------PhysicalQuickSort
-------------------------PhysicalDistribute
+----------------hashJoin[INNER_JOIN](i2.i_item_sk = descending.item_sk)
+------------------PhysicalProject
+--------------------PhysicalOlapScan[item]
+------------------PhysicalDistribute
+--------------------PhysicalProject
+----------------------filter((rnk < 11))
+------------------------PhysicalWindow
--------------------------PhysicalQuickSort
-----------------------------PhysicalProject
-------------------------------NestedLoopJoin[INNER_JOIN](cast(rank_col as DOUBLE) > cast((0.9 * rank_col) as DOUBLE))
---------------------------------hashAgg[GLOBAL]
-----------------------------------PhysicalDistribute
-------------------------------------hashAgg[LOCAL]
---------------------------------------PhysicalProject
-----------------------------------------filter((ss1.ss_store_sk = 146))
-------------------------------------------PhysicalOlapScan[store_sales]
---------------------------------PhysicalDistribute
-----------------------------------PhysicalAssertNumRows
+----------------------------PhysicalDistribute
+------------------------------PhysicalQuickSort
+--------------------------------PhysicalProject
+----------------------------------NestedLoopJoin[INNER_JOIN](cast(rank_col as DOUBLE) > cast((0.9 * rank_col) as DOUBLE))
+------------------------------------hashAgg[GLOBAL]
+--------------------------------------PhysicalDistribute
+----------------------------------------hashAgg[LOCAL]
+------------------------------------------PhysicalProject
+--------------------------------------------filter((ss1.ss_store_sk = 146))
+----------------------------------------------PhysicalOlapScan[store_sales]
------------------------------------PhysicalDistribute
---------------------------------------PhysicalProject
-----------------------------------------hashAgg[GLOBAL]
-------------------------------------------PhysicalDistribute
---------------------------------------------hashAgg[LOCAL]
-----------------------------------------------PhysicalProject
-------------------------------------------------filter(ss_addr_sk IS NULL(store_sales.ss_store_sk = 146))
---------------------------------------------------PhysicalOlapScan[store_sales]
+--------------------------------------PhysicalAssertNumRows
+----------------------------------------PhysicalDistribute
+------------------------------------------PhysicalProject
+--------------------------------------------hashAgg[GLOBAL]
+----------------------------------------------PhysicalDistribute
+------------------------------------------------hashAgg[LOCAL]
+--------------------------------------------------PhysicalProject
+----------------------------------------------------filter(ss_addr_sk IS NULL(store_sales.ss_store_sk = 146))
+------------------------------------------------------PhysicalOlapScan[store_sales]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query70.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query70.out
index b5e0900210..b026346094 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query70.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query70.out
@@ -14,36 +14,36 @@ PhysicalProject
----------------------hashAgg[LOCAL]
------------------------PhysicalRepeat
--------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN](d1.d_date_sk = store_sales.ss_sold_date_sk)
-------------------------------PhysicalProject
---------------------------------filter((d1.d_month_seq <= 1224)(d1.d_month_seq >= 1213))
-----------------------------------PhysicalOlapScan[date_dim]
+----------------------------hashJoin[INNER_JOIN](store.s_store_sk = store_sales.ss_store_sk)
+------------------------------hashJoin[INNER_JOIN](d1.d_date_sk = store_sales.ss_sold_date_sk)
+--------------------------------PhysicalProject
+----------------------------------PhysicalOlapScan[store_sales]
+--------------------------------PhysicalDistribute
+----------------------------------PhysicalProject
+------------------------------------filter((d1.d_month_seq <= 1224)(d1.d_month_seq >= 1213))
+--------------------------------------PhysicalOlapScan[date_dim]
------------------------------PhysicalDistribute
---------------------------------hashJoin[INNER_JOIN](store.s_store_sk = store_sales.ss_store_sk)
+--------------------------------hashJoin[LEFT_SEMI_JOIN](store.s_state = tmp1.s_state)
----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[store_sales]
+------------------------------------PhysicalOlapScan[store]
----------------------------------PhysicalDistribute
-------------------------------------hashJoin[LEFT_SEMI_JOIN](store.s_state = tmp1.s_state)
---------------------------------------PhysicalProject
-----------------------------------------PhysicalOlapScan[store]
---------------------------------------PhysicalDistribute
-----------------------------------------PhysicalProject
-------------------------------------------filter((ranking <= 5))
---------------------------------------------PhysicalWindow
-----------------------------------------------PhysicalQuickSort
-------------------------------------------------hashAgg[GLOBAL]
---------------------------------------------------PhysicalDistribute
-----------------------------------------------------hashAgg[LOCAL]
-------------------------------------------------------PhysicalProject
---------------------------------------------------------hashJoin[INNER_JOIN](store.s_store_sk = store_sales.ss_store_sk)
-----------------------------------------------------------hashJoin[INNER_JOIN](date_dim.d_date_sk = store_sales.ss_sold_date_sk)
-------------------------------------------------------------PhysicalProject
---------------------------------------------------------------PhysicalOlapScan[store_sales]
-------------------------------------------------------------PhysicalDistribute
---------------------------------------------------------------PhysicalProject
-----------------------------------------------------------------filter((date_dim.d_month_seq >= 1213)(date_dim.d_month_seq <= 1224))
-------------------------------------------------------------------PhysicalOlapScan[date_dim]
-----------------------------------------------------------PhysicalDistribute
-------------------------------------------------------------PhysicalProject
---------------------------------------------------------------PhysicalOlapScan[store]
+------------------------------------PhysicalProject
+--------------------------------------filter((ranking <= 5))
+----------------------------------------PhysicalWindow
+------------------------------------------PhysicalQuickSort
+--------------------------------------------hashAgg[GLOBAL]
+----------------------------------------------PhysicalDistribute
+------------------------------------------------hashAgg[LOCAL]
+--------------------------------------------------PhysicalProject
+----------------------------------------------------hashJoin[INNER_JOIN](store.s_store_sk = store_sales.ss_store_sk)
+------------------------------------------------------hashJoin[INNER_JOIN](date_dim.d_date_sk = store_sales.ss_sold_date_sk)
+--------------------------------------------------------PhysicalProject
+----------------------------------------------------------PhysicalOlapScan[store_sales]
+--------------------------------------------------------PhysicalDistribute
+----------------------------------------------------------PhysicalProject
+------------------------------------------------------------filter((date_dim.d_month_seq >= 1213)(date_dim.d_month_seq <= 1224))
+--------------------------------------------------------------PhysicalOlapScan[date_dim]
+------------------------------------------------------PhysicalDistribute
+--------------------------------------------------------PhysicalProject
+----------------------------------------------------------PhysicalOlapScan[store]
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query74.out b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query74.out
index 45d668c27f..633c98d867 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query74.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query74.out
@@ -44,22 +44,21 @@ CteAnchor[cteId= ( CTEId#4=] )
------PhysicalTopN
--------PhysicalProject
----------hashJoin[INNER_JOIN](t_s_firstyear.customer_id = t_w_firstyear.customer_id)(CASE WHEN (year_total > 0.0) THEN (year_total / year_total) ELSE NULL END > CASE WHEN (year_total > 0.0) THEN (year_total / year_total) ELSE NULL END)
-------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id = t_w_secyear.customer_id)
---------------hashJoin[INNER_JOIN](t_s_secyear.customer_id = t_s_firstyear.customer_id)
-----------------PhysicalDistribute
-------------------PhysicalProject
---------------------filter((t_s_firstyear.year = 1999)(t_s_firstyear.sale_type = 's')(t_s_firstyear.year_total > 0.0))
-----------------------CteConsumer[cteId= ( CTEId#4=] )
-----------------PhysicalDistribute
-------------------PhysicalProject
---------------------filter((t_s_secyear.sale_type = 's')(t_s_secyear.year = 2000))
-----------------------CteConsumer[cteId= ( CTEId#4=] )
---------------PhysicalDistribute
+------------PhysicalProject
+--------------filter((t_w_firstyear.year = 1999)(t_w_firstyear.year_total > 0.0)(t_w_firstyear.sale_type = 'w'))
+----------------CteConsumer[cteId= ( CTEId#4=] )
+------------PhysicalDistribute
+--------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id = t_w_secyear.customer_id)
----------------PhysicalProject
------------------filter((t_w_secyear.year = 2000)(t_w_secyear.sale_type = 'w'))
--------------------CteConsumer[cteId= ( CTEId#4=] )
-------------PhysicalDistribute
---------------PhysicalProject
-----------------filter((t_w_firstyear.year = 1999)(t_w_firstyear.year_total > 0.0)(t_w_firstyear.sale_type = 'w'))
-------------------CteConsumer[cteId= ( CTEId#4=] )
+----------------PhysicalDistribute
+------------------hashJoin[INNER_JOIN](t_s_secyear.customer_id = t_s_firstyear.customer_id)
+--------------------PhysicalProject
+----------------------filter((t_s_firstyear.year = 1999)(t_s_firstyear.sale_type = 's')(t_s_firstyear.year_total > 0.0))
+------------------------CteConsumer[cteId= ( CTEId#4=] )
+--------------------PhysicalDistribute
+----------------------PhysicalProject
+------------------------filter((t_s_secyear.sale_type = 's')(t_s_secyear.year = 2000))
+--------------------------CteConsumer[cteId= ( CTEId#4=] )
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org