You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by yi...@apache.org on 2026/03/14 06:41:49 UTC
(doris) branch branch-4.1 updated: branch-4.1:[enhance](shuffle) shuffle key prune opt (#59449) (#61298)
This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-4.1
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-4.1 by this push:
new deb5ab596f8 branch-4.1:[enhance](shuffle) shuffle key prune opt (#59449) (#61298)
deb5ab596f8 is described below
commit deb5ab596f8d0a2cf0fb4f1120e6a483ae7d644a
Author: feiniaofeiafei <mo...@selectdb.com>
AuthorDate: Sat Mar 14 14:41:40 2026 +0800
branch-4.1:[enhance](shuffle) shuffle key prune opt (#59449) (#61298)
picked from #59449
---
.../java/org/apache/doris/nereids/memo/Group.java | 110 ++-
.../apache/doris/nereids/memo/GroupExpression.java | 35 +-
.../java/org/apache/doris/nereids/memo/Memo.java | 13 +-
.../properties/ChildrenPropertiesRegulator.java | 5 +-
.../nereids/properties/RequestPropertyDeriver.java | 79 +-
.../nereids/properties/ShuffleKeyPruneUtils.java | 515 +++++++++++++
.../rules/implementation/SplitAggBaseRule.java | 9 +-
.../rules/implementation/SplitAggMultiPhase.java | 29 +-
.../SplitAggMultiPhaseWithoutGbyKey.java | 20 +-
.../implementation/SplitAggWithoutDistinct.java | 6 +-
.../rules/rewrite/PullUpJoinFromUnionAll.java | 2 +-
.../doris/nereids/rules/rewrite/SaltJoin.java | 6 +-
.../trees/plans/physical/PhysicalDistribute.java | 24 +
.../plans/physical/PhysicalHashAggregate.java | 35 +-
.../java/org/apache/doris/qe/ConnectContext.java | 13 +
.../java/org/apache/doris/qe/SessionVariable.java | 4 +
.../doris/statistics/util/StatisticsUtil.java | 24 +-
.../properties/ChildOutputPropertyDeriverTest.java | 3 +
.../properties/RequestPropertyDeriverTest.java | 11 +-
.../properties/ShuffleKeyPruneUtilsTest.java | 393 ++++++++++
.../doris/nereids/trees/plans/PlanEqualsTest.java | 6 +-
.../apache/doris/utframe/TestWithFeService.java | 23 +
.../hash_shuffle_key_prune.out | 815 +++++++++++++++++++++
.../shape_check/tpcds_sf100/rf_prune/query23.out | 18 +-
.../data/shape_check/tpcds_sf100/shape/query23.out | 18 +-
.../data/shape_check/tpcds_sf1000/hint/query23.out | 18 +-
.../data/shape_check/tpcds_sf1000/hint/query65.out | 8 +-
.../data/shape_check/tpcds_sf1000/hint/query78.out | 2 +-
.../shape_check/tpcds_sf1000/shape/query23.out | 18 +-
.../shape_check/tpcds_sf1000/shape/query65.out | 8 +-
.../shape_check/tpcds_sf1000/shape/query72.out | 28 +-
.../shape/query23.out | 70 +-
.../shape/query65.out | 8 +-
.../shape/query72.out | 20 +-
.../tpch_sf1000/rf_prune/q20-rewrite.out | 2 +-
.../data/shape_check/tpch_sf1000/rf_prune/q20.out | 2 +-
.../shape_check/tpch_sf1000/shape/q20-rewrite.out | 2 +-
.../data/shape_check/tpch_sf1000/shape/q20.out | 2 +-
.../hash_shuffle_key_prune.groovy | 194 +++++
39 files changed, 2361 insertions(+), 237 deletions(-)
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/memo/Group.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/memo/Group.java
index b3d45e6c80a..141719ad821 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/memo/Group.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/memo/Group.java
@@ -455,46 +455,98 @@ public class Group {
@Override
public String toString() {
StringBuilder str = new StringBuilder("Group[" + groupId + "]\n");
- str.append(" logical expressions:\n");
- for (GroupExpression logicalExpression : logicalExpressions) {
- str.append(" ").append(logicalExpression).append("\n");
+ // Logical expressions with numbering
+ str.append(" Logical Expressions:\n");
+ if (logicalExpressions.isEmpty()) {
+ str.append(" (none)\n");
+ } else {
+ int index = 1;
+ for (GroupExpression logicalExpression : logicalExpressions) {
+ str.append(" [").append(index++).append("] ").append(logicalExpression).append("\n");
+ }
}
- str.append(" physical expressions:\n");
- for (GroupExpression physicalExpression : physicalExpressions) {
- str.append(" ").append(physicalExpression).append("\n");
+ // Physical expressions with numbering
+ str.append(" Physical Expressions:\n");
+ if (physicalExpressions.isEmpty()) {
+ str.append(" (none)\n");
+ } else {
+ int index = 1;
+ for (GroupExpression physicalExpression : physicalExpressions) {
+ str.append(" [").append(index++).append("] ").append(physicalExpression).append("\n");
+ }
}
- str.append(" enforcers:\n");
+ // Enforcers with numbering
+ str.append(" Enforcers:\n");
List<GroupExpression> enforcerList = enforcers.keySet().stream()
.sorted(java.util.Comparator.comparing(e1 -> e1.getId().asInt()))
.collect(Collectors.toList());
- for (GroupExpression enforcer : enforcerList) {
- str.append(" ").append(enforcer).append("\n");
+ if (enforcerList.isEmpty()) {
+ str.append(" (none)\n");
+ } else {
+ int index = 1;
+ for (GroupExpression enforcer : enforcerList) {
+ str.append(" [").append(index++).append("] ").append(enforcer).append("\n");
+ }
}
if (!chosenEnforcerIdList.isEmpty()) {
- str.append(" chosen enforcer(id, requiredProperties):\n");
+ str.append(" Chosen Enforcer(ID, RequiredProperties):\n");
for (int i = 0; i < chosenEnforcerIdList.size(); i++) {
str.append(" (").append(i).append(")").append(chosenEnforcerIdList.get(i)).append(", ")
.append(chosenEnforcerPropertiesList.get(i)).append("\n");
}
}
if (chosenGroupExpressionId != -1) {
- str.append(" chosen expression id: ").append(chosenGroupExpressionId).append("\n");
- str.append(" chosen properties: ").append(chosenProperties).append("\n");
+ str.append(" Chosen Expression ID: ").append(chosenGroupExpressionId).append("\n");
+ str.append(" Chosen Properties: ").append(chosenProperties).append("\n");
}
- str.append(" stats").append("\n");
+ str.append(" Statistics").append("\n");
str.append(getStatistics() == null ? "" : getStatistics().detail(" "));
- str.append(" lowest Plan(cost, properties, plan, childrenRequires)");
+ str.append(" Lowest Plan");
DecimalFormat format = new DecimalFormat("#,###.##");
- for (Map.Entry<PhysicalProperties, Pair<Cost, GroupExpression>> entry : lowestCostPlans.entrySet()) {
+ // Sort by cost for better readability
+ List<Map.Entry<PhysicalProperties, Pair<Cost, GroupExpression>>> sortedEntries =
+ lowestCostPlans.entrySet().stream()
+ .sorted(Map.Entry.comparingByValue((a, b) ->
+ Double.compare(a.first.getValue(), b.first.getValue())))
+ .collect(Collectors.toList());
+ int planIndex = 0;
+ for (Map.Entry<PhysicalProperties, Pair<Cost, GroupExpression>> entry : sortedEntries) {
PhysicalProperties prop = entry.getKey();
Pair<Cost, GroupExpression> costGroupExpressionPair = entry.getValue();
Cost cost = costGroupExpressionPair.first;
GroupExpression child = costGroupExpressionPair.second;
- str.append("\n\n ").append(format.format(cost.getValue())).append(" ").append(prop)
- .append("\n ").append(child).append("\n ")
- .append(child.getInputPropertiesListOrEmpty(prop));
+ List<PhysicalProperties> inputProps = child.getInputPropertiesListOrEmpty(prop);
+ boolean isChosen = false;
+ // Check if it's a chosen physical expression
+ if (chosenGroupExpressionId != -1
+ && child.getId().asInt() == chosenGroupExpressionId
+ && prop.equals(chosenProperties)) {
+ isChosen = true;
+ }
+ // Check if it's a chosen enforcer
+ if (!isChosen && !chosenEnforcerIdList.isEmpty()) {
+ for (int i = 0; i < chosenEnforcerIdList.size(); i++) {
+ if (child.getId().asInt() == chosenEnforcerIdList.get(i)
+ && prop.equals(chosenEnforcerPropertiesList.get(i))) {
+ isChosen = true;
+ break;
+ }
+ }
+ }
+ String marker = isChosen ? " BEST" : "";
+ str.append("\n ── Entry #").append(++planIndex)
+ .append(" ──────────────────────────────").append(marker);
+ str.append("\n Cost: ").append(format.format(cost.getValue()));
+ str.append("\n Properties: ").append(prop);
+ str.append("\n Expression ID: ").append(child.getId().asInt()).append("#").append(groupId.asInt());
+ if (!inputProps.isEmpty()) {
+ str.append("\n ChildrenRequires:");
+ for (int i = 0; i < inputProps.size(); i++) {
+ str.append("\n [").append(i).append("] ").append(inputProps.get(i));
+ }
+ }
}
str.append("\n").append(" struct info map").append("\n");
str.append(structInfoMap);
@@ -502,6 +554,28 @@ public class Group {
return str.toString();
}
+ /**
+ * Simplify plan string by removing redundant information.
+ */
+ private String simplifyPlanString(String planStr) {
+ // Remove redundant information that doesn't add value
+ String simplified = planStr;
+ // Remove stats=null (common and not informative)
+ simplified = simplified.replaceAll("\\s*stats=null,?", "");
+ // Remove markJoinSlotReference=Optional.empty (only show if present)
+ simplified = simplified.replaceAll("\\s*markJoinSlotReference=Optional\\.empty,?", "");
+ // Remove empty otherCondition=[]
+ simplified = simplified.replaceAll("\\s*otherCondition=\\[\\],?", "");
+ // Remove empty markCondition=[]
+ simplified = simplified.replaceAll("\\s*markCondition=\\[\\],?", "");
+ // Clean up multiple spaces and commas
+ simplified = simplified.replaceAll(",\\s*,+", ","); // Remove multiple commas
+ simplified = simplified.replaceAll("\\s+", " "); // Normalize spaces
+ simplified = simplified.replaceAll("\\(\\s*,", "("); // Remove leading comma after (
+ simplified = simplified.replaceAll(",\\s*\\)", ")"); // Remove trailing comma before )
+ return simplified.trim();
+ }
+
/**
* Get tree like string describing group.
*
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/memo/GroupExpression.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/memo/GroupExpression.java
index d6a9e29dd9e..63844ef278a 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/memo/GroupExpression.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/memo/GroupExpression.java
@@ -351,23 +351,40 @@ public class GroupExpression {
@Override
public String toString() {
DecimalFormat format = new DecimalFormat("#,###.##");
- StringBuilder builder = new StringBuilder("id:");
- builder.append(id.asInt());
+ StringBuilder builder = new StringBuilder();
+ String separator = " | ";
+ // Format ID
+ builder.append("id:").append(id.asInt());
if (ownerGroup == null) {
builder.append("OWNER GROUP IS NULL[]");
} else {
builder.append("#").append(ownerGroup.getGroupId().asInt());
}
+ // Format cost information
+ builder.append(separator);
if (cost != null) {
- builder.append(" cost=").append(format.format(cost.getValue()) + " " + cost);
+ builder.append("cost=").append(format.format(cost.getValue()));
+ builder.append(" [cpu=").append(format.format(cost.getCpuCost()))
+ .append(", mem=").append(format.format(cost.getMemoryCost()))
+ .append(", net=").append(format.format(cost.getNetworkCost())).append("]");
} else {
- builder.append(" cost=null");
+ builder.append("cost=null");
}
- builder.append(" estRows=").append(format.format(estOutputRowCount));
- builder.append(" children=[").append(Joiner.on(", ").join(
- children.stream().map(Group::getGroupId).collect(Collectors.toList())))
- .append(" ]");
- builder.append(" (plan=").append(plan.toString()).append(")");
+ // Format estimated rows
+ builder.append(separator);
+ builder.append("estRows=").append(format.format(estOutputRowCount));
+
+ // Format children
+ builder.append(separator);
+ if (!children.isEmpty()) {
+ builder.append("children=[").append(Joiner.on(", ").join(
+ children.stream().map(Group::getGroupId).collect(Collectors.toList())))
+ .append("]");
+ } else {
+ builder.append("children=[]");
+ }
+ // Format plan (simplified)
+ builder.append(separator).append(plan.toString());
return builder.toString();
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/memo/Memo.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/memo/Memo.java
index 4eb48d8311f..b1cdfa49dcf 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/memo/Memo.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/memo/Memo.java
@@ -839,9 +839,18 @@ public class Memo {
@Override
public String toString() {
StringBuilder builder = new StringBuilder();
- builder.append("root:").append(getRoot()).append("\n");
+ boolean first = true;
+ Group rootGroup = getRoot();
for (Group group : groups.values()) {
- builder.append("\n\n").append(group).append("\n");
+ if (!first) {
+ builder.append("\n").append("═══════════════════════════════════════════════════════════").append("\n");
+ }
+ if (group.equals(rootGroup)) {
+ builder.append("\n").append("ROOT ").append(group);
+ } else {
+ builder.append("\n").append(group);
+ }
+ first = false;
}
return builder.toString();
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/properties/ChildrenPropertiesRegulator.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/properties/ChildrenPropertiesRegulator.java
index 408961906bd..c9f444e4ba7 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/properties/ChildrenPropertiesRegulator.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/properties/ChildrenPropertiesRegulator.java
@@ -288,10 +288,7 @@ public class ChildrenPropertiesRegulator extends PlanVisitor<List<List<PhysicalP
int bucketNum = candidate.getTable().getDefaultDistributionInfo().getBucketNum();
int totalBucketNum = prunedPartNum * bucketNum;
ConnectContext connectContext = ConnectContext.get();
- int backEndNum = Math.max(1, connectContext.getEnv().getClusterInfo().getBackendsNumber(true));
- String clusterName = connectContext.getSessionVariable().resolveCloudClusterName(connectContext);
- int paraNum = Math.max(1, connectContext.getSessionVariable().getParallelExecInstanceNum(clusterName));
- return totalBucketNum < backEndNum * paraNum * 0.8;
+ return totalBucketNum < connectContext.getTotalInstanceNum() * 0.8;
}
}
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/properties/RequestPropertyDeriver.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/properties/RequestPropertyDeriver.java
index 8b533dca822..76afe67f38a 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/properties/RequestPropertyDeriver.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/properties/RequestPropertyDeriver.java
@@ -61,7 +61,6 @@ import org.apache.doris.nereids.trees.plans.physical.PhysicalWindow;
import org.apache.doris.nereids.trees.plans.visitor.PlanVisitor;
import org.apache.doris.nereids.util.AggregateUtils;
import org.apache.doris.nereids.util.JoinUtils;
-import org.apache.doris.nereids.util.Utils;
import org.apache.doris.qe.ConnectContext;
import org.apache.doris.statistics.Statistics;
@@ -230,13 +229,24 @@ public class RequestPropertyDeriver extends PlanVisitor<Void, PlanContext> {
return null;
}
if (hint.distributeType == DistributeType.SHUFFLE_RIGHT && JoinUtils.couldShuffle(hashJoin)) {
- addShuffleJoinRequestProperty(hashJoin);
+ // shuffle join
+ if (hashJoin.getDistributeHint().getSkewInfo() != null) {
+ addShuffleJoinRequestProperty(hashJoin, ShuffleType.REQUIRE_EQUAL);
+ } else {
+ addShuffleJoinRequestProperty(hashJoin, ShuffleType.REQUIRE);
+ }
hint.setStatus(Hint.HintStatus.SUCCESS);
return null;
}
// for shuffle join
if (JoinUtils.couldShuffle(hashJoin)) {
- addShuffleJoinRequestProperty(hashJoin);
+ // Scenario 3.3: when both children are Global AGG, try unified single shuffle key from join key ∩ gby
+ Optional<Pair<List<ExprId>, List<ExprId>>> optimalKeys =
+ ShuffleKeyPruneUtils.tryFindOptimalShuffleKeyForJoin(hashJoin, context);
+ optimalKeys.ifPresent(pair -> addRequestPropertyToChildren(
+ PhysicalProperties.createHash(pair.first, ShuffleType.REQUIRE),
+ PhysicalProperties.createHash(pair.second, ShuffleType.REQUIRE)));
+ addShuffleJoinRequestProperty(hashJoin, ShuffleType.REQUIRE);
}
// for broadcast join
@@ -428,14 +438,18 @@ public class RequestPropertyDeriver extends PlanVisitor<Void, PlanContext> {
@Override
public Void visitPhysicalHashAggregate(PhysicalHashAggregate<? extends Plan> agg, PlanContext context) {
- DistributionSpec parentDist = requestPropertyFromParent.getDistributionSpec();
if (agg.getAggPhase().isLocal()) {
addRequestPropertyToChildren(PhysicalProperties.ANY);
return null;
} else if (agg.getAggPhase().isGlobal()) {
+ // partition expressions already set by rule
if (agg.getPartitionExpressions().isPresent() && !agg.getPartitionExpressions().get().isEmpty()) {
- addRequestPropertyToChildren(
- PhysicalProperties.createHash(agg.getPartitionExpressions().get(), ShuffleType.REQUIRE));
+ List<Expression> partitionExprs = agg.getPartitionExpressions().get();
+ Optional<List<Expression>> bestGbyKeys =
+ ShuffleKeyPruneUtils.selectBestShuffleKeyForAgg(agg, partitionExprs, connectContext);
+ bestGbyKeys.ifPresent(keys -> addRequestPropertyToChildren(
+ PhysicalProperties.createHash(keys, ShuffleType.REQUIRE)));
+ addRequestPropertyToChildren(PhysicalProperties.createHash(partitionExprs, ShuffleType.REQUIRE));
return null;
}
if (agg.getGroupByExpressions().isEmpty()) {
@@ -447,24 +461,35 @@ public class RequestPropertyDeriver extends PlanVisitor<Void, PlanContext> {
.map(SlotReference.class::cast)
.map(SlotReference::getExprId)
.collect(Collectors.toList());
- // If the request received by agg is (a), the request sent by agg is (a,b), and (a) is a subset of (a,b),
- // then agg sends (a) to the child
+ // If the request received by agg is (a,b,c,d,e), the request sent by agg is (a,b,c,d,e,f,g,h,i,j),
+ // then agg can send (a,b,c,d,e) or further choose one from (a,b,c,d,e) when canShuffleKeyOpt applies.
+ DistributionSpec parentDist = requestPropertyFromParent.getDistributionSpec();
if (parentDist instanceof DistributionSpecHash) {
DistributionSpecHash distributionRequestFromParent = (DistributionSpecHash) parentDist;
List<ExprId> parentHashExprIds = distributionRequestFromParent.getOrderedShuffledColumns();
- Set<ExprId> intersectId = Sets.intersection(new HashSet<>(parentHashExprIds),
+ Set<ExprId> intersectIdSet = Sets.intersection(new HashSet<>(parentHashExprIds),
new HashSet<>(groupByExprIds));
- if (!intersectId.isEmpty() && intersectId.size() < groupByExprIds.size()) {
- if (shouldUseParent(parentHashExprIds, agg, context)) {
- addRequestPropertyToChildren(PhysicalProperties.createHash(
- Utils.fastToImmutableList(intersectId), ShuffleType.REQUIRE));
+ if (!intersectIdSet.isEmpty() && intersectIdSet.size() < groupByExprIds.size()) {
+ List<ExprId> intersectIdList = new ArrayList<>();
+ for (ExprId exprId : parentHashExprIds) {
+ if (!intersectIdSet.contains(exprId)) {
+ continue;
+ }
+ intersectIdList.add(exprId);
+ }
+ if (shouldUseParent(intersectIdList, agg, context)) {
+ List<ExprId> shuffleKeys =
+ ShuffleKeyPruneUtils.selectOptimalShuffleKeyForAggWithParentHashRequest(
+ agg, intersectIdList, context);
+ addRequestPropertyToChildren(PhysicalProperties.createHash(shuffleKeys, ShuffleType.REQUIRE));
}
- addRequestPropertyToChildren(PhysicalProperties.createHash(groupByExprIds, ShuffleType.REQUIRE));
- return null;
}
}
+ Optional<List<Expression>> bestGbyKeys =
+ ShuffleKeyPruneUtils.selectBestShuffleKeyForAgg(agg, agg.getGroupByExpressions(), connectContext);
+ bestGbyKeys.ifPresent(keys -> addRequestPropertyToChildren(PhysicalProperties.createHash(
+ keys, ShuffleType.REQUIRE)));
addRequestPropertyToChildren(PhysicalProperties.createHash(groupByExprIds, ShuffleType.REQUIRE));
- return null;
}
return null;
}
@@ -531,22 +556,14 @@ public class RequestPropertyDeriver extends PlanVisitor<Void, PlanContext> {
addRequestPropertyToChildren(PhysicalProperties.ANY, PhysicalProperties.REPLICATED);
}
- private void addShuffleJoinRequestProperty(PhysicalHashJoin<? extends Plan, ? extends Plan> hashJoin) {
+ private void addShuffleJoinRequestProperty(PhysicalHashJoin<? extends Plan, ? extends Plan> hashJoin,
+ ShuffleType shuffleType) {
Pair<List<ExprId>, List<ExprId>> onClauseUsedSlots = hashJoin.getHashConjunctsExprIds();
- // shuffle join
- if (hashJoin.getDistributeHint().getSkewInfo() != null) {
- addRequestPropertyToChildren(
- PhysicalProperties.createHash(
- new DistributionSpecHash(onClauseUsedSlots.first, ShuffleType.REQUIRE_EQUAL)),
- PhysicalProperties.createHash(
- new DistributionSpecHash(onClauseUsedSlots.second, ShuffleType.REQUIRE_EQUAL)));
- } else {
- addRequestPropertyToChildren(
- PhysicalProperties.createHash(
- new DistributionSpecHash(onClauseUsedSlots.first, ShuffleType.REQUIRE)),
- PhysicalProperties.createHash(
- new DistributionSpecHash(onClauseUsedSlots.second, ShuffleType.REQUIRE)));
- }
+ addRequestPropertyToChildren(
+ PhysicalProperties.createHash(
+ new DistributionSpecHash(onClauseUsedSlots.first, shuffleType)),
+ PhysicalProperties.createHash(
+ new DistributionSpecHash(onClauseUsedSlots.second, shuffleType)));
}
/**
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/properties/ShuffleKeyPruneUtils.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/properties/ShuffleKeyPruneUtils.java
new file mode 100644
index 00000000000..177b1eb4d32
--- /dev/null
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/properties/ShuffleKeyPruneUtils.java
@@ -0,0 +1,515 @@
+// 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.
+
+package org.apache.doris.nereids.properties;
+
+import org.apache.doris.common.Pair;
+import org.apache.doris.nereids.PlanContext;
+import org.apache.doris.nereids.memo.Group;
+import org.apache.doris.nereids.memo.GroupExpression;
+import org.apache.doris.nereids.stats.StatsCalculator;
+import org.apache.doris.nereids.trees.expressions.ExprId;
+import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.Slot;
+import org.apache.doris.nereids.trees.expressions.SlotReference;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalHashAggregate;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalHashJoin;
+import org.apache.doris.nereids.types.DataType;
+import org.apache.doris.nereids.types.coercion.CharacterType;
+import org.apache.doris.nereids.util.AggregateUtils;
+import org.apache.doris.qe.ConnectContext;
+import org.apache.doris.statistics.ColumnStatistic;
+import org.apache.doris.statistics.Statistics;
+import org.apache.doris.statistics.util.StatisticsUtil;
+
+import com.google.common.collect.ImmutableList;
+
+import java.util.ArrayList;
+import java.util.Comparator;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+import java.util.Optional;
+import java.util.Set;
+import java.util.stream.Collectors;
+
+/**AggShuffleKeyOptimize*/
+public class ShuffleKeyPruneUtils {
+ private static GroupExpression getGroupExpression(Group group) {
+ List<GroupExpression> physicalGroupExpressions = group.getPhysicalExpressions();
+ if (!physicalGroupExpressions.isEmpty()) {
+ return physicalGroupExpressions.get(0);
+ } else {
+ return group.getLogicalExpressions().get(0);
+ }
+ }
+
+ /*
+ * @param agg is a global aggregate
+ * @return the Statistics of the children of the local aggregate corresponding to the global aggregate.
+ */
+ private static Optional<Statistics> getGlobalAggChildStats(PhysicalHashAggregate<? extends Plan> agg) {
+ Optional<GroupExpression> groupExpression = agg.getGroupExpression();
+ if (!groupExpression.isPresent()) {
+ return Optional.empty();
+ }
+ Statistics aggChildStats = groupExpression.get().childStatistics(0);
+ Group childGroup = groupExpression.get().child(0);
+ Plan childExpression = getGroupExpression(childGroup).getPlan();
+ if (childExpression instanceof PhysicalHashAggregate
+ && ((PhysicalHashAggregate) childExpression).getAggPhase().isLocal()) {
+ childGroup = childGroup.getPhysicalExpressions().get(0).child(0);
+ aggChildStats = childGroup.getStatistics();
+ }
+ return Optional.ofNullable(aggChildStats);
+ }
+
+ private static boolean canAggShuffleKeyOpt(PhysicalHashAggregate<? extends Plan> agg,
+ List<? extends Expression> partitionExprs, ConnectContext connectContext) {
+ if (!connectContext.getSessionVariable().enableAggShuffleKeyPrune) {
+ return false;
+ }
+ if (agg.hasSourceRepeat()) {
+ return false;
+ }
+ return true;
+ }
+
+ /**
+ * When parent sends shuffle request, choose one optimal key from intersection of parent hash
+ * columns and agg group-by columns, or use full intersection. Returns list of ExprIds as
+ * shuffle keys.
+ */
+ public static List<ExprId> selectOptimalShuffleKeyForAggWithParentHashRequest(
+ PhysicalHashAggregate<? extends Plan> agg, List<ExprId> intersectIdList, PlanContext context) {
+ if (!context.getConnectContext().getSessionVariable().enableAggShuffleKeyPrune) {
+ return intersectIdList;
+ }
+ List<Expression> intersectExprs = new ArrayList<>();
+ Map<ExprId, Slot> exprIdToSlot = new HashMap<>();
+ for (Slot slot : agg.getOutput()) {
+ exprIdToSlot.put(slot.getExprId(), slot);
+ }
+ for (ExprId exprId : intersectIdList) {
+ if (!exprIdToSlot.containsKey(exprId)) {
+ return intersectIdList;
+ }
+ intersectExprs.add(exprIdToSlot.get(exprId));
+ }
+ if (intersectExprs.isEmpty()) {
+ return intersectIdList;
+ }
+ Optional<Statistics> childStats = getGlobalAggChildStats(agg);
+ if (!childStats.isPresent()) {
+ return intersectIdList;
+ }
+ double rowCount = childStats.get().getRowCount();
+ int instanceNum = context.getConnectContext().getTotalInstanceNum();
+ Optional<List<Expression>> optimalKeys = selectOptimalShuffleKeys(
+ intersectExprs, childStats.get(), rowCount, instanceNum);
+ if (optimalKeys.isPresent()) {
+ return optimalKeys.get().stream()
+ .filter(SlotReference.class::isInstance)
+ .map(SlotReference.class::cast)
+ .map(SlotReference::getExprId)
+ .collect(Collectors.toList());
+ }
+ return intersectIdList;
+ }
+
+ /**
+ * Scenario 4: When partition expressions are set by rule, optionally reduce shuffle keys.
+ * Strategy: 1) Try single key (isBalanced); 2) Try numeric+date keys (remove strings);
+ * 3) Fall back to full partitionExprs.
+ * Returns the list of expressions to use as shuffle keys, or empty to use full partitionExprs.
+ */
+ public static Optional<List<Expression>> selectBestShuffleKeyForAgg(
+ PhysicalHashAggregate<? extends Plan> agg, List<Expression> partitionExprs, ConnectContext context) {
+ if (!canAggShuffleKeyOpt(agg, partitionExprs, context)) {
+ return Optional.empty();
+ }
+ Optional<Statistics> childStats = getGlobalAggChildStats(agg);
+ if (!childStats.isPresent()) {
+ return Optional.empty();
+ }
+ double rowCount = childStats.get().getRowCount();
+ int instanceNum = context.getTotalInstanceNum();
+ return selectOptimalShuffleKeys(partitionExprs, childStats.get(), rowCount, instanceNum);
+ }
+
+ /**
+ * Select optimal shuffle keys with three-step strategy:
+ * 1. Try single key: sort by type (numeric/date first, string sorted by avg_size), pick first isBalanced key.
+ * 2. Try remove strings: filter numeric+date keys, if combinedNDV > instanceNum*512 return that list.
+ * 3. Fall back: return empty (caller uses full partitionExprs).
+ */
+ private static Optional<List<Expression>> selectOptimalShuffleKeys(List<Expression> partitionExprs,
+ Statistics childStats, double rowCount, int instanceNum) {
+ List<SlotReference> slotRefs = partitionExprs.stream()
+ .filter(SlotReference.class::isInstance)
+ .map(SlotReference.class::cast)
+ .collect(Collectors.toList());
+ if (slotRefs.isEmpty()) {
+ return Optional.empty();
+ }
+ // If any partition slot lacks column stats, skip optimization and use original partitionExprs.
+ for (SlotReference slotRef : slotRefs) {
+ if (childStats.findColumnStatistics(slotRef) == null) {
+ return Optional.empty();
+ }
+ }
+
+ // Step 1: Try single key - sort by type priority, pick first isBalanced
+ List<SlotReference> sortedByType = sortShuffleKeysByTypePriority(slotRefs, childStats);
+ for (SlotReference slotRef : sortedByType) {
+ ColumnStatistic colStats = childStats.findColumnStatistics(slotRef);
+ if (StatisticsUtil.isBalanced(colStats, rowCount, instanceNum)) {
+ return Optional.of(ImmutableList.of(slotRef));
+ }
+ }
+
+ // Step 2: Try remove string types - filter numeric+date, check combined NDV
+ List<Expression> numericAndDateExprs = slotRefs.stream()
+ .filter(s -> s.getDataType().isNumericType() || s.getDataType().isDateLikeType())
+ .collect(Collectors.toList());
+ if (!numericAndDateExprs.isEmpty()) {
+ double combinedNdv = StatsCalculator.estimateGroupByRowCount(numericAndDateExprs, childStats);
+ long ndvThreshold = (long) instanceNum * AggregateUtils.NDV_INSTANCE_BALANCE_MULTIPLIER;
+ if (combinedNdv > ndvThreshold) {
+ return Optional.of(ImmutableList.copyOf(numericAndDateExprs));
+ }
+ }
+
+ // Step 3: Fall back - return empty, caller uses full partitionExprs
+ return Optional.empty();
+ }
+
+ /**
+ * Sort shuffle keys: numeric and date first, then string types.
+ * String types are sorted by column statistics avg size (avgSizeByte) ascending.
+ */
+ private static List<SlotReference> sortShuffleKeysByTypePriority(List<SlotReference> slotRefs,
+ Statistics childStats) {
+ List<SlotReference> result = new ArrayList<>(slotRefs);
+ result.sort(Comparator
+ .comparingInt((SlotReference s) -> getTypeSortPriority(s.getDataType()))
+ .thenComparingDouble((SlotReference s) -> getStringAvgSizeForSort(s, childStats)));
+ return result;
+ }
+
+ /** 0=numeric/date first, 1=string last. */
+ private static int getTypeSortPriority(DataType dataType) {
+ if (dataType.isNumericType() || dataType.isDateLikeType()) {
+ return 0;
+ }
+ return 1;
+ }
+
+ /** For string types return avg size from stats; for others return 0 (no secondary sort). */
+ private static double getStringAvgSizeForSort(SlotReference slotRef, Statistics childStats) {
+ DataType dataType = slotRef.getDataType();
+ if (dataType instanceof CharacterType) {
+ ColumnStatistic colStats = childStats.findColumnStatistics(slotRef);
+ if (colStats != null && !colStats.isUnKnown && colStats.avgSizeByte > 0) {
+ return colStats.avgSizeByte;
+ }
+ return ((CharacterType) dataType).getLen();
+ }
+ return 0;
+ }
+
+ /**
+ * Get Global AGG plan and its input statistics from a Group (if the group's best plan is Global AGG).
+ */
+ private static Optional<Pair<PhysicalHashAggregate<? extends Plan>, Statistics>> getGlobalAggInputStatsFromGroup(
+ Group group) {
+ for (GroupExpression ge : group.getPhysicalExpressions()) {
+ Plan p = ge.getPlan();
+ if (p instanceof PhysicalHashAggregate && ((PhysicalHashAggregate<?>) p).getAggPhase().isGlobal()) {
+ Optional<Statistics> inputStats = getGlobalAggChildStats((PhysicalHashAggregate<? extends Plan>) p);
+ return inputStats.map(statistics -> Pair.of((PhysicalHashAggregate<? extends Plan>) p, statistics));
+ }
+ }
+ return Optional.empty();
+ }
+
+ /**
+ * Scenario 3.3: when both join children are Global AGG, find optimal shuffle keys from
+ * join key ∩ left_agg.gby ∩ right_agg.gby. Same three-step strategy as agg:
+ * 1) Try single key (isBalanced); 2) Try numeric+date keys (remove strings);
+ * 3) Fall back. Returns (leftKeys, rightKeys) or empty.
+ */
+ public static Optional<Pair<List<ExprId>, List<ExprId>>> tryFindOptimalShuffleKeyForBothAggChildren(
+ PhysicalHashJoin<? extends Plan, ? extends Plan> hashJoin, PlanContext context) {
+ Optional<GroupExpression> joinGroupExpr = hashJoin.getGroupExpression();
+ if (!joinGroupExpr.isPresent()) {
+ return Optional.empty();
+ }
+ Group leftGroup = joinGroupExpr.get().child(0);
+ Group rightGroup = joinGroupExpr.get().child(1);
+ Optional<Pair<PhysicalHashAggregate<? extends Plan>, Statistics>> leftOpt =
+ getGlobalAggInputStatsFromGroup(leftGroup);
+ Optional<Pair<PhysicalHashAggregate<? extends Plan>, Statistics>> rightOpt =
+ getGlobalAggInputStatsFromGroup(rightGroup);
+ if (!leftOpt.isPresent() || !rightOpt.isPresent()) {
+ return Optional.empty();
+ }
+
+ PhysicalHashAggregate<? extends Plan> leftAgg = leftOpt.get().first;
+ PhysicalHashAggregate<? extends Plan> rightAgg = rightOpt.get().first;
+ if (leftAgg.hasSourceRepeat() || rightAgg.hasSourceRepeat()) {
+ return Optional.empty();
+ }
+ Statistics leftStats = leftOpt.get().second;
+ Statistics rightStats = rightOpt.get().second;
+
+ Pair<List<ExprId>, List<ExprId>> joinKeys = hashJoin.getHashConjunctsExprIds();
+ if (joinKeys.first.isEmpty() || joinKeys.second.size() != joinKeys.first.size()) {
+ return Optional.empty();
+ }
+
+ Set<ExprId> leftGbyIds = leftAgg.getGroupByExpressions().stream()
+ .filter(SlotReference.class::isInstance)
+ .map(SlotReference.class::cast)
+ .map(SlotReference::getExprId)
+ .collect(Collectors.toSet());
+ Set<ExprId> rightGbyIds = rightAgg.getGroupByExpressions().stream()
+ .filter(SlotReference.class::isInstance)
+ .map(SlotReference.class::cast)
+ .map(SlotReference::getExprId)
+ .collect(Collectors.toSet());
+
+ double leftRows = leftStats.getRowCount();
+ double rightRows = rightStats.getRowCount();
+ int instanceNum = context.getConnectContext().getTotalInstanceNum();
+
+ // Build (leftSlotRef, rightSlotRef) pairs for join keys in both gby sets
+ List<Pair<SlotReference, SlotReference>> validPairs = new ArrayList<>();
+ for (int i = 0; i < joinKeys.first.size(); i++) {
+ ExprId leftId = joinKeys.first.get(i);
+ ExprId rightId = joinKeys.second.get(i);
+ if (!leftGbyIds.contains(leftId) || !rightGbyIds.contains(rightId)) {
+ continue;
+ }
+ SlotReference leftSlotRef = leftAgg.getGroupByExpressions().stream()
+ .filter(e -> e instanceof SlotReference && ((SlotReference) e).getExprId().equals(leftId))
+ .map(SlotReference.class::cast)
+ .findFirst()
+ .orElse(null);
+ SlotReference rightSlotRef = rightAgg.getGroupByExpressions().stream()
+ .filter(e -> e instanceof SlotReference && ((SlotReference) e).getExprId().equals(rightId))
+ .map(SlotReference.class::cast)
+ .findFirst()
+ .orElse(null);
+ if (leftSlotRef != null && rightSlotRef != null) {
+ validPairs.add(Pair.of(leftSlotRef, rightSlotRef));
+ }
+ }
+ if (validPairs.isEmpty()) {
+ return Optional.empty();
+ }
+ // If any join key pair lacks column stats on either side, skip optimization.
+ for (Pair<SlotReference, SlotReference> pair : validPairs) {
+ if (leftStats.findColumnStatistics(pair.first) == null
+ || rightStats.findColumnStatistics(pair.second) == null) {
+ return Optional.empty();
+ }
+ }
+
+ // Step 1: Try single key - sort by type, pick first where both isBalanced
+ List<Pair<SlotReference, SlotReference>> sortedPairs =
+ sortJoinKeyPairsByTypePriority(validPairs, leftStats, rightStats);
+ for (Pair<SlotReference, SlotReference> pair : sortedPairs) {
+ SlotReference leftSlotRef = pair.first;
+ SlotReference rightSlotRef = pair.second;
+ ColumnStatistic leftColStats = leftStats.findColumnStatistics(leftSlotRef);
+ ColumnStatistic rightColStats = rightStats.findColumnStatistics(rightSlotRef);
+ if (StatisticsUtil.isBalanced(leftColStats, leftRows, instanceNum)
+ && StatisticsUtil.isBalanced(rightColStats, rightRows, instanceNum)) {
+ return Optional.of(Pair.of(
+ ImmutableList.of(leftSlotRef.getExprId()),
+ ImmutableList.of(rightSlotRef.getExprId())));
+ }
+ }
+
+ // Step 2: Try remove string types - filter numeric+date pairs, check combined NDV
+ List<SlotReference> numericDateLeftSlots = new ArrayList<>();
+ List<SlotReference> numericDateRightSlots = new ArrayList<>();
+ for (Pair<SlotReference, SlotReference> pair : validPairs) {
+ if ((pair.first.getDataType().isNumericType() || pair.first.getDataType().isDateLikeType())
+ && (pair.second.getDataType().isNumericType() || pair.second.getDataType().isDateLikeType())) {
+ numericDateLeftSlots.add(pair.first);
+ numericDateRightSlots.add(pair.second);
+ }
+ }
+ if (!numericDateLeftSlots.isEmpty()) {
+ double leftCombinedNdv = StatsCalculator.estimateGroupByRowCount(
+ new ArrayList<>(numericDateLeftSlots), leftStats);
+ double rightCombinedNdv = StatsCalculator.estimateGroupByRowCount(
+ new ArrayList<>(numericDateRightSlots), rightStats);
+ long ndvThreshold = (long) instanceNum * AggregateUtils.NDV_INSTANCE_BALANCE_MULTIPLIER;
+ if (leftCombinedNdv > ndvThreshold && rightCombinedNdv > ndvThreshold) {
+ List<ExprId> leftIds = numericDateLeftSlots.stream()
+ .map(SlotReference::getExprId)
+ .collect(Collectors.toList());
+ List<ExprId> rightIds = numericDateRightSlots.stream()
+ .map(SlotReference::getExprId)
+ .collect(Collectors.toList());
+ return Optional.of(Pair.of(leftIds, rightIds));
+ }
+ }
+
+ // Step 3: Fall back
+ return Optional.empty();
+ }
+
+ /**
+ * Pick optimal shuffle keys for a hash join from its hash join keys.
+ * Uses the same three-step strategy as agg shuffle-key pruning:
+ * 1) Try single key (isBalanced); 2) Try numeric+date keys (remove strings);
+ * 3) Fall back (empty).
+ * @return (leftKeys, rightKeys) or empty.
+ */
+ public static Optional<Pair<List<ExprId>, List<ExprId>>> tryFindOptimalShuffleKeyForJoin(
+ PhysicalHashJoin<? extends Plan, ? extends Plan> hashJoin, PlanContext context) {
+ if (!context.getConnectContext().getSessionVariable().enableAggShuffleKeyPrune) {
+ return Optional.empty();
+ }
+
+ Optional<GroupExpression> joinGroupExpr = hashJoin.getGroupExpression();
+ if (!joinGroupExpr.isPresent()) {
+ return Optional.empty();
+ }
+ Statistics leftStats = joinGroupExpr.get().child(0).getStatistics();
+ Statistics rightStats = joinGroupExpr.get().child(1).getStatistics();
+ if (leftStats == null || rightStats == null) {
+ return Optional.empty();
+ }
+
+ Pair<List<ExprId>, List<ExprId>> joinKeys = hashJoin.getHashConjunctsExprIds();
+ if (joinKeys.first.isEmpty() || joinKeys.second.size() != joinKeys.first.size()) {
+ return Optional.empty();
+ }
+
+ double leftRows = leftStats.getRowCount();
+ double rightRows = rightStats.getRowCount();
+ int instanceNum = context.getConnectContext().getTotalInstanceNum();
+
+ Map<ExprId, SlotReference> leftExprIdToSlotRef = new HashMap<>();
+ for (Slot slot : hashJoin.left().getOutput()) {
+ if (slot instanceof SlotReference) {
+ leftExprIdToSlotRef.put(slot.getExprId(), (SlotReference) slot);
+ }
+ }
+ Map<ExprId, SlotReference> rightExprIdToSlotRef = new HashMap<>();
+ for (Slot slot : hashJoin.right().getOutput()) {
+ if (slot instanceof SlotReference) {
+ rightExprIdToSlotRef.put(slot.getExprId(), (SlotReference) slot);
+ }
+ }
+
+ // Build (leftSlotRef, rightSlotRef) pairs for join keys.
+ List<Pair<SlotReference, SlotReference>> validPairs = new ArrayList<>();
+ for (int i = 0; i < joinKeys.first.size(); i++) {
+ ExprId leftId = joinKeys.first.get(i);
+ ExprId rightId = joinKeys.second.get(i);
+ SlotReference leftSlotRef = leftExprIdToSlotRef.get(leftId);
+ SlotReference rightSlotRef = rightExprIdToSlotRef.get(rightId);
+ if (leftSlotRef != null && rightSlotRef != null) {
+ validPairs.add(Pair.of(leftSlotRef, rightSlotRef));
+ }
+ }
+ if (validPairs.isEmpty()) {
+ return Optional.empty();
+ }
+ // If any join key pair lacks column stats on either side, skip optimization.
+ for (Pair<SlotReference, SlotReference> pair : validPairs) {
+ if (leftStats.findColumnStatistics(pair.first) == null
+ || rightStats.findColumnStatistics(pair.second) == null) {
+ return Optional.empty();
+ }
+ }
+
+ // Step 1: Try single key - sort by type, pick first where both isBalanced
+ List<Pair<SlotReference, SlotReference>> sortedPairs =
+ sortJoinKeyPairsByTypePriority(validPairs, leftStats, rightStats);
+ for (Pair<SlotReference, SlotReference> pair : sortedPairs) {
+ SlotReference leftSlotRef = pair.first;
+ SlotReference rightSlotRef = pair.second;
+ ColumnStatistic leftColStats = leftStats.findColumnStatistics(leftSlotRef);
+ ColumnStatistic rightColStats = rightStats.findColumnStatistics(rightSlotRef);
+ if (StatisticsUtil.isBalanced(leftColStats, leftRows, instanceNum)
+ && StatisticsUtil.isBalanced(rightColStats, rightRows, instanceNum)) {
+ return Optional.of(Pair.of(
+ ImmutableList.of(leftSlotRef.getExprId()),
+ ImmutableList.of(rightSlotRef.getExprId())));
+ }
+ }
+
+ // Step 2: Try remove string types - filter numeric+date pairs, check combined NDV
+ List<SlotReference> numericDateLeftSlots = new ArrayList<>();
+ List<SlotReference> numericDateRightSlots = new ArrayList<>();
+ for (Pair<SlotReference, SlotReference> pair : validPairs) {
+ if ((pair.first.getDataType().isNumericType() || pair.first.getDataType().isDateLikeType())
+ && (pair.second.getDataType().isNumericType() || pair.second.getDataType().isDateLikeType())) {
+ numericDateLeftSlots.add(pair.first);
+ numericDateRightSlots.add(pair.second);
+ }
+ }
+ if (!numericDateLeftSlots.isEmpty()) {
+ double leftCombinedNdv = StatsCalculator.estimateGroupByRowCount(
+ new ArrayList<>(numericDateLeftSlots), leftStats);
+ double rightCombinedNdv = StatsCalculator.estimateGroupByRowCount(
+ new ArrayList<>(numericDateRightSlots), rightStats);
+ long ndvThreshold = (long) instanceNum * AggregateUtils.NDV_INSTANCE_BALANCE_MULTIPLIER;
+ if (leftCombinedNdv > ndvThreshold && rightCombinedNdv > ndvThreshold) {
+ List<ExprId> leftIds = numericDateLeftSlots.stream()
+ .map(SlotReference::getExprId)
+ .collect(Collectors.toList());
+ List<ExprId> rightIds = numericDateRightSlots.stream()
+ .map(SlotReference::getExprId)
+ .collect(Collectors.toList());
+ return Optional.of(Pair.of(leftIds, rightIds));
+ }
+ }
+
+ // Step 3: Fall back
+ return Optional.empty();
+ }
+
+ /** Sort join key pairs by type priority (numeric/date first, string by avg_size). */
+ private static List<Pair<SlotReference, SlotReference>> sortJoinKeyPairsByTypePriority(
+ List<Pair<SlotReference, SlotReference>> pairs, Statistics leftStats, Statistics rightStats) {
+ List<Pair<SlotReference, SlotReference>> result = new ArrayList<>(pairs);
+ result.sort(Comparator
+ .comparingInt((Pair<SlotReference, SlotReference> p) ->
+ getTypeSortPriority(p.first.getDataType()))
+ .thenComparingDouble((Pair<SlotReference, SlotReference> p) ->
+ getJoinPairStringAvgSizeForSort(p, leftStats, rightStats)));
+ return result;
+ }
+
+ /** For string join-key pairs, use avg size of both sides for sorting; for others return 0. */
+ private static double getJoinPairStringAvgSizeForSort(Pair<SlotReference, SlotReference> pair,
+ Statistics leftStats, Statistics rightStats) {
+ if (pair.first.getDataType() instanceof CharacterType && pair.second.getDataType() instanceof CharacterType) {
+ return (getStringAvgSizeForSort(pair.first, leftStats) + getStringAvgSizeForSort(pair.second, rightStats));
+ }
+ return 0;
+ }
+}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/implementation/SplitAggBaseRule.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/implementation/SplitAggBaseRule.java
index a43fb753c2a..197081f5332 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/implementation/SplitAggBaseRule.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/implementation/SplitAggBaseRule.java
@@ -82,7 +82,7 @@ public abstract class SplitAggBaseRule {
}
return new PhysicalHashAggregate<>(localAggGroupBy, localAggOutput, Optional.ofNullable(partitionExpressions),
inputToBufferParam, AggregateUtils.maybeUsingStreamAgg(localAggGroupBy, inputToBufferParam),
- null, child);
+ Optional.empty(), null, aggregate.getSourceRepeat().isPresent(), child);
}
/**
@@ -125,7 +125,7 @@ public abstract class SplitAggBaseRule {
return new PhysicalHashAggregate<>(localAgg.getGroupByExpressions(), middleAggOutput,
Optional.ofNullable(partitionExpressions), bufferToResultParam,
AggregateUtils.maybeUsingStreamAgg(localAgg.getGroupByExpressions(), bufferToResultParam),
- null, localAgg);
+ Optional.empty(), null, aggregate.getSourceRepeat().isPresent(), localAgg);
}
/**
@@ -167,7 +167,8 @@ public abstract class SplitAggBaseRule {
.addAll(aggFuncToAliasThird.values())
.build();
Plan thirdAgg = new PhysicalHashAggregate<>(aggregate.getGroupByExpressions(), thirdAggOutput, thirdParam,
- AggregateUtils.maybeUsingStreamAgg(aggregate.getGroupByExpressions(), thirdParam), null, child);
+ AggregateUtils.maybeUsingStreamAgg(aggregate.getGroupByExpressions(), thirdParam), null,
+ aggregate.getSourceRepeat().isPresent(), child);
// fourth phase
AggregateParam fourthParam = new AggregateParam(AggPhase.DISTINCT_GLOBAL, AggMode.BUFFER_TO_RESULT);
@@ -197,6 +198,6 @@ public abstract class SplitAggBaseRule {
return new PhysicalHashAggregate<>(aggregate.getGroupByExpressions(), globalOutput,
Optional.ofNullable(aggregate.getGroupByExpressions()), fourthParam,
AggregateUtils.maybeUsingStreamAgg(aggregate.getGroupByExpressions(), fourthParam),
- aggregate.getLogicalProperties(), thirdAgg);
+ aggregate.getLogicalProperties(), aggregate.getSourceRepeat().isPresent(), thirdAgg);
}
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/implementation/SplitAggMultiPhase.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/implementation/SplitAggMultiPhase.java
index 11d1baa0f75..9a5b9ad412a 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/implementation/SplitAggMultiPhase.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/implementation/SplitAggMultiPhase.java
@@ -116,14 +116,15 @@ public class SplitAggMultiPhase extends SplitAggBaseRule implements Implementati
if (aggregate.supportAggregatePhase(AggregatePhase.THREE)) {
Set<NamedExpression> localAggGroupBySet = AggregateUtils.getAllKeySet(aggregate);
Map<AggregateFunction, Alias> middleAggFuncToAlias = new LinkedHashMap<>();
+ List<Expression> partitionExprs = Utils.fastToImmutableList(aggregate.getGroupByExpressions());
Plan middleAgg = splitDeduplicateTwoPhase(aggregate, middleAggFuncToAlias,
- aggregate.getGroupByExpressions(), localAggGroupBySet);
+ partitionExprs, localAggGroupBySet);
builder.add(splitDistinctOnePhase(aggregate, middleAggFuncToAlias, middleAgg));
}
if (aggregate.supportAggregatePhase(AggregatePhase.TWO)) {
Map<AggregateFunction, Alias> localAggFuncToAlias = new HashMap<>();
- Plan localAgg = splitToOnePhase(aggregate, Utils.fastToImmutableList(aggregate.getGroupByExpressions()),
- localAggFuncToAlias);
+ List<Expression> partitionExprs = Utils.fastToImmutableList(aggregate.getGroupByExpressions());
+ Plan localAgg = splitToOnePhase(aggregate, partitionExprs, localAggFuncToAlias);
builder.add(splitDistinctOnePhase(aggregate, localAggFuncToAlias, localAgg));
}
return builder.build();
@@ -158,15 +159,21 @@ public class SplitAggMultiPhase extends SplitAggBaseRule implements Implementati
private List<Plan> splitToTwoPlusTwoPhase(LogicalAggregate<? extends Plan> aggregate) {
ImmutableList.Builder<Plan> builder = ImmutableList.builder();
Set<NamedExpression> localAggGroupBySet = AggregateUtils.getAllKeySet(aggregate);
+ // 2+2 lower shuffle: full partition = distinct (a-e) + gby (f-j), mark split for RequestPropertyDeriver
+ List<Expression> distinctExprs = Utils.fastToImmutableList(aggregate.getDistinctArguments());
+ List<Expression> gbyExprs = Utils.fastToImmutableList(aggregate.getGroupByExpressions());
+ List<Expression> partitionExprs = ImmutableList.<Expression>builder()
+ .addAll(gbyExprs)
+ .addAll(distinctExprs)
+ .build();
if (aggregate.supportAggregatePhase(AggregatePhase.FOUR)) {
Map<AggregateFunction, Alias> middleAggFunctionToAlias = new LinkedHashMap<>();
Plan twoPhaseAgg = splitDeduplicateTwoPhase(aggregate, middleAggFunctionToAlias,
- Utils.fastToImmutableList(localAggGroupBySet), localAggGroupBySet);
+ partitionExprs, localAggGroupBySet);
builder.add(splitDistinctTwoPhase(aggregate, middleAggFunctionToAlias, twoPhaseAgg));
}
Map<AggregateFunction, Alias> localAggFunctionToAlias = new HashMap<>();
- Plan onePhaseAgg = splitToOnePhase(aggregate, Utils.fastToImmutableList(localAggGroupBySet),
- localAggFunctionToAlias);
+ Plan onePhaseAgg = splitToOnePhase(aggregate, partitionExprs, localAggFunctionToAlias);
if (aggregate.supportAggregatePhase(AggregatePhase.THREE)) {
builder.add(splitDistinctTwoPhase(aggregate, localAggFunctionToAlias, onePhaseAgg));
}
@@ -229,7 +236,7 @@ public class SplitAggMultiPhase extends SplitAggBaseRule implements Implementati
return new PhysicalHashAggregate<>(aggregate.getGroupByExpressions(), globalOutput,
Optional.ofNullable(aggregate.getGroupByExpressions()), inputToResultParamSecond,
AggregateUtils.maybeUsingStreamAgg(aggregate.getGroupByExpressions(), inputToResultParamSecond),
- aggregate.getLogicalProperties(), child);
+ aggregate.getLogicalProperties(), aggregate.getSourceRepeat().isPresent(), child);
}
/**
@@ -257,7 +264,7 @@ public class SplitAggMultiPhase extends SplitAggBaseRule implements Implementati
boolean maybeUsingStreamAgg = AggregateUtils.maybeUsingStreamAgg(localAggGroupBy, localParam);
PhysicalHashAggregate<Plan> localAgg = new PhysicalHashAggregate<>(localAggGroupBy, localAggOutput,
Optional.empty(), localParam, maybeUsingStreamAgg,
- null, logicalAgg.child());
+ null, logicalAgg.getSourceRepeat().isPresent(), logicalAgg.child());
// add shuffle expr in project
ImmutableList.Builder<NamedExpression> projections = ImmutableList.builderWithExpectedSize(
localAgg.getOutputs().size() + 1);
@@ -290,7 +297,7 @@ public class SplitAggMultiPhase extends SplitAggBaseRule implements Implementati
PhysicalHashAggregate<Plan> secondPhaseAgg = new PhysicalHashAggregate<>(
secondPhaseAggGroupBy, secondPhaseAggOutput.build(),
Optional.of(secondPhaseAggGroupBy), secondParam, false,
- null, physicalProject);
+ null, logicalAgg.getSourceRepeat().isPresent(), physicalProject);
// 3. third phase agg
List<Expression> thirdPhaseAggGroupBy = Utils.fastToImmutableList(logicalAgg.getGroupByExpressions());
@@ -304,7 +311,7 @@ public class SplitAggMultiPhase extends SplitAggBaseRule implements Implementati
PhysicalHashAggregate<Plan> thirdPhaseAgg = new PhysicalHashAggregate<>(
thirdPhaseAggGroupBy, thirdPhaseAggOutput.build(),
Optional.empty(), thirdParam, false,
- null, secondPhaseAgg);
+ null, logicalAgg.getSourceRepeat().isPresent(), secondPhaseAgg);
// 4. fourth phase agg
ImmutableList.Builder<NamedExpression> fourthPhaseAggOutput = ImmutableList.builderWithExpectedSize(
@@ -317,7 +324,7 @@ public class SplitAggMultiPhase extends SplitAggBaseRule implements Implementati
fourthPhaseAggOutput.add(sumAliasFour);
return new PhysicalHashAggregate<>(thirdPhaseAggGroupBy,
fourthPhaseAggOutput.build(), Optional.of(logicalAgg.getGroupByExpressions()), fourthParam,
- false, logicalAgg.getLogicalProperties(), thirdPhaseAgg);
+ false, logicalAgg.getLogicalProperties(), logicalAgg.getSourceRepeat().isPresent(), thirdPhaseAgg);
}
private static AggregateFunction getAggregateFunction(AggregateFunction aggFunc, Slot child) {
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/implementation/SplitAggMultiPhaseWithoutGbyKey.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/implementation/SplitAggMultiPhaseWithoutGbyKey.java
index cb3a7435a0f..294777a900a 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/implementation/SplitAggMultiPhaseWithoutGbyKey.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/implementation/SplitAggMultiPhaseWithoutGbyKey.java
@@ -17,10 +17,12 @@
package org.apache.doris.nereids.rules.implementation;
+import org.apache.doris.nereids.CascadesContext;
import org.apache.doris.nereids.rules.Rule;
import org.apache.doris.nereids.rules.RuleType;
import org.apache.doris.nereids.trees.expressions.AggregateExpression;
import org.apache.doris.nereids.trees.expressions.Alias;
+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.functions.agg.AggregateFunction;
@@ -74,21 +76,21 @@ public class SplitAggMultiPhaseWithoutGbyKey extends SplitAggBaseRule implements
logicalAggregate()
.when(agg -> agg.getGroupByExpressions().isEmpty())
.when(agg -> agg.getDistinctArguments().size() == 1 || agg.distinctFuncNum() == 1)
- .thenApplyMulti(ctx -> rewrite(ctx.root))
+ .thenApplyMulti(ctx -> rewrite(ctx.root, ctx.cascadesContext))
.toRule(RuleType.SPLIT_AGG_MULTI_PHASE_WITHOUT_GBY_KEY)
);
}
- List<Plan> rewrite(LogicalAggregate<? extends Plan> aggregate) {
+ List<Plan> rewrite(LogicalAggregate<? extends Plan> aggregate, CascadesContext cascadesContext) {
if (canUseFinalMultiDistinct(aggregate)) {
return ImmutableList.<Plan>builder()
.addAll(twoPhaseAggregateWithFinalMultiDistinct(aggregate))
- .addAll(splitToFourPhase(aggregate))
+ .addAll(splitToFourPhase(aggregate, cascadesContext))
.build();
} else {
return ImmutableList.<Plan>builder()
.addAll(splitToThreePhase(aggregate))
- .addAll(splitToFourPhase(aggregate))
+ .addAll(splitToFourPhase(aggregate, cascadesContext))
.build();
}
}
@@ -103,13 +105,15 @@ public class SplitAggMultiPhaseWithoutGbyKey extends SplitAggBaseRule implements
* +--hashShuffle(a)
* +--agg(group by a; agg_phase: local)
*/
- private List<Plan> splitToFourPhase(LogicalAggregate<? extends Plan> aggregate) {
+ private List<Plan> splitToFourPhase(LogicalAggregate<? extends Plan> aggregate,
+ CascadesContext cascadesContext) {
if (!aggregate.supportAggregatePhase(AggregatePhase.FOUR)) {
return ImmutableList.of();
}
Map<AggregateFunction, Alias> localAggFuncToAlias = new LinkedHashMap<>();
+ List<Expression> partitionExprs = Utils.fastToImmutableList(aggregate.getDistinctArguments());
Plan secondAgg = splitDeduplicateTwoPhase(aggregate, localAggFuncToAlias,
- Utils.fastToImmutableList(aggregate.getDistinctArguments()), AggregateUtils.getAllKeySet(aggregate));
+ partitionExprs, AggregateUtils.getAllKeySet(aggregate));
return ImmutableList.of(splitDistinctTwoPhase(aggregate, localAggFuncToAlias, secondAgg));
}
@@ -162,7 +166,7 @@ public class SplitAggMultiPhaseWithoutGbyKey extends SplitAggBaseRule implements
Plan anyLocalAgg = new PhysicalHashAggregate<>(logicalAgg.getGroupByExpressions(), localAggOutput,
Optional.of(Utils.fastToImmutableList(logicalAgg.getDistinctArguments())), inputToResultParam,
AggregateUtils.maybeUsingStreamAgg(logicalAgg.getGroupByExpressions(), inputToResultParam),
- null, logicalAgg.child());
+ null, logicalAgg.getSourceRepeat().isPresent(), logicalAgg.child());
AggregateParam param = new AggregateParam(AggPhase.GLOBAL, AggMode.INPUT_TO_RESULT, false);
@@ -205,7 +209,7 @@ public class SplitAggMultiPhaseWithoutGbyKey extends SplitAggBaseRule implements
});
return ImmutableList.of(new PhysicalHashAggregate<>(logicalAgg.getGroupByExpressions(), globalOutput, param,
AggregateUtils.maybeUsingStreamAgg(logicalAgg.getGroupByExpressions(), param),
- logicalAgg.getLogicalProperties(), anyLocalAgg));
+ logicalAgg.getLogicalProperties(), logicalAgg.getSourceRepeat().isPresent(), anyLocalAgg));
}
private boolean canUseFinalMultiDistinct(Aggregate<? extends Plan> agg) {
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/implementation/SplitAggWithoutDistinct.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/implementation/SplitAggWithoutDistinct.java
index de9526005d0..cc241ee5890 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/implementation/SplitAggWithoutDistinct.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/implementation/SplitAggWithoutDistinct.java
@@ -99,7 +99,7 @@ public class SplitAggWithoutDistinct extends OneImplementationRuleFactory {
return ImmutableList.of(new PhysicalHashAggregate<>(logicalAgg.getGroupByExpressions(), aggOutput,
logicalAgg.getPartitionExpressions(), param,
AggregateUtils.maybeUsingStreamAgg(logicalAgg.getGroupByExpressions(), param),
- null, logicalAgg.child()));
+ null, logicalAgg.getSourceRepeat().isPresent(), logicalAgg.child()));
}
/**
@@ -143,7 +143,7 @@ public class SplitAggWithoutDistinct extends OneImplementationRuleFactory {
PhysicalHashAggregate<? extends Plan> localAgg = new PhysicalHashAggregate<>(aggregate.getGroupByExpressions(),
localAggOutput, inputToBufferParam,
AggregateUtils.maybeUsingStreamAgg(aggregate.getGroupByExpressions(), inputToBufferParam),
- null, aggregate.child());
+ null, aggregate.getSourceRepeat().isPresent(), aggregate.child());
// global agg
AggregateParam bufferToResultParam = new AggregateParam(AggPhase.GLOBAL, AggMode.BUFFER_TO_RESULT);
@@ -162,7 +162,7 @@ public class SplitAggWithoutDistinct extends OneImplementationRuleFactory {
return ImmutableList.of(new PhysicalHashAggregate<>(aggregate.getGroupByExpressions(),
globalAggOutput, aggregate.getPartitionExpressions(), bufferToResultParam,
AggregateUtils.maybeUsingStreamAgg(aggregate.getGroupByExpressions(), bufferToResultParam),
- aggregate.getLogicalProperties(), localAgg));
+ aggregate.getLogicalProperties(), aggregate.getSourceRepeat().isPresent(), localAgg));
}
private boolean shouldUseLocalAgg(LogicalAggregate<? extends Plan> aggregate) {
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpJoinFromUnionAll.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpJoinFromUnionAll.java
index 8c21b12a6d8..f7197516011 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpJoinFromUnionAll.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/PullUpJoinFromUnionAll.java
@@ -57,7 +57,7 @@ import javax.annotation.Nullable;
* | |
* Join Join
* / \ / \
- * t1 t2 t1 t3 (t1 is common side; t2,t3 is other side)
+ * t2 t1 t3 t1 (t1 is common side; t2,t3 is other side)
* =====>
* project
* |
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/SaltJoin.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/SaltJoin.java
index 98b82a3212a..bccaac9fb11 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/SaltJoin.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/SaltJoin.java
@@ -343,11 +343,7 @@ public class SaltJoin extends OneRewriteRuleFactory {
int factor = connectContext.getStatementContext().getConnectContext()
.getSessionVariable().skewRewriteJoinSaltExplodeFactor;
if (factor == 0) {
- int beNumber = Math.max(1, connectContext.getEnv().getClusterInfo().getBackendsNumber(true));
- String clusterName = connectContext.getSessionVariable().resolveCloudClusterName(connectContext);
- int parallelInstance = Math.max(1,
- connectContext.getSessionVariable().getParallelExecInstanceNum(clusterName));
- factor = (int) Math.min((long) beNumber * parallelInstance * SALT_FACTOR, Integer.MAX_VALUE);
+ factor = (int) Math.min((long) connectContext.getTotalInstanceNum() * SALT_FACTOR, Integer.MAX_VALUE);
}
return Math.max(factor, 1);
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/physical/PhysicalDistribute.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/physical/PhysicalDistribute.java
index 3dfc30847ef..9c6e0491d52 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/physical/PhysicalDistribute.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/physical/PhysicalDistribute.java
@@ -19,15 +19,19 @@ package org.apache.doris.nereids.trees.plans.physical;
import org.apache.doris.nereids.memo.GroupExpression;
import org.apache.doris.nereids.properties.DistributionSpec;
+import org.apache.doris.nereids.properties.DistributionSpecHash;
import org.apache.doris.nereids.properties.LogicalProperties;
import org.apache.doris.nereids.properties.PhysicalProperties;
+import org.apache.doris.nereids.trees.expressions.ExprId;
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.plans.Plan;
import org.apache.doris.nereids.trees.plans.PlanType;
import org.apache.doris.nereids.trees.plans.PropagateFuncDeps;
import org.apache.doris.nereids.trees.plans.visitor.PlanVisitor;
import org.apache.doris.nereids.util.Utils;
+import org.apache.doris.qe.ConnectContext;
import org.apache.doris.statistics.Statistics;
import com.google.common.base.Preconditions;
@@ -35,7 +39,9 @@ import com.google.common.collect.ImmutableList;
import org.json.JSONObject;
import java.util.List;
+import java.util.Map;
import java.util.Optional;
+import java.util.stream.Collectors;
/**
* Enforcer plan. Generated when upstream's physical property mismatches downstream's required physical property.
@@ -138,6 +144,24 @@ public class PhysicalDistribute<CHILD_TYPE extends Plan> extends PhysicalUnary<C
public String shapeInfo() {
StringBuilder builder = new StringBuilder("PhysicalDistribute");
builder.append("[").append(getDistributionSpec().shapeInfo()).append("]");
+ ConnectContext context = ConnectContext.get();
+ if (context != null
+ && context.getSessionVariable().getDetailShapePlanNodesSet().contains(getClass().getSimpleName())) {
+ if (distributionSpec instanceof DistributionSpecHash) {
+ builder.append(" Hash Columns:");
+ DistributionSpecHash hash = (DistributionSpecHash) distributionSpec;
+ Map<ExprId, Slot> outputById = child().getOutput().stream()
+ .collect(Collectors.toMap(NamedExpression::getExprId, slot -> slot, (a, b) -> a));
+ builder.append(
+ hash.getOrderedShuffledColumns().stream()
+ .map(id -> {
+ Slot slot = outputById.get(id);
+ return slot == null ? String.valueOf(id) : slot.getName();
+ })
+ .collect(Collectors.toList())
+ );
+ }
+ }
return builder.toString();
}
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/physical/PhysicalHashAggregate.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/physical/PhysicalHashAggregate.java
index 3a53381e3bb..a9c709b4738 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/physical/PhysicalHashAggregate.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/physical/PhysicalHashAggregate.java
@@ -70,18 +70,20 @@ public class PhysicalHashAggregate<CHILD_TYPE extends Plan> extends PhysicalUnar
private final boolean maybeUsingStream;
+ private final boolean hasSourceRepeat;
+
public PhysicalHashAggregate(List<Expression> groupByExpressions, List<NamedExpression> outputExpressions,
AggregateParam aggregateParam, boolean maybeUsingStream, LogicalProperties logicalProperties,
- CHILD_TYPE child) {
+ boolean hasSourceRepeat, CHILD_TYPE child) {
this(groupByExpressions, outputExpressions, Optional.empty(), aggregateParam,
- maybeUsingStream, Optional.empty(), logicalProperties, child);
+ maybeUsingStream, Optional.empty(), logicalProperties, hasSourceRepeat, child);
}
public PhysicalHashAggregate(List<Expression> groupByExpressions, List<NamedExpression> outputExpressions,
Optional<List<Expression>> partitionExpressions, AggregateParam aggregateParam,
- boolean maybeUsingStream, LogicalProperties logicalProperties, CHILD_TYPE child) {
+ boolean maybeUsingStream, LogicalProperties logicalProperties, boolean hasSourceRepeat, CHILD_TYPE child) {
this(groupByExpressions, outputExpressions, partitionExpressions, aggregateParam,
- maybeUsingStream, Optional.empty(), logicalProperties, child);
+ maybeUsingStream, Optional.empty(), logicalProperties, hasSourceRepeat, child);
}
/**
@@ -94,7 +96,8 @@ public class PhysicalHashAggregate<CHILD_TYPE extends Plan> extends PhysicalUnar
*/
public PhysicalHashAggregate(List<Expression> groupByExpressions, List<NamedExpression> outputExpressions,
Optional<List<Expression>> partitionExpressions, AggregateParam aggregateParam, boolean maybeUsingStream,
- Optional<GroupExpression> groupExpression, LogicalProperties logicalProperties, CHILD_TYPE child) {
+ Optional<GroupExpression> groupExpression, LogicalProperties logicalProperties,
+ boolean hasSourceRepeat, CHILD_TYPE child) {
super(PlanType.PHYSICAL_HASH_AGGREGATE, groupExpression, logicalProperties, child);
this.groupByExpressions = ImmutableList.copyOf(
Objects.requireNonNull(groupByExpressions, "groupByExpressions cannot be null"));
@@ -104,8 +107,8 @@ public class PhysicalHashAggregate<CHILD_TYPE extends Plan> extends PhysicalUnar
this.partitionExpressions = Objects.requireNonNull(
partitionExpressions, "partitionExpressions cannot be null");
this.aggregateParam = Objects.requireNonNull(aggregateParam, "aggregate param cannot be null");
- // this.aggregateParam = aggregateParam;
this.maybeUsingStream = maybeUsingStream;
+ this.hasSourceRepeat = hasSourceRepeat;
}
/**
@@ -119,7 +122,7 @@ public class PhysicalHashAggregate<CHILD_TYPE extends Plan> extends PhysicalUnar
public PhysicalHashAggregate(List<Expression> groupByExpressions, List<NamedExpression> outputExpressions,
Optional<List<Expression>> partitionExpressions, AggregateParam aggregateParam, boolean maybeUsingStream,
Optional<GroupExpression> groupExpression, LogicalProperties logicalProperties,
- PhysicalProperties physicalProperties, Statistics statistics, CHILD_TYPE child) {
+ PhysicalProperties physicalProperties, Statistics statistics, boolean hasSourceRepeat, CHILD_TYPE child) {
super(PlanType.PHYSICAL_HASH_AGGREGATE, groupExpression, logicalProperties, physicalProperties, statistics,
child);
this.groupByExpressions = ImmutableList.copyOf(
@@ -131,6 +134,7 @@ public class PhysicalHashAggregate<CHILD_TYPE extends Plan> extends PhysicalUnar
partitionExpressions, "partitionExpressions cannot be null");
this.aggregateParam = Objects.requireNonNull(aggregateParam, "aggregate param cannot be null");
this.maybeUsingStream = maybeUsingStream;
+ this.hasSourceRepeat = hasSourceRepeat;
}
@Override
@@ -182,6 +186,10 @@ public class PhysicalHashAggregate<CHILD_TYPE extends Plan> extends PhysicalUnar
.build();
}
+ public boolean hasSourceRepeat() {
+ return hasSourceRepeat;
+ }
+
@Override
public String toString() {
TopnPushInfo topnPushInfo = (TopnPushInfo) getMutableState(
@@ -254,13 +262,13 @@ public class PhysicalHashAggregate<CHILD_TYPE extends Plan> extends PhysicalUnar
Preconditions.checkArgument(children.size() == 1);
return new PhysicalHashAggregate<>(groupByExpressions, outputExpressions, partitionExpressions,
aggregateParam, maybeUsingStream, groupExpression, getLogicalProperties(),
- physicalProperties, statistics, children.get(0));
+ physicalProperties, statistics, hasSourceRepeat, children.get(0));
}
@Override
public PhysicalHashAggregate<CHILD_TYPE> withGroupExpression(Optional<GroupExpression> groupExpression) {
return new PhysicalHashAggregate<>(groupByExpressions, outputExpressions, partitionExpressions,
- aggregateParam, maybeUsingStream, groupExpression, getLogicalProperties(), child());
+ aggregateParam, maybeUsingStream, groupExpression, getLogicalProperties(), hasSourceRepeat, child());
}
@Override
@@ -268,7 +276,8 @@ public class PhysicalHashAggregate<CHILD_TYPE extends Plan> extends PhysicalUnar
Optional<LogicalProperties> logicalProperties, List<Plan> children) {
Preconditions.checkArgument(children.size() == 1);
return new PhysicalHashAggregate<>(groupByExpressions, outputExpressions, partitionExpressions,
- aggregateParam, maybeUsingStream, groupExpression, logicalProperties.get(), children.get(0));
+ aggregateParam, maybeUsingStream, groupExpression, logicalProperties.get(), hasSourceRepeat,
+ children.get(0));
}
@Override
@@ -276,14 +285,14 @@ public class PhysicalHashAggregate<CHILD_TYPE extends Plan> extends PhysicalUnar
Statistics statistics) {
return new PhysicalHashAggregate<>(groupByExpressions, outputExpressions, partitionExpressions,
aggregateParam, maybeUsingStream, groupExpression, getLogicalProperties(),
- physicalProperties, statistics, child());
+ physicalProperties, statistics, hasSourceRepeat, child());
}
@Override
public PhysicalHashAggregate<CHILD_TYPE> withAggOutput(List<NamedExpression> newOutput) {
return new PhysicalHashAggregate<>(groupByExpressions, newOutput, partitionExpressions,
aggregateParam, maybeUsingStream, Optional.empty(), getLogicalProperties(),
- physicalProperties, statistics, child());
+ physicalProperties, statistics, hasSourceRepeat, child());
}
@Override
@@ -316,7 +325,7 @@ public class PhysicalHashAggregate<CHILD_TYPE extends Plan> extends PhysicalUnar
public PhysicalHashAggregate<CHILD_TYPE> resetLogicalProperties() {
return new PhysicalHashAggregate<>(groupByExpressions, outputExpressions, partitionExpressions,
aggregateParam, maybeUsingStream, groupExpression, null,
- physicalProperties, statistics, child());
+ physicalProperties, statistics, hasSourceRepeat, child());
}
/**
diff --git a/fe/fe-core/src/main/java/org/apache/doris/qe/ConnectContext.java b/fe/fe-core/src/main/java/org/apache/doris/qe/ConnectContext.java
index 498aeb51cba..7c0cfd5f3cd 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/qe/ConnectContext.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/qe/ConnectContext.java
@@ -1628,4 +1628,17 @@ public class ConnectContext {
tableNameSet.remove(tableName);
}
}
+
+ public int getAliveBeNumber() {
+ return Math.max(1, this.getEnv().getClusterInfo().getBackendsNumber(true));
+ }
+
+ public int getParallelInstanceNum() {
+ String clusterName = this.getSessionVariable().resolveCloudClusterName(this);
+ return Math.max(1, this.getSessionVariable().getParallelExecInstanceNum(clusterName));
+ }
+
+ public int getTotalInstanceNum() {
+ return getAliveBeNumber() * getParallelInstanceNum();
+ }
}
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 e105a839edd..3b26758df7e 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
@@ -843,6 +843,7 @@ public class SessionVariable implements Serializable, Writable {
public static final String AGG_SHUFFLE_USE_PARENT_KEY = "agg_shuffle_use_parent_key";
public static final String DECOMPOSE_REPEAT_SHUFFLE_INDEX_IN_MAX_GROUP
= "decompose_repeat_shuffle_index_in_max_group";
+ public static final String ENABLE_AGG_SHUFFLE_KEY_PRUNE = "enable_agg_shuffle_key_prune";
public static final String HOT_VALUE_COLLECT_COUNT = "hot_value_collect_count";
@VariableMgr.VarAttr(name = HOT_VALUE_COLLECT_COUNT, needForward = true,
@@ -2755,6 +2756,9 @@ public class SessionVariable implements Serializable, Writable {
}, needForward = false)
public boolean aggShuffleUseParentKey = true;
+ @VariableMgr.VarAttr(name = ENABLE_AGG_SHUFFLE_KEY_PRUNE)
+ public boolean enableAggShuffleKeyPrune = true;
+
@VariableMgr.VarAttr(name = ENABLE_PREFER_CACHED_ROWSET, needForward = false,
description = {"是否启用 prefer cached rowset 功能",
"Whether to enable prefer cached rowset feature"})
diff --git a/fe/fe-core/src/main/java/org/apache/doris/statistics/util/StatisticsUtil.java b/fe/fe-core/src/main/java/org/apache/doris/statistics/util/StatisticsUtil.java
index 3368cf30c5e..09a5226dd3d 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/statistics/util/StatisticsUtil.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/statistics/util/StatisticsUtil.java
@@ -1336,18 +1336,24 @@ public class StatisticsUtil {
return null;
}
- public static boolean isBalanced(ColumnStatistic columnStatistic, double rowCount, int instanceNum) {
- double ndv = columnStatistic.ndv;
- double maxHotValueCntIncludeNull;
+ /**
+ * Get max hot value count including null for balance/skew computation.
+ * When hotValues not exist or empty, treat nulls as the only hot value.
+ * Otherwise use the max of (top hot value count, null count).
+ */
+ public static double getMaxHotValueCntIncludeNull(ColumnStatistic columnStatistic, double rowCount) {
Map<Literal, Float> hotValues = columnStatistic.getHotValues();
- // When hotValues not exist, or exist but unknown, treat nulls as the only hot value.
if (columnStatistic.getHotValues() == null || hotValues.isEmpty()) {
- maxHotValueCntIncludeNull = columnStatistic.numNulls;
- } else {
- double rate = hotValues.values().stream().mapToDouble(Float::doubleValue).max().orElse(0);
- maxHotValueCntIncludeNull = rate * rowCount > columnStatistic.numNulls
- ? rate * rowCount : columnStatistic.numNulls;
+ return columnStatistic.numNulls;
}
+ double maxRate = hotValues.values().stream().mapToDouble(Float::doubleValue).max().orElse(0);
+ double maxHotRows = maxRate * rowCount;
+ return maxHotRows > columnStatistic.numNulls ? maxHotRows : columnStatistic.numNulls;
+ }
+
+ public static boolean isBalanced(ColumnStatistic columnStatistic, double rowCount, int instanceNum) {
+ double ndv = columnStatistic.ndv;
+ double maxHotValueCntIncludeNull = getMaxHotValueCntIncludeNull(columnStatistic, rowCount);
double rowsPerInstance = (rowCount - maxHotValueCntIncludeNull) / instanceNum;
double balanceFactor = maxHotValueCntIncludeNull == 0
? Double.MAX_VALUE : rowsPerInstance / maxHotValueCntIncludeNull;
diff --git a/fe/fe-core/src/test/java/org/apache/doris/nereids/properties/ChildOutputPropertyDeriverTest.java b/fe/fe-core/src/test/java/org/apache/doris/nereids/properties/ChildOutputPropertyDeriverTest.java
index a0e2639771a..91003d55fa0 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/nereids/properties/ChildOutputPropertyDeriverTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/nereids/properties/ChildOutputPropertyDeriverTest.java
@@ -716,6 +716,7 @@ class ChildOutputPropertyDeriverTest {
new AggregateParam(AggPhase.LOCAL, AggMode.INPUT_TO_BUFFER),
true,
logicalProperties,
+ false,
groupPlan
);
GroupExpression groupExpression = new GroupExpression(aggregate);
@@ -740,6 +741,7 @@ class ChildOutputPropertyDeriverTest {
new AggregateParam(AggPhase.GLOBAL, AggMode.BUFFER_TO_RESULT),
true,
logicalProperties,
+ false,
groupPlan
);
GroupExpression groupExpression = new GroupExpression(aggregate);
@@ -769,6 +771,7 @@ class ChildOutputPropertyDeriverTest {
new AggregateParam(AggPhase.LOCAL, AggMode.BUFFER_TO_RESULT),
true,
logicalProperties,
+ false,
groupPlan
);
diff --git a/fe/fe-core/src/test/java/org/apache/doris/nereids/properties/RequestPropertyDeriverTest.java b/fe/fe-core/src/test/java/org/apache/doris/nereids/properties/RequestPropertyDeriverTest.java
index 32a6adcf212..b69a2c5b08f 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/nereids/properties/RequestPropertyDeriverTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/nereids/properties/RequestPropertyDeriverTest.java
@@ -141,7 +141,7 @@ class RequestPropertyDeriverTest {
GroupExpression groupExpression = new GroupExpression(join, Lists.newArrayList(group, group));
new Group(null, groupExpression, null);
- RequestPropertyDeriver requestPropertyDeriver = new RequestPropertyDeriver(null, jobContext);
+ RequestPropertyDeriver requestPropertyDeriver = new RequestPropertyDeriver(connectContext, jobContext);
List<List<PhysicalProperties>> actual
= requestPropertyDeriver.getRequestChildrenPropertyList(groupExpression);
@@ -177,7 +177,7 @@ class RequestPropertyDeriverTest {
GroupExpression groupExpression = new GroupExpression(join, Lists.newArrayList(group, group));
new Group(null, groupExpression, null);
- RequestPropertyDeriver requestPropertyDeriver = new RequestPropertyDeriver(null, jobContext);
+ RequestPropertyDeriver requestPropertyDeriver = new RequestPropertyDeriver(connectContext, jobContext);
List<List<PhysicalProperties>> actual
= requestPropertyDeriver.getRequestChildrenPropertyList(groupExpression);
@@ -200,6 +200,7 @@ class RequestPropertyDeriverTest {
new AggregateParam(AggPhase.LOCAL, AggMode.INPUT_TO_RESULT),
true,
logicalProperties,
+ false,
groupPlan
);
GroupExpression groupExpression = new GroupExpression(aggregate);
@@ -222,11 +223,12 @@ class RequestPropertyDeriverTest {
new AggregateParam(AggPhase.GLOBAL, AggMode.BUFFER_TO_RESULT),
true,
logicalProperties,
+ false,
groupPlan
);
GroupExpression groupExpression = new GroupExpression(aggregate);
new Group(null, groupExpression, null);
- RequestPropertyDeriver requestPropertyDeriver = new RequestPropertyDeriver(null, jobContext);
+ RequestPropertyDeriver requestPropertyDeriver = new RequestPropertyDeriver(connectContext, jobContext);
List<List<PhysicalProperties>> actual
= requestPropertyDeriver.getRequestChildrenPropertyList(groupExpression);
List<List<PhysicalProperties>> expected = Lists.newArrayList();
@@ -246,6 +248,7 @@ class RequestPropertyDeriverTest {
new AggregateParam(AggPhase.GLOBAL, AggMode.BUFFER_TO_RESULT),
true,
logicalProperties,
+ false,
groupPlan
);
GroupExpression groupExpression = new GroupExpression(aggregate);
@@ -383,6 +386,7 @@ class RequestPropertyDeriverTest {
new AggregateParam(AggPhase.GLOBAL, AggMode.BUFFER_TO_RESULT),
true,
logicalProperties,
+ false,
groupPlan
);
GroupExpression groupExpression = new GroupExpression(aggregate);
@@ -426,6 +430,7 @@ class RequestPropertyDeriverTest {
new AggregateParam(AggPhase.GLOBAL, AggMode.BUFFER_TO_RESULT),
true,
logicalProperties,
+ false,
groupPlan
);
GroupExpression groupExpression = new GroupExpression(aggregate);
diff --git a/fe/fe-core/src/test/java/org/apache/doris/nereids/properties/ShuffleKeyPruneUtilsTest.java b/fe/fe-core/src/test/java/org/apache/doris/nereids/properties/ShuffleKeyPruneUtilsTest.java
new file mode 100644
index 00000000000..7b577e71800
--- /dev/null
+++ b/fe/fe-core/src/test/java/org/apache/doris/nereids/properties/ShuffleKeyPruneUtilsTest.java
@@ -0,0 +1,393 @@
+// 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.
+
+package org.apache.doris.nereids.properties;
+
+import org.apache.doris.common.Pair;
+import org.apache.doris.nereids.PlanContext;
+import org.apache.doris.nereids.hint.DistributeHint;
+import org.apache.doris.nereids.memo.Group;
+import org.apache.doris.nereids.memo.GroupExpression;
+import org.apache.doris.nereids.memo.GroupId;
+import org.apache.doris.nereids.trees.expressions.EqualTo;
+import org.apache.doris.nereids.trees.expressions.ExprId;
+import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.Slot;
+import org.apache.doris.nereids.trees.expressions.SlotReference;
+import org.apache.doris.nereids.trees.expressions.functions.agg.AggregateParam;
+import org.apache.doris.nereids.trees.plans.AggMode;
+import org.apache.doris.nereids.trees.plans.AggPhase;
+import org.apache.doris.nereids.trees.plans.DistributeType;
+import org.apache.doris.nereids.trees.plans.GroupPlan;
+import org.apache.doris.nereids.trees.plans.JoinType;
+import org.apache.doris.nereids.trees.plans.Plan;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalEmptyRelation;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalHashAggregate;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalHashJoin;
+import org.apache.doris.nereids.types.DateType;
+import org.apache.doris.nereids.types.IntegerType;
+import org.apache.doris.nereids.types.VarcharType;
+import org.apache.doris.nereids.util.ExpressionUtils;
+import org.apache.doris.nereids.util.Utils;
+import org.apache.doris.qe.ConnectContext;
+import org.apache.doris.utframe.TestWithFeService;
+
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableMap;
+import com.google.common.collect.Lists;
+import com.google.common.collect.Sets;
+import mockit.Mock;
+import mockit.MockUp;
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.Test;
+
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+import java.util.Optional;
+import java.util.Set;
+
+/**
+ * Unit tests for {@link ShuffleKeyPruneUtils}.
+ */
+class ShuffleKeyPruneUtilsTest extends TestWithFeService {
+
+ private static final double ROW_COUNT = 204000;
+ private static final int INSTANCE_NUM = 8;
+
+ private SlotReference slotA;
+ private SlotReference slotB;
+ private SlotReference slotC;
+ private SlotReference slotD;
+ private SlotReference slotE;
+ private SlotReference slotF;
+ private SlotReference slotG;
+ private SlotReference rightSlotA;
+ private SlotReference rightSlotB;
+ private SlotReference rightSlotC;
+ private SlotReference rightSlotD;
+ private SlotReference rightSlotE;
+ private SlotReference rightSlotF;
+ private SlotReference rightSlotG;
+ private GroupExpression emptyExpression;
+
+ @Override
+ protected void runBeforeAll() {
+ slotA = new SlotReference(new ExprId(0), "a", IntegerType.INSTANCE, true, ImmutableList.of());
+ slotB = new SlotReference(new ExprId(1), "b", IntegerType.INSTANCE, true, ImmutableList.of());
+ slotC = new SlotReference(new ExprId(2), "c", IntegerType.INSTANCE, true, ImmutableList.of());
+ slotD = new SlotReference(new ExprId(3), "d", IntegerType.INSTANCE, true, ImmutableList.of());
+ slotE = new SlotReference(new ExprId(4), "e", IntegerType.INSTANCE, true, ImmutableList.of());
+ slotF = new SlotReference(new ExprId(5), "f", IntegerType.INSTANCE, true, ImmutableList.of());
+ slotG = new SlotReference(new ExprId(6), "g", IntegerType.INSTANCE, true, ImmutableList.of());
+ rightSlotA = new SlotReference(new ExprId(7), "a", IntegerType.INSTANCE, true, ImmutableList.of());
+ rightSlotB = new SlotReference(new ExprId(8), "b", IntegerType.INSTANCE, true, ImmutableList.of());
+ rightSlotC = new SlotReference(new ExprId(9), "c", IntegerType.INSTANCE, true, ImmutableList.of());
+ rightSlotD = new SlotReference(new ExprId(10), "d", IntegerType.INSTANCE, true, ImmutableList.of());
+ rightSlotE = new SlotReference(new ExprId(11), "e", IntegerType.INSTANCE, true, ImmutableList.of());
+ rightSlotF = new SlotReference(new ExprId(12), "f", IntegerType.INSTANCE, true, ImmutableList.of());
+ rightSlotG = new SlotReference(new ExprId(13), "g", IntegerType.INSTANCE, true, ImmutableList.of());
+ emptyExpression = new GroupExpression(new PhysicalEmptyRelation(
+ connectContext.getStatementContext().getNextRelationId(),
+ ImmutableList.of(slotA, slotB, slotC, slotD, slotE, slotF), null));
+ new ConnectContextMockUp();
+ }
+
+ /** MockUp for static getTotalInstanceNum - use named class for Java 8 (anonymous class cannot have static members). */
+ private static class ConnectContextMockUp extends MockUp<ConnectContext> {
+ @Mock
+ public int getTotalInstanceNum() {
+ return INSTANCE_NUM;
+ }
+ }
+
+ /** Creates a child Group with emptyRelation and statistics (ndv=5000) for given slots. */
+ private Group createChildGroupWithStats(Expression... slots) {
+ ImmutableMap.Builder<Expression, Double> builder = ImmutableMap.builder();
+ for (Expression slot : slots) {
+ builder.put(slot, 5000.0);
+ }
+ Group group = new Group(GroupId.createGenerator().getNextId(), emptyExpression, null);
+ group.setStatistics(statsWithNdv(builder.build(), ROW_COUNT));
+ return group;
+ }
+
+ /** Creates a child Group with emptyRelation and custom ndv statistics for given expressions. */
+ private Group createChildGroupWithCustomStats(Map<Expression, Double> exprToNdv) {
+ Group group = new Group(GroupId.createGenerator().getNextId(), emptyExpression, null);
+ group.setStatistics(statsWithNdv(exprToNdv, ROW_COUNT));
+ return group;
+ }
+
+ /** Creates a child Group with emptyRelation, without statistics. */
+ private Group createChildGroup() {
+ return new Group(GroupId.createGenerator().getNextId(), emptyExpression, null);
+ }
+
+ /** Creates PhysicalHashAggregate over childGroup, registers in memo. Returns (aggWithGroupExpr, aggGroupExpr). */
+ private Pair<PhysicalHashAggregate<GroupPlan>, GroupExpression> createAggAndRegister(Group childGroup,
+ List<? extends Expression> groupByExprs, boolean hasSourceRepeat) {
+ GroupPlan groupPlan = new GroupPlan(childGroup);
+ PhysicalHashAggregate<GroupPlan> agg = new PhysicalHashAggregate<>(
+ (List) groupByExprs, (List) groupByExprs,
+ new AggregateParam(AggPhase.GLOBAL, AggMode.BUFFER_TO_RESULT),
+ true, null, hasSourceRepeat, groupPlan);
+ GroupExpression aggGroupExpr = new GroupExpression(agg, Lists.newArrayList(childGroup));
+ new Group(GroupId.createGenerator().getNextId(), aggGroupExpr, null);
+ return Pair.of((PhysicalHashAggregate<GroupPlan>) aggGroupExpr.getPlan(), aggGroupExpr);
+ }
+
+ /** Creates left/right agg groups with 7 slots each for join tests. Returns (leftGroup, rightGroup). */
+ private Pair<Group, Group> createJoinAggGroups() {
+ return createJoinAggGroups(false, false);
+ }
+
+ /** Creates left/right agg groups with configurable hasSourceRepeat for join tests. */
+ private Pair<Group, Group> createJoinAggGroups(boolean hasSourceRepeatLeft, boolean hasSourceRepeatRight) {
+ Group leftChildGroup = createChildGroupWithStats(slotA, slotB, slotC, slotD, slotE, slotF, slotG);
+ PhysicalHashAggregate<GroupPlan> leftAgg = new PhysicalHashAggregate<>(
+ Lists.newArrayList(slotA, slotB, slotC, slotD, slotE, slotF, slotG),
+ Lists.newArrayList(slotA, slotB, slotC, slotD, slotE, slotF, slotG),
+ new AggregateParam(AggPhase.GLOBAL, AggMode.BUFFER_TO_RESULT),
+ true, null, hasSourceRepeatLeft, new GroupPlan(leftChildGroup));
+ GroupExpression leftAggGroupExpr = new GroupExpression(leftAgg, Lists.newArrayList(leftChildGroup));
+ Group leftGroup = new Group(GroupId.createGenerator().getNextId(), leftAggGroupExpr, null);
+
+ Group rightChildGroup = createChildGroupWithStats(rightSlotA, rightSlotB, rightSlotC, rightSlotD,
+ rightSlotE, rightSlotF, rightSlotG);
+ PhysicalHashAggregate<GroupPlan> rightAgg = new PhysicalHashAggregate<>(
+ Lists.newArrayList(rightSlotA, rightSlotB, rightSlotC, rightSlotD, rightSlotE, rightSlotF, rightSlotG),
+ Lists.newArrayList(rightSlotA, rightSlotB, rightSlotC, rightSlotD, rightSlotE, rightSlotF, rightSlotG),
+ new AggregateParam(AggPhase.GLOBAL, AggMode.BUFFER_TO_RESULT),
+ true, null, hasSourceRepeatRight, new GroupPlan(rightChildGroup));
+ GroupExpression rightAggGroupExpr = new GroupExpression(rightAgg, Lists.newArrayList(rightChildGroup));
+ Group rightGroup = new Group(GroupId.createGenerator().getNextId(), rightAggGroupExpr, null);
+
+ return Pair.of(leftGroup, rightGroup);
+ }
+
+ /** Creates hash join with given number of conjuncts and mocks getHashConjunctsExprIds. */
+ private PhysicalHashJoin<GroupPlan, GroupPlan> createHashJoinWithConjuncts(Group leftGroup, Group rightGroup,
+ int numConjuncts) {
+ List<Slot> leftSlots = ImmutableList.of(slotA, slotB, slotC, slotD, slotE, slotF, slotG);
+ List<Slot> rightSlots = ImmutableList.of(rightSlotA, rightSlotB, rightSlotC, rightSlotD,
+ rightSlotE, rightSlotF, rightSlotG);
+ List<Expression> hashJoinConjuncts = Lists.newArrayList();
+ List<ExprId> leftIds = Lists.newArrayList();
+ List<ExprId> rightIds = Lists.newArrayList();
+ for (int i = 0; i < numConjuncts; i++) {
+ hashJoinConjuncts.add(new EqualTo(leftSlots.get(i), rightSlots.get(i)));
+ leftIds.add(leftSlots.get(i).getExprId());
+ rightIds.add(rightSlots.get(i).getExprId());
+ }
+ PhysicalHashJoin<GroupPlan, GroupPlan> hashJoin = new PhysicalHashJoin<>(
+ JoinType.INNER_JOIN,
+ hashJoinConjuncts,
+ ExpressionUtils.EMPTY_CONDITION,
+ new DistributeHint(DistributeType.NONE),
+ Optional.empty(),
+ null, new GroupPlan(leftGroup), new GroupPlan(rightGroup));
+
+ final List<ExprId> finalLeftIds = leftIds;
+ final List<ExprId> finalRightIds = rightIds;
+ new MockUp<PhysicalHashJoin<GroupPlan, GroupPlan>>() {
+ @Mock
+ public Pair<List<ExprId>, List<ExprId>> getHashConjunctsExprIds() {
+ return Pair.of(finalLeftIds, finalRightIds);
+ }
+ };
+ return hashJoin;
+ }
+
+ @Test
+ void testSelectOptimalShuffleKeyForAggWithParentHashRequest_disabled() {
+ connectContext.getSessionVariable().enableAggShuffleKeyPrune = false;
+
+ Group childGroup = createChildGroupWithStats(slotA, slotB, slotC, slotD, slotE, slotF);
+ Pair<PhysicalHashAggregate<GroupPlan>, GroupExpression> aggSetup = createAggAndRegister(childGroup,
+ ImmutableList.of(slotA, slotB, slotC, slotD, slotE, slotF), false);
+
+ Set<ExprId> intersectIdSet = Sets.newHashSet(slotA.getExprId(), slotB.getExprId(), slotC.getExprId(),
+ slotD.getExprId(), slotE.getExprId(), slotF.getExprId());
+ PlanContext planContext = new PlanContext(connectContext, aggSetup.second);
+
+ List<ExprId> result = ShuffleKeyPruneUtils.selectOptimalShuffleKeyForAggWithParentHashRequest(
+ aggSetup.first, Utils.fastToImmutableList(intersectIdSet), planContext);
+
+ Assertions.assertEquals(6, result.size());
+ connectContext.getSessionVariable().enableAggShuffleKeyPrune = true;
+ }
+
+ @Test
+ void testSelectOptimalShuffleKeyForAggWithParentHashRequest_noStatistics() {
+ Set<ExprId> intersectIdSet = Sets.newHashSet(slotA.getExprId(), slotB.getExprId(), slotC.getExprId(),
+ slotD.getExprId(), slotE.getExprId(), slotF.getExprId());
+
+ Group childGroup = createChildGroup(); // no statistics
+ Pair<PhysicalHashAggregate<GroupPlan>, GroupExpression> aggSetup = createAggAndRegister(childGroup,
+ ImmutableList.of(slotA, slotB, slotC, slotD, slotE, slotF), false);
+
+ PlanContext planContext = new PlanContext(connectContext, aggSetup.second);
+
+ List<ExprId> result = ShuffleKeyPruneUtils.selectOptimalShuffleKeyForAggWithParentHashRequest(
+ aggSetup.first, Utils.fastToImmutableList(intersectIdSet), planContext);
+
+ Assertions.assertEquals(6, result.size());
+ }
+
+ @Test
+ void testSelectOptimalShuffleKeyForAggWithParentHashRequest_selectBestKey() {
+ Set<ExprId> intersectIdSet = Sets.newHashSet(slotA.getExprId(), slotB.getExprId(), slotC.getExprId(),
+ slotD.getExprId(), slotE.getExprId(), slotF.getExprId());
+
+ Group childGroup = createChildGroupWithStats(slotA, slotB, slotC, slotD, slotE, slotF);
+ Pair<PhysicalHashAggregate<GroupPlan>, GroupExpression> aggSetup = createAggAndRegister(childGroup,
+ ImmutableList.of(slotA, slotB, slotC, slotD, slotE, slotF), false);
+
+ PlanContext planContext = new PlanContext(connectContext, aggSetup.second);
+ List<ExprId> result = ShuffleKeyPruneUtils.selectOptimalShuffleKeyForAggWithParentHashRequest(
+ aggSetup.first, Utils.fastToImmutableList(intersectIdSet), planContext);
+
+ Assertions.assertEquals(1, result.size());
+ }
+
+ @Test
+ void testSelectBestShuffleKeyForAgg_disabled() {
+ connectContext.getSessionVariable().enableAggShuffleKeyPrune = false;
+
+ Group childGroup = createChildGroupWithStats(slotA, slotB, slotC, slotD, slotE, slotF);
+ Pair<PhysicalHashAggregate<GroupPlan>, GroupExpression> aggSetup = createAggAndRegister(childGroup,
+ ImmutableList.of(slotA, slotB, slotC, slotD, slotE, slotF), false);
+
+ Optional<List<Expression>> result = ShuffleKeyPruneUtils.selectBestShuffleKeyForAgg(aggSetup.first,
+ ImmutableList.of(slotA, slotB, slotC, slotD, slotE, slotF), connectContext);
+
+ Assertions.assertFalse(result.isPresent());
+ connectContext.getSessionVariable().enableAggShuffleKeyPrune = true;
+ }
+
+ @Test
+ void testSelectBestShuffleKeyForAgg_hasSourceRepeat() {
+ Group childGroup = createChildGroupWithStats(slotA, slotB, slotC, slotD, slotE, slotF);
+ Pair<PhysicalHashAggregate<GroupPlan>, GroupExpression> aggSetup = createAggAndRegister(childGroup,
+ ImmutableList.of(slotA, slotB, slotC, slotD, slotE, slotF), true);
+
+ Optional<List<Expression>> result = ShuffleKeyPruneUtils.selectBestShuffleKeyForAgg(aggSetup.first,
+ ImmutableList.of(slotA, slotB, slotC, slotD, slotE, slotF), connectContext);
+
+ Assertions.assertFalse(result.isPresent());
+ }
+
+ @Test
+ void testSelectBestShuffleKeyForAgg_noChildStats() {
+ Group childGroup = createChildGroup(); // no statistics
+ Pair<PhysicalHashAggregate<GroupPlan>, GroupExpression> aggSetup = createAggAndRegister(childGroup,
+ ImmutableList.of(slotA, slotB, slotC, slotD, slotE, slotF), false);
+
+ Optional<List<Expression>> result = ShuffleKeyPruneUtils.selectBestShuffleKeyForAgg(aggSetup.first,
+ aggSetup.first.getGroupByExpressions(), connectContext);
+
+ Assertions.assertFalse(result.isPresent());
+ }
+
+ @Test
+ void testSelectBestShuffleKeyForAgg_selectBestKey() {
+ Group childGroup = createChildGroupWithStats(slotA, slotB, slotC, slotD, slotE, slotF);
+ Pair<PhysicalHashAggregate<GroupPlan>, GroupExpression> aggSetup = createAggAndRegister(childGroup,
+ ImmutableList.of(slotA, slotB, slotC, slotD, slotE, slotF), false);
+
+ Optional<List<Expression>> result = ShuffleKeyPruneUtils.selectBestShuffleKeyForAgg(aggSetup.first,
+ aggSetup.first.getGroupByExpressions(), connectContext);
+
+ Assertions.assertTrue(result.isPresent());
+ Assertions.assertFalse(result.get().isEmpty());
+ Assertions.assertInstanceOf(SlotReference.class, result.get().get(0));
+ }
+
+ @Test
+ void testTryFindOptimalShuffleKeyForBothAggChildren_selectBestKey() {
+ Pair<Group, Group> groups = createJoinAggGroups();
+ PhysicalHashJoin<GroupPlan, GroupPlan> hashJoin = createHashJoinWithConjuncts(groups.first, groups.second, 7);
+
+ GroupExpression joinGroupExpr = new GroupExpression(hashJoin, Lists.newArrayList(groups.first, groups.second));
+ new Group(GroupId.createGenerator().getNextId(), joinGroupExpr, null);
+ PlanContext planContext = new PlanContext(connectContext, joinGroupExpr);
+
+ Optional<Pair<List<ExprId>, List<ExprId>>> result = ShuffleKeyPruneUtils.tryFindOptimalShuffleKeyForBothAggChildren(
+ (PhysicalHashJoin) joinGroupExpr.getPlan(), planContext);
+
+ Assertions.assertTrue(result.isPresent());
+ Assertions.assertFalse(result.get().first.isEmpty());
+ Assertions.assertFalse(result.get().second.isEmpty());
+ }
+
+ @Test
+ void testSelectBestShuffleKeyForAgg_pruneStringKeysWithCombinedNdv() {
+ SlotReference numericSlot = new SlotReference(new ExprId(14), "num_col", IntegerType.INSTANCE, true,
+ ImmutableList.of());
+ SlotReference dateSlot = new SlotReference(new ExprId(15), "date_col", DateType.INSTANCE, true,
+ ImmutableList.of());
+ SlotReference stringSlot = new SlotReference(new ExprId(16), "str_col", new VarcharType(64), true,
+ ImmutableList.of());
+
+ Map<Expression, Double> exprToNdv = new HashMap<>();
+ exprToNdv.put(numericSlot, 3000.0);
+ exprToNdv.put(dateSlot, 3000.0);
+ exprToNdv.put(stringSlot, 3000.0);
+ Group childGroup = createChildGroupWithCustomStats(exprToNdv);
+ Pair<PhysicalHashAggregate<GroupPlan>, GroupExpression> aggSetup = createAggAndRegister(childGroup,
+ ImmutableList.of(numericSlot, dateSlot, stringSlot), false);
+
+ Optional<List<Expression>> result = ShuffleKeyPruneUtils.selectBestShuffleKeyForAgg(aggSetup.first,
+ ImmutableList.of(numericSlot, dateSlot, stringSlot), connectContext);
+
+ Assertions.assertTrue(result.isPresent());
+ Assertions.assertEquals(2, result.get().size());
+ Assertions.assertEquals(numericSlot, result.get().get(0));
+ Assertions.assertEquals(dateSlot, result.get().get(1));
+ }
+
+ @Test
+ void testTryFindOptimalShuffleKeyForBothAggChildren_leftHasSourceRepeat() {
+ Pair<Group, Group> groups = createJoinAggGroups(true, false);
+ PhysicalHashJoin<GroupPlan, GroupPlan> hashJoin = createHashJoinWithConjuncts(groups.first, groups.second, 7);
+
+ GroupExpression joinGroupExpr = new GroupExpression(hashJoin, Lists.newArrayList(groups.first, groups.second));
+ new Group(GroupId.createGenerator().getNextId(), joinGroupExpr, null);
+ PlanContext planContext = new PlanContext(connectContext, joinGroupExpr);
+
+ Optional<Pair<List<ExprId>, List<ExprId>>> result = ShuffleKeyPruneUtils.tryFindOptimalShuffleKeyForBothAggChildren(
+ (PhysicalHashJoin<? extends Plan, ? extends Plan>) joinGroupExpr.getPlan(), planContext);
+
+ Assertions.assertFalse(result.isPresent());
+ }
+
+ @Test
+ void testTryFindOptimalShuffleKeyForBothAggChildren_rightHasSourceRepeat() {
+ Pair<Group, Group> groups = createJoinAggGroups(false, true);
+ PhysicalHashJoin<GroupPlan, GroupPlan> hashJoin = createHashJoinWithConjuncts(groups.first, groups.second, 7);
+
+ GroupExpression joinGroupExpr = new GroupExpression(hashJoin, Lists.newArrayList(groups.first, groups.second));
+ new Group(GroupId.createGenerator().getNextId(), joinGroupExpr, null);
+ PlanContext planContext = new PlanContext(connectContext, joinGroupExpr);
+
+ Optional<Pair<List<ExprId>, List<ExprId>>> result = ShuffleKeyPruneUtils.tryFindOptimalShuffleKeyForBothAggChildren(
+ (PhysicalHashJoin<? extends Plan, ? extends Plan>) joinGroupExpr.getPlan(), planContext);
+
+ Assertions.assertFalse(result.isPresent());
+ }
+}
diff --git a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/plans/PlanEqualsTest.java b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/plans/PlanEqualsTest.java
index 6f42d6a5839..18e626bf0f0 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/plans/PlanEqualsTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/plans/PlanEqualsTest.java
@@ -249,14 +249,14 @@ class PlanEqualsTest {
new SlotReference(new ExprId(0), "a", BigIntType.INSTANCE, true, Lists.newArrayList()));
PhysicalHashAggregate<Plan> actual = new PhysicalHashAggregate<>(Lists.newArrayList(), outputExpressionList,
new AggregateParam(AggPhase.LOCAL, AggMode.INPUT_TO_RESULT), true, logicalProperties,
- child);
+ false, child);
List<NamedExpression> outputExpressionList1 = ImmutableList.of(
new SlotReference(new ExprId(0), "a", BigIntType.INSTANCE, true, Lists.newArrayList()));
PhysicalHashAggregate<Plan> expected = new PhysicalHashAggregate<>(Lists.newArrayList(),
outputExpressionList1,
new AggregateParam(AggPhase.LOCAL, AggMode.INPUT_TO_RESULT), true, logicalProperties,
- child);
+ false, child);
Assertions.assertEquals(expected, actual);
List<NamedExpression> outputExpressionList2 = ImmutableList.of(
@@ -264,7 +264,7 @@ class PlanEqualsTest {
PhysicalHashAggregate<Plan> unexpected = new PhysicalHashAggregate<>(Lists.newArrayList(),
outputExpressionList2,
new AggregateParam(AggPhase.LOCAL, AggMode.INPUT_TO_RESULT), false, logicalProperties,
- child);
+ false, child);
Assertions.assertNotEquals(unexpected, actual);
}
diff --git a/fe/fe-core/src/test/java/org/apache/doris/utframe/TestWithFeService.java b/fe/fe-core/src/test/java/org/apache/doris/utframe/TestWithFeService.java
index 6e88eaa0551..97993175d9f 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/utframe/TestWithFeService.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/utframe/TestWithFeService.java
@@ -41,6 +41,7 @@ import org.apache.doris.nereids.StatementContext;
import org.apache.doris.nereids.glue.LogicalPlanAdapter;
import org.apache.doris.nereids.parser.NereidsParser;
import org.apache.doris.nereids.rules.RuleType;
+import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.StatementScopeIdGenerator;
import org.apache.doris.nereids.trees.plans.commands.AddConstraintCommand;
import org.apache.doris.nereids.trees.plans.commands.AlterMTMVCommand;
@@ -79,6 +80,9 @@ import org.apache.doris.qe.ConnectContext;
import org.apache.doris.qe.OriginStatement;
import org.apache.doris.qe.QueryState;
import org.apache.doris.qe.StmtExecutor;
+import org.apache.doris.statistics.ColumnStatistic;
+import org.apache.doris.statistics.ColumnStatisticBuilder;
+import org.apache.doris.statistics.Statistics;
import org.apache.doris.system.Backend;
import org.apache.doris.thrift.TNetworkAddress;
import org.apache.doris.utframe.MockedBackendFactory.DefaultBeThriftServiceImpl;
@@ -111,6 +115,7 @@ import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.Comparator;
+import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
@@ -995,4 +1000,22 @@ public abstract class TestWithFeService {
System.out.println("No need clean DIR: " + dir);
}
}
+
+ /** Helper: build Statistics with column ndv for given expressions. */
+ protected static Statistics statsWithNdv(Map<Expression, Double> exprToNdv, double rowCount) {
+ Map<Expression, ColumnStatistic> map = new HashMap<>();
+ for (Map.Entry<Expression, Double> e : exprToNdv.entrySet()) {
+ ColumnStatistic col = new ColumnStatisticBuilder(1)
+ .setNdv(e.getValue())
+ .setAvgSizeByte(4)
+ .setNumNulls(0)
+ .setMinValue(0)
+ .setMaxValue(100)
+ .setIsUnknown(false)
+ .setUpdatedTime("")
+ .build();
+ map.put(e.getKey(), col);
+ }
+ return new Statistics(rowCount, map);
+ }
}
diff --git a/regression-test/data/nereids_p0/hash_shuffle_key_prune/hash_shuffle_key_prune.out b/regression-test/data/nereids_p0/hash_shuffle_key_prune/hash_shuffle_key_prune.out
new file mode 100644
index 00000000000..4e915314c7d
--- /dev/null
+++ b/regression-test/data/nereids_p0/hash_shuffle_key_prune/hash_shuffle_key_prune.out
@@ -0,0 +1,815 @@
+-- This file is automatically generated. You should know what you did if you want to edit this
+-- !simple_agg_shape --
+PhysicalResultSink
+--PhysicalProject
+----PhysicalQuickSort[MERGE_SORT]
+------PhysicalDistribute[DistributionSpecGather]
+--------PhysicalQuickSort[LOCAL_SORT]
+----------hashAgg[GLOBAL]
+------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[d]
+--------------hashAgg[LOCAL]
+----------------PhysicalProject
+------------------PhysicalOlapScan[t1]
+
+-- !simple_agg_result --
+1
+1
+1
+2
+2
+3
+
+-- !agg_receive_shuffle_request_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------PhysicalProject
+----------PhysicalWindow
+------------PhysicalQuickSort[LOCAL_SORT]
+--------------PhysicalProject
+----------------hashAgg[GLOBAL]
+------------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[d]
+--------------------hashAgg[LOCAL]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[t1]
+
+-- !agg_receive_shuffle_request_result --
+1
+1
+1
+2
+2
+3
+
+-- !distinct_agg_func_with_random_distribute_gby_key_greater_than_threshold_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------hashAgg[GLOBAL]
+----------hashAgg[GLOBAL]
+------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[d]
+--------------hashAgg[LOCAL]
+----------------PhysicalOlapScan[t1]
+
+-- !distinct_agg_func_with_random_distribute_gby_key_greater_than_threshold_result --
+1 1 1 1 1 1 10 1
+1 1 1 1 1 1 20 1
+2 2 2 2 2 2 30 1
+2 2 2 2 2 2 40 1
+2 2 2 2 2 2 50 1
+3 3 3 3 3 3 60 1
+3 3 3 3 3 3 70 1
+
+-- !distinct_agg_func_with_random_distribute_distinct_key_greater_than_threshold_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------hashAgg[GLOBAL]
+----------hashAgg[GLOBAL]
+------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[d]
+--------------hashAgg[LOCAL]
+----------------PhysicalOlapScan[t1]
+
+-- !distinct_agg_func_with_random_distribute_distinct_key_greater_than_threshold_result --
+1 1 1 1 1 1 10 1
+1 1 1 1 1 1 20 1
+2 2 2 2 2 2 30 1
+2 2 2 2 2 2 40 1
+2 2 2 2 2 2 50 1
+3 3 3 3 3 3 60 1
+3 3 3 3 3 3 70 1
+
+-- !distinct_agg_func_with_random_distribute_gby_key_greater_than_threshold_2_plus_2_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------hashAgg[DISTINCT_GLOBAL]
+----------hashAgg[DISTINCT_LOCAL]
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[d]
+----------------hashAgg[LOCAL]
+------------------PhysicalOlapScan[t1]
+
+-- !distinct_agg_func_with_random_distribute_gby_key_greater_than_threshold_2_plus_2_result --
+1 1 1 1 1 1 10 1
+1 1 1 1 1 1 20 1
+2 2 2 2 2 2 30 1
+2 2 2 2 2 2 40 1
+2 2 2 2 2 2 50 1
+3 3 3 3 3 3 60 1
+3 3 3 3 3 3 70 1
+
+-- !distinct_agg_func_with_random_distribute_distinct_key_greater_than_threshold_2_plus_2_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------hashAgg[DISTINCT_GLOBAL]
+----------hashAgg[DISTINCT_LOCAL]
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[d]
+----------------hashAgg[LOCAL]
+------------------PhysicalOlapScan[t1]
+
+-- !distinct_agg_func_with_random_distribute_distinct_key_greater_than_threshold_2_plus_2_result --
+1 1 1 1 1 1 10 1
+1 1 1 1 1 1 20 1
+2 2 2 2 2 2 30 1
+2 2 2 2 2 2 40 1
+2 2 2 2 2 2 50 1
+3 3 3 3 3 3 60 1
+3 3 3 3 3 3 70 1
+
+-- !distinct_agg_func_with_random_distribute_without_redundant_agg_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------hashAgg[DISTINCT_GLOBAL]
+----------hashAgg[DISTINCT_LOCAL]
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[d]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t1]
+
+-- !distinct_agg_func_with_random_distribute_without_redundant_agg_result --
+1 1 1 1 1 1 1
+2 2 2 2 2 1 2
+2 2 2 2 2 2 1
+3 3 3 3 3 2 1
+3 3 3 3 3 3 1
+
+-- !distinct_agg_func_with_random_distribute_with_redundant_agg_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------hashAgg[DISTINCT_GLOBAL]
+----------hashAgg[DISTINCT_LOCAL]
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[d]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t1]
+
+-- !distinct_agg_func_with_random_distribute_with_redundant_agg_result --
+1 1 1 1 1 1 1
+2 2 2 2 2 1 2
+2 2 2 2 2 2 1
+3 3 3 3 3 2 1
+3 3 3 3 3 3 1
+
+-- !distinct_agg_func_with_a_hash_distribute_gby_key_has_a_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------hashAgg[GLOBAL]
+----------hashAgg[GLOBAL]
+------------PhysicalProject
+--------------PhysicalOlapScan[t2]
+
+-- !distinct_agg_func_with_a_hash_distribute_gby_key_has_a_result --
+1 1 1 1 1 1 10 1
+1 1 1 1 1 1 20 1
+1 2 2 2 2 2 30 1
+1 2 2 2 2 2 40 1
+2 2 2 2 2 2 50 1
+2 3 3 3 3 3 60 1
+3 3 3 3 3 3 70 1
+
+-- !distinct_agg_func_with_e_hash_distribute_gby_key_has_a_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------hashAgg[GLOBAL]
+----------hashAgg[GLOBAL]
+------------PhysicalProject
+--------------PhysicalOlapScan[t2]
+
+-- !distinct_agg_func_with_e_hash_distribute_gby_key_has_a_result --
+1 1 1 1 1 1 10 1
+1 1 1 1 1 1 20 1
+1 2 2 2 2 2 30 1
+1 2 2 2 2 2 40 1
+2 2 2 2 2 2 50 1
+2 3 3 3 3 3 60 1
+3 3 3 3 3 3 70 1
+
+-- !distinct_agg_func_with_a_hash_distribute_distinct_key_has_a_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------hashAgg[GLOBAL]
+----------PhysicalDistribute[DistributionSpecHash] Hash Columns:[d]
+------------hashAgg[LOCAL]
+--------------hashAgg[GLOBAL]
+----------------PhysicalOlapScan[t2]
+
+-- !distinct_agg_func_with_a_hash_distribute_distinct_key_has_a_result --
+1 1 1 1 1 1 10 1
+1 1 1 1 1 1 20 1
+2 2 2 2 2 2 30 1
+2 2 2 2 2 2 40 1
+2 2 2 2 2 2 50 1
+3 3 3 3 3 3 60 1
+3 3 3 3 3 3 70 1
+
+-- !distinct_agg_func_with_a_hash_distribute_gby_key_has_a_1_plus_2_redundant_agg_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------hashAgg[DISTINCT_GLOBAL]
+----------hashAgg[GLOBAL]
+------------PhysicalProject
+--------------PhysicalOlapScan[t2]
+
+-- !distinct_agg_func_with_a_hash_distribute_gby_key_has_a_1_plus_2_redundant_agg_result --
+1 1 1 1 1 1 10 1
+1 1 1 1 1 1 20 1
+1 2 2 2 2 2 30 1
+1 2 2 2 2 2 40 1
+2 2 2 2 2 2 50 1
+2 3 3 3 3 3 60 1
+3 3 3 3 3 3 70 1
+
+-- !distinct_agg_func_with_e_hash_distribute_gby_key_has_a_1_plus_2_redundant_agg_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------hashAgg[DISTINCT_GLOBAL]
+----------hashAgg[GLOBAL]
+------------PhysicalProject
+--------------PhysicalOlapScan[t3]
+
+-- !distinct_agg_func_with_e_hash_distribute_gby_key_has_a_1_plus_2_redundant_agg_result --
+1 1 1 1 1 1 10 1
+1 1 1 1 1 1 20 1
+1 2 2 2 2 2 30 1
+1 2 2 2 2 2 40 1
+2 2 2 2 2 2 50 1
+2 3 3 3 3 3 60 1
+3 3 3 3 3 3 70 1
+
+-- !distinct_agg_func_with_random_distribute_gby_key_greater_than_threshold_avg_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------PhysicalProject
+----------hashAgg[DISTINCT_GLOBAL]
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[d]
+----------------hashAgg[LOCAL]
+------------------PhysicalOlapScan[t1]
+
+-- !distinct_agg_func_with_random_distribute_gby_key_greater_than_threshold_avg_result --
+1 1 1 1 1 1 10 1 1
+1 1 1 1 1 1 20 1 1
+2 2 2 2 2 2 30 1 1
+2 2 2 2 2 2 40 1 1
+2 2 2 2 2 2 50 1 2
+3 3 3 3 3 3 60 1 2
+3 3 3 3 3 3 70 1 3
+
+-- !distinct_agg_func_with_random_distribute_distinct_key_greater_than_threshold_avg_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------PhysicalProject
+----------hashAgg[DISTINCT_GLOBAL]
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[d]
+----------------hashAgg[LOCAL]
+------------------PhysicalOlapScan[t1]
+
+-- !distinct_agg_func_with_random_distribute_distinct_key_greater_than_threshold_avg_result --
+1 1 1 1 1 1 10 1 1
+1 1 1 1 1 1 20 1 1
+2 2 2 2 2 2 30 1 1
+2 2 2 2 2 2 40 1 1
+2 2 2 2 2 2 50 1 2
+3 3 3 3 3 3 60 1 2
+3 3 3 3 3 3 70 1 3
+
+-- !distinct_agg_func_with_random_distribute_gby_key_greater_than_threshold_2_plus_2_avg_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------PhysicalProject
+----------hashAgg[DISTINCT_GLOBAL]
+------------hashAgg[DISTINCT_LOCAL]
+--------------hashAgg[GLOBAL]
+----------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[d]
+------------------hashAgg[LOCAL]
+--------------------PhysicalOlapScan[t1]
+
+-- !distinct_agg_func_with_random_distribute_gby_key_greater_than_threshold_2_plus_2_avg_result --
+1 1 1 1 1 1 10 1 1
+1 1 1 1 1 1 20 1 1
+2 2 2 2 2 2 30 1 1
+2 2 2 2 2 2 40 1 1
+2 2 2 2 2 2 50 1 2
+3 3 3 3 3 3 60 1 2
+3 3 3 3 3 3 70 1 3
+
+-- !distinct_agg_func_with_random_distribute_distinct_key_greater_than_threshold_2_plus_2_avg_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------PhysicalProject
+----------hashAgg[DISTINCT_GLOBAL]
+------------hashAgg[DISTINCT_LOCAL]
+--------------hashAgg[GLOBAL]
+----------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[d]
+------------------hashAgg[LOCAL]
+--------------------PhysicalOlapScan[t1]
+
+-- !distinct_agg_func_with_random_distribute_distinct_key_greater_than_threshold_2_plus_2_avg_result --
+1 1 1 1 1 1 10 1 1
+1 1 1 1 1 1 20 1 1
+2 2 2 2 2 2 30 1 1
+2 2 2 2 2 2 40 1 1
+2 2 2 2 2 2 50 1 2
+3 3 3 3 3 3 60 1 2
+3 3 3 3 3 3 70 1 3
+
+-- !distinct_agg_func_with_random_distribute_without_redundant_agg_avg_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------PhysicalProject
+----------hashAgg[DISTINCT_GLOBAL]
+------------hashAgg[DISTINCT_LOCAL]
+--------------hashAgg[GLOBAL]
+----------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[d]
+------------------hashAgg[LOCAL]
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t1]
+
+-- !distinct_agg_func_with_random_distribute_without_redundant_agg_avg_result --
+1 1 1 1 1 1 1 1
+2 2 2 2 2 1 2 1
+2 2 2 2 2 2 1 2
+3 3 3 3 3 2 1 2
+3 3 3 3 3 3 1 3
+
+-- !distinct_agg_func_with_random_distribute_with_redundant_agg_avg_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------PhysicalProject
+----------hashAgg[DISTINCT_GLOBAL]
+------------hashAgg[DISTINCT_LOCAL]
+--------------hashAgg[GLOBAL]
+----------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[d]
+------------------hashAgg[LOCAL]
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t1]
+
+-- !distinct_agg_func_with_random_distribute_with_redundant_agg_avg_result --
+1 1 1 1 1 1 1 1
+2 2 2 2 2 1 2 1
+2 2 2 2 2 2 1 2
+3 3 3 3 3 2 1 2
+3 3 3 3 3 3 1 3
+
+-- !distinct_agg_func_with_a_hash_distribute_gby_key_has_a_avg_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------PhysicalProject
+----------hashAgg[DISTINCT_GLOBAL]
+------------hashAgg[GLOBAL]
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+
+-- !distinct_agg_func_with_a_hash_distribute_gby_key_has_a_avg_result --
+1 1 1 1 1 1 10 1 1
+1 1 1 1 1 1 20 1 1
+1 2 2 2 2 2 30 1 1
+1 2 2 2 2 2 40 1 1
+2 2 2 2 2 2 50 1 2
+2 3 3 3 3 3 60 1 2
+3 3 3 3 3 3 70 1 3
+
+-- !distinct_agg_func_with_e_hash_distribute_gby_key_has_a_avg_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------PhysicalProject
+----------hashAgg[DISTINCT_GLOBAL]
+------------hashAgg[GLOBAL]
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+
+-- !distinct_agg_func_with_e_hash_distribute_gby_key_has_a_avg_result --
+1 1 1 1 1 1 10 1 1
+1 1 1 1 1 1 20 1 1
+1 2 2 2 2 2 30 1 1
+1 2 2 2 2 2 40 1 1
+2 2 2 2 2 2 50 1 2
+2 3 3 3 3 3 60 1 2
+3 3 3 3 3 3 70 1 3
+
+-- !distinct_agg_func_with_a_hash_distribute_distinct_key_has_a_avg_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------PhysicalProject
+----------hashAgg[DISTINCT_GLOBAL]
+------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[d]
+--------------hashAgg[DISTINCT_LOCAL]
+----------------hashAgg[GLOBAL]
+------------------PhysicalOlapScan[t2]
+
+-- !distinct_agg_func_with_a_hash_distribute_distinct_key_has_a_avg_result --
+1 1 1 1 1 1 10 1 1
+1 1 1 1 1 1 20 1 1
+2 2 2 2 2 2 30 1 1
+2 2 2 2 2 2 40 1 1
+2 2 2 2 2 2 50 1 2
+3 3 3 3 3 3 60 1 2
+3 3 3 3 3 3 70 1 3
+
+-- !distinct_agg_func_with_a_hash_distribute_gby_key_has_a_1_plus_2_redundant_agg_avg_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------PhysicalProject
+----------hashAgg[DISTINCT_GLOBAL]
+------------hashAgg[GLOBAL]
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+
+-- !distinct_agg_func_with_a_hash_distribute_gby_key_has_a_1_plus_2_redundant_agg_avg_result --
+1 1 1 1 1 1 10 1 1
+1 1 1 1 1 1 20 1 1
+1 2 2 2 2 2 30 1 1
+1 2 2 2 2 2 40 1 1
+2 2 2 2 2 2 50 1 2
+2 3 3 3 3 3 60 1 2
+3 3 3 3 3 3 70 1 3
+
+-- !distinct_agg_func_with_e_hash_distribute_gby_key_has_a_1_plus_2_redundant_agg_avg_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------PhysicalProject
+----------hashAgg[DISTINCT_GLOBAL]
+------------hashAgg[GLOBAL]
+--------------PhysicalProject
+----------------PhysicalOlapScan[t3]
+
+-- !distinct_agg_func_with_e_hash_distribute_gby_key_has_a_1_plus_2_redundant_agg_avg_result --
+1 1 1 1 1 1 10 1 1
+1 1 1 1 1 1 20 1 1
+1 2 2 2 2 2 30 1 1
+1 2 2 2 2 2 40 1 1
+2 2 2 2 2 2 50 1 2
+2 3 3 3 3 3 60 1 2
+3 3 3 3 3 3 70 1 3
+
+-- !agg_join_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN colocated] hashCondition=((t1.a = t2.a) and (t1.b = t2.b) and (t1.c = t2.c) and (t1.d = t2.d) and (t1.e = t2.e) and (t1.f = t2.f) and (t1.g = t2.g)) otherCondition=()
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[d]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t1]
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[d]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t1]
+
+-- !agg_join_result --
+1 1 1 1 1 1 1
+1 1 1 2 2 2 2
+1 2 2 2 2 2 2
+2 2 2 2 2 2 2
+2 2 3 3 3 3 3
+3 3 3 3 3 3 3
+
+-- !agg_join_5_keys_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN colocated] hashCondition=((t1.a = t2.a) and (t1.b = t2.b) and (t1.c = t2.c) and (t1.d = t2.d) and (t1.e = t2.e)) otherCondition=()
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[d]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t1]
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[d]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t1]
+
+-- !agg_join_5_keys_result --
+1 1 1 1 1
+1 1 1 2 2
+1 2 2 2 2
+2 2 2 2 2
+2 2 3 3 3
+3 3 3 3 3
+
+-- !agg_7_join_5_keys_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN colocated] hashCondition=((t1.a = t2.a) and (t1.b = t2.b) and (t1.c = t2.c) and (t1.d = t2.d) and (t1.e = t2.e)) otherCondition=()
+------------PhysicalProject
+--------------hashAgg[GLOBAL]
+----------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[d]
+------------------hashAgg[LOCAL]
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t1]
+------------PhysicalProject
+--------------hashAgg[GLOBAL]
+----------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[d]
+------------------hashAgg[LOCAL]
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t1]
+
+-- !agg_7_join_5_keys_result --
+1 1 1 1 1
+1 1 1 2 2
+1 2 2 2 2
+2 2 2 2 2
+2 2 3 3 3
+3 3 3 3 3
+
+-- !agg_join_3_keys_not_choose_1_key_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN colocated] hashCondition=((t1.a = t2.a) and (t1.b = t2.b) and (t1.c = t2.c)) otherCondition=()
+------------PhysicalProject
+--------------hashAgg[GLOBAL]
+----------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[a, b, c]
+------------------hashAgg[LOCAL]
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t1]
+------------PhysicalProject
+--------------hashAgg[GLOBAL]
+----------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[a, b, c]
+------------------hashAgg[LOCAL]
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t1]
+
+-- !agg_join_3_keys_not_choose_1_key_result --
+1 1 1
+1 1 1
+1 1 1
+1 1 1
+1 2 2
+2 2 2
+2 2 3
+3 3 3
+
+-- !agg_7_join_3_keys_choose_1_key_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN colocated] hashCondition=((t1.a = t2.a) and (t1.b = t2.b) and (t1.e = t2.e)) otherCondition=()
+------------PhysicalProject
+--------------hashAgg[GLOBAL]
+----------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[e]
+------------------hashAgg[LOCAL]
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t1]
+------------PhysicalProject
+--------------hashAgg[GLOBAL]
+----------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[e]
+------------------hashAgg[LOCAL]
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[t1]
+
+-- !agg_7_join_3_keys_choose_1_key_result --
+1 1 1 1
+1 1 1 2
+1 2 2 2
+2 2 2 2
+2 2 3 3
+3 3 3 3
+
+-- !agg_join_t1_t2_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN bucketShuffle] hashCondition=((t1.a = t2.a) and (t1.b = t2.b) and (t1.c = t2.c) and (t1.d = t2.d) and (t1.e = t2.e) and (t1.f = t2.f) and (t1.g = t2.g)) otherCondition=()
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[d]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t1]
+------------hashAgg[GLOBAL]
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+
+-- !agg_join_t1_t2_result --
+1 1 1 1 1 1 1
+1 1 1 2 2 2 2
+1 2 2 2 2 2 2
+2 2 2 2 2 2 2
+2 2 3 3 3 3 3
+3 3 3 3 3 3 3
+
+-- !agg_join_one_side_agg_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN broadcast] hashCondition=((t1.a = t2.a) and (t1.b = t2.b) and (t1.c = t2.c)) otherCondition=()
+------------PhysicalProject
+--------------PhysicalOlapScan[t1]
+------------hashAgg[GLOBAL]
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+
+-- !agg_join_one_side_agg_result --
+1 1 1
+1 1 1
+1 1 1
+1 2 2
+2 2 2
+2 2 3
+3 3 3
+
+-- !join_e_equal_f_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN colocated] hashCondition=((t1.a = t2.a) and (t1.b = t2.b) and (t1.c = t2.c) and (t1.d = t2.d) and (t1.e = t2.f) and (t1.f = t2.e) and (t1.g = t2.g)) otherCondition=()
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[d]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t1]
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[d]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t1]
+
+-- !join_e_equal_f_result --
+1 1 1 1 1 1 1
+1 1 1 2 2 2 2
+1 2 2 2 2 2 2
+2 2 2 2 2 2 2
+2 2 3 3 3 3 3
+3 3 3 3 3 3 3
+
+-- !join_both_side_satisfy_agg_distribution_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN colocated] hashCondition=((t1.a = t2.a) and (t1.b = t2.b) and (t1.c = t2.c) and (t1.d = t2.d) and (t1.e = t2.e) and (t1.f = t2.f) and (t1.g = t2.g)) otherCondition=()
+------------hashAgg[GLOBAL]
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+------------hashAgg[GLOBAL]
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+
+-- !join_both_side_satisfy_agg_distribution_result --
+1 1 1 1 1 1 1
+1 1 1 2 2 2 2
+1 2 2 2 2 2 2
+2 2 2 2 2 2 2
+2 2 3 3 3 3 3
+3 3 3 3 3 3 3
+
+-- !join_one_side_satisfy_agg_distribution_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN bucketShuffle] hashCondition=((t1.a = t2.a) and (t1.b = t2.b) and (t1.c = t2.c) and (t1.d = t2.d) and (t1.e = t2.e) and (t1.f = t2.f) and (t1.g = t2.g)) otherCondition=()
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[d]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t1]
+------------hashAgg[GLOBAL]
+--------------PhysicalProject
+----------------PhysicalOlapScan[t2]
+
+-- !join_one_side_satisfy_agg_distribution_result --
+1 1 1 1 1 1 1
+1 1 1 2 2 2 2
+1 2 2 2 2 2 2
+2 2 2 2 2 2 2
+2 2 3 3 3 3 3
+3 3 3 3 3 3 3
+
+-- !agg_join_no_opt_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN colocated] hashCondition=((t1.a = t2.a) and (t1.b = t2.b) and (t1.c = t2.c) and (t1.d = t2.d) and (t1.e = t2.e) and (t1.f = t2.f) and (t1.g = t2.g)) otherCondition=()
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[a, b, c, d, e, f, g]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t1]
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[a, b, c, d, e, f, g]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t1]
+
+-- !agg_join_no_opt_result --
+1 1 1 1 1 1 1
+1 1 1 2 2 2 2
+1 2 2 2 2 2 2
+2 2 2 2 2 2 2
+2 2 3 3 3 3 3
+3 3 3 3 3 3 3
+
+-- !join_project_agg_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------hashJoin[INNER_JOIN colocated] hashCondition=((t1.a = t2.a) and (t1.b = t2.b) and (t1.c = t2.c) and (t1.col1 = t2.col1) and (t1.col2 = t2.col2) and (t1.e = t2.e)) otherCondition=()
+----------PhysicalProject
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[e]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t1]
+----------PhysicalProject
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute[DistributionSpecHash] Hash Columns:[e]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[t1]
+
+-- !join_project_agg_result --
+1 1 1 1 2 3 1 1 1 1 2 3
+1 2 2 2 3 4 1 2 2 2 3 4
+2 2 2 2 3 4 2 2 2 2 3 4
+3 3 3 3 4 5 3 3 3 3 4 5
+
+-- !distinct_agg_func_with_a_hash_distribute_no_optimize_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalDistribute[DistributionSpecGather]
+------PhysicalQuickSort[LOCAL_SORT]
+--------hashAgg[GLOBAL]
+----------PhysicalDistribute[DistributionSpecHash] Hash Columns:[d]
+------------hashAgg[LOCAL]
+--------------hashAgg[GLOBAL]
+----------------PhysicalProject
+------------------PhysicalOlapScan[t2]
+
+-- !distinct_agg_func_with_a_hash_distribute_no_optimize_result --
+1 1 1 1
+2 2 2 3
+3 3 3 2
+
diff --git a/regression-test/data/shape_check/tpcds_sf100/rf_prune/query23.out b/regression-test/data/shape_check/tpcds_sf100/rf_prune/query23.out
index 7ff3bbb732c..8b30252987f 100644
--- a/regression-test/data/shape_check/tpcds_sf100/rf_prune/query23.out
+++ b/regression-test/data/shape_check/tpcds_sf100/rf_prune/query23.out
@@ -5,19 +5,17 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
----PhysicalProject
------filter((cnt > 4))
--------hashAgg[GLOBAL]
-----------PhysicalDistribute[DistributionSpecHash]
-------------hashAgg[LOCAL]
+----------PhysicalProject
+------------hashJoin[INNER_JOIN shuffle] hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=()
--------------PhysicalProject
-----------------hashJoin[INNER_JOIN broadcast] hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=()
+----------------hashJoin[INNER_JOIN broadcast] hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ss_sold_date_sk]
------------------PhysicalProject
---------------------hashJoin[INNER_JOIN broadcast] hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ss_sold_date_sk]
-----------------------PhysicalProject
-------------------------PhysicalOlapScan[store_sales] apply RFs: RF0
-----------------------PhysicalProject
-------------------------filter(d_year IN (2000, 2001, 2002, 2003))
---------------------------PhysicalOlapScan[date_dim]
+--------------------PhysicalOlapScan[store_sales] apply RFs: RF0
------------------PhysicalProject
---------------------PhysicalOlapScan[item]
+--------------------filter(d_year IN (2000, 2001, 2002, 2003))
+----------------------PhysicalOlapScan[date_dim]
+--------------PhysicalProject
+----------------PhysicalOlapScan[item]
--PhysicalCteAnchor ( cteId=CTEId#2 )
----PhysicalCteProducer ( cteId=CTEId#2 )
------PhysicalProject
diff --git a/regression-test/data/shape_check/tpcds_sf100/shape/query23.out b/regression-test/data/shape_check/tpcds_sf100/shape/query23.out
index 17c6c0e8b7a..d9d9316520d 100644
--- a/regression-test/data/shape_check/tpcds_sf100/shape/query23.out
+++ b/regression-test/data/shape_check/tpcds_sf100/shape/query23.out
@@ -5,19 +5,17 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
----PhysicalProject
------filter((cnt > 4))
--------hashAgg[GLOBAL]
-----------PhysicalDistribute[DistributionSpecHash]
-------------hashAgg[LOCAL]
+----------PhysicalProject
+------------hashJoin[INNER_JOIN shuffle] hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=() build RFs:RF1 i_item_sk->[ss_item_sk]
--------------PhysicalProject
-----------------hashJoin[INNER_JOIN broadcast] hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=() build RFs:RF1 i_item_sk->[ss_item_sk]
+----------------hashJoin[INNER_JOIN broadcast] hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ss_sold_date_sk]
------------------PhysicalProject
---------------------hashJoin[INNER_JOIN broadcast] hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ss_sold_date_sk]
-----------------------PhysicalProject
-------------------------PhysicalOlapScan[store_sales] apply RFs: RF0 RF1
-----------------------PhysicalProject
-------------------------filter(d_year IN (2000, 2001, 2002, 2003))
---------------------------PhysicalOlapScan[date_dim]
+--------------------PhysicalOlapScan[store_sales] apply RFs: RF0 RF1
------------------PhysicalProject
---------------------PhysicalOlapScan[item]
+--------------------filter(d_year IN (2000, 2001, 2002, 2003))
+----------------------PhysicalOlapScan[date_dim]
+--------------PhysicalProject
+----------------PhysicalOlapScan[item]
--PhysicalCteAnchor ( cteId=CTEId#2 )
----PhysicalCteProducer ( cteId=CTEId#2 )
------PhysicalProject
diff --git a/regression-test/data/shape_check/tpcds_sf1000/hint/query23.out b/regression-test/data/shape_check/tpcds_sf1000/hint/query23.out
index f514575b396..8dab50e01d6 100644
--- a/regression-test/data/shape_check/tpcds_sf1000/hint/query23.out
+++ b/regression-test/data/shape_check/tpcds_sf1000/hint/query23.out
@@ -5,19 +5,17 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
----PhysicalProject
------filter((cnt > 4))
--------hashAgg[GLOBAL]
-----------PhysicalDistribute[DistributionSpecHash]
-------------hashAgg[LOCAL]
+----------PhysicalProject
+------------hashJoin[INNER_JOIN shuffle] hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=() build RFs:RF1 i_item_sk->[ss_item_sk]
--------------PhysicalProject
-----------------hashJoin[INNER_JOIN broadcast] hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=() build RFs:RF1 i_item_sk->[ss_item_sk]
+----------------hashJoin[INNER_JOIN broadcast] hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ss_sold_date_sk]
------------------PhysicalProject
---------------------hashJoin[INNER_JOIN broadcast] hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ss_sold_date_sk]
-----------------------PhysicalProject
-------------------------PhysicalOlapScan[store_sales] apply RFs: RF0 RF1
-----------------------PhysicalProject
-------------------------filter(d_year IN (2000, 2001, 2002, 2003))
---------------------------PhysicalOlapScan[date_dim]
+--------------------PhysicalOlapScan[store_sales] apply RFs: RF0 RF1
------------------PhysicalProject
---------------------PhysicalOlapScan[item]
+--------------------filter(d_year IN (2000, 2001, 2002, 2003))
+----------------------PhysicalOlapScan[date_dim]
+--------------PhysicalProject
+----------------PhysicalOlapScan[item]
--PhysicalCteAnchor ( cteId=CTEId#2 )
----PhysicalCteProducer ( cteId=CTEId#2 )
------PhysicalProject
diff --git a/regression-test/data/shape_check/tpcds_sf1000/hint/query65.out b/regression-test/data/shape_check/tpcds_sf1000/hint/query65.out
index 7be8f9a355b..f52a4be6a97 100644
--- a/regression-test/data/shape_check/tpcds_sf1000/hint/query65.out
+++ b/regression-test/data/shape_check/tpcds_sf1000/hint/query65.out
@@ -9,9 +9,9 @@ PhysicalResultSink
------------PhysicalProject
--------------hashJoin[INNER_JOIN broadcast] hashCondition=((sb.ss_store_sk = sc.ss_store_sk)) otherCondition=((cast(revenue as DECIMALV3(38, 5)) <= (0.1 * sb.ave))) build RFs:RF4 ss_store_sk->[s_store_sk,ss_store_sk]
----------------PhysicalProject
-------------------hashJoin[INNER_JOIN broadcast] hashCondition=((store.s_store_sk = sc.ss_store_sk)) otherCondition=() build RFs:RF3 s_store_sk->[ss_store_sk]
+------------------hashJoin[INNER_JOIN bucketShuffle] hashCondition=((item.i_item_sk = sc.ss_item_sk)) otherCondition=() build RFs:RF3 i_item_sk->[ss_item_sk]
--------------------PhysicalProject
-----------------------hashJoin[INNER_JOIN bucketShuffle] hashCondition=((item.i_item_sk = sc.ss_item_sk)) otherCondition=() build RFs:RF2 i_item_sk->[ss_item_sk]
+----------------------hashJoin[INNER_JOIN broadcast] hashCondition=((store.s_store_sk = sc.ss_store_sk)) otherCondition=() build RFs:RF2 s_store_sk->[ss_store_sk]
------------------------PhysicalProject
--------------------------hashAgg[GLOBAL]
----------------------------PhysicalDistribute[DistributionSpecHash]
@@ -24,9 +24,9 @@ PhysicalResultSink
--------------------------------------filter((date_dim.d_month_seq <= 1187) and (date_dim.d_month_seq >= 1176))
----------------------------------------PhysicalOlapScan[date_dim]
------------------------PhysicalProject
---------------------------PhysicalLazyMaterializeOlapScan[item lazySlots:(item.i_current_price,item.i_wholesale_cost,item.i_brand)]
+--------------------------PhysicalOlapScan[store] apply RFs: RF4
--------------------PhysicalProject
-----------------------PhysicalOlapScan[store] apply RFs: RF4
+----------------------PhysicalLazyMaterializeOlapScan[item lazySlots:(item.i_current_price,item.i_wholesale_cost,item.i_brand)]
----------------hashAgg[GLOBAL]
------------------PhysicalDistribute[DistributionSpecHash]
--------------------hashAgg[LOCAL]
diff --git a/regression-test/data/shape_check/tpcds_sf1000/hint/query78.out b/regression-test/data/shape_check/tpcds_sf1000/hint/query78.out
index 990698673eb..a503af2ad56 100644
--- a/regression-test/data/shape_check/tpcds_sf1000/hint/query78.out
+++ b/regression-test/data/shape_check/tpcds_sf1000/hint/query78.out
@@ -8,7 +8,7 @@ PhysicalResultSink
----------filter(OR[(coalesce(ws_qty, 0) > 0),(coalesce(cs_qty, 0) > 0)])
------------hashJoin[LEFT_OUTER_JOIN colocated] hashCondition=((cs.cs_customer_sk = ss.ss_customer_sk) and (cs.cs_item_sk = ss.ss_item_sk) and (cs.cs_sold_year = ss.ss_sold_year)) otherCondition=()
--------------PhysicalProject
-----------------hashJoin[LEFT_OUTER_JOIN colocated] hashCondition=((ws.ws_customer_sk = ss.ss_customer_sk) and (ws.ws_item_sk = ss.ss_item_sk) and (ws.ws_sold_year = ss.ss_sold_year)) otherCondition=()
+----------------hashJoin[LEFT_OUTER_JOIN bucketShuffle] hashCondition=((ws.ws_customer_sk = ss.ss_customer_sk) and (ws.ws_item_sk = ss.ss_item_sk) and (ws.ws_sold_year = ss.ss_sold_year)) otherCondition=()
------------------PhysicalProject
--------------------hashAgg[GLOBAL]
----------------------PhysicalDistribute[DistributionSpecHash]
diff --git a/regression-test/data/shape_check/tpcds_sf1000/shape/query23.out b/regression-test/data/shape_check/tpcds_sf1000/shape/query23.out
index f514575b396..8dab50e01d6 100644
--- a/regression-test/data/shape_check/tpcds_sf1000/shape/query23.out
+++ b/regression-test/data/shape_check/tpcds_sf1000/shape/query23.out
@@ -5,19 +5,17 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
----PhysicalProject
------filter((cnt > 4))
--------hashAgg[GLOBAL]
-----------PhysicalDistribute[DistributionSpecHash]
-------------hashAgg[LOCAL]
+----------PhysicalProject
+------------hashJoin[INNER_JOIN shuffle] hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=() build RFs:RF1 i_item_sk->[ss_item_sk]
--------------PhysicalProject
-----------------hashJoin[INNER_JOIN broadcast] hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=() build RFs:RF1 i_item_sk->[ss_item_sk]
+----------------hashJoin[INNER_JOIN broadcast] hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ss_sold_date_sk]
------------------PhysicalProject
---------------------hashJoin[INNER_JOIN broadcast] hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ss_sold_date_sk]
-----------------------PhysicalProject
-------------------------PhysicalOlapScan[store_sales] apply RFs: RF0 RF1
-----------------------PhysicalProject
-------------------------filter(d_year IN (2000, 2001, 2002, 2003))
---------------------------PhysicalOlapScan[date_dim]
+--------------------PhysicalOlapScan[store_sales] apply RFs: RF0 RF1
------------------PhysicalProject
---------------------PhysicalOlapScan[item]
+--------------------filter(d_year IN (2000, 2001, 2002, 2003))
+----------------------PhysicalOlapScan[date_dim]
+--------------PhysicalProject
+----------------PhysicalOlapScan[item]
--PhysicalCteAnchor ( cteId=CTEId#2 )
----PhysicalCteProducer ( cteId=CTEId#2 )
------PhysicalProject
diff --git a/regression-test/data/shape_check/tpcds_sf1000/shape/query65.out b/regression-test/data/shape_check/tpcds_sf1000/shape/query65.out
index 267b157be5f..b2612f6bffc 100644
--- a/regression-test/data/shape_check/tpcds_sf1000/shape/query65.out
+++ b/regression-test/data/shape_check/tpcds_sf1000/shape/query65.out
@@ -9,9 +9,9 @@ PhysicalResultSink
------------PhysicalProject
--------------hashJoin[INNER_JOIN broadcast] hashCondition=((sb.ss_store_sk = sc.ss_store_sk)) otherCondition=((cast(revenue as DECIMALV3(38, 5)) <= (0.1 * sb.ave))) build RFs:RF4 ss_store_sk->[s_store_sk,ss_store_sk]
----------------PhysicalProject
-------------------hashJoin[INNER_JOIN broadcast] hashCondition=((store.s_store_sk = sc.ss_store_sk)) otherCondition=() build RFs:RF3 s_store_sk->[ss_store_sk]
+------------------hashJoin[INNER_JOIN bucketShuffle] hashCondition=((item.i_item_sk = sc.ss_item_sk)) otherCondition=() build RFs:RF3 i_item_sk->[ss_item_sk]
--------------------PhysicalProject
-----------------------hashJoin[INNER_JOIN bucketShuffle] hashCondition=((item.i_item_sk = sc.ss_item_sk)) otherCondition=() build RFs:RF2 i_item_sk->[ss_item_sk]
+----------------------hashJoin[INNER_JOIN broadcast] hashCondition=((store.s_store_sk = sc.ss_store_sk)) otherCondition=() build RFs:RF2 s_store_sk->[ss_store_sk]
------------------------hashAgg[GLOBAL]
--------------------------PhysicalDistribute[DistributionSpecHash]
----------------------------hashAgg[LOCAL]
@@ -23,9 +23,9 @@ PhysicalResultSink
------------------------------------filter((date_dim.d_month_seq <= 1187) and (date_dim.d_month_seq >= 1176))
--------------------------------------PhysicalOlapScan[date_dim]
------------------------PhysicalProject
---------------------------PhysicalLazyMaterializeOlapScan[item lazySlots:(item.i_current_price,item.i_wholesale_cost,item.i_brand)]
+--------------------------PhysicalOlapScan[store] apply RFs: RF4
--------------------PhysicalProject
-----------------------PhysicalOlapScan[store] apply RFs: RF4
+----------------------PhysicalLazyMaterializeOlapScan[item lazySlots:(item.i_current_price,item.i_wholesale_cost,item.i_brand)]
----------------hashAgg[GLOBAL]
------------------PhysicalDistribute[DistributionSpecHash]
--------------------hashAgg[LOCAL]
diff --git a/regression-test/data/shape_check/tpcds_sf1000/shape/query72.out b/regression-test/data/shape_check/tpcds_sf1000/shape/query72.out
index c35f0dea45c..37d3a8e2abe 100644
--- a/regression-test/data/shape_check/tpcds_sf1000/shape/query72.out
+++ b/regression-test/data/shape_check/tpcds_sf1000/shape/query72.out
@@ -8,17 +8,16 @@ PhysicalResultSink
----------PhysicalDistribute[DistributionSpecHash]
------------hashAgg[LOCAL]
--------------PhysicalProject
-----------------hashJoin[INNER_JOIN broadcast] hashCondition=((warehouse.w_warehouse_sk = inventory.inv_warehouse_sk)) otherCondition=() build RFs:RF10 w_warehouse_sk->[inv_warehouse_sk]
+----------------hashJoin[LEFT_OUTER_JOIN bucketShuffle] hashCondition=((catalog_returns.cr_item_sk = catalog_sales.cs_item_sk) and (catalog_returns.cr_order_number = catalog_sales.cs_order_number)) otherCondition=()
------------------PhysicalProject
---------------------hashJoin[INNER_JOIN bucketShuffle] hashCondition=((catalog_sales.cs_item_sk = inventory.inv_item_sk) and (inventory.inv_date_sk = d2.d_date_sk)) otherCondition=((inventory.inv_quantity_on_hand < catalog_sales.cs_quantity)) build RFs:RF8 inv_date_sk->[d_date_sk];RF9 inv_item_sk->[cs_item_sk,i_item_sk]
+--------------------hashJoin[INNER_JOIN broadcast] hashCondition=((warehouse.w_warehouse_sk = inventory.inv_warehouse_sk)) otherCondition=() build RFs:RF8 w_warehouse_sk->[inv_warehouse_sk]
----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN shuffle] hashCondition=((item.i_item_sk = catalog_sales.cs_item_sk)) otherCondition=() build RFs:RF7 i_item_sk->[cs_item_sk]
+------------------------hashJoin[INNER_JOIN shuffle] hashCondition=((catalog_sales.cs_item_sk = inventory.inv_item_sk) and (inventory.inv_date_sk = d2.d_date_sk)) otherCondition=((inventory.inv_quantity_on_hand < catalog_sales.cs_quantity)) build RFs:RF6 d_date_sk->[inv_date_sk];RF7 cs_item_sk->[inv_item_sk]
+--------------------------PhysicalOlapScan[inventory] apply RFs: RF6 RF7 RF8
--------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN broadcast] hashCondition=((d1.d_week_seq = d2.d_week_seq)) otherCondition=() build RFs:RF6 d_week_seq->[d_week_seq]
+----------------------------hashJoin[INNER_JOIN broadcast] hashCondition=((d1.d_week_seq = d2.d_week_seq)) otherCondition=() build RFs:RF5 d_week_seq->[d_week_seq]
------------------------------PhysicalProject
---------------------------------hashJoin[RIGHT_OUTER_JOIN colocated] hashCondition=((catalog_returns.cr_item_sk = catalog_sales.cs_item_sk) and (catalog_returns.cr_order_number = catalog_sales.cs_order_number)) otherCondition=() build RFs:RF4 cs_item_sk->[cr_item_sk];RF5 cs_order_number->[cr_order_number]
-----------------------------------PhysicalProject
-------------------------------------PhysicalOlapScan[catalog_returns] apply RFs: RF4 RF5
+--------------------------------hashJoin[INNER_JOIN broadcast] hashCondition=((item.i_item_sk = catalog_sales.cs_item_sk)) otherCondition=() build RFs:RF4 i_item_sk->[cs_item_sk]
----------------------------------PhysicalProject
------------------------------------hashJoin[LEFT_OUTER_JOIN broadcast] hashCondition=((catalog_sales.cs_promo_sk = promotion.p_promo_sk)) otherCondition=()
--------------------------------------PhysicalProject
@@ -28,14 +27,14 @@ PhysicalResultSink
----------------------------------------------PhysicalProject
------------------------------------------------hashJoin[INNER_JOIN broadcast] hashCondition=((catalog_sales.cs_ship_date_sk = d3.d_date_sk) and (catalog_sales.cs_sold_date_sk = d1.d_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[cs_ship_date_sk];RF1 d_date_sk->[cs_sold_date_sk]
--------------------------------------------------PhysicalProject
-----------------------------------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF0 RF1 RF2 RF3 RF7 RF9
+----------------------------------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF0 RF1 RF2 RF3 RF4
--------------------------------------------------PhysicalProject
----------------------------------------------------NestedLoopJoin[INNER_JOIN](d3.d_date > days_add(d_date, INTERVAL 5 DAY))
------------------------------------------------------PhysicalProject
--------------------------------------------------------PhysicalOlapScan[date_dim]
------------------------------------------------------PhysicalProject
--------------------------------------------------------filter((d1.d_year = 1998))
-----------------------------------------------------------PhysicalOlapScan[date_dim] apply RFs: RF6
+----------------------------------------------------------PhysicalOlapScan[date_dim] apply RFs: RF5
----------------------------------------------PhysicalProject
------------------------------------------------filter((household_demographics.hd_buy_potential = '1001-5000'))
--------------------------------------------------PhysicalOlapScan[household_demographics]
@@ -44,13 +43,14 @@ PhysicalResultSink
----------------------------------------------PhysicalOlapScan[customer_demographics]
--------------------------------------PhysicalProject
----------------------------------------PhysicalOlapScan[promotion]
+----------------------------------PhysicalProject
+------------------------------------PhysicalOlapScan[item]
------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[date_dim] apply RFs: RF8
---------------------------PhysicalProject
-----------------------------PhysicalOlapScan[item] apply RFs: RF9
-----------------------PhysicalOlapScan[inventory] apply RFs: RF10
+--------------------------------PhysicalOlapScan[date_dim]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[warehouse]
------------------PhysicalProject
---------------------PhysicalOlapScan[warehouse]
+--------------------PhysicalOlapScan[catalog_returns]
diff --git a/regression-test/data/shape_check/tpcds_sf1000/shape/query23.out b/regression-test/data/shape_check/tpcds_sf1000_nopkfk/shape/query23.out
similarity index 57%
copy from regression-test/data/shape_check/tpcds_sf1000/shape/query23.out
copy to regression-test/data/shape_check/tpcds_sf1000_nopkfk/shape/query23.out
index f514575b396..7df481026b4 100644
--- a/regression-test/data/shape_check/tpcds_sf1000/shape/query23.out
+++ b/regression-test/data/shape_check/tpcds_sf1000_nopkfk/shape/query23.out
@@ -5,46 +5,52 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
----PhysicalProject
------filter((cnt > 4))
--------hashAgg[GLOBAL]
-----------PhysicalDistribute[DistributionSpecHash]
-------------hashAgg[LOCAL]
+----------PhysicalProject
+------------hashJoin[INNER_JOIN shuffle] hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=() build RFs:RF1 i_item_sk->[ss_item_sk]
--------------PhysicalProject
-----------------hashJoin[INNER_JOIN broadcast] hashCondition=((store_sales.ss_item_sk = item.i_item_sk)) otherCondition=() build RFs:RF1 i_item_sk->[ss_item_sk]
+----------------hashJoin[INNER_JOIN broadcast] hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ss_sold_date_sk]
------------------PhysicalProject
---------------------hashJoin[INNER_JOIN broadcast] hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[ss_sold_date_sk]
-----------------------PhysicalProject
-------------------------PhysicalOlapScan[store_sales] apply RFs: RF0 RF1
-----------------------PhysicalProject
-------------------------filter(d_year IN (2000, 2001, 2002, 2003))
---------------------------PhysicalOlapScan[date_dim]
+--------------------PhysicalOlapScan[store_sales] apply RFs: RF0 RF1
------------------PhysicalProject
---------------------PhysicalOlapScan[item]
+--------------------filter(d_year IN (2000, 2001, 2002, 2003))
+----------------------PhysicalOlapScan[date_dim]
+--------------PhysicalProject
+----------------PhysicalOlapScan[item]
--PhysicalCteAnchor ( cteId=CTEId#2 )
----PhysicalCteProducer ( cteId=CTEId#2 )
------PhysicalProject
--------NestedLoopJoin[INNER_JOIN](cast(ssales as DECIMALV3(38, 6)) > (0.9500 * tpcds_cmax))
----------PhysicalProject
------------hashAgg[GLOBAL]
---------------PhysicalDistribute[DistributionSpecHash]
-----------------hashAgg[LOCAL]
+--------------PhysicalProject
+----------------hashJoin[INNER_JOIN bucketShuffle] hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk)) otherCondition=() build RFs:RF4 c_customer_sk->[ss_customer_sk]
+------------------hashAgg[GLOBAL]
+--------------------PhysicalDistribute[DistributionSpecHash]
+----------------------hashAgg[LOCAL]
+------------------------PhysicalProject
+--------------------------PhysicalOlapScan[store_sales] apply RFs: RF4
------------------PhysicalProject
---------------------filter(( not ss_customer_sk IS NULL))
-----------------------PhysicalOlapScan[store_sales]
+--------------------PhysicalOlapScan[customer]
----------PhysicalProject
------------hashAgg[GLOBAL]
--------------PhysicalDistribute[DistributionSpecGather]
----------------hashAgg[LOCAL]
------------------PhysicalProject
--------------------hashAgg[GLOBAL]
-----------------------PhysicalDistribute[DistributionSpecHash]
-------------------------hashAgg[LOCAL]
+----------------------PhysicalProject
+------------------------hashJoin[INNER_JOIN broadcast] hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF3 d_date_sk->[ss_sold_date_sk]
--------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN broadcast] hashCondition=((store_sales.ss_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF2 d_date_sk->[ss_sold_date_sk]
-------------------------------PhysicalProject
---------------------------------filter(( not ss_customer_sk IS NULL))
-----------------------------------PhysicalOlapScan[store_sales] apply RFs: RF2
+----------------------------hashJoin[INNER_JOIN bucketShuffle] hashCondition=((store_sales.ss_customer_sk = customer.c_customer_sk)) otherCondition=() build RFs:RF2 c_customer_sk->[ss_customer_sk]
+------------------------------hashAgg[GLOBAL]
+--------------------------------PhysicalDistribute[DistributionSpecHash]
+----------------------------------hashAgg[LOCAL]
+------------------------------------PhysicalProject
+--------------------------------------PhysicalOlapScan[store_sales] apply RFs: RF2 RF3
------------------------------PhysicalProject
---------------------------------filter(d_year IN (2000, 2001, 2002, 2003))
-----------------------------------PhysicalOlapScan[date_dim]
+--------------------------------PhysicalOlapScan[customer]
+--------------------------PhysicalProject
+----------------------------filter(d_year IN (2000, 2001, 2002, 2003))
+------------------------------PhysicalOlapScan[date_dim]
----PhysicalResultSink
------PhysicalLimit[GLOBAL]
--------PhysicalLimit[LOCAL]
@@ -53,27 +59,27 @@ PhysicalCteAnchor ( cteId=CTEId#0 )
--------------hashAgg[LOCAL]
----------------PhysicalUnion
------------------PhysicalProject
---------------------hashJoin[RIGHT_SEMI_JOIN shuffle] hashCondition=((catalog_sales.cs_item_sk = frequent_ss_items.item_sk)) otherCondition=() build RFs:RF5 cs_item_sk->[item_sk]
-----------------------PhysicalCteConsumer ( cteId=CTEId#0 ) apply RFs: RF5
+--------------------hashJoin[RIGHT_SEMI_JOIN shuffle] hashCondition=((catalog_sales.cs_item_sk = frequent_ss_items.item_sk)) otherCondition=() build RFs:RF7 cs_item_sk->[item_sk]
+----------------------PhysicalCteConsumer ( cteId=CTEId#0 ) apply RFs: RF7
----------------------PhysicalProject
-------------------------hashJoin[LEFT_SEMI_JOIN broadcast] hashCondition=((catalog_sales.cs_bill_customer_sk = best_ss_customer.c_customer_sk)) otherCondition=() build RFs:RF4 c_customer_sk->[cs_bill_customer_sk]
+------------------------hashJoin[LEFT_SEMI_JOIN broadcast] hashCondition=((catalog_sales.cs_bill_customer_sk = best_ss_customer.c_customer_sk)) otherCondition=() build RFs:RF6 c_customer_sk->[cs_bill_customer_sk]
--------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN broadcast] hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF3 d_date_sk->[cs_sold_date_sk]
+----------------------------hashJoin[INNER_JOIN broadcast] hashCondition=((catalog_sales.cs_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF5 d_date_sk->[cs_sold_date_sk]
------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF3 RF4
+--------------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF5 RF6
------------------------------PhysicalProject
--------------------------------filter((date_dim.d_moy = 7) and (date_dim.d_year = 2000))
----------------------------------PhysicalOlapScan[date_dim]
--------------------------PhysicalCteConsumer ( cteId=CTEId#2 )
------------------PhysicalProject
---------------------hashJoin[RIGHT_SEMI_JOIN shuffle] hashCondition=((web_sales.ws_item_sk = frequent_ss_items.item_sk)) otherCondition=() build RFs:RF8 ws_item_sk->[item_sk]
-----------------------PhysicalCteConsumer ( cteId=CTEId#0 ) apply RFs: RF8
+--------------------hashJoin[RIGHT_SEMI_JOIN shuffle] hashCondition=((web_sales.ws_item_sk = frequent_ss_items.item_sk)) otherCondition=() build RFs:RF10 ws_item_sk->[item_sk]
+----------------------PhysicalCteConsumer ( cteId=CTEId#0 ) apply RFs: RF10
----------------------PhysicalProject
-------------------------hashJoin[LEFT_SEMI_JOIN broadcast] hashCondition=((web_sales.ws_bill_customer_sk = best_ss_customer.c_customer_sk)) otherCondition=() build RFs:RF7 c_customer_sk->[ws_bill_customer_sk]
+------------------------hashJoin[LEFT_SEMI_JOIN broadcast] hashCondition=((web_sales.ws_bill_customer_sk = best_ss_customer.c_customer_sk)) otherCondition=() build RFs:RF9 c_customer_sk->[ws_bill_customer_sk]
--------------------------PhysicalProject
-----------------------------hashJoin[INNER_JOIN broadcast] hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF6 d_date_sk->[ws_sold_date_sk]
+----------------------------hashJoin[INNER_JOIN broadcast] hashCondition=((web_sales.ws_sold_date_sk = date_dim.d_date_sk)) otherCondition=() build RFs:RF8 d_date_sk->[ws_sold_date_sk]
------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[web_sales] apply RFs: RF6 RF7
+--------------------------------PhysicalOlapScan[web_sales] apply RFs: RF8 RF9
------------------------------PhysicalProject
--------------------------------filter((date_dim.d_moy = 7) and (date_dim.d_year = 2000))
----------------------------------PhysicalOlapScan[date_dim]
diff --git a/regression-test/data/shape_check/tpcds_sf1000/shape/query65.out b/regression-test/data/shape_check/tpcds_sf1000_nopkfk/shape/query65.out
similarity index 82%
copy from regression-test/data/shape_check/tpcds_sf1000/shape/query65.out
copy to regression-test/data/shape_check/tpcds_sf1000_nopkfk/shape/query65.out
index 267b157be5f..b2612f6bffc 100644
--- a/regression-test/data/shape_check/tpcds_sf1000/shape/query65.out
+++ b/regression-test/data/shape_check/tpcds_sf1000_nopkfk/shape/query65.out
@@ -9,9 +9,9 @@ PhysicalResultSink
------------PhysicalProject
--------------hashJoin[INNER_JOIN broadcast] hashCondition=((sb.ss_store_sk = sc.ss_store_sk)) otherCondition=((cast(revenue as DECIMALV3(38, 5)) <= (0.1 * sb.ave))) build RFs:RF4 ss_store_sk->[s_store_sk,ss_store_sk]
----------------PhysicalProject
-------------------hashJoin[INNER_JOIN broadcast] hashCondition=((store.s_store_sk = sc.ss_store_sk)) otherCondition=() build RFs:RF3 s_store_sk->[ss_store_sk]
+------------------hashJoin[INNER_JOIN bucketShuffle] hashCondition=((item.i_item_sk = sc.ss_item_sk)) otherCondition=() build RFs:RF3 i_item_sk->[ss_item_sk]
--------------------PhysicalProject
-----------------------hashJoin[INNER_JOIN bucketShuffle] hashCondition=((item.i_item_sk = sc.ss_item_sk)) otherCondition=() build RFs:RF2 i_item_sk->[ss_item_sk]
+----------------------hashJoin[INNER_JOIN broadcast] hashCondition=((store.s_store_sk = sc.ss_store_sk)) otherCondition=() build RFs:RF2 s_store_sk->[ss_store_sk]
------------------------hashAgg[GLOBAL]
--------------------------PhysicalDistribute[DistributionSpecHash]
----------------------------hashAgg[LOCAL]
@@ -23,9 +23,9 @@ PhysicalResultSink
------------------------------------filter((date_dim.d_month_seq <= 1187) and (date_dim.d_month_seq >= 1176))
--------------------------------------PhysicalOlapScan[date_dim]
------------------------PhysicalProject
---------------------------PhysicalLazyMaterializeOlapScan[item lazySlots:(item.i_current_price,item.i_wholesale_cost,item.i_brand)]
+--------------------------PhysicalOlapScan[store] apply RFs: RF4
--------------------PhysicalProject
-----------------------PhysicalOlapScan[store] apply RFs: RF4
+----------------------PhysicalLazyMaterializeOlapScan[item lazySlots:(item.i_current_price,item.i_wholesale_cost,item.i_brand)]
----------------hashAgg[GLOBAL]
------------------PhysicalDistribute[DistributionSpecHash]
--------------------hashAgg[LOCAL]
diff --git a/regression-test/data/shape_check/tpcds_sf1000/shape/query72.out b/regression-test/data/shape_check/tpcds_sf1000_nopkfk/shape/query72.out
similarity index 80%
copy from regression-test/data/shape_check/tpcds_sf1000/shape/query72.out
copy to regression-test/data/shape_check/tpcds_sf1000_nopkfk/shape/query72.out
index c35f0dea45c..40f6a12a4d3 100644
--- a/regression-test/data/shape_check/tpcds_sf1000/shape/query72.out
+++ b/regression-test/data/shape_check/tpcds_sf1000_nopkfk/shape/query72.out
@@ -10,13 +10,14 @@ PhysicalResultSink
--------------PhysicalProject
----------------hashJoin[INNER_JOIN broadcast] hashCondition=((warehouse.w_warehouse_sk = inventory.inv_warehouse_sk)) otherCondition=() build RFs:RF10 w_warehouse_sk->[inv_warehouse_sk]
------------------PhysicalProject
---------------------hashJoin[INNER_JOIN bucketShuffle] hashCondition=((catalog_sales.cs_item_sk = inventory.inv_item_sk) and (inventory.inv_date_sk = d2.d_date_sk)) otherCondition=((inventory.inv_quantity_on_hand < catalog_sales.cs_quantity)) build RFs:RF8 inv_date_sk->[d_date_sk];RF9 inv_item_sk->[cs_item_sk,i_item_sk]
+--------------------hashJoin[INNER_JOIN bucketShuffle] hashCondition=((catalog_sales.cs_item_sk = inventory.inv_item_sk) and (inventory.inv_date_sk = d2.d_date_sk)) otherCondition=((inventory.inv_quantity_on_hand < catalog_sales.cs_quantity)) build RFs:RF8 d_date_sk->[inv_date_sk];RF9 cs_item_sk->[inv_item_sk]
+----------------------PhysicalOlapScan[inventory] apply RFs: RF8 RF9 RF10
----------------------PhysicalProject
-------------------------hashJoin[INNER_JOIN shuffle] hashCondition=((item.i_item_sk = catalog_sales.cs_item_sk)) otherCondition=() build RFs:RF7 i_item_sk->[cs_item_sk]
+------------------------hashJoin[INNER_JOIN bucketShuffle] hashCondition=((item.i_item_sk = catalog_sales.cs_item_sk)) otherCondition=() build RFs:RF7 i_item_sk->[cs_item_sk]
--------------------------PhysicalProject
----------------------------hashJoin[INNER_JOIN broadcast] hashCondition=((d1.d_week_seq = d2.d_week_seq)) otherCondition=() build RFs:RF6 d_week_seq->[d_week_seq]
------------------------------PhysicalProject
---------------------------------hashJoin[RIGHT_OUTER_JOIN colocated] hashCondition=((catalog_returns.cr_item_sk = catalog_sales.cs_item_sk) and (catalog_returns.cr_order_number = catalog_sales.cs_order_number)) otherCondition=() build RFs:RF4 cs_item_sk->[cr_item_sk];RF5 cs_order_number->[cr_order_number]
+--------------------------------hashJoin[RIGHT_OUTER_JOIN shuffle] hashCondition=((catalog_returns.cr_item_sk = catalog_sales.cs_item_sk) and (catalog_returns.cr_order_number = catalog_sales.cs_order_number)) otherCondition=() build RFs:RF4 cs_item_sk->[cr_item_sk];RF5 cs_order_number->[cr_order_number]
----------------------------------PhysicalProject
------------------------------------PhysicalOlapScan[catalog_returns] apply RFs: RF4 RF5
----------------------------------PhysicalProject
@@ -28,14 +29,14 @@ PhysicalResultSink
----------------------------------------------PhysicalProject
------------------------------------------------hashJoin[INNER_JOIN broadcast] hashCondition=((catalog_sales.cs_ship_date_sk = d3.d_date_sk) and (catalog_sales.cs_sold_date_sk = d1.d_date_sk)) otherCondition=() build RFs:RF0 d_date_sk->[cs_ship_date_sk];RF1 d_date_sk->[cs_sold_date_sk]
--------------------------------------------------PhysicalProject
-----------------------------------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF0 RF1 RF2 RF3 RF7 RF9
+----------------------------------------------------PhysicalOlapScan[catalog_sales] apply RFs: RF0 RF1 RF2 RF3 RF7
--------------------------------------------------PhysicalProject
-----------------------------------------------------NestedLoopJoin[INNER_JOIN](d3.d_date > days_add(d_date, INTERVAL 5 DAY))
+----------------------------------------------------NestedLoopJoin[INNER_JOIN](d3.d_date > days_add(d_date, 5))
------------------------------------------------------PhysicalProject
---------------------------------------------------------PhysicalOlapScan[date_dim]
+--------------------------------------------------------PhysicalOlapScan[date_dim(d3)]
------------------------------------------------------PhysicalProject
--------------------------------------------------------filter((d1.d_year = 1998))
-----------------------------------------------------------PhysicalOlapScan[date_dim] apply RFs: RF6
+----------------------------------------------------------PhysicalOlapScan[date_dim(d1)] apply RFs: RF6
----------------------------------------------PhysicalProject
------------------------------------------------filter((household_demographics.hd_buy_potential = '1001-5000'))
--------------------------------------------------PhysicalOlapScan[household_demographics]
@@ -45,10 +46,9 @@ PhysicalResultSink
--------------------------------------PhysicalProject
----------------------------------------PhysicalOlapScan[promotion]
------------------------------PhysicalProject
---------------------------------PhysicalOlapScan[date_dim] apply RFs: RF8
+--------------------------------PhysicalOlapScan[date_dim(d2)]
--------------------------PhysicalProject
-----------------------------PhysicalOlapScan[item] apply RFs: RF9
-----------------------PhysicalOlapScan[inventory] apply RFs: RF10
+----------------------------PhysicalOlapScan[item]
------------------PhysicalProject
--------------------PhysicalOlapScan[warehouse]
diff --git a/regression-test/data/shape_check/tpch_sf1000/rf_prune/q20-rewrite.out b/regression-test/data/shape_check/tpch_sf1000/rf_prune/q20-rewrite.out
index 3c5d16708be..ef604a2ae52 100644
--- a/regression-test/data/shape_check/tpch_sf1000/rf_prune/q20-rewrite.out
+++ b/regression-test/data/shape_check/tpch_sf1000/rf_prune/q20-rewrite.out
@@ -5,7 +5,7 @@ PhysicalResultSink
----PhysicalDistribute[DistributionSpecGather]
------PhysicalQuickSort[LOCAL_SORT]
--------PhysicalProject
-----------hashJoin[RIGHT_SEMI_JOIN shuffle] hashCondition=((supplier.s_suppkey = t3.ps_suppkey)) otherCondition=() build RFs:RF4 s_suppkey->[l_suppkey,ps_suppkey]
+----------hashJoin[RIGHT_SEMI_JOIN bucketShuffle] hashCondition=((supplier.s_suppkey = t3.ps_suppkey)) otherCondition=() build RFs:RF4 s_suppkey->[l_suppkey,ps_suppkey]
------------PhysicalProject
--------------hashJoin[INNER_JOIN bucketShuffle] hashCondition=((t2.l_partkey = t1.ps_partkey) and (t2.l_suppkey = t1.ps_suppkey)) otherCondition=((cast(ps_availqty as DECIMALV3(38, 3)) > t2.l_q)) build RFs:RF2 ps_partkey->[l_partkey];RF3 ps_suppkey->[l_suppkey]
----------------PhysicalProject
diff --git a/regression-test/data/shape_check/tpch_sf1000/rf_prune/q20.out b/regression-test/data/shape_check/tpch_sf1000/rf_prune/q20.out
index 38ecc268fdf..9294ea8fbc5 100644
--- a/regression-test/data/shape_check/tpch_sf1000/rf_prune/q20.out
+++ b/regression-test/data/shape_check/tpch_sf1000/rf_prune/q20.out
@@ -5,7 +5,7 @@ PhysicalResultSink
----PhysicalDistribute[DistributionSpecGather]
------PhysicalQuickSort[LOCAL_SORT]
--------PhysicalProject
-----------hashJoin[RIGHT_SEMI_JOIN shuffle] hashCondition=((supplier.s_suppkey = partsupp.ps_suppkey)) otherCondition=() build RFs:RF4 s_suppkey->[l_suppkey,ps_suppkey]
+----------hashJoin[RIGHT_SEMI_JOIN bucketShuffle] hashCondition=((supplier.s_suppkey = partsupp.ps_suppkey)) otherCondition=() build RFs:RF4 s_suppkey->[l_suppkey,ps_suppkey]
------------PhysicalProject
--------------hashJoin[INNER_JOIN bucketShuffle] hashCondition=((lineitem.l_partkey = partsupp.ps_partkey) and (lineitem.l_suppkey = partsupp.ps_suppkey)) otherCondition=((cast(ps_availqty as DECIMALV3(38, 3)) > (0.5 * sum(l_quantity)))) build RFs:RF2 ps_partkey->[l_partkey];RF3 ps_suppkey->[l_suppkey]
----------------hashAgg[GLOBAL]
diff --git a/regression-test/data/shape_check/tpch_sf1000/shape/q20-rewrite.out b/regression-test/data/shape_check/tpch_sf1000/shape/q20-rewrite.out
index 3c5d16708be..ef604a2ae52 100644
--- a/regression-test/data/shape_check/tpch_sf1000/shape/q20-rewrite.out
+++ b/regression-test/data/shape_check/tpch_sf1000/shape/q20-rewrite.out
@@ -5,7 +5,7 @@ PhysicalResultSink
----PhysicalDistribute[DistributionSpecGather]
------PhysicalQuickSort[LOCAL_SORT]
--------PhysicalProject
-----------hashJoin[RIGHT_SEMI_JOIN shuffle] hashCondition=((supplier.s_suppkey = t3.ps_suppkey)) otherCondition=() build RFs:RF4 s_suppkey->[l_suppkey,ps_suppkey]
+----------hashJoin[RIGHT_SEMI_JOIN bucketShuffle] hashCondition=((supplier.s_suppkey = t3.ps_suppkey)) otherCondition=() build RFs:RF4 s_suppkey->[l_suppkey,ps_suppkey]
------------PhysicalProject
--------------hashJoin[INNER_JOIN bucketShuffle] hashCondition=((t2.l_partkey = t1.ps_partkey) and (t2.l_suppkey = t1.ps_suppkey)) otherCondition=((cast(ps_availqty as DECIMALV3(38, 3)) > t2.l_q)) build RFs:RF2 ps_partkey->[l_partkey];RF3 ps_suppkey->[l_suppkey]
----------------PhysicalProject
diff --git a/regression-test/data/shape_check/tpch_sf1000/shape/q20.out b/regression-test/data/shape_check/tpch_sf1000/shape/q20.out
index 38ecc268fdf..9294ea8fbc5 100644
--- a/regression-test/data/shape_check/tpch_sf1000/shape/q20.out
+++ b/regression-test/data/shape_check/tpch_sf1000/shape/q20.out
@@ -5,7 +5,7 @@ PhysicalResultSink
----PhysicalDistribute[DistributionSpecGather]
------PhysicalQuickSort[LOCAL_SORT]
--------PhysicalProject
-----------hashJoin[RIGHT_SEMI_JOIN shuffle] hashCondition=((supplier.s_suppkey = partsupp.ps_suppkey)) otherCondition=() build RFs:RF4 s_suppkey->[l_suppkey,ps_suppkey]
+----------hashJoin[RIGHT_SEMI_JOIN bucketShuffle] hashCondition=((supplier.s_suppkey = partsupp.ps_suppkey)) otherCondition=() build RFs:RF4 s_suppkey->[l_suppkey,ps_suppkey]
------------PhysicalProject
--------------hashJoin[INNER_JOIN bucketShuffle] hashCondition=((lineitem.l_partkey = partsupp.ps_partkey) and (lineitem.l_suppkey = partsupp.ps_suppkey)) otherCondition=((cast(ps_availqty as DECIMALV3(38, 3)) > (0.5 * sum(l_quantity)))) build RFs:RF2 ps_partkey->[l_partkey];RF3 ps_suppkey->[l_suppkey]
----------------hashAgg[GLOBAL]
diff --git a/regression-test/suites/nereids_p0/hash_shuffle_key_prune/hash_shuffle_key_prune.groovy b/regression-test/suites/nereids_p0/hash_shuffle_key_prune/hash_shuffle_key_prune.groovy
new file mode 100644
index 00000000000..eac912b995b
--- /dev/null
+++ b/regression-test/suites/nereids_p0/hash_shuffle_key_prune/hash_shuffle_key_prune.groovy
@@ -0,0 +1,194 @@
+// 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("hash_shuffle_key_prune") {
+ multi_sql """
+ drop table if exists t1;
+ create table t1(a int, b int, c int, d int, e int, f int, g int, h int, i int, j int) properties("replication_num"="1");
+ alter table t1 modify column a set stats ('row_count'='204000', 'ndv'='8', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+ alter table t1 modify column b set stats ('row_count'='204000', 'ndv'='30', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+ alter table t1 modify column c set stats ('row_count'='204000', 'ndv'='100', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+ alter table t1 modify column d set stats ('row_count'='204000', 'ndv'='15000', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+ alter table t1 modify column e set stats ('row_count'='204000', 'ndv'='20000', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+ alter table t1 modify column f set stats ('row_count'='204000', 'ndv'='8', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+ alter table t1 modify column g set stats ('row_count'='204000', 'ndv'='30', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+ alter table t1 modify column h set stats ('row_count'='204000', 'ndv'='100', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+ alter table t1 modify column i set stats ('row_count'='204000', 'ndv'='15000', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+ alter table t1 modify column j set stats ('row_count'='204000', 'ndv'='20000', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+
+ drop table if exists t2;
+ create table t2(a int, b int, c int, d int, e int, f int, g int, h int, i int, j int) distributed by hash(a) properties("replication_num"="1");
+ alter table t2 modify column a set stats ('row_count'='204000', 'ndv'='8', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+ alter table t2 modify column b set stats ('row_count'='204000', 'ndv'='30', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+ alter table t2 modify column c set stats ('row_count'='204000', 'ndv'='100', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+ alter table t2 modify column d set stats ('row_count'='204000', 'ndv'='15000', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+ alter table t2 modify column e set stats ('row_count'='204000', 'ndv'='20000', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+ alter table t2 modify column f set stats ('row_count'='204000', 'ndv'='8', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+ alter table t2 modify column g set stats ('row_count'='204000', 'ndv'='30', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+ alter table t2 modify column h set stats ('row_count'='204000', 'ndv'='100', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+ alter table t2 modify column i set stats ('row_count'='204000', 'ndv'='15000', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+ alter table t2 modify column j set stats ('row_count'='204000', 'ndv'='20000', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+
+ drop table if exists t3;
+ create table t3(a int, b int, c int, d int, e int, f int, g int, h int, i int, j int) distributed by hash(e) properties("replication_num"="1");
+ alter table t3 modify column a set stats ('row_count'='204000', 'ndv'='2000', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+ alter table t3 modify column b set stats ('row_count'='204000', 'ndv'='30', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+ alter table t3 modify column c set stats ('row_count'='204000', 'ndv'='100', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+ alter table t3 modify column d set stats ('row_count'='204000', 'ndv'='15000', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+ alter table t3 modify column e set stats ('row_count'='204000', 'ndv'='20000', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+ alter table t3 modify column f set stats ('row_count'='204000', 'ndv'='8', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+ alter table t3 modify column g set stats ('row_count'='204000', 'ndv'='30', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+ alter table t3 modify column h set stats ('row_count'='204000', 'ndv'='100', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+ alter table t3 modify column i set stats ('row_count'='204000', 'ndv'='15000', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+ alter table t3 modify column j set stats ('row_count'='204000', 'ndv'='20000', 'min_value'='0', 'max_value'='1000', 'avg_size'='880961', 'max_size'='880961' );
+
+ set disable_nereids_rules='prune_empty_partition';
+ set enable_agg_shuffle_key_prune=true;
+ set detail_shape_nodes='PhysicalDistribute';
+ set runtime_filter_mode=OFF;
+ set global enable_auto_analyze=false;
+ set parallel_pipeline_task_num=4;
+ """
+ sql """
+ insert into t1 values
+ (1,1,1,1,1,1,1,1,1,10),
+ (1,1,1,1,1,1,1,1,1,20),
+ (1,1,1,2,2,2,2,2,2,30),
+ (1,2,2,2,2,2,2,2,2,40),
+ (2,2,2,2,2,2,2,2,2,50),
+ (2,2,3,3,3,3,3,3,3,60),
+ (3,3,3,3,3,3,3,3,3,70);
+ """
+ sql """
+ insert into t2 values
+ (1,1,1,1,1,1,1,1,1,10),
+ (1,1,1,1,1,1,1,1,1,20),
+ (1,1,1,2,2,2,2,2,2,30),
+ (1,2,2,2,2,2,2,2,2,40),
+ (2,2,2,2,2,2,2,2,2,50),
+ (2,2,3,3,3,3,3,3,3,60),
+ (3,3,3,3,3,3,3,3,3,70);
+ """
+ sql """
+ insert into t3 values
+ (1,1,1,1,1,1,1,1,1,10),
+ (1,1,1,1,1,1,1,1,1,20),
+ (1,1,1,2,2,2,2,2,2,30),
+ (1,2,2,2,2,2,2,2,2,40),
+ (2,2,2,2,2,2,2,2,2,50),
+ (2,2,3,3,3,3,3,3,3,60),
+ (3,3,3,3,3,3,3,3,3,70);
+ """
+ def checkShuffleKey = { String sqlString, String... expectedHashCols ->
+ def result = sql """ explain shape plan ${sqlString}"""
+ for (def expected : expectedHashCols) {
+ assertTrue(result.toString().contains("Hash Columns:[${expected}]"), "\n${result.collect { it[0] }.join('\n')} not contains expected hash cols: ${expected}")
+ }
+ }
+ // 1. simple agg
+ explainAndOrderResult "simple_agg", "select a from t1 group by a,b,c,d,e,f order by a,b,c,d,e,f;"
+ // 2. agg receive a hash request from upper node
+ explainAndOrderResult "agg_receive_shuffle_request", "select sum(a) over (partition by a,b,c,d,e,f order by a,b,c) from (select a,b,c,d,e,f,g,h from t1 group by a,b,c,d,e,f,g,h) t order by 1;"
+ // 3. agg with distinct agg function
+ // 3.1 random distribute (not satisfy group by key)
+ // 2+1
+ explainAndOrderResult "distinct_agg_func_with_random_distribute_gby_key_greater_than_threshold", "select d,e,f,g,h,i,j, count(distinct a,b,c) from t1 group by d,e,f,g,h,i,j order by d,e,f,g,h,i,j;"
+ explainAndOrderResult "distinct_agg_func_with_random_distribute_distinct_key_greater_than_threshold", "select d,e,f,g,h,i,j, count(distinct a,b,c,d,e,f) from t1 group by d,e,f,g,h,i,j order by d,e,f,g,h,i,j;"
+ // 2+2
+ sql "set agg_phase=4"
+ explainAndOrderResult "distinct_agg_func_with_random_distribute_gby_key_greater_than_threshold_2_plus_2", "select d,e,f,g,h,i,j, count(distinct a,b,c) from t1 group by d,e,f,g,h,i,j order by d,e,f,g,h,i,j;"
+ explainAndOrderResult "distinct_agg_func_with_random_distribute_distinct_key_greater_than_threshold_2_plus_2", "select d,e,f,g,h,i,j, count(distinct a,b,c,d,e,f) from t1 group by d,e,f,g,h,i,j order by d,e,f,g,h,i,j;"
+ explainAndOrderResult "distinct_agg_func_with_random_distribute_without_redundant_agg", "select d,f,g,h,i,a, count(distinct e,b,c) from t1 group by d,f,g,h,i,a order by d,f,g,h,i,a;"
+ explainAndOrderResult "distinct_agg_func_with_random_distribute_with_redundant_agg", "select d,f,e,h,i,a, count(distinct a,b,c) from t1 group by d,f,e,h,i,a order by d,f,e,h,i,a;"
+ sql "set agg_phase=0"
+
+ // 3.2 hash distribute by a
+ // 1+1
+ explainAndOrderResult "distinct_agg_func_with_a_hash_distribute_gby_key_has_a", "select a,e,f,g,h,i,j, count(distinct e,b,c) from t2 group by a,e,f,g,h,i,j order by a,e,f,g,h,i,j;"
+ explainAndOrderResult "distinct_agg_func_with_e_hash_distribute_gby_key_has_a", "select a,e,f,g,h,i,j, count(distinct e,b,c) from t2 group by a,e,f,g,h,i,j order by a,e,f,g,h,i,j;"
+ // 1+2 satisfy distinct key
+ explainAndOrderResult "distinct_agg_func_with_a_hash_distribute_distinct_key_has_a", "select d,e,f,g,h,i,j, count(distinct a,b,c) from t2 group by d,e,f,g,h,i,j order by d,e,f,g,h,i,j;"
+ // 1+2 with redundant agg
+ sql "set agg_phase=4"
+ explainAndOrderResult "distinct_agg_func_with_a_hash_distribute_gby_key_has_a_1_plus_2_redundant_agg", "select a,e,f,g,h,i,j, count(distinct e,b,c) from t2 group by a,e,f,g,h,i,j order by a,e,f,g,h,i,j;"
+ explainAndOrderResult "distinct_agg_func_with_e_hash_distribute_gby_key_has_a_1_plus_2_redundant_agg", "select a,e,f,g,h,i,j, count(distinct e,b,c) from t3 group by a,e,f,g,h,i,j order by a,e,f,g,h,i,j;"
+ sql "set agg_phase=0"
+
+ // 4. agg with distinct agg function and a avg
+ // 4.1 random distribute
+ // 2+1
+ explainAndOrderResult "distinct_agg_func_with_random_distribute_gby_key_greater_than_threshold_avg", "select d,e,f,g,h,i,j, count(distinct a,b,c), round(avg(a),4) from t1 group by d,e,f,g,h,i,j order by d,e,f,g,h,i,j;"
+ explainAndOrderResult "distinct_agg_func_with_random_distribute_distinct_key_greater_than_threshold_avg", "select d,e,f,g,h,i,j, count(distinct a,b,c,d,e,f), round(avg(a),4) from t1 group by d,e,f,g,h,i,j order by d,e,f,g,h,i,j;"
+ // 2+2
+ sql "set agg_phase=4"
+ explainAndOrderResult "distinct_agg_func_with_random_distribute_gby_key_greater_than_threshold_2_plus_2_avg", "select d,e,f,g,h,i,j, count(distinct a,b,c), round(avg(a),4) from t1 group by d,e,f,g,h,i,j order by d,e,f,g,h,i,j;"
+ explainAndOrderResult "distinct_agg_func_with_random_distribute_distinct_key_greater_than_threshold_2_plus_2_avg", "select d,e,f,g,h,i,j, count(distinct a,b,c,d,e,f), round(avg(a),4) from t1 group by d,e,f,g,h,i,j order by d,e,f,g,h,i,j;"
+ explainAndOrderResult "distinct_agg_func_with_random_distribute_without_redundant_agg_avg", "select d,f,g,h,i,a, count(distinct e,b,c), round(avg(a),4) from t1 group by d,f,g,h,i,a order by d,f,g,h,i,a;"
+ explainAndOrderResult "distinct_agg_func_with_random_distribute_with_redundant_agg_avg", "select d,f,e,h,i,a, count(distinct a,b,c), round(avg(a),4) from t1 group by d,f,e,h,i,a order by d,f,e,h,i,a;"
+ sql "set agg_phase=0"
+
+ // 4.2 hash distribute by a
+ // 1+1
+ explainAndOrderResult "distinct_agg_func_with_a_hash_distribute_gby_key_has_a_avg", "select a,e,f,g,h,i,j, count(distinct e,b,c), round(avg(a),4) from t2 group by a,e,f,g,h,i,j order by a,e,f,g,h,i,j;"
+ explainAndOrderResult "distinct_agg_func_with_e_hash_distribute_gby_key_has_a_avg", "select a,e,f,g,h,i,j, count(distinct e,b,c), round(avg(a),4) from t2 group by a,e,f,g,h,i,j order by a,e,f,g,h,i,j;"
+ // 1+2 satisfy distinct key
+ explainAndOrderResult "distinct_agg_func_with_a_hash_distribute_distinct_key_has_a_avg", "select d,e,f,g,h,i,j, count(distinct a,b,c), round(avg(a),4) from t2 group by d,e,f,g,h,i,j order by d,e,f,g,h,i,j;"
+ // 1+2 with redundant agg
+ sql "set agg_phase=4"
+ explainAndOrderResult "distinct_agg_func_with_a_hash_distribute_gby_key_has_a_1_plus_2_redundant_agg_avg", "select a,e,f,g,h,i,j, count(distinct e,b,c), round(avg(a),4) from t2 group by a,e,f,g,h,i,j order by a,e,f,g,h,i,j;"
+ explainAndOrderResult "distinct_agg_func_with_e_hash_distribute_gby_key_has_a_1_plus_2_redundant_agg_avg", "select a,e,f,g,h,i,j, count(distinct e,b,c), round(avg(a),4) from t3 group by a,e,f,g,h,i,j order by a,e,f,g,h,i,j;"
+ sql "set agg_phase=0"
+
+ // 5. agg and join (visitPhysicalHashJoin single shuffle key selection)
+ // Both sides Global AGG, join 7 columns (>5): should choose single column shuffle (e.g. e)
+ explainAndOrderResult "agg_join", """select t1.a,t1.b,t1.c,t1.d,t1.e,t1.f,t1.g from (select a,b,c,d,e,f,g from t1 group by a,b,c,d,e,f,g) t1 inner join (select a,b,c,d,e,f,g from t1 group by a,b,c,d,e,f,g) t2 on t1.a=t2.a and t1.b=t2.b and t1.c=t2.c and t1.d=t2.d and t1.e=t2.e and t1.f=t2.f and t1.g=t2.g order by t1.a,t1.b,t1.c,t1.d,t1.e,t1.f,t1.g;"""
+ // Both sides Global AGG, join 5 columns, agg gby key also 5 columns (=threshold): size>5 not satisfied, use full 5 column shuffle, no single key
+ explainAndOrderResult "agg_join_5_keys", """select t1.a,t1.b,t1.c,t1.d,t1.e from (select a,b,c,d,e from t1 group by a,b,c,d,e) t1 inner join (select a,b,c,d,e from t1 group by a,b,c,d,e) t2 on t1.a=t2.a and t1.b=t2.b and t1.c=t2.c and t1.d=t2.d and t1.e=t2.e order by t1.a,t1.b,t1.c,t1.d,t1.e;"""
+ // join 5 columns, agg gby key > 5 columns
+ // Why does this use 1 shuffle key? Because after agg with 1 shuffle key e, join does not need to re-hash.
+ explainAndOrderResult "agg_7_join_5_keys", """select t1.a,t1.b,t1.c,t1.d,t1.e from (select a,b,c,d,e from t1 group by a,b,c,d,e,f,g) t1 inner join (select a,b,c,d,e from t1 group by a,b,c,d,e,f,g) t2 on t1.a=t2.a and t1.b=t2.b and t1.c=t2.c and t1.d=t2.d and t1.e=t2.e order by t1.a,t1.b,t1.c,t1.d,t1.e;"""
+
+ // Both sides Global AGG, join 3 columns (<threshold): full 3 column shuffle
+ // Why does this use 3 shuffle keys? If we used one shuffle key e, join would still need to do hash shuffle again.
+ explainAndOrderResult "agg_join_3_keys_not_choose_1_key", """select t1.a,t1.b,t1.c from (select a,b,c from t1 group by a,b,c,d,e,f,g) t1 inner join (select a,b,c from t1 group by a,b,c,d,e,f,g) t2 on t1.a=t2.a and t1.b=t2.b and t1.c=t2.c order by t1.a,t1.b,t1.c;"""
+ // If join key contains e, agg shuffle uses single shuffle key e, join does not need to re-hash.
+ explainAndOrderResult "agg_7_join_3_keys_choose_1_key", """select t1.a,t1.b,t1.c,t1.e from (select a,b,c,e from t1 group by a,b,c,d,e,f,g) t1 inner join (select a,b,c,e from t1 group by a,b,c,d,e,f,g) t2 on t1.a=t2.a and t1.b=t2.b and t1.e=t2.e order by t1.a,t1.b,t1.c,t1.e;"""
+
+ // Both sides from different tables, both Global AGG, join 7 columns: single key optimization
+ explainAndOrderResult "agg_join_t1_t2", """select t1.a,t1.b,t1.c,t1.d,t1.e,t1.f,t1.g from (select a,b,c,d,e,f,g from t1 group by a,b,c,d,e,f,g) t1 inner join (select a,b,c,d,e,f,g from t2 group by a,b,c,d,e,f,g) t2 on t1.a=t2.a and t1.b=t2.b and t1.c=t2.c and t1.d=t2.d and t1.e=t2.e and t1.f=t2.f and t1.g=t2.g order by t1.a,t1.b,t1.c,t1.d,t1.e,t1.f,t1.g;"""
+ // one-side is scan, the other side is agg, not satisfy "both sides are Global AGG", use normal shuffle (full join key)
+ explainAndOrderResult "agg_join_one_side_agg", """select t1.a,t1.b,t1.c from t1 inner join (select a,b,c from t2 group by a,b,c) t2 on t1.a=t2.a and t1.b=t2.b and t1.c=t2.c order by t1.a,t1.b,t1.c;"""
+
+ // e=f f=e non-one-to-one mapping scenario
+ explainAndOrderResult "join_e_equal_f", """select t1.a,t1.b,t1.c,t1.d,t1.e,t1.f,t1.g from (select a,b,c,d,e,f,g from t1 group by a,b,c,d,e,f,g) t1 inner join (select a,b,c,d,e,f,g from t1 group by a,b,c,d,e,f,g) t2 on t1.a=t2.a and t1.b=t2.b and t1.c=t2.c and t1.d=t2.d and t1.e=t2.f and t1.f=t2.e and t1.g=t2.g order by t1.a,t1.b,t1.c,t1.d,t1.e,t1.f,t1.g;"""
+
+ // When both sides satisfy agg distribution
+ explainAndOrderResult "join_both_side_satisfy_agg_distribution", """select t1.a,t1.b,t1.c,t1.d,t1.e,t1.f,t1.g from (select a,b,c,d,e,f,g from t2 group by a,b,c,d,e,f,g) t1 inner join (select a,b,c,d,e,f,g from t2 group by a,b,c,d,e,f,g) t2 on t1.a=t2.a and t1.b=t2.b and t1.c=t2.c and t1.d=t2.d and t1.e=t2.e and t1.f=t2.f and t1.g=t2.g order by t1.a,t1.b,t1.c,t1.d,t1.e,t1.f,t1.g;"""
+ // One side of join satisfies agg distribution
+ explainAndOrderResult "join_one_side_satisfy_agg_distribution", """select t1.a,t1.b,t1.c,t1.d,t1.e,t1.f,t1.g from (select a,b,c,d,e,f,g from t1 group by a,b,c,d,e,f,g) t1 inner join (select a,b,c,d,e,f,g from t2 group by a,b,c,d,e,f,g) t2 on t1.a=t2.a and t1.b=t2.b and t1.c=t2.c and t1.d=t2.d and t1.e=t2.e and t1.f=t2.f and t1.g=t2.g order by t1.a,t1.b,t1.c,t1.d,t1.e,t1.f,t1.g;"""
+
+ // 5.7 Disable single key optimization: should use full join column shuffle
+ sql "set enable_agg_shuffle_key_prune=false"
+ explainAndOrderResult "agg_join_no_opt", """select t1.a,t1.b,t1.c,t1.d,t1.e,t1.f,t1.g from (select a,b,c,d,e,f,g from t1 group by a,b,c,d,e,f,g) t1 inner join (select a,b,c,d,e,f,g from t1 group by a,b,c,d,e,f,g) t2 on t1.a=t2.a and t1.b=t2.b and t1.c=t2.c and t1.d=t2.d and t1.e=t2.e and t1.f=t2.f and t1.g=t2.g order by t1.a,t1.b,t1.c,t1.d,t1.e,t1.f,t1.g;"""
+ sql "set enable_agg_shuffle_key_prune=true"
+
+ // No optimization, because under join is project, not agg
+ explainAndOrderResult "join_project_agg", """select * from (select a,b,c,e,c+1 col1 ,b+2 col2 from t1 group by a,b,c,e) t1 inner join (select a,b,c,e,e+1 col1,c+2 col2 from t1 group by a,b,c,e) t2 on t1.a=t2.a and t1.b=t2.b and t1.c=t2.c and t1.e=t2.e and t1.col1=t2.col1 and t1.col2=t2.col2 order by t1.a,t1.b,t1.c,t1.e;"""
+
+ // Not optimize
+ explainAndOrderResult "distinct_agg_func_with_a_hash_distribute_no_optimize", "select d,e,f, count(distinct a,b,c) from t2 group by d,e,f order by d,e,f;"
+}
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org