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