diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/Stats.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/Stats.java
index 63c67a5..254d364 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/Stats.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/Stats.java
@@ -53,6 +53,7 @@
 import org.apache.hyracks.algebricks.core.algebra.base.LogicalOperatorTag;
 import org.apache.hyracks.algebricks.core.algebra.base.LogicalVariable;
 import org.apache.hyracks.algebricks.core.algebra.expressions.AbstractFunctionCallExpression;
+import org.apache.hyracks.algebricks.core.algebra.expressions.AbstractLogicalExpression;
 import org.apache.hyracks.algebricks.core.algebra.expressions.AggregateFunctionCallExpression;
 import org.apache.hyracks.algebricks.core.algebra.expressions.ConstantExpression;
 import org.apache.hyracks.algebricks.core.algebra.expressions.JoinProductivityAnnotation;
@@ -162,11 +163,19 @@
                 return productivity / card1;
             }
         } else {
-            if (card1 < card2) {
-                // we are assuming that the smaller side is the primary side and that the join is Pk-Fk join.
-                return 1.0 / card1;
+            ILogicalOperator leafInput = joinEnum.leafInputs.get(idx2 - 1); // we arbitrarily pick one side
+            Index index = findIndex(leafInput);
+            if (index == null) {
+                return 1.0;
             }
-            return 1.0 / card2;
+            List<List<IAObject>> result = runSamplingQueryDistinct(this.optCtx, leafInput, exprUsedVars.get(1), index);
+            if (result == null) {
+                return 1.0;
+            }
+
+            double estDistinctCardinalityFromSample = findPredicateCardinality(result, false);
+            double numDistincts = distinctEstimator2(estDistinctCardinalityFromSample, index);
+            return 1.0 / numDistincts; // this is the expected selectivity for joins.
         }
     }
 
@@ -576,6 +585,101 @@
         return AnalysisUtil.runQuery(newAggOpRef, Arrays.asList(aggVar), newCtx, IRuleSetFactory.RuleSetKind.SAMPLING);
     }
 
+    protected Index findIndex(ILogicalOperator logOp) throws AlgebricksException {
+        ILogicalOperator parent = joinEnum.findDataSourceScanOperatorParent(logOp);
+        if (parent == null) {
+            return null;
+        }
+        DataSourceScanOperator scanOp;
+        if (parent instanceof DataSourceScanOperator) {
+            scanOp = (DataSourceScanOperator) parent;
+        } else {
+            scanOp = (DataSourceScanOperator) parent.getInputs().get(0).getValue();
+        }
+
+        if (scanOp == null) {
+            return null;
+        }
+        Index index = findSampleIndex(scanOp, optCtx);
+        if (index == null) {
+            return null;
+        }
+        return index;
+    }
+
+    protected List<List<IAObject>> runSamplingQueryDistinct(IOptimizationContext ctx, ILogicalOperator logOp,
+            LogicalVariable var, Index index) throws AlgebricksException {
+        LOGGER.info("***running sample query***");
+
+        IOptimizationContext newCtx = ctx.getOptimizationContextFactory().cloneOptimizationContext(ctx);
+
+        ILogicalOperator newLogOp = OperatorManipulationUtil.bottomUpCopyOperators(logOp);
+        storeSelectConditionsAndMakeThemTrue(newLogOp, null);
+        // by passing in null, all select expression will become true.
+        // no need to restore them either as this is dne on a copy of the logOp.
+
+        ILogicalOperator parent = joinEnum.findDataSourceScanOperatorParent(newLogOp);
+        DataSourceScanOperator scanOp;
+        if (parent instanceof DataSourceScanOperator) {
+            scanOp = (DataSourceScanOperator) parent;
+        } else {
+            scanOp = (DataSourceScanOperator) parent.getInputs().get(0).getValue();
+        }
+        Index.SampleIndexDetails idxDetails = (Index.SampleIndexDetails) index.getIndexDetails();
+        double origDatasetCard = idxDetails.getSourceCardinality();
+        double sampleCard = Math.min(idxDetails.getSampleCardinalityTarget(), origDatasetCard);
+        if (sampleCard == 0) {
+            sampleCard = 1;
+            IWarningCollector warningCollector = optCtx.getWarningCollector();
+            if (warningCollector.shouldWarn()) {
+                warningCollector.warn(Warning.of(scanOp.getSourceLocation(),
+                        org.apache.asterix.common.exceptions.ErrorCode.SAMPLE_HAS_ZERO_ROWS));
+            }
+        }
+
+        // replace the dataScanSourceOperator with the sampling source
+        SampleDataSource sampledatasource = joinEnum.getSampleDataSource(scanOp);
+        DataSourceScanOperator deepCopyofScan =
+                (DataSourceScanOperator) OperatorManipulationUtil.bottomUpCopyOperators(scanOp);
+
+        if (!(parent instanceof DataSourceScanOperator)) {
+            deepCopyofScan.setDataSource(sampledatasource);
+            parent.getInputs().get(0).setValue(deepCopyofScan);
+        } else {
+            scanOp.setDataSource(sampledatasource);
+        }
+
+        List<Mutable<ILogicalExpression>> aggFunArgs = new ArrayList<>(1);
+        aggFunArgs.add(new MutableObject<>(ConstantExpression.TRUE));
+
+        AbstractLogicalExpression inputVarRef = new VariableReferenceExpression(var, newLogOp.getSourceLocation());
+        List<Mutable<ILogicalExpression>> fields = new ArrayList<>(1);
+        fields.add(new MutableObject<>(inputVarRef));
+
+        BuiltinFunctionInfo countFn = BuiltinFunctions.getBuiltinFunctionInfo(BuiltinFunctions.SQL_COUNT_DISTINCT);
+        AggregateFunctionCallExpression aggExpr = new AggregateFunctionCallExpression(countFn, false, fields);
+
+        List<Mutable<ILogicalExpression>> aggExprList = new ArrayList<>(1);
+        aggExprList.add(new MutableObject<>(aggExpr));
+
+        List<LogicalVariable> aggVarList = new ArrayList<>(1);
+        LogicalVariable aggVar = newCtx.newVar();
+        aggVarList.add(aggVar);
+
+        AggregateOperator newAggOp = new AggregateOperator(aggVarList, aggExprList);
+        newAggOp.getInputs().add(new MutableObject<>(newLogOp));
+
+        Mutable<ILogicalOperator> newAggOpRef = new MutableObject<>(newAggOp);
+
+        OperatorPropertiesUtil.typeOpRec(newAggOpRef, newCtx);
+        LOGGER.info("***returning from sample query***");
+
+        String viewInPlan = new ALogicalPlanImpl(newAggOpRef).toString(); //useful when debugging
+        LOGGER.trace("viewInPlan");
+        LOGGER.trace(viewInPlan);
+        return AnalysisUtil.runQuery(newAggOpRef, Arrays.asList(aggVar), newCtx, IRuleSetFactory.RuleSetKind.SAMPLING);
+    }
+
     // This one gets the cardinality and also projection sizes
     protected List<List<IAObject>> runSamplingQueryProjection(IOptimizationContext ctx, ILogicalOperator logOp,
             int dataset, LogicalVariable primaryKey) throws AlgebricksException {
@@ -778,6 +882,41 @@
         return Math.round(estDistCardinality);
     }
 
+    // Formula is d = D (1 - e^(-sampleCard/D))
+    double DistinctFormula(double sampleCard, double D) {
+        double a, b, c;
+
+        a = -sampleCard / D;
+        b = Math.exp(a);
+        c = 1.0 - b;
+        double x = D * c;
+        return x;
+    }
+
+    private double distinctEstimator2(double estDistinctCardinalityFromSample, Index index) throws AlgebricksException {
+
+        Index.SampleIndexDetails idxDetails = (Index.SampleIndexDetails) index.getIndexDetails();
+        double origDatasetCardinality = idxDetails.getSourceCardinality();
+        double sampleCard = idxDetails.getSampleCardinalityTarget();
+
+        double D, Dmin, Dmax;
+
+        Dmin = 1;
+        Dmax = origDatasetCardinality; // initial estimate. Binary search follows
+        D = estDistinctCardinalityFromSample;
+        int i = 0;
+        while ((Dmin < Dmax) && i < 100) { // just being very cautious to avoid infinite loops.
+            i++;
+            D = (Dmax + Dmin) / 2.0;
+            double x = DistinctFormula(sampleCard, D);
+            if (x < estDistinctCardinalityFromSample)
+                Dmin = D + 1;
+            else
+                Dmax = D - 1;
+        }
+        return D;
+    }
+
     // Use the Newton-Raphson method for distinct cardinality estimation.
     private double distinctEstimator(double estDistinctCardinalityFromSample, double origDatasetCardinality) {
         // initialize the estimate to be the number of distinct values from the sample.
@@ -858,4 +997,4 @@
         }
         return null; // no SelectOp in the query tree
     }
-}
\ No newline at end of file
+}
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/btree-index-join/secondary-equi-join_04.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/btree-index-join/secondary-equi-join_04.plan
new file mode 100644
index 0000000..df3edba
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/btree-index-join/secondary-equi-join_04.plan
@@ -0,0 +1,32 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+            -- HYBRID_HASH_JOIN [$$45][$$49]  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- STREAM_SELECT  |PARTITIONED|
+                  -- STREAM_PROJECT  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- BTREE_SEARCH (test.testdst3.testdst3)  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STABLE_SORT [$$52(ASC)]  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- BTREE_SEARCH (test.testdst3.sec3_Idx)  |PARTITIONED|
+                                    -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                      -- ASSIGN  |PARTITIONED|
+                                        -- STREAM_PROJECT  |PARTITIONED|
+                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                            -- DATASOURCE_SCAN (test.testdst2)  |PARTITIONED|
+                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+              -- BROADCAST_EXCHANGE  |PARTITIONED|
+                -- ASSIGN  |PARTITIONED|
+                  -- STREAM_PROJECT  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- DATASOURCE_SCAN (test.testdst)  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/btree-ternary-inlj/query4.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/btree-ternary-inlj/query4.plan
index 9b920b4..a374bc4 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/btree-ternary-inlj/query4.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/btree-ternary-inlj/query4.plan
@@ -6,20 +6,20 @@
           -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
             -- AGGREGATE  |PARTITIONED|
               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                -- BTREE_SEARCH (tpcds.customer_address.customer_address)  |PARTITIONED|
+                -- HYBRID_HASH_JOIN [$$61][$$74]  |PARTITIONED|
                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                    -- STABLE_SORT [$$78(ASC)]  |PARTITIONED|
-                      -- HASH_PARTITION_EXCHANGE [$$78]  |PARTITIONED|
-                        -- STREAM_PROJECT  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- BTREE_SEARCH (tpcds.customer_address.customer_address)  |PARTITIONED|
                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                            -- BTREE_SEARCH (tpcds.customer_demographics.customer_demographics)  |PARTITIONED|
-                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                -- STABLE_SORT [$$76(ASC)]  |PARTITIONED|
-                                  -- HASH_PARTITION_EXCHANGE [$$76]  |PARTITIONED|
-                                    -- STREAM_PROJECT  |PARTITIONED|
+                            -- STABLE_SORT [$$78(ASC)]  |PARTITIONED|
+                              -- HASH_PARTITION_EXCHANGE [$$78]  |PARTITIONED|
+                                -- STREAM_PROJECT  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- BTREE_SEARCH (tpcds.customer_demographics.customer_demographics)  |PARTITIONED|
                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                        -- HYBRID_HASH_JOIN [$$61][$$74]  |PARTITIONED|
-                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                        -- STABLE_SORT [$$76(ASC)]  |PARTITIONED|
+                                          -- HASH_PARTITION_EXCHANGE [$$76]  |PARTITIONED|
                                             -- STREAM_PROJECT  |PARTITIONED|
                                               -- ASSIGN  |PARTITIONED|
                                                 -- STREAM_PROJECT  |PARTITIONED|
@@ -27,6 +27,6 @@
                                                     -- DATASOURCE_SCAN (tpcds.customer)  |PARTITIONED|
                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                         -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                          -- BROADCAST_EXCHANGE  |PARTITIONED|
-                                            -- UNNEST  |UNPARTITIONED|
-                                              -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+                  -- BROADCAST_EXCHANGE  |PARTITIONED|
+                    -- UNNEST  |UNPARTITIONED|
+                      -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q10.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q10.plan
new file mode 100644
index 0000000..5e1de06
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q10.plan
@@ -0,0 +1,59 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_LIMIT  |UNPARTITIONED|
+      -- STREAM_PROJECT  |PARTITIONED|
+        -- ASSIGN  |PARTITIONED|
+          -- SORT_MERGE_EXCHANGE [$$175(DESC) ]  |PARTITIONED|
+            -- STREAM_LIMIT  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- STABLE_SORT [topK: 20] [$$175(DESC)]  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- SORT_GROUP_BY[$$182, $$183, $$184, $$185, $$186]  |PARTITIONED|
+                            {
+                              -- AGGREGATE  |LOCAL|
+                                -- NESTED_TUPLE_SOURCE  |LOCAL|
+                            }
+                      -- HASH_PARTITION_EXCHANGE [$$182, $$183, $$184, $$185, $$186]  |PARTITIONED|
+                        -- SORT_GROUP_BY[$$166, $$161, $$162, $$163, $$164]  |PARTITIONED|
+                                {
+                                  -- AGGREGATE  |LOCAL|
+                                    -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                }
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- STREAM_PROJECT  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- HYBRID_HASH_JOIN [$$166, $$171, $$173][$$170, $$172, $$174]  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- STREAM_PROJECT  |PARTITIONED|
+                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                        -- HYBRID_HASH_JOIN [$$176][$$177]  |PARTITIONED|
+                                          -- HASH_PARTITION_EXCHANGE [$$176]  |PARTITIONED|
+                                            -- STREAM_PROJECT  |PARTITIONED|
+                                              -- ASSIGN  |PARTITIONED|
+                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- DATASOURCE_SCAN (test.nation)  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                          -- HASH_PARTITION_EXCHANGE [$$177]  |PARTITIONED|
+                                            -- STREAM_PROJECT  |PARTITIONED|
+                                              -- ASSIGN  |PARTITIONED|
+                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- DATASOURCE_SCAN (test.customer)  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                  -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                    -- STREAM_PROJECT  |PARTITIONED|
+                                      -- ASSIGN  |PARTITIONED|
+                                        -- STREAM_PROJECT  |PARTITIONED|
+                                          -- UNNEST  |PARTITIONED|
+                                            -- STREAM_PROJECT  |PARTITIONED|
+                                              -- STREAM_SELECT  |PARTITIONED|
+                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                  -- ASSIGN  |PARTITIONED|
+                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
+                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q11.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q11.plan
new file mode 100644
index 0000000..ed6d2b0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q11.plan
@@ -0,0 +1,119 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$188(DESC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$188(DESC)]  |PARTITIONED|
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- NESTED_LOOP  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- SORT_GROUP_BY[$$214]  |PARTITIONED|
+                              {
+                                -- AGGREGATE  |LOCAL|
+                                  -- NESTED_TUPLE_SOURCE  |LOCAL|
+                              }
+                        -- HASH_PARTITION_EXCHANGE [$$214]  |PARTITIONED|
+                          -- SORT_GROUP_BY[$$178]  |PARTITIONED|
+                                  {
+                                    -- AGGREGATE  |LOCAL|
+                                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                  }
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- HYBRID_HASH_JOIN [$$186][$$185]  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- HYBRID_HASH_JOIN [$$198][$$197]  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                -- ASSIGN  |PARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- REPLICATE  |PARTITIONED|
+                                                      -- HASH_PARTITION_EXCHANGE [$$203]  |PARTITIONED|
+                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                          -- STREAM_SELECT  |PARTITIONED|
+                                                            -- ASSIGN  |PARTITIONED|
+                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                  -- DATASOURCE_SCAN (test.nation)  |PARTITIONED|
+                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                -- ASSIGN  |PARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- REPLICATE  |PARTITIONED|
+                                                      -- HASH_PARTITION_EXCHANGE [$$202]  |PARTITIONED|
+                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                          -- ASSIGN  |PARTITIONED|
+                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                -- DATASOURCE_SCAN (test.supplier)  |PARTITIONED|
+                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                    -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ASSIGN  |PARTITIONED|
+                                          -- ASSIGN  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- REPLICATE  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- DATASOURCE_SCAN (test.stock)  |PARTITIONED|
+                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                    -- BROADCAST_EXCHANGE  |PARTITIONED|
+                      -- STREAM_PROJECT  |UNPARTITIONED|
+                        -- ASSIGN  |UNPARTITIONED|
+                          -- AGGREGATE  |UNPARTITIONED|
+                            -- STREAM_PROJECT  |UNPARTITIONED|
+                              -- ASSIGN  |UNPARTITIONED|
+                                -- AGGREGATE  |UNPARTITIONED|
+                                  -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+                                    -- AGGREGATE  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- HYBRID_HASH_JOIN [$$201][$$200]  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- HYBRID_HASH_JOIN [$$203][$$202]  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- REPLICATE  |PARTITIONED|
+                                                        -- HASH_PARTITION_EXCHANGE [$$203]  |PARTITIONED|
+                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                            -- STREAM_SELECT  |PARTITIONED|
+                                                              -- ASSIGN  |PARTITIONED|
+                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                    -- DATASOURCE_SCAN (test.nation)  |PARTITIONED|
+                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- REPLICATE  |PARTITIONED|
+                                                        -- HASH_PARTITION_EXCHANGE [$$202]  |PARTITIONED|
+                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                            -- ASSIGN  |PARTITIONED|
+                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                  -- DATASOURCE_SCAN (test.supplier)  |PARTITIONED|
+                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                            -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                -- ASSIGN  |PARTITIONED|
+                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                    -- ASSIGN  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- REPLICATE  |PARTITIONED|
+                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                -- DATASOURCE_SCAN (test.stock)  |PARTITIONED|
+                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q16.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q16.plan
index 11ad1fa..715a293 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q16.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q16.plan
@@ -33,33 +33,33 @@
                                         -- HASH_PARTITION_EXCHANGE [$$133, $$134]  |PARTITIONED|
                                           -- STREAM_PROJECT  |PARTITIONED|
                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                              -- NESTED_LOOP  |PARTITIONED|
+                                              -- HYBRID_HASH_JOIN [$$138][$$137]  |PARTITIONED|
                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                   -- STREAM_PROJECT  |PARTITIONED|
                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                      -- HYBRID_HASH_JOIN [$$138][$$137]  |PARTITIONED|
-                                                        -- HASH_PARTITION_EXCHANGE [$$138]  |PARTITIONED|
+                                                      -- NESTED_LOOP  |PARTITIONED|
+                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                           -- STREAM_PROJECT  |PARTITIONED|
                                                             -- ASSIGN  |PARTITIONED|
                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                 -- DATASOURCE_SCAN (test.stock)  |PARTITIONED|
                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                     -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                        -- HASH_PARTITION_EXCHANGE [$$137]  |PARTITIONED|
-                                                          -- STREAM_SELECT  |PARTITIONED|
+                                                        -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                          -- ASSIGN  |PARTITIONED|
                                                             -- STREAM_PROJECT  |PARTITIONED|
-                                                              -- ASSIGN  |PARTITIONED|
-                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                  -- DATASOURCE_SCAN (test.item)  |PARTITIONED|
+                                                              -- STREAM_SELECT  |PARTITIONED|
+                                                                -- ASSIGN  |PARTITIONED|
+                                                                  -- STREAM_PROJECT  |PARTITIONED|
                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                      -- DATASOURCE_SCAN (test.supplier)  |PARTITIONED|
+                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                                                 -- BROADCAST_EXCHANGE  |PARTITIONED|
-                                                  -- ASSIGN  |PARTITIONED|
+                                                  -- STREAM_SELECT  |PARTITIONED|
                                                     -- STREAM_PROJECT  |PARTITIONED|
-                                                      -- STREAM_SELECT  |PARTITIONED|
-                                                        -- ASSIGN  |PARTITIONED|
-                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                      -- ASSIGN  |PARTITIONED|
+                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                          -- DATASOURCE_SCAN (test.item)  |PARTITIONED|
                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                              -- DATASOURCE_SCAN (test.supplier)  |PARTITIONED|
-                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q2.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q2.plan
index 6d36713..c1d568c 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q2.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q2.plan
@@ -45,11 +45,11 @@
                                                                         -- STREAM_PROJECT  |PARTITIONED|
                                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                             -- HYBRID_HASH_JOIN [$$255][$$256]  |PARTITIONED|
-                                                                              -- HASH_PARTITION_EXCHANGE [$$255]  |PARTITIONED|
+                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                 -- STREAM_PROJECT  |PARTITIONED|
                                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                     -- HYBRID_HASH_JOIN [$$257][$$269]  |PARTITIONED|
-                                                                                      -- HASH_PARTITION_EXCHANGE [$$257]  |PARTITIONED|
+                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                         -- STREAM_PROJECT  |PARTITIONED|
                                                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                             -- HYBRID_HASH_JOIN [$$261][$$270]  |PARTITIONED|
@@ -76,7 +76,7 @@
                                                                                                               -- DATASOURCE_SCAN (test.supplier)  |PARTITIONED|
                                                                                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                   -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                                      -- HASH_PARTITION_EXCHANGE [$$269]  |PARTITIONED|
+                                                                                      -- BROADCAST_EXCHANGE  |PARTITIONED|
                                                                                         -- STREAM_PROJECT  |PARTITIONED|
                                                                                           -- ASSIGN  |PARTITIONED|
                                                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -87,9 +87,9 @@
                                                                                                       -- DATASOURCE_SCAN (test.nation)  |PARTITIONED|
                                                                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                           -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                              -- BROADCAST_EXCHANGE  |PARTITIONED|
                                                                                 -- REPLICATE  |PARTITIONED|
-                                                                                  -- HASH_PARTITION_EXCHANGE [$$256]  |PARTITIONED|
+                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                     -- STREAM_PROJECT  |PARTITIONED|
                                                                                       -- STREAM_SELECT  |PARTITIONED|
                                                                                         -- ASSIGN  |PARTITIONED|
@@ -146,12 +146,12 @@
                                                       -- DATASOURCE_SCAN (test.nation)  |PARTITIONED|
                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                           -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- HASH_PARTITION_EXCHANGE [$$253]  |PARTITIONED|
                             -- STREAM_PROJECT  |PARTITIONED|
                               -- ASSIGN  |PARTITIONED|
                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                   -- REPLICATE  |PARTITIONED|
-                                    -- HASH_PARTITION_EXCHANGE [$$256]  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                       -- STREAM_PROJECT  |PARTITIONED|
                                         -- STREAM_SELECT  |PARTITIONED|
                                           -- ASSIGN  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q21.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q21.plan
index fa7aebe..78fb5a9 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q21.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q21.plan
@@ -42,11 +42,11 @@
                                                   -- STREAM_PROJECT  |PARTITIONED|
                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                       -- HYBRID_HASH_JOIN [$$351, $$350][$$352, $$362]  |PARTITIONED|
-                                                        -- HASH_PARTITION_EXCHANGE [$$351, $$350]  |PARTITIONED|
+                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                           -- STREAM_PROJECT  |PARTITIONED|
                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                               -- HYBRID_HASH_JOIN [$$355][$$366]  |PARTITIONED|
-                                                                -- HASH_PARTITION_EXCHANGE [$$355]  |PARTITIONED|
+                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                   -- STREAM_PROJECT  |PARTITIONED|
                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                       -- HYBRID_HASH_JOIN [$$354][$$365]  |PARTITIONED|
@@ -67,7 +67,7 @@
                                                                                   -- DATASOURCE_SCAN (test.supplier)  |PARTITIONED|
                                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                       -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                -- HASH_PARTITION_EXCHANGE [$$366]  |PARTITIONED|
+                                                                -- BROADCAST_EXCHANGE  |PARTITIONED|
                                                                   -- ASSIGN  |PARTITIONED|
                                                                     -- STREAM_PROJECT  |PARTITIONED|
                                                                       -- ASSIGN  |PARTITIONED|
@@ -76,7 +76,7 @@
                                                                             -- DATASOURCE_SCAN (test.stock)  |PARTITIONED|
                                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                 -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                        -- HASH_PARTITION_EXCHANGE [$$352, $$362]  |PARTITIONED|
+                                                        -- BROADCAST_EXCHANGE  |PARTITIONED|
                                                           -- STREAM_PROJECT  |PARTITIONED|
                                                             -- STREAM_SELECT  |PARTITIONED|
                                                               -- STREAM_PROJECT  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q5.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q5.plan
index 19bf0b1..e58b399 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q5.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q5.plan
@@ -23,21 +23,21 @@
                             -- STREAM_PROJECT  |PARTITIONED|
                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                 -- HYBRID_HASH_JOIN [$$274, $$290][$$266, $$269]  |PARTITIONED|
-                                  -- HASH_PARTITION_EXCHANGE [$$274, $$290]  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                     -- STREAM_PROJECT  |PARTITIONED|
                                       -- ASSIGN  |PARTITIONED|
                                         -- STREAM_PROJECT  |PARTITIONED|
                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                             -- HYBRID_HASH_JOIN [$$256][$$257]  |PARTITIONED|
-                                              -- HASH_PARTITION_EXCHANGE [$$256]  |PARTITIONED|
+                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                 -- STREAM_PROJECT  |PARTITIONED|
                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                     -- HYBRID_HASH_JOIN [$$258][$$274]  |PARTITIONED|
-                                                      -- HASH_PARTITION_EXCHANGE [$$258]  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                         -- STREAM_PROJECT  |PARTITIONED|
                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                             -- HYBRID_HASH_JOIN [$$260, $$254, $$263][$$276, $$277, $$278]  |PARTITIONED|
-                                                              -- HASH_PARTITION_EXCHANGE [$$260, $$254, $$263]  |PARTITIONED|
+                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                 -- STREAM_PROJECT  |PARTITIONED|
                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                     -- HYBRID_HASH_JOIN [$$245, $$246][$$254, $$281]  |PARTITIONED|
@@ -63,7 +63,7 @@
                                                                                             -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
                                                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                 -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                              -- HASH_PARTITION_EXCHANGE [$$276, $$277, $$278]  |PARTITIONED|
+                                                              -- BROADCAST_EXCHANGE  |PARTITIONED|
                                                                 -- STREAM_PROJECT  |PARTITIONED|
                                                                   -- ASSIGN  |PARTITIONED|
                                                                     -- STREAM_PROJECT  |PARTITIONED|
@@ -71,7 +71,7 @@
                                                                         -- DATASOURCE_SCAN (test.customer)  |PARTITIONED|
                                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                             -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                      -- HASH_PARTITION_EXCHANGE [$$274]  |PARTITIONED|
+                                                      -- BROADCAST_EXCHANGE  |PARTITIONED|
                                                         -- STREAM_PROJECT  |PARTITIONED|
                                                           -- ASSIGN  |PARTITIONED|
                                                             -- STREAM_PROJECT  |PARTITIONED|
@@ -79,7 +79,7 @@
                                                                 -- DATASOURCE_SCAN (test.nation)  |PARTITIONED|
                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                     -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                              -- HASH_PARTITION_EXCHANGE [$$257]  |PARTITIONED|
+                                              -- BROADCAST_EXCHANGE  |PARTITIONED|
                                                 -- STREAM_PROJECT  |PARTITIONED|
                                                   -- STREAM_SELECT  |PARTITIONED|
                                                     -- ASSIGN  |PARTITIONED|
@@ -88,7 +88,7 @@
                                                           -- DATASOURCE_SCAN (test.region)  |PARTITIONED|
                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                               -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                  -- HASH_PARTITION_EXCHANGE [$$266, $$269]  |PARTITIONED|
+                                  -- BROADCAST_EXCHANGE  |PARTITIONED|
                                     -- STREAM_PROJECT  |PARTITIONED|
                                       -- ASSIGN  |PARTITIONED|
                                         -- STREAM_PROJECT  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q7.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q7.plan
index 3a82fb7..cd27697 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q7.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q7.plan
@@ -18,87 +18,85 @@
                   -- STREAM_PROJECT  |PARTITIONED|
                     -- ASSIGN  |PARTITIONED|
                       -- STREAM_PROJECT  |PARTITIONED|
-                        -- STREAM_SELECT  |PARTITIONED|
-                          -- STREAM_PROJECT  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- HYBRID_HASH_JOIN [$$303, $$326][$$277, $$300]  |PARTITIONED|
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              -- HYBRID_HASH_JOIN [$$277][$$303]  |PARTITIONED|
-                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ASSIGN  |PARTITIONED|
                                   -- STREAM_PROJECT  |PARTITIONED|
                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                      -- HYBRID_HASH_JOIN [$$326][$$300]  |PARTITIONED|
-                                        -- HASH_PARTITION_EXCHANGE [$$326]  |PARTITIONED|
+                                      -- HYBRID_HASH_JOIN [$$310, $$311][$$275, $$276]  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                           -- STREAM_PROJECT  |PARTITIONED|
-                                            -- ASSIGN  |PARTITIONED|
-                                              -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- HYBRID_HASH_JOIN [$$305, $$306, $$307][$$291, $$293, $$295]  |PARTITIONED|
                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                  -- HYBRID_HASH_JOIN [$$288][$$304]  |PARTITIONED|
-                                                    -- HASH_PARTITION_EXCHANGE [$$288]  |PARTITIONED|
-                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- HYBRID_HASH_JOIN [$$304][$$288]  |PARTITIONED|
                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                          -- HYBRID_HASH_JOIN [$$291, $$293, $$295][$$305, $$306, $$307]  |PARTITIONED|
-                                                            -- HASH_PARTITION_EXCHANGE [$$291, $$293, $$295]  |PARTITIONED|
-                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                              -- NESTED_LOOP  |PARTITIONED|
                                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                  -- HYBRID_HASH_JOIN [$$275, $$276][$$310, $$311]  |PARTITIONED|
-                                                                    -- HASH_PARTITION_EXCHANGE [$$275, $$276]  |PARTITIONED|
+                                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                                    -- ASSIGN  |PARTITIONED|
+                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                        -- REPLICATE  |PARTITIONED|
+                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                                              -- ASSIGN  |PARTITIONED|
+                                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                    -- DATASOURCE_SCAN (test.nation)  |PARTITIONED|
+                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                                  -- REPLICATE  |PARTITIONED|
+                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                       -- STREAM_PROJECT  |PARTITIONED|
                                                                         -- ASSIGN  |PARTITIONED|
                                                                           -- STREAM_PROJECT  |PARTITIONED|
                                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                              -- DATASOURCE_SCAN (test.stock)  |PARTITIONED|
+                                                                              -- DATASOURCE_SCAN (test.nation)  |PARTITIONED|
                                                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                   -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                    -- HASH_PARTITION_EXCHANGE [$$310, $$311]  |PARTITIONED|
-                                                                      -- STREAM_PROJECT  |PARTITIONED|
-                                                                        -- STREAM_SELECT  |PARTITIONED|
-                                                                          -- STREAM_PROJECT  |PARTITIONED|
-                                                                            -- ASSIGN  |PARTITIONED|
-                                                                              -- STREAM_PROJECT  |PARTITIONED|
-                                                                                -- UNNEST  |PARTITIONED|
-                                                                                  -- STREAM_PROJECT  |PARTITIONED|
-                                                                                    -- ASSIGN  |PARTITIONED|
-                                                                                      -- STREAM_PROJECT  |PARTITIONED|
-                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                          -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
-                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                            -- HASH_PARTITION_EXCHANGE [$$305, $$306, $$307]  |PARTITIONED|
+                                                        -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                          -- ASSIGN  |PARTITIONED|
+                                                            -- STREAM_PROJECT  |PARTITIONED|
                                                               -- ASSIGN  |PARTITIONED|
                                                                 -- STREAM_PROJECT  |PARTITIONED|
-                                                                  -- ASSIGN  |PARTITIONED|
-                                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                    -- DATASOURCE_SCAN (test.customer)  |PARTITIONED|
                                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                        -- DATASOURCE_SCAN (test.customer)  |PARTITIONED|
-                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                    -- HASH_PARTITION_EXCHANGE [$$304]  |PARTITIONED|
-                                                      -- REPLICATE  |PARTITIONED|
-                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                    -- STREAM_SELECT  |PARTITIONED|
+                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                        -- ASSIGN  |PARTITIONED|
                                                           -- STREAM_PROJECT  |PARTITIONED|
-                                                            -- ASSIGN  |PARTITIONED|
+                                                            -- UNNEST  |PARTITIONED|
                                                               -- STREAM_PROJECT  |PARTITIONED|
-                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                  -- DATASOURCE_SCAN (test.nation)  |PARTITIONED|
+                                                                -- ASSIGN  |PARTITIONED|
+                                                                  -- STREAM_PROJECT  |PARTITIONED|
                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                        -- HASH_PARTITION_EXCHANGE [$$300]  |PARTITIONED|
+                                                                      -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
+                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                        -- BROADCAST_EXCHANGE  |PARTITIONED|
                                           -- STREAM_PROJECT  |PARTITIONED|
                                             -- ASSIGN  |PARTITIONED|
                                               -- STREAM_PROJECT  |PARTITIONED|
                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                  -- DATASOURCE_SCAN (test.supplier)  |PARTITIONED|
+                                                  -- DATASOURCE_SCAN (test.stock)  |PARTITIONED|
                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                       -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                -- BROADCAST_EXCHANGE  |PARTITIONED|
+                            -- BROADCAST_EXCHANGE  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ASSIGN  |PARTITIONED|
                                   -- STREAM_PROJECT  |PARTITIONED|
-                                    -- ASSIGN  |PARTITIONED|
-                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                        -- REPLICATE  |PARTITIONED|
-                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                            -- STREAM_PROJECT  |PARTITIONED|
-                                              -- ASSIGN  |PARTITIONED|
-                                                -- STREAM_PROJECT  |PARTITIONED|
-                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                    -- DATASOURCE_SCAN (test.nation)  |PARTITIONED|
-                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- DATASOURCE_SCAN (test.supplier)  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q8.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q8.plan
index cad6a92..e3ebbc7 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q8.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q8.plan
@@ -24,25 +24,25 @@
                               -- STREAM_PROJECT  |PARTITIONED|
                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                   -- HYBRID_HASH_JOIN [$$348][$$341]  |PARTITIONED|
-                                    -- HASH_PARTITION_EXCHANGE [$$348]  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                       -- STREAM_PROJECT  |PARTITIONED|
                                         -- ASSIGN  |PARTITIONED|
                                           -- STREAM_PROJECT  |PARTITIONED|
                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                               -- HYBRID_HASH_JOIN [$$308][$$309]  |PARTITIONED|
-                                                -- HASH_PARTITION_EXCHANGE [$$308]  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                   -- STREAM_PROJECT  |PARTITIONED|
                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                       -- HYBRID_HASH_JOIN [$$311][$$331]  |PARTITIONED|
-                                                        -- HASH_PARTITION_EXCHANGE [$$311]  |PARTITIONED|
+                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                           -- STREAM_PROJECT  |PARTITIONED|
                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                               -- HYBRID_HASH_JOIN [$$313, $$315, $$317][$$333, $$334, $$332]  |PARTITIONED|
-                                                                -- HASH_PARTITION_EXCHANGE [$$313, $$315, $$317]  |PARTITIONED|
+                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                   -- STREAM_PROJECT  |PARTITIONED|
                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                       -- HYBRID_HASH_JOIN [$$306][$$324]  |PARTITIONED|
-                                                                        -- HASH_PARTITION_EXCHANGE [$$306]  |PARTITIONED|
+                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                           -- STREAM_PROJECT  |PARTITIONED|
                                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                               -- HYBRID_HASH_JOIN [$$296, $$295][$$306, $$336]  |PARTITIONED|
@@ -68,7 +68,7 @@
                                                                                                       -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
                                                                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                           -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                        -- HASH_PARTITION_EXCHANGE [$$324]  |PARTITIONED|
+                                                                        -- BROADCAST_EXCHANGE  |PARTITIONED|
                                                                           -- STREAM_PROJECT  |PARTITIONED|
                                                                             -- STREAM_SELECT  |PARTITIONED|
                                                                               -- ASSIGN  |PARTITIONED|
@@ -77,7 +77,7 @@
                                                                                     -- DATASOURCE_SCAN (test.item)  |PARTITIONED|
                                                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                         -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                -- HASH_PARTITION_EXCHANGE [$$333, $$334, $$332]  |PARTITIONED|
+                                                                -- BROADCAST_EXCHANGE  |PARTITIONED|
                                                                   -- STREAM_PROJECT  |PARTITIONED|
                                                                     -- ASSIGN  |PARTITIONED|
                                                                       -- STREAM_PROJECT  |PARTITIONED|
@@ -85,7 +85,7 @@
                                                                           -- DATASOURCE_SCAN (test.customer)  |PARTITIONED|
                                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                               -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                        -- HASH_PARTITION_EXCHANGE [$$331]  |PARTITIONED|
+                                                        -- BROADCAST_EXCHANGE  |PARTITIONED|
                                                           -- STREAM_PROJECT  |PARTITIONED|
                                                             -- ASSIGN  |PARTITIONED|
                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -96,7 +96,7 @@
                                                                         -- DATASOURCE_SCAN (test.nation)  |PARTITIONED|
                                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                             -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                -- HASH_PARTITION_EXCHANGE [$$309]  |PARTITIONED|
+                                                -- BROADCAST_EXCHANGE  |PARTITIONED|
                                                   -- STREAM_PROJECT  |PARTITIONED|
                                                     -- STREAM_SELECT  |PARTITIONED|
                                                       -- ASSIGN  |PARTITIONED|
@@ -105,7 +105,7 @@
                                                             -- DATASOURCE_SCAN (test.region)  |PARTITIONED|
                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                 -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                    -- HASH_PARTITION_EXCHANGE [$$341]  |PARTITIONED|
+                                    -- BROADCAST_EXCHANGE  |PARTITIONED|
                                       -- STREAM_PROJECT  |PARTITIONED|
                                         -- ASSIGN  |PARTITIONED|
                                           -- STREAM_PROJECT  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q9.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q9.plan
index 12f531e..78777b0 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q9.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q9.plan
@@ -24,13 +24,13 @@
                               -- STREAM_PROJECT  |PARTITIONED|
                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                   -- HYBRID_HASH_JOIN [$$229][$$224]  |PARTITIONED|
-                                    -- HASH_PARTITION_EXCHANGE [$$229]  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                       -- STREAM_PROJECT  |PARTITIONED|
                                         -- ASSIGN  |PARTITIONED|
                                           -- STREAM_PROJECT  |PARTITIONED|
                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                               -- HYBRID_HASH_JOIN [$$206][$$207]  |PARTITIONED|
-                                                -- HASH_PARTITION_EXCHANGE [$$206]  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                   -- STREAM_PROJECT  |PARTITIONED|
                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                       -- HYBRID_HASH_JOIN [$$200, $$199][$$206, $$217]  |PARTITIONED|
@@ -54,7 +54,7 @@
                                                                           -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
                                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                               -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                -- HASH_PARTITION_EXCHANGE [$$207]  |PARTITIONED|
+                                                -- BROADCAST_EXCHANGE  |PARTITIONED|
                                                   -- STREAM_PROJECT  |PARTITIONED|
                                                     -- STREAM_SELECT  |PARTITIONED|
                                                       -- ASSIGN  |PARTITIONED|
@@ -63,7 +63,7 @@
                                                             -- DATASOURCE_SCAN (test.item)  |PARTITIONED|
                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                 -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                    -- HASH_PARTITION_EXCHANGE [$$224]  |PARTITIONED|
+                                    -- BROADCAST_EXCHANGE  |PARTITIONED|
                                       -- STREAM_PROJECT  |PARTITIONED|
                                         -- ASSIGN  |PARTITIONED|
                                           -- STREAM_PROJECT  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/column-pushdown/meta.001.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/column-pushdown/meta.001.plan
new file mode 100644
index 0000000..d90d07b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/column-pushdown/meta.001.plan
@@ -0,0 +1,119 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$194(DESC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$194(DESC)]  |PARTITIONED|
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- NESTED_LOOP  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- SORT_GROUP_BY[$$223]  |PARTITIONED|
+                              {
+                                -- AGGREGATE  |LOCAL|
+                                  -- NESTED_TUPLE_SOURCE  |LOCAL|
+                              }
+                        -- HASH_PARTITION_EXCHANGE [$$223]  |PARTITIONED|
+                          -- SORT_GROUP_BY[$$178]  |PARTITIONED|
+                                  {
+                                    -- AGGREGATE  |LOCAL|
+                                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                  }
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- HYBRID_HASH_JOIN [$$192][$$191]  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- HYBRID_HASH_JOIN [$$207][$$206]  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                -- ASSIGN  |PARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- REPLICATE  |PARTITIONED|
+                                                      -- HASH_PARTITION_EXCHANGE [$$212]  |PARTITIONED|
+                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                          -- STREAM_SELECT  |PARTITIONED|
+                                                            -- ASSIGN  |PARTITIONED|
+                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                  -- DATASOURCE_SCAN (test.nation)  |PARTITIONED|
+                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                -- ASSIGN  |PARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- REPLICATE  |PARTITIONED|
+                                                      -- HASH_PARTITION_EXCHANGE [$$211]  |PARTITIONED|
+                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                          -- ASSIGN  |PARTITIONED|
+                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                -- DATASOURCE_SCAN (test.supplier)  |PARTITIONED|
+                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                    -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ASSIGN  |PARTITIONED|
+                                          -- ASSIGN  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- REPLICATE  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- DATASOURCE_SCAN (test.stock)  |PARTITIONED|
+                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                    -- BROADCAST_EXCHANGE  |PARTITIONED|
+                      -- STREAM_PROJECT  |UNPARTITIONED|
+                        -- ASSIGN  |UNPARTITIONED|
+                          -- AGGREGATE  |UNPARTITIONED|
+                            -- STREAM_PROJECT  |UNPARTITIONED|
+                              -- ASSIGN  |UNPARTITIONED|
+                                -- AGGREGATE  |UNPARTITIONED|
+                                  -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+                                    -- AGGREGATE  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- HYBRID_HASH_JOIN [$$210][$$209]  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- HYBRID_HASH_JOIN [$$212][$$211]  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- REPLICATE  |PARTITIONED|
+                                                        -- HASH_PARTITION_EXCHANGE [$$212]  |PARTITIONED|
+                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                            -- STREAM_SELECT  |PARTITIONED|
+                                                              -- ASSIGN  |PARTITIONED|
+                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                    -- DATASOURCE_SCAN (test.nation)  |PARTITIONED|
+                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- REPLICATE  |PARTITIONED|
+                                                        -- HASH_PARTITION_EXCHANGE [$$211]  |PARTITIONED|
+                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                            -- ASSIGN  |PARTITIONED|
+                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                  -- DATASOURCE_SCAN (test.supplier)  |PARTITIONED|
+                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                            -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                -- ASSIGN  |PARTITIONED|
+                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                    -- ASSIGN  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- REPLICATE  |PARTITIONED|
+                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                -- DATASOURCE_SCAN (test.stock)  |PARTITIONED|
+                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/extract-common-operators/extract-common-operators.01.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/extract-common-operators/extract-common-operators.01.plan
index c66d648..ae07a55 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/extract-common-operators/extract-common-operators.01.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/extract-common-operators/extract-common-operators.01.plan
@@ -13,76 +13,85 @@
                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                           -- STREAM_PROJECT  |PARTITIONED|
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              -- HYBRID_HASH_JOIN [$$408][$$ds_name]  |PARTITIONED|
-                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                  -- HYBRID_HASH_JOIN [$$408][$$414]  |PARTITIONED|
-                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- HYBRID_HASH_JOIN [$$414][$$408]  |PARTITIONED|
+                                -- HASH_PARTITION_EXCHANGE [$$414]  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ASSIGN  |PARTITIONED|
                                       -- STREAM_PROJECT  |PARTITIONED|
                                         -- ASSIGN  |PARTITIONED|
                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                             -- REPLICATE  |PARTITIONED|
-                                              -- HASH_PARTITION_EXCHANGE [$$412]  |PARTITIONED|
-                                                -- STREAM_PROJECT  |PARTITIONED|
+                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                -- DATASOURCE_SCAN (Metadata.Synonym)  |PARTITIONED|
                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                    -- BTREE_SEARCH (Metadata.Dataset.Dataset)  |PARTITIONED|
-                                                      -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                -- HASH_PARTITION_EXCHANGE [$$408]  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- HYBRID_HASH_JOIN [$$408, $$407][$$ds_name, $$dv_name]  |PARTITIONED|
+                                        -- RANDOM_PARTITION_EXCHANGE  |PARTITIONED|
+                                          -- REPLICATE  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- DATASOURCE_SCAN (Metadata.Dataset)  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                        -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                          -- NESTED_LOOP  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+                                              -- STREAM_PROJECT  |UNPARTITIONED|
+                                                -- ASSIGN  |UNPARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+                                                    -- REPLICATE  |UNPARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
                                                         -- UNNEST  |UNPARTITIONED|
                                                           -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
-                                    -- HASH_PARTITION_EXCHANGE [$$414]  |PARTITIONED|
-                                      -- STREAM_PROJECT  |PARTITIONED|
-                                        -- ASSIGN  |PARTITIONED|
-                                          -- STREAM_PROJECT  |PARTITIONED|
-                                            -- ASSIGN  |PARTITIONED|
-                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                -- REPLICATE  |PARTITIONED|
-                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                    -- DATASOURCE_SCAN (Metadata.Synonym)  |PARTITIONED|
-                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                  -- REPLICATE  |PARTITIONED|
-                                    -- BROADCAST_EXCHANGE  |PARTITIONED|
-                                      -- UNNEST  |UNPARTITIONED|
-                                        -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+                                              -- UNNEST  |UNPARTITIONED|
+                                                -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
                     -- HASH_PARTITION_EXCHANGE [$$410]  |PARTITIONED|
                       -- STREAM_PROJECT  |PARTITIONED|
                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                          -- HYBRID_HASH_JOIN [$$412][$$ds_name]  |PARTITIONED|
+                          -- HYBRID_HASH_JOIN [$$428, $$ds_name, $$dv_name][$$412, $$443, $$411]  |PARTITIONED|
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                               -- STREAM_PROJECT  |PARTITIONED|
                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                   -- HYBRID_HASH_JOIN [$$410][$$syn_name]  |PARTITIONED|
-                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- RANDOM_PARTITION_EXCHANGE  |PARTITIONED|
                                       -- STREAM_PROJECT  |PARTITIONED|
-                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                          -- HYBRID_HASH_JOIN [$$412][$$428]  |PARTITIONED|
+                                        -- ASSIGN  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                               -- REPLICATE  |PARTITIONED|
-                                                -- HASH_PARTITION_EXCHANGE [$$412]  |PARTITIONED|
-                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- DATASOURCE_SCAN (Metadata.Synonym)  |PARTITIONED|
                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                      -- BTREE_SEARCH (Metadata.Dataset.Dataset)  |PARTITIONED|
-                                                        -- BROADCAST_EXCHANGE  |PARTITIONED|
-                                                          -- UNNEST  |UNPARTITIONED|
-                                                            -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
-                                            -- HASH_PARTITION_EXCHANGE [$$428]  |PARTITIONED|
-                                              -- STREAM_PROJECT  |PARTITIONED|
-                                                -- ASSIGN  |PARTITIONED|
-                                                  -- STREAM_PROJECT  |PARTITIONED|
-                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                      -- REPLICATE  |PARTITIONED|
-                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                          -- DATASOURCE_SCAN (Metadata.Synonym)  |PARTITIONED|
-                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                                     -- BROADCAST_EXCHANGE  |PARTITIONED|
-                                      -- UNNEST  |UNPARTITIONED|
-                                        -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
-                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              -- STREAM_PROJECT  |PARTITIONED|
-                                -- ASSIGN  |PARTITIONED|
-                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                    -- REPLICATE  |PARTITIONED|
-                                      -- BROADCAST_EXCHANGE  |PARTITIONED|
-                                        -- UNNEST  |UNPARTITIONED|
-                                          -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+                                      -- NESTED_LOOP  |PARTITIONED|
+                                        -- RANDOM_PARTITION_EXCHANGE  |PARTITIONED|
+                                          -- NESTED_LOOP  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+                                              -- UNNEST  |UNPARTITIONED|
+                                                -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+                                              -- REPLICATE  |UNPARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+                                                  -- UNNEST  |UNPARTITIONED|
+                                                    -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+                                        -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                          -- UNNEST  |UNPARTITIONED|
+                                            -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+                            -- BROADCAST_EXCHANGE  |PARTITIONED|
+                              -- ASSIGN  |PARTITIONED|
+                                -- STREAM_PROJECT  |PARTITIONED|
+                                  -- ASSIGN  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- REPLICATE  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- DATASOURCE_SCAN (Metadata.Dataset)  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/hash-join-with-redundant-variable/hash-join-with-redundant-variable.1.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/hash-join-with-redundant-variable/hash-join-with-redundant-variable.1.plan
index 3a7bf12..ce3065b 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/hash-join-with-redundant-variable/hash-join-with-redundant-variable.1.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/hash-join-with-redundant-variable/hash-join-with-redundant-variable.1.plan
@@ -4,71 +4,71 @@
       -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
         -- AGGREGATE  |PARTITIONED|
           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-            -- HYBRID_HASH_JOIN [$$137][$$136]  |PARTITIONED|
+            -- HYBRID_HASH_JOIN [$$134, $$124][$$135, $$123]  |PARTITIONED|
               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                 -- STREAM_PROJECT  |PARTITIONED|
-                  -- STREAM_SELECT  |PARTITIONED|
-                    -- ASSIGN  |PARTITIONED|
-                      -- STREAM_PROJECT  |PARTITIONED|
-                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                          -- DATASOURCE_SCAN (test.region)  |PARTITIONED|
-                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-              -- BROADCAST_EXCHANGE  |PARTITIONED|
-                -- STREAM_PROJECT  |PARTITIONED|
                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                    -- HYBRID_HASH_JOIN [$$124][$$123]  |PARTITIONED|
+                    -- HYBRID_HASH_JOIN [$$139][$$138]  |PARTITIONED|
                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                         -- STREAM_PROJECT  |PARTITIONED|
-                          -- ASSIGN  |PARTITIONED|
-                            -- STREAM_PROJECT  |PARTITIONED|
-                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                -- DATASOURCE_SCAN (test.nation)  |PARTITIONED|
-                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                      -- BROADCAST_EXCHANGE  |PARTITIONED|
-                        -- STREAM_PROJECT  |PARTITIONED|
                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                            -- HYBRID_HASH_JOIN [$$134, $$122][$$135, $$123]  |PARTITIONED|
-                              -- HASH_PARTITION_EXCHANGE [$$134, $$122]  |PARTITIONED|
+                            -- HYBRID_HASH_JOIN [$$132][$$133]  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                 -- STREAM_PROJECT  |PARTITIONED|
                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                    -- HYBRID_HASH_JOIN [$$139][$$138]  |PARTITIONED|
-                                      -- HASH_PARTITION_EXCHANGE [$$139]  |PARTITIONED|
+                                    -- HYBRID_HASH_JOIN [$$124][$$122]  |PARTITIONED|
+                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                         -- STREAM_PROJECT  |PARTITIONED|
                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                            -- HYBRID_HASH_JOIN [$$132][$$133]  |PARTITIONED|
-                                              -- HASH_PARTITION_EXCHANGE [$$132]  |PARTITIONED|
+                                            -- HYBRID_HASH_JOIN [$$137][$$136]  |PARTITIONED|
+                                              -- HASH_PARTITION_EXCHANGE [$$137]  |PARTITIONED|
+                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                  -- STREAM_SELECT  |PARTITIONED|
+                                                    -- ASSIGN  |PARTITIONED|
+                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                          -- DATASOURCE_SCAN (test.region)  |PARTITIONED|
+                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                              -- HASH_PARTITION_EXCHANGE [$$136]  |PARTITIONED|
                                                 -- STREAM_PROJECT  |PARTITIONED|
                                                   -- ASSIGN  |PARTITIONED|
                                                     -- STREAM_PROJECT  |PARTITIONED|
                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                        -- DATASOURCE_SCAN (test.customer)  |PARTITIONED|
+                                                        -- DATASOURCE_SCAN (test.nation)  |PARTITIONED|
                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                             -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                              -- HASH_PARTITION_EXCHANGE [$$133]  |PARTITIONED|
-                                                -- STREAM_PROJECT  |PARTITIONED|
-                                                  -- STREAM_SELECT  |PARTITIONED|
-                                                    -- STREAM_PROJECT  |PARTITIONED|
-                                                      -- ASSIGN  |PARTITIONED|
-                                                        -- STREAM_PROJECT  |PARTITIONED|
-                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                            -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
-                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                      -- HASH_PARTITION_EXCHANGE [$$138]  |PARTITIONED|
+                                      -- BROADCAST_EXCHANGE  |PARTITIONED|
                                         -- STREAM_PROJECT  |PARTITIONED|
                                           -- ASSIGN  |PARTITIONED|
                                             -- STREAM_PROJECT  |PARTITIONED|
                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                -- DATASOURCE_SCAN (test.lineitem)  |PARTITIONED|
+                                                -- DATASOURCE_SCAN (test.customer)  |PARTITIONED|
                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                     -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                              -- HASH_PARTITION_EXCHANGE [$$135, $$123]  |PARTITIONED|
+                              -- BROADCAST_EXCHANGE  |PARTITIONED|
                                 -- STREAM_PROJECT  |PARTITIONED|
-                                  -- ASSIGN  |PARTITIONED|
+                                  -- STREAM_SELECT  |PARTITIONED|
                                     -- STREAM_PROJECT  |PARTITIONED|
-                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                        -- DATASOURCE_SCAN (test.supplier)  |PARTITIONED|
+                                      -- ASSIGN  |PARTITIONED|
+                                        -- STREAM_PROJECT  |PARTITIONED|
                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                            -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
+                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                      -- BROADCAST_EXCHANGE  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ASSIGN  |PARTITIONED|
+                            -- STREAM_PROJECT  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- DATASOURCE_SCAN (test.lineitem)  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+              -- BROADCAST_EXCHANGE  |PARTITIONED|
+                -- STREAM_PROJECT  |PARTITIONED|
+                  -- ASSIGN  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- DATASOURCE_SCAN (test.supplier)  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/hash-join-with-redundant-variable/hash-join-with-redundant-variable.2.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/hash-join-with-redundant-variable/hash-join-with-redundant-variable.2.plan
new file mode 100644
index 0000000..cdd3368
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/hash-join-with-redundant-variable/hash-join-with-redundant-variable.2.plan
@@ -0,0 +1,35 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- AGGREGATE  |UNPARTITIONED|
+      -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+        -- AGGREGATE  |PARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+            -- HYBRID_HASH_JOIN [$$69][$$67]  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- STREAM_PROJECT  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- HYBRID_HASH_JOIN [$$69][$$68]  |PARTITIONED|
+                      -- HASH_PARTITION_EXCHANGE [$$69]  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ASSIGN  |PARTITIONED|
+                            -- STREAM_PROJECT  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- DATASOURCE_SCAN (test.region)  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                      -- HASH_PARTITION_EXCHANGE [$$68]  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ASSIGN  |PARTITIONED|
+                            -- STREAM_PROJECT  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+              -- BROADCAST_EXCHANGE  |PARTITIONED|
+                -- STREAM_PROJECT  |PARTITIONED|
+                  -- ASSIGN  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- DATASOURCE_SCAN (test.nation)  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/hash-join-with-redundant-variable/hash-join-with-redundant-variable.3.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/hash-join-with-redundant-variable/hash-join-with-redundant-variable.3.plan
new file mode 100644
index 0000000..686ad89
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/hash-join-with-redundant-variable/hash-join-with-redundant-variable.3.plan
@@ -0,0 +1,35 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- AGGREGATE  |UNPARTITIONED|
+      -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+        -- AGGREGATE  |PARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+            -- HYBRID_HASH_JOIN [$$72][$$70]  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- STREAM_PROJECT  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- HYBRID_HASH_JOIN [$$71][$$72]  |PARTITIONED|
+                      -- HASH_PARTITION_EXCHANGE [$$71]  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ASSIGN  |PARTITIONED|
+                            -- STREAM_PROJECT  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- DATASOURCE_SCAN (test.region)  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                      -- HASH_PARTITION_EXCHANGE [$$72]  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ASSIGN  |PARTITIONED|
+                            -- STREAM_PROJECT  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- DATASOURCE_SCAN (test.nation)  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+              -- BROADCAST_EXCHANGE  |PARTITIONED|
+                -- STREAM_PROJECT  |PARTITIONED|
+                  -- ASSIGN  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/insert-and-scan-dataset.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/insert-and-scan-dataset.plan
new file mode 100644
index 0000000..f556169
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/insert-and-scan-dataset.plan
@@ -0,0 +1,15 @@
+-- COMMIT  |PARTITIONED|
+  -- STREAM_PROJECT  |PARTITIONED|
+    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+      -- INSERT_DELETE  |PARTITIONED|
+        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+          -- MATERIALIZE  |PARTITIONED|
+            -- HASH_PARTITION_EXCHANGE [$$17]  |PARTITIONED|
+              -- ASSIGN  |PARTITIONED|
+                -- STREAM_PROJECT  |PARTITIONED|
+                  -- ASSIGN  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- DATASOURCE_SCAN (test.myData)  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/joins/inner_right_corr.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/joins/inner_right_corr.plan
new file mode 100644
index 0000000..8327ffc
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/joins/inner_right_corr.plan
@@ -0,0 +1,55 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+            -- PRE_CLUSTERED_GROUP_BY[$$76]  |PARTITIONED|
+                    {
+                      -- AGGREGATE  |LOCAL|
+                        -- AGGREGATE  |LOCAL|
+                          -- STREAM_SELECT  |LOCAL|
+                            -- NESTED_TUPLE_SOURCE  |LOCAL|
+                    }
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- STABLE_SORT [$$76(ASC)]  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- HYBRID_HASH_JOIN [$$76][$$92]  |PARTITIONED|
+                          -- HASH_PARTITION_EXCHANGE [$$76]  |PARTITIONED|
+                            -- STREAM_PROJECT  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- DATASOURCE_SCAN (test.t1)  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                          -- HASH_PARTITION_EXCHANGE [$$92]  |PARTITIONED|
+                            -- ASSIGN  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- UNNEST  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- SUBPLAN  |PARTITIONED|
+                                            {
+                                              -- AGGREGATE  |LOCAL|
+                                                -- STREAM_SELECT  |LOCAL|
+                                                  -- ASSIGN  |LOCAL|
+                                                    -- UNNEST  |LOCAL|
+                                                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                            }
+                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                        -- NESTED_LOOP  |PARTITIONED|
+                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                            -- STREAM_PROJECT  |PARTITIONED|
+                                              -- ASSIGN  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- DATASOURCE_SCAN (test.t1)  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                          -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                            -- STREAM_PROJECT  |PARTITIONED|
+                                              -- ASSIGN  |PARTITIONED|
+                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- DATASOURCE_SCAN (test.t2)  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/joins/nlj_partitioning_property_1.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/joins/nlj_partitioning_property_1.plan
index 91a6aca..5f3c681 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/joins/nlj_partitioning_property_1.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/joins/nlj_partitioning_property_1.plan
@@ -6,26 +6,24 @@
           -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
             -- AGGREGATE  |PARTITIONED|
               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                -- NESTED_LOOP  |PARTITIONED|
+                -- HYBRID_HASH_JOIN [$$76][$$78]  |PARTITIONED|
                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                    -- STREAM_PROJECT  |PARTITIONED|
+                    -- NESTED_LOOP  |PARTITIONED|
                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                        -- HYBRID_HASH_JOIN [$$76][$$78]  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                            -- STREAM_PROJECT  |PARTITIONED|
+                            -- DATASOURCE_SCAN (tpch.Supplier)  |PARTITIONED|
                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                -- DATASOURCE_SCAN (tpch.Supplier)  |PARTITIONED|
-                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                          -- BROADCAST_EXCHANGE  |PARTITIONED|
-                            -- STREAM_PROJECT  |PARTITIONED|
+                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                      -- BROADCAST_EXCHANGE  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- DATASOURCE_SCAN (tpch.Part)  |PARTITIONED|
                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                -- DATASOURCE_SCAN (tpch.Partsupp)  |PARTITIONED|
-                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                   -- BROADCAST_EXCHANGE  |PARTITIONED|
                     -- STREAM_PROJECT  |PARTITIONED|
                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                        -- DATASOURCE_SCAN (tpch.Part)  |PARTITIONED|
+                        -- DATASOURCE_SCAN (tpch.Partsupp)  |PARTITIONED|
                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                             -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/leftouterjoin/loj-03-no-listify.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/leftouterjoin/loj-03-no-listify.plan
new file mode 100644
index 0000000..e57acc8
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/leftouterjoin/loj-03-no-listify.plan
@@ -0,0 +1,127 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$taskId(ASC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$taskId(ASC)]  |PARTITIONED|
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- HYBRID_HASH_JOIN [$$taskId][$$taskId]  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STREAM_PROJECT  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- HYBRID_HASH_JOIN [$$taskId][$$taskId]  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- HYBRID_HASH_JOIN [$$taskId][$$taskId]  |PARTITIONED|
+                                    -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$taskId(ASC)] HASH:[$$taskId]  |PARTITIONED|
+                                      -- SORT_GROUP_BY[$$295]  |PARTITIONED|
+                                              {
+                                                -- AGGREGATE  |LOCAL|
+                                                  -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                              }
+                                        -- HASH_PARTITION_EXCHANGE [$$295]  |PARTITIONED|
+                                          -- SORT_GROUP_BY[$$258]  |PARTITIONED|
+                                                  {
+                                                    -- AGGREGATE  |LOCAL|
+                                                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                  }
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                -- ASSIGN  |PARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- REPLICATE  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- ASSIGN  |PARTITIONED|
+                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                              -- DATASOURCE_SCAN (test.tasks)  |PARTITIONED|
+                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ASSIGN  |PARTITIONED|
+                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                            -- SORT_GROUP_BY[$$297]  |PARTITIONED|
+                                                    {
+                                                      -- AGGREGATE  |LOCAL|
+                                                        -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                    }
+                                              -- HASH_PARTITION_EXCHANGE [$$297]  |PARTITIONED|
+                                                -- SORT_GROUP_BY[$$259]  |PARTITIONED|
+                                                        {
+                                                          -- AGGREGATE  |LOCAL|
+                                                            -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                        }
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                      -- STREAM_SELECT  |PARTITIONED|
+                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                          -- REPLICATE  |PARTITIONED|
+                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                              -- ASSIGN  |PARTITIONED|
+                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                    -- DATASOURCE_SCAN (test.tasks)  |PARTITIONED|
+                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                            -- BROADCAST_EXCHANGE  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ASSIGN  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- SORT_GROUP_BY[$$299]  |PARTITIONED|
+                                            {
+                                              -- AGGREGATE  |LOCAL|
+                                                -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                            }
+                                      -- HASH_PARTITION_EXCHANGE [$$299]  |PARTITIONED|
+                                        -- SORT_GROUP_BY[$$260]  |PARTITIONED|
+                                                {
+                                                  -- AGGREGATE  |LOCAL|
+                                                    -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                }
+                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                            -- STREAM_PROJECT  |PARTITIONED|
+                                              -- STREAM_SELECT  |PARTITIONED|
+                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                  -- ASSIGN  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- REPLICATE  |PARTITIONED|
+                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                          -- ASSIGN  |PARTITIONED|
+                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                -- DATASOURCE_SCAN (test.tasks)  |PARTITIONED|
+                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                    -- BROADCAST_EXCHANGE  |PARTITIONED|
+                      -- STREAM_PROJECT  |PARTITIONED|
+                        -- ASSIGN  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- SORT_GROUP_BY[$$301]  |PARTITIONED|
+                                    {
+                                      -- AGGREGATE  |LOCAL|
+                                        -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                    }
+                              -- HASH_PARTITION_EXCHANGE [$$301]  |PARTITIONED|
+                                -- SORT_GROUP_BY[$$261]  |PARTITIONED|
+                                        {
+                                          -- AGGREGATE  |LOCAL|
+                                            -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                        }
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- STREAM_PROJECT  |PARTITIONED|
+                                      -- STREAM_SELECT  |PARTITIONED|
+                                        -- STREAM_PROJECT  |PARTITIONED|
+                                          -- ASSIGN  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- REPLICATE  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- ASSIGN  |PARTITIONED|
+                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- DATASOURCE_SCAN (test.tasks)  |PARTITIONED|
+                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/nested_loj2.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/nested_loj2.plan
new file mode 100644
index 0000000..15b8d6c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/nested_loj2.plan
@@ -0,0 +1,43 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+            -- PRE_CLUSTERED_GROUP_BY[$$71]  |PARTITIONED|
+                    {
+                      -- AGGREGATE  |LOCAL|
+                        -- MICRO_PRE_CLUSTERED_GROUP_BY[$$72]  |LOCAL|
+                                {
+                                  -- AGGREGATE  |LOCAL|
+                                    -- STREAM_SELECT  |LOCAL|
+                                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                }
+                          -- STREAM_SELECT  |LOCAL|
+                            -- NESTED_TUPLE_SOURCE  |LOCAL|
+                    }
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- STABLE_SORT [$$71(ASC), $$72(ASC)]  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- HYBRID_HASH_JOIN [$$72][$$73]  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- HYBRID_HASH_JOIN [$$71][$$75]  |PARTITIONED|
+                              -- HASH_PARTITION_EXCHANGE [$$71]  |PARTITIONED|
+                                -- DATASOURCE_SCAN (tpch.Customers)  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                              -- HASH_PARTITION_EXCHANGE [$$75]  |PARTITIONED|
+                                -- ASSIGN  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- DATASOURCE_SCAN (tpch.Orders)  |PARTITIONED|
+                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                          -- BROADCAST_EXCHANGE  |PARTITIONED|
+                            -- ASSIGN  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- DATASOURCE_SCAN (tpch.LineItems)  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/q08_group_by.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/q08_group_by.plan
index 2b4df2f..21c0576 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/q08_group_by.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/q08_group_by.plan
@@ -5,23 +5,23 @@
         -- STREAM_PROJECT  |PARTITIONED|
           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
             -- HYBRID_HASH_JOIN [$$230][$$212]  |PARTITIONED|
-              -- HASH_PARTITION_EXCHANGE [$$230]  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                 -- STREAM_PROJECT  |PARTITIONED|
                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                     -- HYBRID_HASH_JOIN [$$216][$$211]  |PARTITIONED|
-                      -- HASH_PARTITION_EXCHANGE [$$216]  |PARTITIONED|
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                         -- STREAM_PROJECT  |PARTITIONED|
                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                             -- HYBRID_HASH_JOIN [$$218][$$210]  |PARTITIONED|
-                              -- HASH_PARTITION_EXCHANGE [$$218]  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                 -- STREAM_PROJECT  |PARTITIONED|
                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                     -- HYBRID_HASH_JOIN [$$221][$$209]  |PARTITIONED|
-                                      -- HASH_PARTITION_EXCHANGE [$$221]  |PARTITIONED|
+                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                         -- STREAM_PROJECT  |PARTITIONED|
                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                             -- HYBRID_HASH_JOIN [$$206][$$208]  |PARTITIONED|
-                                              -- HASH_PARTITION_EXCHANGE [$$206]  |PARTITIONED|
+                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                 -- STREAM_PROJECT  |PARTITIONED|
                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                     -- HYBRID_HASH_JOIN [$$205][$$233]  |PARTITIONED|
@@ -40,7 +40,7 @@
                                                                 -- DATASOURCE_SCAN (q08_group_by.LineItem)  |PARTITIONED|
                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                     -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                              -- HASH_PARTITION_EXCHANGE [$$208]  |PARTITIONED|
+                                              -- BROADCAST_EXCHANGE  |PARTITIONED|
                                                 -- STREAM_SELECT  |PARTITIONED|
                                                   -- STREAM_PROJECT  |PARTITIONED|
                                                     -- ASSIGN  |PARTITIONED|
@@ -48,28 +48,28 @@
                                                         -- DATASOURCE_SCAN (q08_group_by.Orders)  |PARTITIONED|
                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                             -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                      -- HASH_PARTITION_EXCHANGE [$$209]  |PARTITIONED|
+                                      -- BROADCAST_EXCHANGE  |PARTITIONED|
                                         -- STREAM_PROJECT  |PARTITIONED|
                                           -- ASSIGN  |PARTITIONED|
                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                               -- DATASOURCE_SCAN (q08_group_by.Customer)  |PARTITIONED|
                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                   -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                              -- HASH_PARTITION_EXCHANGE [$$210]  |PARTITIONED|
+                              -- BROADCAST_EXCHANGE  |PARTITIONED|
                                 -- STREAM_PROJECT  |PARTITIONED|
                                   -- ASSIGN  |PARTITIONED|
                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                       -- DATASOURCE_SCAN (q08_group_by.Nation)  |PARTITIONED|
                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                           -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                      -- HASH_PARTITION_EXCHANGE [$$211]  |PARTITIONED|
+                      -- BROADCAST_EXCHANGE  |PARTITIONED|
                         -- STREAM_PROJECT  |PARTITIONED|
                           -- STREAM_SELECT  |PARTITIONED|
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                               -- DATASOURCE_SCAN (q08_group_by.Region)  |PARTITIONED|
                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                   -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-              -- HASH_PARTITION_EXCHANGE [$$212]  |PARTITIONED|
+              -- BROADCAST_EXCHANGE  |PARTITIONED|
                 -- STREAM_PROJECT  |PARTITIONED|
                   -- STREAM_SELECT  |PARTITIONED|
                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/q09_group_by.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/q09_group_by.plan
index ec25577..f258777 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/q09_group_by.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/q09_group_by.plan
@@ -5,15 +5,15 @@
         -- STREAM_PROJECT  |PARTITIONED|
           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
             -- HYBRID_HASH_JOIN [$$157][$$151]  |PARTITIONED|
-              -- HASH_PARTITION_EXCHANGE [$$157]  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                 -- STREAM_PROJECT  |PARTITIONED|
                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                     -- HYBRID_HASH_JOIN [$$154][$$150]  |PARTITIONED|
-                      -- HASH_PARTITION_EXCHANGE [$$154]  |PARTITIONED|
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                         -- STREAM_PROJECT  |PARTITIONED|
                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                             -- HYBRID_HASH_JOIN [$$169, $$154][$$148, $$149]  |PARTITIONED|
-                              -- HASH_PARTITION_EXCHANGE [$$169, $$154]  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                 -- STREAM_PROJECT  |PARTITIONED|
                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                     -- HYBRID_HASH_JOIN [$$147][$$169]  |PARTITIONED|
@@ -32,21 +32,21 @@
                                                 -- DATASOURCE_SCAN (q09_group_by.LineItem)  |PARTITIONED|
                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                     -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                              -- HASH_PARTITION_EXCHANGE [$$148, $$149]  |PARTITIONED|
+                              -- BROADCAST_EXCHANGE  |PARTITIONED|
                                 -- STREAM_PROJECT  |PARTITIONED|
                                   -- ASSIGN  |PARTITIONED|
                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                       -- DATASOURCE_SCAN (q09_group_by.Partsupp)  |PARTITIONED|
                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                           -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                      -- HASH_PARTITION_EXCHANGE [$$150]  |PARTITIONED|
+                      -- BROADCAST_EXCHANGE  |PARTITIONED|
                         -- STREAM_PROJECT  |PARTITIONED|
                           -- ASSIGN  |PARTITIONED|
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                               -- DATASOURCE_SCAN (q09_group_by.Supplier)  |PARTITIONED|
                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                   -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-              -- HASH_PARTITION_EXCHANGE [$$151]  |PARTITIONED|
+              -- BROADCAST_EXCHANGE  |PARTITIONED|
                 -- STREAM_PROJECT  |PARTITIONED|
                   -- ASSIGN  |PARTITIONED|
                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/query_issue3316.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/query_issue3316.plan
index 0da4c1d..755f182 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/query_issue3316.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/query_issue3316.plan
@@ -112,108 +112,111 @@
                                                                                                       -- HASH_PARTITION_EXCHANGE [$$240, $$237]  |PARTITIONED|
                                                                                                         -- STREAM_PROJECT  |PARTITIONED|
                                                                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                            -- HYBRID_HASH_JOIN [$$200][$$137]  |PARTITIONED|
-                                                                                                              -- RANDOM_PARTITION_EXCHANGE  |PARTITIONED|
-                                                                                                                -- UNNEST  |UNPARTITIONED|
-                                                                                                                  -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
-                                                                                                              -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                                                                            -- HYBRID_HASH_JOIN [$$251, $$231][$$237, $$230]  |PARTITIONED|
+                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                 -- STREAM_PROJECT  |PARTITIONED|
                                                                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                    -- HYBRID_HASH_JOIN [$$212][$$245]  |PARTITIONED|
+                                                                                                                    -- HYBRID_HASH_JOIN [$$245][$$244]  |PARTITIONED|
                                                                                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                         -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                          -- STREAM_SELECT  |PARTITIONED|
-                                                                                                                            -- ASSIGN  |PARTITIONED|
-                                                                                                                              -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                -- ASSIGN  |PARTITIONED|
-                                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                    -- REPLICATE  |PARTITIONED|
-                                                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                        -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                            -- DATASOURCE_SCAN (test.collection1)  |PARTITIONED|
-                                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                                                                      -- BROADCAST_EXCHANGE  |PARTITIONED|
-                                                                                                                        -- STREAM_PROJECT  |PARTITIONED|
                                                                                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                            -- HYBRID_HASH_JOIN [$$251, $$231][$$237, $$230]  |PARTITIONED|
-                                                                                                                              -- HASH_PARTITION_EXCHANGE [$$251, $$231]  |PARTITIONED|
-                                                                                                                                -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                    -- HYBRID_HASH_JOIN [$$254][$$240]  |PARTITIONED|
-                                                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                        -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                          -- ASSIGN  |PARTITIONED|
-                                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                              -- REPLICATE  |PARTITIONED|
-                                                                                                                                                -- HASH_PARTITION_EXCHANGE [$$437]  |PARTITIONED|
-                                                                                                                                                  -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                      -- HYBRID_HASH_JOIN [$$447][$$450]  |PARTITIONED|
-                                                                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                          -- REPLICATE  |PARTITIONED|
-                                                                                                                                                            -- HASH_PARTITION_EXCHANGE [$$447]  |PARTITIONED|
-                                                                                                                                                              -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                -- ASSIGN  |PARTITIONED|
-                                                                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                    -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                        -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                          -- ASSIGN  |PARTITIONED|
-                                                                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                              -- DATASOURCE_SCAN (test.collection0)  |PARTITIONED|
-                                                                                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                                                                                                        -- HASH_PARTITION_EXCHANGE [$$450]  |PARTITIONED|
-                                                                                                                                                          -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                            -- STREAM_SELECT  |PARTITIONED|
-                                                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                    -- ASSIGN  |PARTITIONED|
-                                                                                                                                                                      -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                        -- ASSIGN  |PARTITIONED|
-                                                                                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                            -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                -- DATASOURCE_SCAN (test.collection1)  |PARTITIONED|
-                                                                                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                        -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                          -- ASSIGN  |PARTITIONED|
-                                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                              -- REPLICATE  |PARTITIONED|
-                                                                                                                                                -- HASH_PARTITION_EXCHANGE [$$427]  |PARTITIONED|
-                                                                                                                                                  -- REPLICATE  |PARTITIONED|
-                                                                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                      -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                        -- ASSIGN  |PARTITIONED|
-                                                                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                            -- DATASOURCE_SCAN (test.collection0)  |PARTITIONED|
-                                                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                                            -- HYBRID_HASH_JOIN [$$254][$$240]  |PARTITIONED|
                                                                                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                                 -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                  -- ASSIGN  |PARTITIONED|
-                                                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                      -- REPLICATE  |PARTITIONED|
-                                                                                                                                        -- HASH_PARTITION_EXCHANGE [$$428, $$429]  |PARTITIONED|
-                                                                                                                                          -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                            -- STREAM_SELECT  |PARTITIONED|
+                                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                    -- HYBRID_HASH_JOIN [$$212][$$245]  |PARTITIONED|
+                                                                                                                                      -- HASH_PARTITION_EXCHANGE [$$212]  |PARTITIONED|
+                                                                                                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                            -- HYBRID_HASH_JOIN [$$137][$$200]  |PARTITIONED|
                                                                                                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                -- REPLICATE  |PARTITIONED|
-                                                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                  -- STREAM_SELECT  |PARTITIONED|
                                                                                                                                                     -- ASSIGN  |PARTITIONED|
                                                                                                                                                       -- STREAM_PROJECT  |PARTITIONED|
                                                                                                                                                         -- ASSIGN  |PARTITIONED|
                                                                                                                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                                                             -- REPLICATE  |PARTITIONED|
                                                                                                                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                -- DATASOURCE_SCAN (test.collection1)  |PARTITIONED|
+                                                                                                                                                                -- STREAM_PROJECT  |PARTITIONED|
                                                                                                                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                                                                                    -- DATASOURCE_SCAN (test.collection1)  |PARTITIONED|
+                                                                                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                                                              -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                                                                                                                -- UNNEST  |UNPARTITIONED|
+                                                                                                                                                  -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+                                                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                          -- ASSIGN  |PARTITIONED|
+                                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                              -- REPLICATE  |PARTITIONED|
+                                                                                                                                                -- HASH_PARTITION_EXCHANGE [$$447]  |PARTITIONED|
+                                                                                                                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                    -- ASSIGN  |PARTITIONED|
+                                                                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                        -- REPLICATE  |PARTITIONED|
+                                                                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                              -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                  -- DATASOURCE_SCAN (test.collection0)  |PARTITIONED|
+                                                                                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                  -- ASSIGN  |PARTITIONED|
+                                                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                      -- REPLICATE  |PARTITIONED|
+                                                                                                                                        -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                                                                                                          -- REPLICATE  |PARTITIONED|
+                                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                -- ASSIGN  |PARTITIONED|
+                                                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                    -- DATASOURCE_SCAN (test.collection0)  |PARTITIONED|
+                                                                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                          -- ASSIGN  |PARTITIONED|
+                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                              -- REPLICATE  |PARTITIONED|
+                                                                                                                                -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                                                                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                    -- STREAM_SELECT  |PARTITIONED|
+                                                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                        -- REPLICATE  |PARTITIONED|
+                                                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                            -- ASSIGN  |PARTITIONED|
+                                                                                                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                -- ASSIGN  |PARTITIONED|
+                                                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                    -- REPLICATE  |PARTITIONED|
+                                                                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                        -- DATASOURCE_SCAN (test.collection1)  |PARTITIONED|
+                                                                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                  -- ASSIGN  |PARTITIONED|
+                                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                      -- REPLICATE  |PARTITIONED|
+                                                                                                                        -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                                                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                            -- STREAM_SELECT  |PARTITIONED|
+                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                -- REPLICATE  |PARTITIONED|
+                                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                    -- ASSIGN  |PARTITIONED|
+                                                                                                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                        -- ASSIGN  |PARTITIONED|
+                                                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                            -- REPLICATE  |PARTITIONED|
+                                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                -- DATASOURCE_SCAN (test.collection1)  |PARTITIONED|
+                                                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                                                           -- BROADCAST_EXCHANGE  |PARTITIONED|
                                                             -- UNNEST  |UNPARTITIONED|
                                                               -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
@@ -266,15 +269,18 @@
                                                                                                       -- ASSIGN  |PARTITIONED|
                                                                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                           -- REPLICATE  |PARTITIONED|
-                                                                                                            -- HASH_PARTITION_EXCHANGE [$$427]  |PARTITIONED|
-                                                                                                              -- REPLICATE  |PARTITIONED|
-                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                  -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                    -- ASSIGN  |PARTITIONED|
+                                                                                                            -- HASH_PARTITION_EXCHANGE [$$401]  |PARTITIONED|
+                                                                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                -- ASSIGN  |PARTITIONED|
+                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                    -- REPLICATE  |PARTITIONED|
                                                                                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                        -- DATASOURCE_SCAN (test.collection0)  |PARTITIONED|
-                                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                          -- ASSIGN  |PARTITIONED|
+                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                              -- DATASOURCE_SCAN (test.collection0)  |PARTITIONED|
+                                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                                                                                                   -- HASH_PARTITION_EXCHANGE [$$263]  |PARTITIONED|
                                                                                                     -- ASSIGN  |PARTITIONED|
                                                                                                       -- STREAM_PROJECT  |PARTITIONED|
@@ -351,111 +357,114 @@
                                                                                                                                                         -- HASH_PARTITION_EXCHANGE [$$286, $$287]  |PARTITIONED|
                                                                                                                                                           -- STREAM_PROJECT  |PARTITIONED|
                                                                                                                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                              -- HYBRID_HASH_JOIN [$$303][$$304]  |PARTITIONED|
-                                                                                                                                                                -- RANDOM_PARTITION_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                  -- UNNEST  |UNPARTITIONED|
-                                                                                                                                                                    -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
-                                                                                                                                                                -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                                                                                                                              -- HYBRID_HASH_JOIN [$$297, $$289][$$287, $$288]  |PARTITIONED|
+                                                                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                                                                   -- STREAM_PROJECT  |PARTITIONED|
                                                                                                                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                      -- HYBRID_HASH_JOIN [$$305][$$306]  |PARTITIONED|
+                                                                                                                                                                      -- HYBRID_HASH_JOIN [$$306][$$309]  |PARTITIONED|
                                                                                                                                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                                                                           -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                            -- ASSIGN  |PARTITIONED|
-                                                                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                    -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                      -- STREAM_SELECT  |PARTITIONED|
-                                                                                                                                                                                        -- ASSIGN  |PARTITIONED|
-                                                                                                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                            -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                    -- DATASOURCE_SCAN (test.collection1)  |PARTITIONED|
-                                                                                                                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                                                                                                                        -- BROADCAST_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                          -- STREAM_PROJECT  |PARTITIONED|
                                                                                                                                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                              -- HYBRID_HASH_JOIN [$$297, $$289][$$287, $$288]  |PARTITIONED|
-                                                                                                                                                                                -- HASH_PARTITION_EXCHANGE [$$297, $$289]  |PARTITIONED|
+                                                                                                                                                                              -- HYBRID_HASH_JOIN [$$296][$$286]  |PARTITIONED|
+                                                                                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                                                                                   -- STREAM_PROJECT  |PARTITIONED|
                                                                                                                                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                      -- HYBRID_HASH_JOIN [$$296][$$286]  |PARTITIONED|
-                                                                                                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                      -- HYBRID_HASH_JOIN [$$305][$$306]  |PARTITIONED|
+                                                                                                                                                                                        -- HASH_PARTITION_EXCHANGE [$$305]  |PARTITIONED|
                                                                                                                                                                                           -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                            -- ASSIGN  |PARTITIONED|
-                                                                                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                                  -- HASH_PARTITION_EXCHANGE [$$437]  |PARTITIONED|
-                                                                                                                                                                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                              -- HYBRID_HASH_JOIN [$$304][$$303]  |PARTITIONED|
+                                                                                                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                                                    -- ASSIGN  |PARTITIONED|
                                                                                                                                                                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                        -- HYBRID_HASH_JOIN [$$447][$$450]  |PARTITIONED|
+                                                                                                                                                                                                        -- REPLICATE  |PARTITIONED|
                                                                                                                                                                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                            -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                                              -- HASH_PARTITION_EXCHANGE [$$447]  |PARTITIONED|
-                                                                                                                                                                                                                -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                                                  -- ASSIGN  |PARTITIONED|
-                                                                                                                                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                      -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                          -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                                                            -- ASSIGN  |PARTITIONED|
-                                                                                                                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                -- DATASOURCE_SCAN (test.collection0)  |PARTITIONED|
-                                                                                                                                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                                                                                                                                                          -- HASH_PARTITION_EXCHANGE [$$450]  |PARTITIONED|
                                                                                                                                                                                                             -- STREAM_PROJECT  |PARTITIONED|
                                                                                                                                                                                                               -- STREAM_SELECT  |PARTITIONED|
-                                                                                                                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                  -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                      -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                                                -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                    -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                                                                                                                         -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                                                          -- ASSIGN  |PARTITIONED|
-                                                                                                                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                              -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                  -- DATASOURCE_SCAN (test.collection1)  |PARTITIONED|
-                                                                                                                                                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                                                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                            -- DATASOURCE_SCAN (test.collection1)  |PARTITIONED|
+                                                                                                                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                                                                                                                -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                  -- UNNEST  |UNPARTITIONED|
+                                                                                                                                                                                                    -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
                                                                                                                                                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                                                                                           -- STREAM_PROJECT  |PARTITIONED|
                                                                                                                                                                                             -- ASSIGN  |PARTITIONED|
                                                                                                                                                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                                                                                                 -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                                  -- HASH_PARTITION_EXCHANGE [$$427]  |PARTITIONED|
-                                                                                                                                                                                                    -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                        -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                                          -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                                  -- HASH_PARTITION_EXCHANGE [$$447]  |PARTITIONED|
+                                                                                                                                                                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                                                      -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                          -- REPLICATE  |PARTITIONED|
                                                                                                                                                                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                              -- DATASOURCE_SCAN (test.collection0)  |PARTITIONED|
-                                                                                                                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                                                                                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                                                                -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                    -- DATASOURCE_SCAN (test.collection0)  |PARTITIONED|
+                                                                                                                                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                                                                                                                                                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                                                                                   -- STREAM_PROJECT  |PARTITIONED|
                                                                                                                                                                                     -- ASSIGN  |PARTITIONED|
                                                                                                                                                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                                                                                         -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                          -- HASH_PARTITION_EXCHANGE [$$428, $$429]  |PARTITIONED|
-                                                                                                                                                                                            -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                              -- STREAM_SELECT  |PARTITIONED|
-                                                                                                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                  -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                          -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                            -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                                                  -- ASSIGN  |PARTITIONED|
                                                                                                                                                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                      -- ASSIGN  |PARTITIONED|
-                                                                                                                                                                                                        -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                                          -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                                      -- DATASOURCE_SCAN (test.collection0)  |PARTITIONED|
+                                                                                                                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                            -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                  -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                                      -- STREAM_SELECT  |PARTITIONED|
+                                                                                                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                          -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                              -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                                                  -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                      -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                          -- DATASOURCE_SCAN (test.collection1)  |PARTITIONED|
                                                                                                                                                                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                              -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                  -- DATASOURCE_SCAN (test.collection1)  |PARTITIONED|
-                                                                                                                                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                                                                                                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                    -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                        -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                          -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                              -- STREAM_SELECT  |PARTITIONED|
+                                                                                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                  -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                      -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                                          -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                              -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                  -- DATASOURCE_SCAN (test.collection1)  |PARTITIONED|
+                                                                                                                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                                                                                                             -- BROADCAST_EXCHANGE  |PARTITIONED|
                                                                                                               -- UNNEST  |UNPARTITIONED|
                                                                                                                 -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
@@ -512,15 +521,18 @@
                                                                                                                                   -- ASSIGN  |PARTITIONED|
                                                                                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                                       -- REPLICATE  |PARTITIONED|
-                                                                                                                                        -- HASH_PARTITION_EXCHANGE [$$427]  |PARTITIONED|
-                                                                                                                                          -- REPLICATE  |PARTITIONED|
-                                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                              -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                -- ASSIGN  |PARTITIONED|
+                                                                                                                                        -- HASH_PARTITION_EXCHANGE [$$401]  |PARTITIONED|
+                                                                                                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                            -- ASSIGN  |PARTITIONED|
+                                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                -- REPLICATE  |PARTITIONED|
                                                                                                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                    -- DATASOURCE_SCAN (test.collection0)  |PARTITIONED|
-                                                                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                      -- ASSIGN  |PARTITIONED|
+                                                                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                          -- DATASOURCE_SCAN (test.collection0)  |PARTITIONED|
+                                                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                                                                                                                               -- HASH_PARTITION_EXCHANGE [$$333]  |PARTITIONED|
                                                                                                                                 -- ASSIGN  |PARTITIONED|
                                                                                                                                   -- STREAM_PROJECT  |PARTITIONED|
@@ -597,111 +609,114 @@
                                                                                                                                                                                     -- HASH_PARTITION_EXCHANGE [$$356, $$357]  |PARTITIONED|
                                                                                                                                                                                       -- STREAM_PROJECT  |PARTITIONED|
                                                                                                                                                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                          -- HYBRID_HASH_JOIN [$$373][$$374]  |PARTITIONED|
-                                                                                                                                                                                            -- RANDOM_PARTITION_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                              -- UNNEST  |UNPARTITIONED|
-                                                                                                                                                                                                -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
-                                                                                                                                                                                            -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                          -- HYBRID_HASH_JOIN [$$367, $$359][$$357, $$358]  |PARTITIONED|
+                                                                                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                                                                                               -- STREAM_PROJECT  |PARTITIONED|
                                                                                                                                                                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                  -- HYBRID_HASH_JOIN [$$375][$$376]  |PARTITIONED|
+                                                                                                                                                                                                  -- HYBRID_HASH_JOIN [$$376][$$379]  |PARTITIONED|
                                                                                                                                                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                                                                                                       -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                                        -- ASSIGN  |PARTITIONED|
-                                                                                                                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                            -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                                                  -- STREAM_SELECT  |PARTITIONED|
-                                                                                                                                                                                                                    -- ASSIGN  |PARTITIONED|
-                                                                                                                                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                        -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                            -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                -- DATASOURCE_SCAN (test.collection1)  |PARTITIONED|
-                                                                                                                                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                                                                                                                                                    -- BROADCAST_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                      -- STREAM_PROJECT  |PARTITIONED|
                                                                                                                                                                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                          -- HYBRID_HASH_JOIN [$$367, $$359][$$357, $$358]  |PARTITIONED|
-                                                                                                                                                                                                            -- HASH_PARTITION_EXCHANGE [$$367, $$359]  |PARTITIONED|
+                                                                                                                                                                                                          -- HYBRID_HASH_JOIN [$$366][$$356]  |PARTITIONED|
+                                                                                                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                                                                                                               -- STREAM_PROJECT  |PARTITIONED|
                                                                                                                                                                                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                  -- HYBRID_HASH_JOIN [$$366][$$356]  |PARTITIONED|
-                                                                                                                                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                  -- HYBRID_HASH_JOIN [$$375][$$376]  |PARTITIONED|
+                                                                                                                                                                                                                    -- HASH_PARTITION_EXCHANGE [$$375]  |PARTITIONED|
                                                                                                                                                                                                                       -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                                                        -- ASSIGN  |PARTITIONED|
-                                                                                                                                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                            -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                                                              -- HASH_PARTITION_EXCHANGE [$$437]  |PARTITIONED|
-                                                                                                                                                                                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                          -- HYBRID_HASH_JOIN [$$374][$$373]  |PARTITIONED|
+                                                                                                                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                                                                                -- ASSIGN  |PARTITIONED|
                                                                                                                                                                                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                    -- HYBRID_HASH_JOIN [$$447][$$450]  |PARTITIONED|
+                                                                                                                                                                                                                                    -- REPLICATE  |PARTITIONED|
                                                                                                                                                                                                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                        -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                                                                          -- HASH_PARTITION_EXCHANGE [$$447]  |PARTITIONED|
-                                                                                                                                                                                                                                            -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                                                                              -- ASSIGN  |PARTITIONED|
-                                                                                                                                                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                                  -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                                      -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                                                                                        -- ASSIGN  |PARTITIONED|
-                                                                                                                                                                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                                            -- DATASOURCE_SCAN (test.collection0)  |PARTITIONED|
-                                                                                                                                                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                                                                                                                                                                                      -- HASH_PARTITION_EXCHANGE [$$450]  |PARTITIONED|
                                                                                                                                                                                                                                         -- STREAM_PROJECT  |PARTITIONED|
                                                                                                                                                                                                                                           -- STREAM_SELECT  |PARTITIONED|
-                                                                                                                                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                              -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                                  -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                                                                            -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                                -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                                                                                                                                                     -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                                                                                      -- ASSIGN  |PARTITIONED|
-                                                                                                                                                                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                                          -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                                              -- DATASOURCE_SCAN (test.collection1)  |PARTITIONED|
-                                                                                                                                                                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                                                                                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                                        -- DATASOURCE_SCAN (test.collection1)  |PARTITIONED|
+                                                                                                                                                                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                                                                                                                                            -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                              -- UNNEST  |UNPARTITIONED|
+                                                                                                                                                                                                                                -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
                                                                                                                                                                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                                                                                                                       -- STREAM_PROJECT  |PARTITIONED|
                                                                                                                                                                                                                         -- ASSIGN  |PARTITIONED|
                                                                                                                                                                                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                                                                                                                             -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                                                              -- HASH_PARTITION_EXCHANGE [$$427]  |PARTITIONED|
-                                                                                                                                                                                                                                -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                    -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                                                                      -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                                                              -- HASH_PARTITION_EXCHANGE [$$447]  |PARTITIONED|
+                                                                                                                                                                                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                                                                                  -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                      -- REPLICATE  |PARTITIONED|
                                                                                                                                                                                                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                          -- DATASOURCE_SCAN (test.collection0)  |PARTITIONED|
-                                                                                                                                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                                                                                                                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                                                                                            -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                                -- DATASOURCE_SCAN (test.collection0)  |PARTITIONED|
+                                                                                                                                                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                                                                                                                                                                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                                                                                                               -- STREAM_PROJECT  |PARTITIONED|
                                                                                                                                                                                                                 -- ASSIGN  |PARTITIONED|
                                                                                                                                                                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                                                                                                                     -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                                                      -- HASH_PARTITION_EXCHANGE [$$428, $$429]  |PARTITIONED|
-                                                                                                                                                                                                                        -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                                                          -- STREAM_SELECT  |PARTITIONED|
-                                                                                                                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                              -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                                                      -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                        -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                                                                              -- ASSIGN  |PARTITIONED|
                                                                                                                                                                                                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                  -- ASSIGN  |PARTITIONED|
-                                                                                                                                                                                                                                    -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                                                                      -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                                                                  -- DATASOURCE_SCAN (test.collection0)  |PARTITIONED|
+                                                                                                                                                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                                                        -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                            -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                                              -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                                                                  -- STREAM_SELECT  |PARTITIONED|
+                                                                                                                                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                      -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                          -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                                                                              -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                  -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                      -- DATASOURCE_SCAN (test.collection1)  |PARTITIONED|
                                                                                                                                                                                                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                          -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                              -- DATASOURCE_SCAN (test.collection1)  |PARTITIONED|
-                                                                                                                                                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                                                                                                                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                                                -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                    -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                                      -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                                                          -- STREAM_SELECT  |PARTITIONED|
+                                                                                                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                              -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                  -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                                                                      -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                          -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                              -- DATASOURCE_SCAN (test.collection1)  |PARTITIONED|
+                                                                                                                                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                                                                                                                                         -- BROADCAST_EXCHANGE  |PARTITIONED|
                                                                                                                                           -- UNNEST  |UNPARTITIONED|
                                                                                                                                             -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
@@ -747,11 +762,11 @@
                                                                                                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                                               -- HYBRID_HASH_JOIN [$$401][$$404]  |PARTITIONED|
                                                                                                                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                  -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                    -- ASSIGN  |PARTITIONED|
-                                                                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                        -- REPLICATE  |PARTITIONED|
-                                                                                                                                                          -- HASH_PARTITION_EXCHANGE [$$427]  |PARTITIONED|
+                                                                                                                                                  -- REPLICATE  |PARTITIONED|
+                                                                                                                                                    -- HASH_PARTITION_EXCHANGE [$$401]  |PARTITIONED|
+                                                                                                                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                        -- ASSIGN  |PARTITIONED|
+                                                                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                                                             -- REPLICATE  |PARTITIONED|
                                                                                                                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                                                                 -- STREAM_PROJECT  |PARTITIONED|
@@ -833,99 +848,99 @@
                                                                                                                                                                                                       -- HASH_PARTITION_EXCHANGE [$$427, $$428]  |PARTITIONED|
                                                                                                                                                                                                         -- STREAM_PROJECT  |PARTITIONED|
                                                                                                                                                                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                            -- HYBRID_HASH_JOIN [$$444][$$445]  |PARTITIONED|
-                                                                                                                                                                                                              -- RANDOM_PARTITION_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                -- UNNEST  |UNPARTITIONED|
-                                                                                                                                                                                                                  -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
-                                                                                                                                                                                                              -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                            -- HYBRID_HASH_JOIN [$$438, $$430][$$428, $$429]  |PARTITIONED|
+                                                                                                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                                                                                                                 -- STREAM_PROJECT  |PARTITIONED|
                                                                                                                                                                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                    -- HYBRID_HASH_JOIN [$$446][$$447]  |PARTITIONED|
+                                                                                                                                                                                                                    -- HYBRID_HASH_JOIN [$$447][$$450]  |PARTITIONED|
                                                                                                                                                                                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                        -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                                                        -- STREAM_PROJECT  |PARTITIONED|
                                                                                                                                                                                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                            -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                                                              -- STREAM_SELECT  |PARTITIONED|
-                                                                                                                                                                                                                                -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                                                            -- HYBRID_HASH_JOIN [$$437][$$427]  |PARTITIONED|
+                                                                                                                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                -- STREAM_PROJECT  |PARTITIONED|
                                                                                                                                                                                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                    -- HYBRID_HASH_JOIN [$$446][$$447]  |PARTITIONED|
+                                                                                                                                                                                                                                      -- HASH_PARTITION_EXCHANGE [$$446]  |PARTITIONED|
+                                                                                                                                                                                                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                            -- HYBRID_HASH_JOIN [$$445][$$444]  |PARTITIONED|
+                                                                                                                                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                                -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                                                                                                      -- STREAM_SELECT  |PARTITIONED|
+                                                                                                                                                                                                                                                        -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                                            -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                                                    -- DATASOURCE_SCAN (test.collection1)  |PARTITIONED|
+                                                                                                                                                                                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                                                                                                                                                              -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                                -- UNNEST  |UNPARTITIONED|
+                                                                                                                                                                                                                                                  -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+                                                                                                                                                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                        -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                                                                          -- HASH_PARTITION_EXCHANGE [$$447]  |PARTITIONED|
+                                                                                                                                                                                                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                                                                                              -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                                  -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                                                                                                        -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                                            -- DATASOURCE_SCAN (test.collection0)  |PARTITIONED|
+                                                                                                                                                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                                                                  -- BROADCAST_EXCHANGE  |PARTITIONED|
                                                                                                                                                                                                                                     -- REPLICATE  |PARTITIONED|
                                                                                                                                                                                                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                                                                                                                                         -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                            -- DATASOURCE_SCAN (test.collection1)  |PARTITIONED|
-                                                                                                                                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                                                                                                                                                                      -- BROADCAST_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                        -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                            -- HYBRID_HASH_JOIN [$$438, $$430][$$428, $$429]  |PARTITIONED|
-                                                                                                                                                                                                                              -- HASH_PARTITION_EXCHANGE [$$438, $$430]  |PARTITIONED|
-                                                                                                                                                                                                                                -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                    -- HYBRID_HASH_JOIN [$$437][$$427]  |PARTITIONED|
-                                                                                                                                                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                        -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                                                                          -- HASH_PARTITION_EXCHANGE [$$437]  |PARTITIONED|
-                                                                                                                                                                                                                                            -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                                -- HYBRID_HASH_JOIN [$$447][$$450]  |PARTITIONED|
-                                                                                                                                                                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                                    -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                                                                                      -- HASH_PARTITION_EXCHANGE [$$447]  |PARTITIONED|
-                                                                                                                                                                                                                                                        -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                                                                                          -- ASSIGN  |PARTITIONED|
-                                                                                                                                                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                                              -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                                                  -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                                                                                                    -- ASSIGN  |PARTITIONED|
-                                                                                                                                                                                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                                                        -- DATASOURCE_SCAN (test.collection0)  |PARTITIONED|
-                                                                                                                                                                                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                                                                                                                                                                                                  -- HASH_PARTITION_EXCHANGE [$$450]  |PARTITIONED|
-                                                                                                                                                                                                                                                    -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                                                                                      -- STREAM_SELECT  |PARTITIONED|
-                                                                                                                                                                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                                          -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                                              -- ASSIGN  |PARTITIONED|
-                                                                                                                                                                                                                                                                -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                                                                                                  -- ASSIGN  |PARTITIONED|
-                                                                                                                                                                                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                                                      -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                                                          -- DATASOURCE_SCAN (test.collection1)  |PARTITIONED|
-                                                                                                                                                                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                                                                                                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                        -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                                                                          -- HASH_PARTITION_EXCHANGE [$$427]  |PARTITIONED|
-                                                                                                                                                                                                                                            -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                                -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                                                                                  -- ASSIGN  |PARTITIONED|
-                                                                                                                                                                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                                      -- DATASOURCE_SCAN (test.collection0)  |PARTITIONED|
-                                                                                                                                                                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                                                                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                                                                  -- HASH_PARTITION_EXCHANGE [$$428, $$429]  |PARTITIONED|
-                                                                                                                                                                                                                                    -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                                                                      -- STREAM_SELECT  |PARTITIONED|
-                                                                                                                                                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                          -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                                                                          -- ASSIGN  |PARTITIONED|
                                                                                                                                                                                                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                              -- ASSIGN  |PARTITIONED|
-                                                                                                                                                                                                                                                -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                                                                                                                                                  -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                                                                              -- DATASOURCE_SCAN (test.collection0)  |PARTITIONED|
+                                                                                                                                                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                                                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                        -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                                                          -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                                                                              -- STREAM_SELECT  |PARTITIONED|
+                                                                                                                                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                  -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                      -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                                                                                          -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                              -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                                  -- DATASOURCE_SCAN (test.collection1)  |PARTITIONED|
                                                                                                                                                                                                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                                      -- REPLICATE  |PARTITIONED|
-                                                                                                                                                                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                                          -- DATASOURCE_SCAN (test.collection1)  |PARTITIONED|
-                                                                                                                                                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                                                                                                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                                                                                                                                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                                                  -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                                                                      -- STREAM_SELECT  |PARTITIONED|
+                                                                                                                                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                          -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                              -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                                                                                                                                  -- ASSIGN  |PARTITIONED|
+                                                                                                                                                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                      -- REPLICATE  |PARTITIONED|
+                                                                                                                                                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                          -- DATASOURCE_SCAN (test.collection1)  |PARTITIONED|
+                                                                                                                                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                                                                                                                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                                                                                                                                                           -- BROADCAST_EXCHANGE  |PARTITIONED|
                                                                                                                                                             -- UNNEST  |UNPARTITIONED|
                                                                                                                                                               -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/split-materialization.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/split-materialization.plan
index 34f5238..3235136 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/split-materialization.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/split-materialization.plan
@@ -2,19 +2,30 @@
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
-        -- SORT_MERGE_EXCHANGE [$$91(ASC), $$92(ASC) ]  |PARTITIONED|
-          -- STABLE_SORT [$$91(ASC), $$92(ASC)]  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$97(ASC), $$98(ASC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$97(ASC), $$98(ASC)]  |PARTITIONED|
             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-              -- STREAM_PROJECT  |PARTITIONED|
-                -- STREAM_SELECT  |PARTITIONED|
-                  -- ASSIGN  |PARTITIONED|
+              -- NESTED_LOOP  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- STREAM_PROJECT  |PARTITIONED|
+                    -- ASSIGN  |PARTITIONED|
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- REPLICATE  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- STREAM_PROJECT  |PARTITIONED|
+                              -- STREAM_SELECT  |PARTITIONED|
+                                -- ASSIGN  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- DATASOURCE_SCAN (TinySocial.FacebookUsers)  |PARTITIONED|
+                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                -- BROADCAST_EXCHANGE  |PARTITIONED|
+                  -- REPLICATE  |PARTITIONED|
                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                      -- BTREE_SEARCH (TinySocial.FacebookUsers.FacebookUsers)  |PARTITIONED|
-                        -- BROADCAST_EXCHANGE  |PARTITIONED|
-                          -- STREAM_PROJECT  |PARTITIONED|
-                            -- STREAM_SELECT  |PARTITIONED|
-                              -- ASSIGN  |PARTITIONED|
+                      -- STREAM_PROJECT  |PARTITIONED|
+                        -- STREAM_SELECT  |PARTITIONED|
+                          -- ASSIGN  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- DATASOURCE_SCAN (TinySocial.FacebookUsers)  |PARTITIONED|
                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                  -- DATASOURCE_SCAN (TinySocial.FacebookUsers)  |PARTITIONED|
-                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/split-materialization_ps.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/split-materialization_ps.plan
index 2881c66..cd7f5d9 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/split-materialization_ps.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/split-materialization_ps.plan
@@ -3,25 +3,36 @@
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-          -- STABLE_SORT [$$91(ASC), $$92(ASC)]  |PARTITIONED|
-            -- RANGE_PARTITION_EXCHANGE [$$91(ASC), $$92(ASC)]  |PARTITIONED|
+          -- STABLE_SORT [$$97(ASC), $$98(ASC)]  |PARTITIONED|
+            -- RANGE_PARTITION_EXCHANGE [$$97(ASC), $$98(ASC)]  |PARTITIONED|
               -- FORWARD  |PARTITIONED|
                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                   -- REPLICATE  |PARTITIONED|
                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                      -- STREAM_PROJECT  |PARTITIONED|
-                        -- STREAM_SELECT  |PARTITIONED|
-                          -- ASSIGN  |PARTITIONED|
+                      -- NESTED_LOOP  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ASSIGN  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- REPLICATE  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- STREAM_PROJECT  |PARTITIONED|
+                                      -- STREAM_SELECT  |PARTITIONED|
+                                        -- ASSIGN  |PARTITIONED|
+                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                            -- DATASOURCE_SCAN (TinySocial.FacebookUsers)  |PARTITIONED|
+                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                        -- BROADCAST_EXCHANGE  |PARTITIONED|
+                          -- REPLICATE  |PARTITIONED|
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              -- BTREE_SEARCH (TinySocial.FacebookUsers.FacebookUsers)  |PARTITIONED|
-                                -- BROADCAST_EXCHANGE  |PARTITIONED|
-                                  -- STREAM_PROJECT  |PARTITIONED|
-                                    -- STREAM_SELECT  |PARTITIONED|
-                                      -- ASSIGN  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- STREAM_SELECT  |PARTITIONED|
+                                  -- ASSIGN  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- DATASOURCE_SCAN (TinySocial.FacebookUsers)  |PARTITIONED|
                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                          -- DATASOURCE_SCAN (TinySocial.FacebookUsers)  |PARTITIONED|
-                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                 -- BROADCAST_EXCHANGE  |PARTITIONED|
                   -- AGGREGATE  |UNPARTITIONED|
                     -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
@@ -30,16 +41,27 @@
                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                             -- REPLICATE  |PARTITIONED|
                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                -- STREAM_PROJECT  |PARTITIONED|
-                                  -- STREAM_SELECT  |PARTITIONED|
-                                    -- ASSIGN  |PARTITIONED|
+                                -- NESTED_LOOP  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- STREAM_PROJECT  |PARTITIONED|
+                                      -- ASSIGN  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- REPLICATE  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                -- STREAM_SELECT  |PARTITIONED|
+                                                  -- ASSIGN  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- DATASOURCE_SCAN (TinySocial.FacebookUsers)  |PARTITIONED|
+                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                  -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                    -- REPLICATE  |PARTITIONED|
                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                        -- BTREE_SEARCH (TinySocial.FacebookUsers.FacebookUsers)  |PARTITIONED|
-                                          -- BROADCAST_EXCHANGE  |PARTITIONED|
-                                            -- STREAM_PROJECT  |PARTITIONED|
-                                              -- STREAM_SELECT  |PARTITIONED|
-                                                -- ASSIGN  |PARTITIONED|
+                                        -- STREAM_PROJECT  |PARTITIONED|
+                                          -- STREAM_SELECT  |PARTITIONED|
+                                            -- ASSIGN  |PARTITIONED|
+                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                -- DATASOURCE_SCAN (TinySocial.FacebookUsers)  |PARTITIONED|
                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                    -- DATASOURCE_SCAN (TinySocial.FacebookUsers)  |PARTITIONED|
-                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/tpcds/query-ASTERIXDB-1580.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/tpcds/query-ASTERIXDB-1580.plan
new file mode 100644
index 0000000..47ede28
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/tpcds/query-ASTERIXDB-1580.plan
@@ -0,0 +1,73 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_LIMIT  |UNPARTITIONED|
+      -- STREAM_PROJECT  |PARTITIONED|
+        -- ASSIGN  |PARTITIONED|
+          -- SORT_MERGE_EXCHANGE [$$136(ASC) ]  |PARTITIONED|
+            -- STREAM_LIMIT  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- STABLE_SORT [topK: 100] [$$136(ASC)]  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- STREAM_SELECT  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- SORT_GROUP_BY[$$145]  |PARTITIONED|
+                                  {
+                                    -- AGGREGATE  |LOCAL|
+                                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                  }
+                            -- HASH_PARTITION_EXCHANGE [$$145]  |PARTITIONED|
+                              -- SORT_GROUP_BY[$$126]  |PARTITIONED|
+                                      {
+                                        -- AGGREGATE  |LOCAL|
+                                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                      }
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- HYBRID_HASH_JOIN [$$129][$$132]  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- HYBRID_HASH_JOIN [$$137][$$131]  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- HYBRID_HASH_JOIN [$$128][$$142]  |PARTITIONED|
+                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                              -- HYBRID_HASH_JOIN [$$127][$$140]  |PARTITIONED|
+                                                                -- HASH_PARTITION_EXCHANGE [$$127]  |PARTITIONED|
+                                                                  -- ASSIGN  |PARTITIONED|
+                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                      -- DATASOURCE_SCAN (tpcds.customer_address)  |PARTITIONED|
+                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                -- HASH_PARTITION_EXCHANGE [$$140]  |PARTITIONED|
+                                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                                    -- ASSIGN  |PARTITIONED|
+                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                        -- DATASOURCE_SCAN (tpcds.customer)  |PARTITIONED|
+                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                        -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                            -- ASSIGN  |PARTITIONED|
+                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                  -- DATASOURCE_SCAN (tpcds.store_sales)  |PARTITIONED|
+                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- DATASOURCE_SCAN (tpcds.date_dim)  |PARTITIONED|
+                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                        -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- DATASOURCE_SCAN (tpcds.item)  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/tpcds/query-ASTERIXDB-1591.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/tpcds/query-ASTERIXDB-1591.plan
index 17eb2c4..cb5a46e 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/tpcds/query-ASTERIXDB-1591.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/tpcds/query-ASTERIXDB-1591.plan
@@ -69,7 +69,7 @@
                                                                                   -- STREAM_PROJECT  |PARTITIONED|
                                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                       -- HYBRID_HASH_JOIN [$$161][$$151]  |PARTITIONED|
-                                                                                        -- HASH_PARTITION_EXCHANGE [$$161]  |PARTITIONED|
+                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                           -- STREAM_PROJECT  |PARTITIONED|
                                                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                               -- HYBRID_HASH_JOIN [$$150][$$167]  |PARTITIONED|
@@ -99,7 +99,7 @@
                                                                                                                     -- DATASOURCE_SCAN (tpcds.date_dim)  |PARTITIONED|
                                                                                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                         -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                                        -- HASH_PARTITION_EXCHANGE [$$151]  |PARTITIONED|
+                                                                                        -- BROADCAST_EXCHANGE  |PARTITIONED|
                                                                                           -- DATASOURCE_SCAN (tpcds.customer_address)  |PARTITIONED|
                                                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                               -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/tpcds/query-ASTERIXDB-1596.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/tpcds/query-ASTERIXDB-1596.plan
new file mode 100644
index 0000000..e92bd13
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/tpcds/query-ASTERIXDB-1596.plan
@@ -0,0 +1,27 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- SORT_MERGE_EXCHANGE [$$53(ASC), $$54(ASC) ]  |PARTITIONED|
+            -- STABLE_SORT [$$53(ASC), $$54(ASC)]  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- STREAM_PROJECT  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- HYBRID_HASH_JOIN [$$53][$$57]  |PARTITIONED|
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- HYBRID_HASH_JOIN [$$54, $$53][$$56, $$55]  |PARTITIONED|
+                              -- HASH_PARTITION_EXCHANGE [$$54, $$53]  |PARTITIONED|
+                                -- DATASOURCE_SCAN (tpcds.catalog_sales)  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                              -- HASH_PARTITION_EXCHANGE [$$56, $$55]  |PARTITIONED|
+                                -- DATASOURCE_SCAN (tpcds.catalog_returns)  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                      -- BROADCAST_EXCHANGE  |PARTITIONED|
+                        -- DATASOURCE_SCAN (tpcds.item)  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/tpcds/query-ASTERIXDB-1596_ps.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/tpcds/query-ASTERIXDB-1596_ps.plan
new file mode 100644
index 0000000..2b3a728
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/tpcds/query-ASTERIXDB-1596_ps.plan
@@ -0,0 +1,58 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+            -- STABLE_SORT [$$53(ASC), $$54(ASC)]  |PARTITIONED|
+              -- RANGE_PARTITION_EXCHANGE [$$53(ASC), $$54(ASC)]  |PARTITIONED|
+                -- FORWARD  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- REPLICATE  |PARTITIONED|
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- HYBRID_HASH_JOIN [$$53][$$57]  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- STREAM_PROJECT  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- HYBRID_HASH_JOIN [$$54, $$53][$$56, $$55]  |PARTITIONED|
+                                      -- HASH_PARTITION_EXCHANGE [$$54, $$53]  |PARTITIONED|
+                                        -- DATASOURCE_SCAN (tpcds.catalog_sales)  |PARTITIONED|
+                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                      -- HASH_PARTITION_EXCHANGE [$$56, $$55]  |PARTITIONED|
+                                        -- DATASOURCE_SCAN (tpcds.catalog_returns)  |PARTITIONED|
+                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                              -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                -- DATASOURCE_SCAN (tpcds.item)  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                  -- BROADCAST_EXCHANGE  |PARTITIONED|
+                    -- AGGREGATE  |UNPARTITIONED|
+                      -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+                        -- AGGREGATE  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- REPLICATE  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- HYBRID_HASH_JOIN [$$53][$$57]  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- HYBRID_HASH_JOIN [$$54, $$53][$$56, $$55]  |PARTITIONED|
+                                                -- HASH_PARTITION_EXCHANGE [$$54, $$53]  |PARTITIONED|
+                                                  -- DATASOURCE_SCAN (tpcds.catalog_sales)  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                -- HASH_PARTITION_EXCHANGE [$$56, $$55]  |PARTITIONED|
+                                                  -- DATASOURCE_SCAN (tpcds.catalog_returns)  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                        -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                          -- DATASOURCE_SCAN (tpcds.item)  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.3.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.3.plan
index cd3f2d4..04f4f79 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.3.plan
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.3.plan
@@ -1,20 +1,20 @@
-distribute result [$$35] [cardinality: 960.71, op-cost: 0.0, total-cost: 136471.34]
+distribute result [$$35] [cardinality: 961.01, op-cost: 0.0, total-cost: 136513.95]
 -- DISTRIBUTE_RESULT  |PARTITIONED|
-  exchange [cardinality: 960.71, op-cost: 0.0, total-cost: 136471.34]
+  exchange [cardinality: 961.01, op-cost: 0.0, total-cost: 136513.95]
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-    distinct ([$$35]) [cardinality: 960.71, op-cost: 58474.7, total-cost: 136471.34]
+    distinct ([$$35]) [cardinality: 961.01, op-cost: 58495.07, total-cost: 136513.95]
     -- PRE_SORTED_DISTINCT_BY  |PARTITIONED|
-      exchange [cardinality: 4783.64, op-cost: 0.0, total-cost: 77996.64]
+      exchange [cardinality: 4785.13, op-cost: 0.0, total-cost: 78018.88]
       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-        order (ASC, $$35) [cardinality: 4783.64, op-cost: 58474.7, total-cost: 77996.64]
+        order (ASC, $$35) [cardinality: 4785.13, op-cost: 58495.07, total-cost: 78018.88]
         -- STABLE_SORT [$$35(ASC)]  |PARTITIONED|
-          exchange [cardinality: 4783.64, op-cost: 0.0, total-cost: 19521.94]
+          exchange [cardinality: 4785.13, op-cost: 0.0, total-cost: 19523.81]
           -- HASH_PARTITION_EXCHANGE [$$35]  |PARTITIONED|
-            project ([$$35]) [cardinality: 4783.64, op-cost: 0.0, total-cost: 19521.94]
+            project ([$$35]) [cardinality: 4785.13, op-cost: 0.0, total-cost: 19523.81]
             -- STREAM_PROJECT  |PARTITIONED|
-              assign [$$35] <- [{"l_linenumber": $$37, "l_partkey": $$l.getField(1), "o_custkey": $$o.getField(1)}] [cardinality: 4783.64, op-cost: 0.0, total-cost: 19521.94]
+              assign [$$35] <- [{"l_linenumber": $$37, "l_partkey": $$l.getField(1), "o_custkey": $$o.getField(1)}] [cardinality: 4785.13, op-cost: 0.0, total-cost: 19523.81]
               -- ASSIGN  |PARTITIONED|
-                select (gt($$l.getField(4), 10)) [cardinality: 4783.64, op-cost: 12016.29, total-cost: 19521.94]
+                select (gt($$l.getField(4), 10)) [cardinality: 4785.13, op-cost: 12018.16, total-cost: 19523.81]
                 -- STREAM_SELECT  |PARTITIONED|
                   project ([$$o, $$37, $$l]) [cardinality: 4779.14, op-cost: 0.0, total-cost: 6005.0]
                   -- STREAM_PROJECT  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.4.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.4.plan
index 490133a..4f0d55b 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.4.plan
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.4.plan
@@ -1,22 +1,22 @@
-distribute result [$$34] [cardinality: 1400.38, op-cost: 0.0, total-cost: 166792.72]
+distribute result [$$34] [cardinality: 1400.81, op-cost: 0.0, total-cost: 166845.41]
 -- DISTRIBUTE_RESULT  |PARTITIONED|
-  exchange [cardinality: 1400.38, op-cost: 0.0, total-cost: 166792.72]
+  exchange [cardinality: 1400.81, op-cost: 0.0, total-cost: 166845.41]
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-    distinct ([$$34]) [cardinality: 1400.38, op-cost: 73635.39, total-cost: 166792.72]
+    distinct ([$$34]) [cardinality: 1400.81, op-cost: 73660.8, total-cost: 166845.41]
     -- PRE_SORTED_DISTINCT_BY  |PARTITIONED|
-      exchange [cardinality: 5880.6, op-cost: 0.0, total-cost: 93157.33]
+      exchange [cardinality: 5882.42, op-cost: 0.0, total-cost: 93184.61]
       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-        order (ASC, $$34) [cardinality: 5880.6, op-cost: 73635.39, total-cost: 93157.33]
+        order (ASC, $$34) [cardinality: 5882.42, op-cost: 73660.8, total-cost: 93184.61]
         -- STABLE_SORT [$$34(ASC)]  |PARTITIONED|
-          exchange [cardinality: 5880.6, op-cost: 0.0, total-cost: 19521.94]
+          exchange [cardinality: 5882.42, op-cost: 0.0, total-cost: 19523.81]
           -- HASH_PARTITION_EXCHANGE [$$34]  |PARTITIONED|
-            project ([$$34]) [cardinality: 5880.6, op-cost: 0.0, total-cost: 19521.94]
+            project ([$$34]) [cardinality: 5882.42, op-cost: 0.0, total-cost: 19523.81]
             -- STREAM_PROJECT  |PARTITIONED|
-              assign [$$34] <- [{"l_orderkey": $$36, "o_custkey": $$o.getField(1)}] [cardinality: 5880.6, op-cost: 0.0, total-cost: 19521.94]
+              assign [$$34] <- [{"l_orderkey": $$36, "o_custkey": $$o.getField(1)}] [cardinality: 5882.42, op-cost: 0.0, total-cost: 19523.81]
               -- ASSIGN  |PARTITIONED|
-                project ([$$o, $$36]) [cardinality: 5880.6, op-cost: 0.0, total-cost: 19521.94]
+                project ([$$o, $$36]) [cardinality: 5882.42, op-cost: 0.0, total-cost: 19523.81]
                 -- STREAM_PROJECT  |PARTITIONED|
-                  select (gt($$l.getField(1), 5)) [cardinality: 5880.6, op-cost: 12016.29, total-cost: 19521.94]
+                  select (gt($$l.getField(1), 5)) [cardinality: 5882.42, op-cost: 12018.16, total-cost: 19523.81]
                   -- STREAM_SELECT  |PARTITIONED|
                     project ([$$o, $$36, $$l]) [cardinality: 5875.07, op-cost: 0.0, total-cost: 6005.0]
                     -- STREAM_PROJECT  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.5.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.5.plan
index 1526603..c981568 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.5.plan
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.5.plan
@@ -1,30 +1,30 @@
-distribute result [$$101] [cardinality: 1002.74, op-cost: 0.0, total-cost: 18037.6]
+distribute result [$$101] [cardinality: 1004.25, op-cost: 0.0, total-cost: 18055.78]
 -- DISTRIBUTE_RESULT  |PARTITIONED|
-  exchange [cardinality: 1002.74, op-cost: 0.0, total-cost: 18037.6]
+  exchange [cardinality: 1004.25, op-cost: 0.0, total-cost: 18055.78]
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-    project ([$$101]) [cardinality: 1002.74, op-cost: 0.0, total-cost: 18037.6]
+    project ([$$101]) [cardinality: 1004.25, op-cost: 0.0, total-cost: 18055.78]
     -- STREAM_PROJECT  |PARTITIONED|
-      assign [$$101] <- [{"$1": $$111}] [cardinality: 1002.74, op-cost: 0.0, total-cost: 18037.6]
+      assign [$$101] <- [{"$1": $$111}] [cardinality: 1004.25, op-cost: 0.0, total-cost: 18055.78]
       -- ASSIGN  |PARTITIONED|
-        project ([$$111]) [cardinality: 1002.74, op-cost: 0.0, total-cost: 18037.6]
+        project ([$$111]) [cardinality: 1004.25, op-cost: 0.0, total-cost: 18055.78]
         -- STREAM_PROJECT  |PARTITIONED|
-          exchange [cardinality: 1002.74, op-cost: 0.0, total-cost: 18037.6]
+          exchange [cardinality: 1004.25, op-cost: 0.0, total-cost: 18055.78]
           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
             group by ([$$l_partkey := $$115; $$o_orderstatus := $$116; $$c_nationkey := $$117]) decor ([]) {
                       aggregate [$$111] <- [sql-sum-serial($$114)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                       -- AGGREGATE  |LOCAL|
                         nested tuple source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                         -- NESTED_TUPLE_SOURCE  |LOCAL|
-                   } [cardinality: 1002.74, op-cost: 6016.3, total-cost: 18037.6]
+                   } [cardinality: 1004.25, op-cost: 6025.39, total-cost: 18055.78]
             -- EXTERNAL_GROUP_BY[$$115, $$116, $$117]  |PARTITIONED|
-              exchange [cardinality: 1002.74, op-cost: 0.0, total-cost: 12021.3]
+              exchange [cardinality: 1004.25, op-cost: 0.0, total-cost: 12030.39]
               -- HASH_PARTITION_EXCHANGE [$$115, $$116, $$117]  |PARTITIONED|
                 group by ([$$115 := $$102; $$116 := $$103; $$117 := $$104]) decor ([]) {
                           aggregate [$$114] <- [sql-count-serial(1)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                           -- AGGREGATE  |LOCAL|
                             nested tuple source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                             -- NESTED_TUPLE_SOURCE  |LOCAL|
-                       } [cardinality: 1002.74, op-cost: 6016.3, total-cost: 12021.3]
+                       } [cardinality: 1004.25, op-cost: 6025.39, total-cost: 12030.39]
                 -- EXTERNAL_GROUP_BY[$$102, $$103, $$104]  |PARTITIONED|
                   exchange [cardinality: 6010.65, op-cost: 0.0, total-cost: 6005.0]
                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -44,7 +44,7 @@
                                 -- STREAM_PROJECT  |PARTITIONED|
                                   exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                    join (eq($$109, $$108)) [cardinality: 1501.41, op-cost: 2101.41, total-cost: 4351.41]
+                                    join (eq($$109, $$108)) [cardinality: 1503.21, op-cost: 2101.41, total-cost: 4351.41]
                                     -- HYBRID_HASH_JOIN [$$109][$$108]  |PARTITIONED|
                                       exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.6.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.6.plan
index 792ea6b..e286ea8 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.6.plan
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.6.plan
@@ -1,38 +1,38 @@
-distribute result [$$101] [cardinality: 1002.74, op-cost: 0.0, total-cost: 34405.95]
+distribute result [$$101] [cardinality: 1004.25, op-cost: 0.0, total-cost: 34447.61]
 -- DISTRIBUTE_RESULT  |PARTITIONED|
-  exchange [cardinality: 1002.74, op-cost: 0.0, total-cost: 34405.95]
+  exchange [cardinality: 1004.25, op-cost: 0.0, total-cost: 34447.61]
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-    project ([$$101]) [cardinality: 1002.74, op-cost: 0.0, total-cost: 34405.95]
+    project ([$$101]) [cardinality: 1004.25, op-cost: 0.0, total-cost: 34447.61]
     -- STREAM_PROJECT  |PARTITIONED|
-      assign [$$101] <- [{"$1": $$111}] [cardinality: 1002.74, op-cost: 0.0, total-cost: 34405.95]
+      assign [$$101] <- [{"$1": $$111}] [cardinality: 1004.25, op-cost: 0.0, total-cost: 34447.61]
       -- ASSIGN  |PARTITIONED|
-        project ([$$111]) [cardinality: 1002.74, op-cost: 0.0, total-cost: 34405.95]
+        project ([$$111]) [cardinality: 1004.25, op-cost: 0.0, total-cost: 34447.61]
         -- STREAM_PROJECT  |PARTITIONED|
-          exchange [cardinality: 1002.74, op-cost: 0.0, total-cost: 34405.95]
+          exchange [cardinality: 1004.25, op-cost: 0.0, total-cost: 34447.61]
           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
             group by ([$$l_linenumber := $$115; $$o_orderstatus := $$116; $$c_nationkey := $$117]) decor ([]) {
                       aggregate [$$111] <- [sql-sum-serial($$114)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                       -- AGGREGATE  |LOCAL|
                         nested tuple source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                         -- NESTED_TUPLE_SOURCE  |LOCAL|
-                   } [cardinality: 1002.74, op-cost: 6016.3, total-cost: 34405.95]
+                   } [cardinality: 1004.25, op-cost: 6025.39, total-cost: 34447.61]
             -- EXTERNAL_GROUP_BY[$$115, $$116, $$117]  |PARTITIONED|
-              exchange [cardinality: 1002.74, op-cost: 0.0, total-cost: 28389.65]
+              exchange [cardinality: 1004.25, op-cost: 0.0, total-cost: 28422.22]
               -- HASH_PARTITION_EXCHANGE [$$115, $$116, $$117]  |PARTITIONED|
                 group by ([$$115 := $$106; $$116 := $$103; $$117 := $$104]) decor ([]) {
                           aggregate [$$114] <- [sql-count-serial(1)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                           -- AGGREGATE  |LOCAL|
                             nested tuple source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                             -- NESTED_TUPLE_SOURCE  |LOCAL|
-                       } [cardinality: 1002.74, op-cost: 6016.3, total-cost: 28389.65]
+                       } [cardinality: 1004.25, op-cost: 6025.39, total-cost: 28422.22]
                 -- EXTERNAL_GROUP_BY[$$106, $$103, $$104]  |PARTITIONED|
-                  exchange [cardinality: 6016.3, op-cost: 0.0, total-cost: 22373.35]
+                  exchange [cardinality: 6025.39, op-cost: 0.0, total-cost: 22396.83]
                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                    project ([$$106, $$103, $$104]) [cardinality: 6016.3, op-cost: 0.0, total-cost: 22373.35]
+                    project ([$$106, $$103, $$104]) [cardinality: 6025.39, op-cost: 0.0, total-cost: 22396.83]
                     -- STREAM_PROJECT  |PARTITIONED|
-                      exchange [cardinality: 6016.3, op-cost: 0.0, total-cost: 22373.35]
+                      exchange [cardinality: 6025.39, op-cost: 0.0, total-cost: 22396.83]
                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                        unnest-map [$$105, $$106, $$l] <- index-search("LineItem", 0, "Default", "tpch", "LineItem", true, true, 1, $$107, 1, $$107, true, true, true) [cardinality: 6016.3, op-cost: 12016.29, total-cost: 22373.35]
+                        unnest-map [$$105, $$106, $$l] <- index-search("LineItem", 0, "Default", "tpch", "LineItem", true, true, 1, $$107, 1, $$107, true, true, true) [cardinality: 6025.39, op-cost: 12032.57, total-cost: 22396.83]
                         -- BTREE_SEARCH  |PARTITIONED|
                           exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                           -- BROADCAST_EXCHANGE  |PARTITIONED|
@@ -40,7 +40,7 @@
                             -- STREAM_PROJECT  |PARTITIONED|
                               exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                join (eq($$109, $$108)) [cardinality: 1501.41, op-cost: 2101.41, total-cost: 4351.41]
+                                join (eq($$109, $$108)) [cardinality: 1503.21, op-cost: 2101.41, total-cost: 4351.41]
                                 -- HYBRID_HASH_JOIN [$$109][$$108]  |PARTITIONED|
                                   exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.7.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.7.plan
index af17e0f..ccdd8c9 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.7.plan
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.7.plan
@@ -1,38 +1,38 @@
-distribute result [$$101] [cardinality: 1002.74, op-cost: 0.0, total-cost: 34405.95]
+distribute result [$$101] [cardinality: 1004.25, op-cost: 0.0, total-cost: 34447.61]
 -- DISTRIBUTE_RESULT  |PARTITIONED|
-  exchange [cardinality: 1002.74, op-cost: 0.0, total-cost: 34405.95]
+  exchange [cardinality: 1004.25, op-cost: 0.0, total-cost: 34447.61]
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-    project ([$$101]) [cardinality: 1002.74, op-cost: 0.0, total-cost: 34405.95]
+    project ([$$101]) [cardinality: 1004.25, op-cost: 0.0, total-cost: 34447.61]
     -- STREAM_PROJECT  |PARTITIONED|
-      assign [$$101] <- [{"$1": $$111}] [cardinality: 1002.74, op-cost: 0.0, total-cost: 34405.95]
+      assign [$$101] <- [{"$1": $$111}] [cardinality: 1004.25, op-cost: 0.0, total-cost: 34447.61]
       -- ASSIGN  |PARTITIONED|
-        project ([$$111]) [cardinality: 1002.74, op-cost: 0.0, total-cost: 34405.95]
+        project ([$$111]) [cardinality: 1004.25, op-cost: 0.0, total-cost: 34447.61]
         -- STREAM_PROJECT  |PARTITIONED|
-          exchange [cardinality: 1002.74, op-cost: 0.0, total-cost: 34405.95]
+          exchange [cardinality: 1004.25, op-cost: 0.0, total-cost: 34447.61]
           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
             group by ([$$l_linenumber := $$115; $$c_nationkey := $$116; $$o_orderstatus := $$117]) decor ([]) {
                       aggregate [$$111] <- [sql-sum-serial($$114)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                       -- AGGREGATE  |LOCAL|
                         nested tuple source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                         -- NESTED_TUPLE_SOURCE  |LOCAL|
-                   } [cardinality: 1002.74, op-cost: 6016.3, total-cost: 34405.95]
+                   } [cardinality: 1004.25, op-cost: 6025.39, total-cost: 34447.61]
             -- EXTERNAL_GROUP_BY[$$115, $$116, $$117]  |PARTITIONED|
-              exchange [cardinality: 1002.74, op-cost: 0.0, total-cost: 28389.65]
+              exchange [cardinality: 1004.25, op-cost: 0.0, total-cost: 28422.22]
               -- HASH_PARTITION_EXCHANGE [$$115, $$116, $$117]  |PARTITIONED|
                 group by ([$$115 := $$106; $$116 := $$103; $$117 := $$104]) decor ([]) {
                           aggregate [$$114] <- [sql-count-serial(1)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                           -- AGGREGATE  |LOCAL|
                             nested tuple source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                             -- NESTED_TUPLE_SOURCE  |LOCAL|
-                       } [cardinality: 1002.74, op-cost: 6016.3, total-cost: 28389.65]
+                       } [cardinality: 1004.25, op-cost: 6025.39, total-cost: 28422.22]
                 -- EXTERNAL_GROUP_BY[$$106, $$103, $$104]  |PARTITIONED|
-                  exchange [cardinality: 6016.3, op-cost: 0.0, total-cost: 22373.35]
+                  exchange [cardinality: 6025.39, op-cost: 0.0, total-cost: 22396.83]
                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                    project ([$$106, $$103, $$104]) [cardinality: 6016.3, op-cost: 0.0, total-cost: 22373.35]
+                    project ([$$106, $$103, $$104]) [cardinality: 6025.39, op-cost: 0.0, total-cost: 22396.83]
                     -- STREAM_PROJECT  |PARTITIONED|
-                      exchange [cardinality: 6016.3, op-cost: 0.0, total-cost: 22373.35]
+                      exchange [cardinality: 6025.39, op-cost: 0.0, total-cost: 22396.83]
                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                        unnest-map [$$105, $$106, $$l] <- index-search("LineItem", 0, "Default", "tpch", "LineItem", true, true, 1, $$107, 1, $$107, true, true, true) [cardinality: 6016.3, op-cost: 12016.29, total-cost: 22373.35]
+                        unnest-map [$$105, $$106, $$l] <- index-search("LineItem", 0, "Default", "tpch", "LineItem", true, true, 1, $$107, 1, $$107, true, true, true) [cardinality: 6025.39, op-cost: 12032.57, total-cost: 22396.83]
                         -- BTREE_SEARCH  |PARTITIONED|
                           exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                           -- BROADCAST_EXCHANGE  |PARTITIONED|
@@ -40,7 +40,7 @@
                             -- STREAM_PROJECT  |PARTITIONED|
                               exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                join (eq($$109, $$108)) [cardinality: 1501.41, op-cost: 2101.41, total-cost: 4351.41]
+                                join (eq($$109, $$108)) [cardinality: 1503.21, op-cost: 2101.41, total-cost: 4351.41]
                                 -- HYBRID_HASH_JOIN [$$109][$$108]  |PARTITIONED|
                                   exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.8.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.8.plan
index 7db992d..c769ae2 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.8.plan
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.8.plan
@@ -1,36 +1,36 @@
-distribute result [$$119] [cardinality: 25.0, op-cost: 0.0, total-cost: 7087.0]
+distribute result [$$119] [cardinality: 25.0, op-cost: 0.0, total-cost: 9097.68]
 -- DISTRIBUTE_RESULT  |PARTITIONED|
-  exchange [cardinality: 25.0, op-cost: 0.0, total-cost: 7087.0]
+  exchange [cardinality: 25.0, op-cost: 0.0, total-cost: 9097.68]
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-    project ([$$119]) [cardinality: 25.0, op-cost: 0.0, total-cost: 7087.0]
+    project ([$$119]) [cardinality: 25.0, op-cost: 0.0, total-cost: 9097.68]
     -- STREAM_PROJECT  |PARTITIONED|
-      assign [$$119] <- [{"n_name": $$n_name, "revenue": $$132}] [cardinality: 25.0, op-cost: 0.0, total-cost: 7087.0]
+      assign [$$119] <- [{"n_name": $$n_name, "revenue": $$132}] [cardinality: 25.0, op-cost: 0.0, total-cost: 9097.68]
       -- ASSIGN  |PARTITIONED|
-        exchange [cardinality: 25.0, op-cost: 0.0, total-cost: 7087.0]
+        exchange [cardinality: 25.0, op-cost: 0.0, total-cost: 9097.68]
         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
           group by ([$$n_name := $$142]) decor ([]) {
                     aggregate [$$132] <- [global-sql-sum-serial($$141)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                     -- AGGREGATE  |LOCAL|
                       nested tuple source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                       -- NESTED_TUPLE_SOURCE  |LOCAL|
-                 } [cardinality: 25.0, op-cost: 99.52, total-cost: 7087.0]
+                 } [cardinality: 25.0, op-cost: 64.54, total-cost: 9097.68]
           -- EXTERNAL_GROUP_BY[$$142]  |PARTITIONED|
-            exchange [cardinality: 25.0, op-cost: 0.0, total-cost: 6987.48]
+            exchange [cardinality: 25.0, op-cost: 0.0, total-cost: 9033.14]
             -- HASH_PARTITION_EXCHANGE [$$142]  |PARTITIONED|
               group by ([$$142 := $$120]) decor ([]) {
                         aggregate [$$141] <- [local-sql-sum-serial(numeric-multiply($$139, numeric-subtract(1, $$140)))] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                         -- AGGREGATE  |LOCAL|
                           nested tuple source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                           -- NESTED_TUPLE_SOURCE  |LOCAL|
-                     } [cardinality: 25.0, op-cost: 99.52, total-cost: 6987.48]
+                     } [cardinality: 25.0, op-cost: 64.54, total-cost: 9033.14]
               -- EXTERNAL_GROUP_BY[$$120]  |PARTITIONED|
-                exchange [cardinality: 99.52, op-cost: 0.0, total-cost: 6887.96]
+                exchange [cardinality: 64.54, op-cost: 0.0, total-cost: 8968.6]
                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                  project ([$$139, $$140, $$120]) [cardinality: 99.52, op-cost: 0.0, total-cost: 6887.96]
+                  project ([$$139, $$140, $$120]) [cardinality: 64.54, op-cost: 0.0, total-cost: 8968.6]
                   -- STREAM_PROJECT  |PARTITIONED|
-                    exchange [cardinality: 99.52, op-cost: 0.0, total-cost: 6887.96]
+                    exchange [cardinality: 64.54, op-cost: 0.0, total-cost: 8968.6]
                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                      join (and(eq($$130, $$128), eq($$133, $$127))) [cardinality: 99.52, op-cost: 1035.18, total-cost: 6887.96]
+                      join (and(eq($$130, $$128), eq($$133, $$127))) [cardinality: 64.54, op-cost: 1554.23, total-cost: 8968.6]
                       -- HYBRID_HASH_JOIN [$$128, $$133][$$130, $$127]  |PARTITIONED|
                         exchange [cardinality: 6010.65, op-cost: 0.0, total-cost: 6005.0]
                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -50,7 +50,7 @@
                                       -- STREAM_PROJECT  |PARTITIONED|
                                         exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                          join (eq($$123, $$136)) [cardinality: 248.35, op-cost: 398.35, total-cost: 2821.71]
+                                          join (eq($$123, $$136)) [cardinality: 377.77, op-cost: 402.2, total-cost: 2829.4]
                                           -- HYBRID_HASH_JOIN [$$123][$$136]  |PARTITIONED|
                                             exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                             -- HASH_PARTITION_EXCHANGE [$$123]  |PARTITIONED|
@@ -58,7 +58,7 @@
                                               -- STREAM_PROJECT  |PARTITIONED|
                                                 exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                  join (eq($$131, $$128)) [cardinality: 150.0, op-cost: 175.0, total-cost: 525.0]
+                                                  join (eq($$131, $$128)) [cardinality: 153.85, op-cost: 175.0, total-cost: 525.0]
                                                   -- HYBRID_HASH_JOIN [$$131][$$128]  |PARTITIONED|
                                                     exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                                     -- HASH_PARTITION_EXCHANGE [$$131]  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/pushdown/field-access-pushdown/field-access-pushdown.006.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/pushdown/field-access-pushdown/field-access-pushdown.006.plan
index 2a54fe3..20f5102 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/pushdown/field-access-pushdown/field-access-pushdown.006.plan
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/pushdown/field-access-pushdown/field-access-pushdown.006.plan
@@ -1,22 +1,22 @@
-distribute result [$$30] [cardinality: 7.0, op-cost: 0.0, total-cost: 61.65]
+distribute result [$$30] [cardinality: 8.17, op-cost: 0.0, total-cost: 66.76]
 -- DISTRIBUTE_RESULT  |PARTITIONED|
-  exchange [cardinality: 7.0, op-cost: 0.0, total-cost: 61.65]
+  exchange [cardinality: 8.17, op-cost: 0.0, total-cost: 66.76]
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-    project ([$$30]) [cardinality: 7.0, op-cost: 0.0, total-cost: 61.65]
+    project ([$$30]) [cardinality: 8.17, op-cost: 0.0, total-cost: 66.76]
     -- STREAM_PROJECT  |PARTITIONED|
-      assign [$$30] <- [{"p1": $$p1, "id": $$34}] [cardinality: 7.0, op-cost: 0.0, total-cost: 61.65]
+      assign [$$30] <- [{"p1": $$p1, "id": $$34}] [cardinality: 8.17, op-cost: 0.0, total-cost: 66.76]
       -- ASSIGN  |PARTITIONED|
-        exchange [cardinality: 7.0, op-cost: 0.0, total-cost: 61.65]
+        exchange [cardinality: 8.17, op-cost: 0.0, total-cost: 66.76]
         -- SORT_MERGE_EXCHANGE [$$34(ASC) ]  |PARTITIONED|
-          order (ASC, $$34) [cardinality: 7.0, op-cost: 19.65, total-cost: 61.65]
+          order (ASC, $$34) [cardinality: 8.17, op-cost: 24.76, total-cost: 66.76]
           -- STABLE_SORT [$$34(ASC)]  |PARTITIONED|
-            exchange [cardinality: 7.0, op-cost: 0.0, total-cost: 42.0]
+            exchange [cardinality: 8.17, op-cost: 0.0, total-cost: 42.0]
             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-              project ([$$p1, $$34]) [cardinality: 7.0, op-cost: 0.0, total-cost: 42.0]
+              project ([$$p1, $$34]) [cardinality: 8.17, op-cost: 0.0, total-cost: 42.0]
               -- STREAM_PROJECT  |PARTITIONED|
-                exchange [cardinality: 7.0, op-cost: 0.0, total-cost: 42.0]
+                exchange [cardinality: 8.17, op-cost: 0.0, total-cost: 42.0]
                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                  join (eq($$33, $$34)) [cardinality: 7.0, op-cost: 14.0, total-cost: 42.0]
+                  join (eq($$33, $$34)) [cardinality: 8.17, op-cost: 14.0, total-cost: 42.0]
                   -- HYBRID_HASH_JOIN [$$33][$$34]  |PARTITIONED|
                     exchange [cardinality: 7.0, op-cost: 7.0, total-cost: 14.0]
                     -- HASH_PARTITION_EXCHANGE [$$33]  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/pushdown/field-access-pushdown/field-access-pushdown.008.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/pushdown/field-access-pushdown/field-access-pushdown.008.plan
index d1f713d..2bf5a05 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/pushdown/field-access-pushdown/field-access-pushdown.008.plan
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/pushdown/field-access-pushdown/field-access-pushdown.008.plan
@@ -1,24 +1,24 @@
-distribute result [$$31] [cardinality: 8.0, op-cost: 0.0, total-cost: 69.0]
+distribute result [$$31] [cardinality: 7.11, op-cost: 0.0, total-cost: 65.12]
 -- DISTRIBUTE_RESULT  |PARTITIONED|
-  exchange [cardinality: 8.0, op-cost: 0.0, total-cost: 69.0]
+  exchange [cardinality: 7.11, op-cost: 0.0, total-cost: 65.12]
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-    project ([$$31]) [cardinality: 8.0, op-cost: 0.0, total-cost: 69.0]
+    project ([$$31]) [cardinality: 7.11, op-cost: 0.0, total-cost: 65.12]
     -- STREAM_PROJECT  |PARTITIONED|
-      assign [$$31] <- [{"age": $$38, "name": $$39}] [cardinality: 8.0, op-cost: 0.0, total-cost: 69.0]
+      assign [$$31] <- [{"age": $$38, "name": $$39}] [cardinality: 7.11, op-cost: 0.0, total-cost: 65.12]
       -- ASSIGN  |PARTITIONED|
-        project ([$$38, $$39]) [cardinality: 8.0, op-cost: 0.0, total-cost: 69.0]
+        project ([$$38, $$39]) [cardinality: 7.11, op-cost: 0.0, total-cost: 65.12]
         -- STREAM_PROJECT  |PARTITIONED|
-          exchange [cardinality: 8.0, op-cost: 0.0, total-cost: 69.0]
+          exchange [cardinality: 7.11, op-cost: 0.0, total-cost: 65.12]
           -- SORT_MERGE_EXCHANGE [$$34(ASC) ]  |PARTITIONED|
-            order (ASC, $$34) [cardinality: 8.0, op-cost: 24.0, total-cost: 69.0]
+            order (ASC, $$34) [cardinality: 7.11, op-cost: 20.12, total-cost: 65.12]
             -- STABLE_SORT [$$34(ASC)]  |PARTITIONED|
-              exchange [cardinality: 8.0, op-cost: 0.0, total-cost: 45.0]
+              exchange [cardinality: 7.11, op-cost: 0.0, total-cost: 45.0]
               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                project ([$$38, $$39, $$34]) [cardinality: 8.0, op-cost: 0.0, total-cost: 45.0]
+                project ([$$38, $$39, $$34]) [cardinality: 7.11, op-cost: 0.0, total-cost: 45.0]
                 -- STREAM_PROJECT  |PARTITIONED|
-                  exchange [cardinality: 8.0, op-cost: 0.0, total-cost: 45.0]
+                  exchange [cardinality: 7.11, op-cost: 0.0, total-cost: 45.0]
                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                    join (eq($$33, $$34)) [cardinality: 8.0, op-cost: 15.0, total-cost: 45.0]
+                    join (eq($$33, $$34)) [cardinality: 7.11, op-cost: 15.0, total-cost: 45.0]
                     -- HYBRID_HASH_JOIN [$$34][$$33]  |PARTITIONED|
                       exchange [cardinality: 8.0, op-cost: 8.0, total-cost: 16.0]
                       -- HASH_PARTITION_EXCHANGE [$$34]  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/pushdown/other-pushdowns/other-pushdowns.004.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/pushdown/other-pushdowns/other-pushdowns.004.plan
index f003312..d5b5f9a 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/pushdown/other-pushdowns/other-pushdowns.004.plan
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/pushdown/other-pushdowns/other-pushdowns.004.plan
@@ -1,26 +1,26 @@
-distribute result [$$61] [cardinality: 2.1, op-cost: 0.0, total-cost: 12.0]
+distribute result [$$61] [cardinality: 2.67, op-cost: 0.0, total-cost: 12.0]
 -- DISTRIBUTE_RESULT  |UNPARTITIONED|
-  exchange [cardinality: 2.1, op-cost: 0.0, total-cost: 12.0]
+  exchange [cardinality: 2.67, op-cost: 0.0, total-cost: 12.0]
   -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
-    project ([$$61]) [cardinality: 2.1, op-cost: 0.0, total-cost: 12.0]
+    project ([$$61]) [cardinality: 2.67, op-cost: 0.0, total-cost: 12.0]
     -- STREAM_PROJECT  |UNPARTITIONED|
-      assign [$$61] <- [{"$1": $$66, "$2": $$67}] [cardinality: 2.1, op-cost: 0.0, total-cost: 12.0]
+      assign [$$61] <- [{"$1": $$66, "$2": $$67}] [cardinality: 2.67, op-cost: 0.0, total-cost: 12.0]
       -- ASSIGN  |UNPARTITIONED|
-        aggregate [$$66, $$67] <- [agg-global-sql-sum($$70), agg-global-sql-sum($$71)] [cardinality: 2.1, op-cost: 0.0, total-cost: 12.0]
+        aggregate [$$66, $$67] <- [agg-global-sql-sum($$70), agg-global-sql-sum($$71)] [cardinality: 2.67, op-cost: 0.0, total-cost: 12.0]
         -- AGGREGATE  |UNPARTITIONED|
-          exchange [cardinality: 2.1, op-cost: 0.0, total-cost: 12.0]
+          exchange [cardinality: 2.67, op-cost: 0.0, total-cost: 12.0]
           -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
-            aggregate [$$70, $$71] <- [agg-local-sql-sum($$54), agg-local-sql-sum($$59)] [cardinality: 2.1, op-cost: 0.0, total-cost: 12.0]
+            aggregate [$$70, $$71] <- [agg-local-sql-sum($$54), agg-local-sql-sum($$59)] [cardinality: 2.67, op-cost: 0.0, total-cost: 12.0]
             -- AGGREGATE  |PARTITIONED|
-              project ([$$54, $$59]) [cardinality: 2.1, op-cost: 0.0, total-cost: 12.0]
+              project ([$$54, $$59]) [cardinality: 2.67, op-cost: 0.0, total-cost: 12.0]
               -- STREAM_PROJECT  |PARTITIONED|
-                assign [$$59, $$54] <- [object-length($$p2), object-length($$p1)] [cardinality: 2.1, op-cost: 0.0, total-cost: 12.0]
+                assign [$$59, $$54] <- [object-length($$p2), object-length($$p1)] [cardinality: 2.67, op-cost: 0.0, total-cost: 12.0]
                 -- ASSIGN  |PARTITIONED|
-                  project ([$$p1, $$p2]) [cardinality: 2.1, op-cost: 0.0, total-cost: 12.0]
+                  project ([$$p1, $$p2]) [cardinality: 2.67, op-cost: 0.0, total-cost: 12.0]
                   -- STREAM_PROJECT  |PARTITIONED|
-                    exchange [cardinality: 2.1, op-cost: 0.0, total-cost: 12.0]
+                    exchange [cardinality: 2.67, op-cost: 0.0, total-cost: 12.0]
                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                      join (eq($$63, $$62)) [cardinality: 2.1, op-cost: 4.0, total-cost: 12.0]
+                      join (eq($$63, $$62)) [cardinality: 2.67, op-cost: 4.0, total-cost: 12.0]
                       -- HYBRID_HASH_JOIN [$$62][$$63]  |PARTITIONED|
                         exchange [cardinality: 2.0, op-cost: 2.0, total-cost: 4.0]
                         -- HASH_PARTITION_EXCHANGE [$$62]  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/pushdown/other-pushdowns/other-pushdowns.015.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/pushdown/other-pushdowns/other-pushdowns.015.plan
index 69b9fe2..dbbed89 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/pushdown/other-pushdowns/other-pushdowns.015.plan
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/pushdown/other-pushdowns/other-pushdowns.015.plan
@@ -1,22 +1,22 @@
-distribute result [$$52] [cardinality: 2.1, op-cost: 0.0, total-cost: 12.0]
+distribute result [$$52] [cardinality: 2.67, op-cost: 0.0, total-cost: 12.0]
 -- DISTRIBUTE_RESULT  |UNPARTITIONED|
-  exchange [cardinality: 2.1, op-cost: 0.0, total-cost: 12.0]
+  exchange [cardinality: 2.67, op-cost: 0.0, total-cost: 12.0]
   -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
-    project ([$$52]) [cardinality: 2.1, op-cost: 0.0, total-cost: 12.0]
+    project ([$$52]) [cardinality: 2.67, op-cost: 0.0, total-cost: 12.0]
     -- STREAM_PROJECT  |UNPARTITIONED|
-      assign [$$52] <- [{"$1": $$57}] [cardinality: 2.1, op-cost: 0.0, total-cost: 12.0]
+      assign [$$52] <- [{"$1": $$57}] [cardinality: 2.67, op-cost: 0.0, total-cost: 12.0]
       -- ASSIGN  |UNPARTITIONED|
-        aggregate [$$57] <- [agg-sql-sum($$60)] [cardinality: 2.1, op-cost: 0.0, total-cost: 12.0]
+        aggregate [$$57] <- [agg-sql-sum($$60)] [cardinality: 2.67, op-cost: 0.0, total-cost: 12.0]
         -- AGGREGATE  |UNPARTITIONED|
-          exchange [cardinality: 2.1, op-cost: 0.0, total-cost: 12.0]
+          exchange [cardinality: 2.67, op-cost: 0.0, total-cost: 12.0]
           -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
-            aggregate [$$60] <- [agg-sql-count($$50)] [cardinality: 2.1, op-cost: 0.0, total-cost: 12.0]
+            aggregate [$$60] <- [agg-sql-count($$50)] [cardinality: 2.67, op-cost: 0.0, total-cost: 12.0]
             -- AGGREGATE  |PARTITIONED|
-              project ([$$50]) [cardinality: 2.1, op-cost: 0.0, total-cost: 12.0]
+              project ([$$50]) [cardinality: 2.67, op-cost: 0.0, total-cost: 12.0]
               -- STREAM_PROJECT  |PARTITIONED|
-                exchange [cardinality: 2.1, op-cost: 0.0, total-cost: 12.0]
+                exchange [cardinality: 2.67, op-cost: 0.0, total-cost: 12.0]
                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                  join (eq($$55, $$56)) [cardinality: 2.1, op-cost: 4.0, total-cost: 12.0]
+                  join (eq($$55, $$56)) [cardinality: 2.67, op-cost: 4.0, total-cost: 12.0]
                   -- HYBRID_HASH_JOIN [$$55][$$56]  |PARTITIONED|
                     exchange [cardinality: 2.0, op-cost: 2.0, total-cost: 4.0]
                     -- HASH_PARTITION_EXCHANGE [$$55]  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/join/hash-join-with-redundant-variable/hash-join-with-redundant-variable.04.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/join/hash-join-with-redundant-variable/hash-join-with-redundant-variable.04.plan
index 65cdc79..40fb0c0 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/join/hash-join-with-redundant-variable/hash-join-with-redundant-variable.04.plan
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/join/hash-join-with-redundant-variable/hash-join-with-redundant-variable.04.plan
@@ -1,64 +1,66 @@
-distribute result [$$51] [cardinality: 150.0, op-cost: 0.0, total-cost: 1689.32]
+distribute result [$$51] [cardinality: 62.1, op-cost: 0.0, total-cost: 852.21]
 -- DISTRIBUTE_RESULT  |PARTITIONED|
-  exchange [cardinality: 150.0, op-cost: 0.0, total-cost: 1689.32]
+  exchange [cardinality: 62.1, op-cost: 0.0, total-cost: 852.21]
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-    project ([$$51]) [cardinality: 150.0, op-cost: 0.0, total-cost: 1689.32]
+    project ([$$51]) [cardinality: 62.1, op-cost: 0.0, total-cost: 852.21]
     -- STREAM_PROJECT  |PARTITIONED|
-      assign [$$51] <- [{"n_nationkey": $$58, "s_nationkey": $$56, "c_nationkey": $$55}] [cardinality: 150.0, op-cost: 0.0, total-cost: 1689.32]
+      assign [$$51] <- [{"n_nationkey": $$58, "s_nationkey": $$56, "c_nationkey": $$55}] [cardinality: 62.1, op-cost: 0.0, total-cost: 852.21]
       -- ASSIGN  |PARTITIONED|
-        exchange [cardinality: 150.0, op-cost: 0.0, total-cost: 1689.32]
+        exchange [cardinality: 62.1, op-cost: 0.0, total-cost: 852.21]
         -- SORT_MERGE_EXCHANGE [$$58(ASC), $$56(ASC), $$55(ASC) ]  |PARTITIONED|
-          order (ASC, $$58) (ASC, $$56) (ASC, $$55) [cardinality: 150.0, op-cost: 1084.32, total-cost: 1689.32]
+          order (ASC, $$58) (ASC, $$56) (ASC, $$55) [cardinality: 62.1, op-cost: 369.9, total-cost: 852.21]
           -- STABLE_SORT [$$58(ASC), $$56(ASC), $$55(ASC)]  |PARTITIONED|
-            exchange [cardinality: 150.0, op-cost: 0.0, total-cost: 605.0]
+            exchange [cardinality: 62.1, op-cost: 0.0, total-cost: 482.31]
             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-              join (eq($$55, $$58)) [cardinality: 150.0, op-cost: 175.0, total-cost: 605.0]
-              -- HYBRID_HASH_JOIN [$$55][$$58]  |PARTITIONED|
-                exchange [cardinality: 150.0, op-cost: 150.0, total-cost: 300.0]
-                -- HASH_PARTITION_EXCHANGE [$$55]  |PARTITIONED|
-                  project ([$$55]) [cardinality: 150.0, op-cost: 0.0, total-cost: 150.0]
-                  -- STREAM_PROJECT  |PARTITIONED|
-                    assign [$$55] <- [$$c.getField(3)] [cardinality: 150.0, op-cost: 0.0, total-cost: 150.0]
-                    -- ASSIGN  |PARTITIONED|
-                      project ([$$c]) [cardinality: 150.0, op-cost: 0.0, total-cost: 150.0]
-                      -- STREAM_PROJECT  |PARTITIONED|
-                        exchange [cardinality: 150.0, op-cost: 150.0, total-cost: 300.0]
-                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                          data-scan []<-[$$60, $$c] <- tpch.Customer [cardinality: 150.0, op-cost: 150.0, total-cost: 150.0]
-                          -- DATASOURCE_SCAN  |PARTITIONED|
-                            exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
-                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
-                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                exchange [cardinality: 25.0, op-cost: 25.0, total-cost: 130.0]
+              project ([$$58, $$56, $$55]) [cardinality: 62.1, op-cost: 0.0, total-cost: 482.31]
+              -- STREAM_PROJECT  |PARTITIONED|
+                exchange [cardinality: 62.1, op-cost: 0.0, total-cost: 482.31]
                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                  join (eq($$56, $$58)) [cardinality: 25.0, op-cost: 35.0, total-cost: 105.0]
-                  -- HYBRID_HASH_JOIN [$$58][$$56]  |PARTITIONED|
-                    exchange [cardinality: 25.0, op-cost: 25.0, total-cost: 50.0]
-                    -- HASH_PARTITION_EXCHANGE [$$58]  |PARTITIONED|
-                      project ([$$58]) [cardinality: 25.0, op-cost: 0.0, total-cost: 25.0]
+                  join (and(eq($$55, $$58), eq($$56, $$66))) [cardinality: 62.1, op-cost: 191.03, total-cost: 482.31]
+                  -- HYBRID_HASH_JOIN [$$55, $$66][$$58, $$56]  |PARTITIONED|
+                    exchange [cardinality: 150.0, op-cost: 0.0, total-cost: 150.0]
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      assign [$$66] <- [$$55] [cardinality: 150.0, op-cost: 0.0, total-cost: 150.0]
+                      -- ASSIGN  |PARTITIONED|
+                        project ([$$55]) [cardinality: 150.0, op-cost: 0.0, total-cost: 150.0]
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          assign [$$55] <- [$$c.getField(3)] [cardinality: 150.0, op-cost: 0.0, total-cost: 150.0]
+                          -- ASSIGN  |PARTITIONED|
+                            project ([$$c]) [cardinality: 150.0, op-cost: 0.0, total-cost: 150.0]
+                            -- STREAM_PROJECT  |PARTITIONED|
+                              exchange [cardinality: 150.0, op-cost: 0.0, total-cost: 150.0]
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                data-scan []<-[$$60, $$c] <- tpch.Customer [cardinality: 150.0, op-cost: 150.0, total-cost: 150.0]
+                                -- DATASOURCE_SCAN  |PARTITIONED|
+                                  exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                    exchange [cardinality: 10.26, op-cost: 41.03, total-cost: 141.29]
+                    -- BROADCAST_EXCHANGE  |PARTITIONED|
+                      project ([$$56, $$58]) [cardinality: 10.26, op-cost: 0.0, total-cost: 100.26]
                       -- STREAM_PROJECT  |PARTITIONED|
-                        exchange [cardinality: 25.0, op-cost: 25.0, total-cost: 50.0]
+                        exchange [cardinality: 10.26, op-cost: 41.03, total-cost: 141.29]
                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                          data-scan []<-[$$58, $$n] <- tpch.Nation [cardinality: 25.0, op-cost: 25.0, total-cost: 25.0]
-                          -- DATASOURCE_SCAN  |PARTITIONED|
+                          unnest-map [$$58, $$n] <- index-search("Nation", 0, "Default", "tpch", "Nation", true, true, 1, $$56, 1, $$56, true, true, true) [cardinality: 10.26, op-cost: 50.26, total-cost: 100.26]
+                          -- BTREE_SEARCH  |PARTITIONED|
                             exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
-                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                    exchange [cardinality: 10.0, op-cost: 10.0, total-cost: 20.0]
-                    -- HASH_PARTITION_EXCHANGE [$$56]  |PARTITIONED|
-                      project ([$$56]) [cardinality: 10.0, op-cost: 0.0, total-cost: 10.0]
-                      -- STREAM_PROJECT  |PARTITIONED|
-                        assign [$$56] <- [$$s.getField(3)] [cardinality: 10.0, op-cost: 0.0, total-cost: 10.0]
-                        -- ASSIGN  |PARTITIONED|
-                          project ([$$s]) [cardinality: 10.0, op-cost: 0.0, total-cost: 10.0]
-                          -- STREAM_PROJECT  |PARTITIONED|
-                            exchange [cardinality: 10.0, op-cost: 10.0, total-cost: 20.0]
-                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              data-scan []<-[$$59, $$s] <- tpch.Supplier [cardinality: 10.0, op-cost: 10.0, total-cost: 10.0]
-                              -- DATASOURCE_SCAN  |PARTITIONED|
+                              order (ASC, $$56) [cardinality: 10.26, op-cost: 50.26, total-cost: 100.26]
+                              -- STABLE_SORT [$$56(ASC)]  |PARTITIONED|
                                 exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
-                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                  empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
-                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                -- HASH_PARTITION_EXCHANGE [$$56]  |PARTITIONED|
+                                  project ([$$56]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    assign [$$56] <- [$$s.getField(3)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                    -- ASSIGN  |PARTITIONED|
+                                      project ([$$s]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          data-scan []<-[$$59, $$s] <- tpch.Supplier [cardinality: 10.0, op-cost: 10.0, total-cost: 10.0]
+                                          -- DATASOURCE_SCAN  |PARTITIONED|
+                                            exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/join/hash-join-with-redundant-variable/hash-join-with-redundant-variable.06.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/join/hash-join-with-redundant-variable/hash-join-with-redundant-variable.06.plan
index 70813fa..88f0fd3 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/join/hash-join-with-redundant-variable/hash-join-with-redundant-variable.06.plan
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/join/hash-join-with-redundant-variable/hash-join-with-redundant-variable.06.plan
@@ -1,22 +1,22 @@
-distribute result [$$36] [cardinality: 6016.3, op-cost: 0.0, total-cost: 98061.73]
+distribute result [$$36] [cardinality: 6364.57, op-cost: 0.0, total-cost: 102950.85]
 -- DISTRIBUTE_RESULT  |PARTITIONED|
-  exchange [cardinality: 6016.3, op-cost: 0.0, total-cost: 98061.73]
+  exchange [cardinality: 6364.57, op-cost: 0.0, total-cost: 102950.85]
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-    project ([$$36]) [cardinality: 6016.3, op-cost: 0.0, total-cost: 98061.73]
+    project ([$$36]) [cardinality: 6364.57, op-cost: 0.0, total-cost: 102950.85]
     -- STREAM_PROJECT  |PARTITIONED|
-      assign [$$36] <- [{"o_orderkey": $$43, "l_orderkey": $$44, "l_suppkey": $$42}] [cardinality: 6016.3, op-cost: 0.0, total-cost: 98061.73]
+      assign [$$36] <- [{"o_orderkey": $$43, "l_orderkey": $$44, "l_suppkey": $$42}] [cardinality: 6364.57, op-cost: 0.0, total-cost: 102950.85]
       -- ASSIGN  |PARTITIONED|
-        exchange [cardinality: 6016.3, op-cost: 0.0, total-cost: 98061.73]
+        exchange [cardinality: 6364.57, op-cost: 0.0, total-cost: 102950.85]
         -- SORT_MERGE_EXCHANGE [$$43(ASC), $$44(ASC), $$42(ASC) ]  |PARTITIONED|
-          order (ASC, $$43) (ASC, $$44) (ASC, $$42) [cardinality: 6016.3, op-cost: 75532.61, total-cost: 98061.73]
+          order (ASC, $$43) (ASC, $$44) (ASC, $$42) [cardinality: 6364.57, op-cost: 80421.73, total-cost: 102950.85]
           -- STABLE_SORT [$$43(ASC), $$44(ASC), $$42(ASC)]  |PARTITIONED|
-            exchange [cardinality: 6016.3, op-cost: 0.0, total-cost: 22529.12]
+            exchange [cardinality: 6364.57, op-cost: 0.0, total-cost: 22529.12]
             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-              project ([$$43, $$44, $$42]) [cardinality: 6016.3, op-cost: 0.0, total-cost: 22529.12]
+              project ([$$43, $$44, $$42]) [cardinality: 6364.57, op-cost: 0.0, total-cost: 22529.12]
               -- STREAM_PROJECT  |PARTITIONED|
-                exchange [cardinality: 6016.3, op-cost: 0.0, total-cost: 22529.12]
+                exchange [cardinality: 6364.57, op-cost: 0.0, total-cost: 22529.12]
                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                  join (and(eq($$43, $$44), eq($$49, $$42))) [cardinality: 6016.3, op-cost: 7512.06, total-cost: 22529.12]
+                  join (and(eq($$43, $$44), eq($$49, $$42))) [cardinality: 6364.57, op-cost: 7512.06, total-cost: 22529.12]
                   -- HYBRID_HASH_JOIN [$$44, $$42][$$43, $$49]  |PARTITIONED|
                     exchange [cardinality: 6005.0, op-cost: 6010.65, total-cost: 12015.65]
                     -- HASH_PARTITION_EXCHANGE [$$44, $$42]  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/join/hash-join-with-redundant-variable/hash-join-with-redundant-variable.08.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/join/hash-join-with-redundant-variable/hash-join-with-redundant-variable.08.plan
index 0668ea4..e322d8f 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/join/hash-join-with-redundant-variable/hash-join-with-redundant-variable.08.plan
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/join/hash-join-with-redundant-variable/hash-join-with-redundant-variable.08.plan
@@ -1,14 +1,14 @@
-distribute result [$$38] [cardinality: 1500.0, op-cost: 0.0, total-cost: 77032.61]
+distribute result [$$38] [cardinality: 1500.0, op-cost: 0.0, total-cost: 81921.73]
 -- DISTRIBUTE_RESULT  |PARTITIONED|
-  exchange [cardinality: 1500.0, op-cost: 0.0, total-cost: 77032.61]
+  exchange [cardinality: 1500.0, op-cost: 0.0, total-cost: 81921.73]
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-    project ([$$38]) [cardinality: 1500.0, op-cost: 0.0, total-cost: 77032.61]
+    project ([$$38]) [cardinality: 1500.0, op-cost: 0.0, total-cost: 81921.73]
     -- STREAM_PROJECT  |PARTITIONED|
-      assign [$$38] <- [{"o_orderkey": $$43, "l_orderkey": $$44, "l_suppkey": $$47}] [cardinality: 1500.0, op-cost: 0.0, total-cost: 77032.61]
+      assign [$$38] <- [{"o_orderkey": $$43, "l_orderkey": $$44, "l_suppkey": $$47}] [cardinality: 1500.0, op-cost: 0.0, total-cost: 81921.73]
       -- ASSIGN  |PARTITIONED|
-        exchange [cardinality: 1500.0, op-cost: 0.0, total-cost: 77032.61]
+        exchange [cardinality: 1500.0, op-cost: 0.0, total-cost: 81921.73]
         -- SORT_MERGE_EXCHANGE [$$43(ASC), $$44(ASC), $$47(ASC) ]  |PARTITIONED|
-          order (ASC, $$43) (ASC, $$44) (ASC, $$47) [cardinality: 1500.0, op-cost: 75532.61, total-cost: 77032.61]
+          order (ASC, $$43) (ASC, $$44) (ASC, $$47) [cardinality: 1500.0, op-cost: 80421.73, total-cost: 81921.73]
           -- STABLE_SORT [$$43(ASC), $$44(ASC), $$47(ASC)]  |PARTITIONED|
             exchange [cardinality: 1500.0, op-cost: 0.0, total-cost: 1500.0]
             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -16,7 +16,7 @@
               -- STREAM_PROJECT  |PARTITIONED|
                 exchange [cardinality: 1500.0, op-cost: 0.0, total-cost: 1500.0]
                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                  left outer join (and(eq($$43, $$44), eq($$56, $$47))) [cardinality: 6016.3, op-cost: 7512.06, total-cost: 22529.12]
+                  left outer join (and(eq($$43, $$44), eq($$56, $$47))) [cardinality: 6364.57, op-cost: 7512.06, total-cost: 22529.12]
                   -- HYBRID_HASH_JOIN [$$43, $$56][$$44, $$47]  |PARTITIONED|
                     exchange [cardinality: 1500.0, op-cost: 0.0, total-cost: 1500.0]
                     -- HASH_PARTITION_EXCHANGE [$$43, $$56]  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/join/hash-join-with-redundant-variable/hash-join-with-redundant-variable.10.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/join/hash-join-with-redundant-variable/hash-join-with-redundant-variable.10.plan
index 107be09..f032041 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/join/hash-join-with-redundant-variable/hash-join-with-redundant-variable.10.plan
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/join/hash-join-with-redundant-variable/hash-join-with-redundant-variable.10.plan
@@ -1,64 +1,66 @@
-distribute result [$$51] [cardinality: 150.0, op-cost: 0.0, total-cost: 1639.32]
+distribute result [$$51] [cardinality: 62.1, op-cost: 0.0, total-cost: 821.69]
 -- DISTRIBUTE_RESULT  |PARTITIONED|
-  exchange [cardinality: 150.0, op-cost: 0.0, total-cost: 1639.32]
+  exchange [cardinality: 62.1, op-cost: 0.0, total-cost: 821.69]
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-    project ([$$51]) [cardinality: 150.0, op-cost: 0.0, total-cost: 1639.32]
+    project ([$$51]) [cardinality: 62.1, op-cost: 0.0, total-cost: 821.69]
     -- STREAM_PROJECT  |PARTITIONED|
-      assign [$$51] <- [{"n_nationkey": $$58, "s_nationkey": $$56, "c_nationkey": $$55}] [cardinality: 150.0, op-cost: 0.0, total-cost: 1639.32]
+      assign [$$51] <- [{"n_nationkey": $$58, "s_nationkey": $$56, "c_nationkey": $$55}] [cardinality: 62.1, op-cost: 0.0, total-cost: 821.69]
       -- ASSIGN  |PARTITIONED|
-        exchange [cardinality: 150.0, op-cost: 0.0, total-cost: 1639.32]
+        exchange [cardinality: 62.1, op-cost: 0.0, total-cost: 821.69]
         -- SORT_MERGE_EXCHANGE [$$58(ASC), $$56(ASC), $$55(ASC) ]  |PARTITIONED|
-          order (ASC, $$58) (ASC, $$56) (ASC, $$55) [cardinality: 150.0, op-cost: 1084.32, total-cost: 1639.32]
+          order (ASC, $$58) (ASC, $$56) (ASC, $$55) [cardinality: 62.1, op-cost: 369.9, total-cost: 821.69]
           -- STABLE_SORT [$$58(ASC), $$56(ASC), $$55(ASC)]  |PARTITIONED|
-            exchange [cardinality: 150.0, op-cost: 0.0, total-cost: 555.0]
+            exchange [cardinality: 62.1, op-cost: 0.0, total-cost: 451.79]
             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-              join (eq($$55, $$58)) [cardinality: 150.0, op-cost: 225.0, total-cost: 555.0]
-              -- HYBRID_HASH_JOIN [$$55][$$58]  |PARTITIONED|
-                exchange [cardinality: 150.0, op-cost: 0.0, total-cost: 150.0]
-                -- RANDOM_PARTITION_EXCHANGE  |PARTITIONED|
-                  project ([$$55]) [cardinality: 150.0, op-cost: 0.0, total-cost: 150.0]
-                  -- STREAM_PROJECT  |PARTITIONED|
-                    assign [$$55] <- [$$c.getField(3)] [cardinality: 150.0, op-cost: 0.0, total-cost: 150.0]
-                    -- ASSIGN  |PARTITIONED|
-                      project ([$$c]) [cardinality: 150.0, op-cost: 0.0, total-cost: 150.0]
+              project ([$$58, $$56, $$55]) [cardinality: 62.1, op-cost: 0.0, total-cost: 451.79]
+              -- STREAM_PROJECT  |PARTITIONED|
+                exchange [cardinality: 62.1, op-cost: 0.0, total-cost: 451.79]
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  join (and(eq($$55, $$58), eq($$56, $$66))) [cardinality: 62.1, op-cost: 180.77, total-cost: 451.79]
+                  -- HYBRID_HASH_JOIN [$$55, $$66][$$58, $$56]  |PARTITIONED|
+                    exchange [cardinality: 150.0, op-cost: 0.0, total-cost: 150.0]
+                    -- RANDOM_PARTITION_EXCHANGE  |PARTITIONED|
+                      assign [$$66] <- [$$55] [cardinality: 150.0, op-cost: 0.0, total-cost: 150.0]
+                      -- ASSIGN  |PARTITIONED|
+                        project ([$$55]) [cardinality: 150.0, op-cost: 0.0, total-cost: 150.0]
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          assign [$$55] <- [$$c.getField(3)] [cardinality: 150.0, op-cost: 0.0, total-cost: 150.0]
+                          -- ASSIGN  |PARTITIONED|
+                            project ([$$c]) [cardinality: 150.0, op-cost: 0.0, total-cost: 150.0]
+                            -- STREAM_PROJECT  |PARTITIONED|
+                              exchange [cardinality: 150.0, op-cost: 0.0, total-cost: 150.0]
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                data-scan []<-[$$60, $$c] <- tpch.Customer [cardinality: 150.0, op-cost: 150.0, total-cost: 150.0]
+                                -- DATASOURCE_SCAN  |PARTITIONED|
+                                  exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                    exchange [cardinality: 10.26, op-cost: 30.77, total-cost: 121.03]
+                    -- BROADCAST_EXCHANGE  |PARTITIONED|
+                      project ([$$56, $$58]) [cardinality: 10.26, op-cost: 0.0, total-cost: 90.26]
                       -- STREAM_PROJECT  |PARTITIONED|
-                        exchange [cardinality: 150.0, op-cost: 0.0, total-cost: 150.0]
+                        exchange [cardinality: 10.26, op-cost: 30.77, total-cost: 121.03]
                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                          data-scan []<-[$$60, $$c] <- tpch.Customer [cardinality: 150.0, op-cost: 150.0, total-cost: 150.0]
-                          -- DATASOURCE_SCAN  |PARTITIONED|
+                          unnest-map [$$58, $$n] <- index-search("Nation", 0, "Default", "tpch", "Nation", true, true, 1, $$56, 1, $$56, true, true, true) [cardinality: 10.26, op-cost: 50.26, total-cost: 90.26]
+                          -- BTREE_SEARCH  |PARTITIONED|
                             exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
-                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                exchange [cardinality: 25.0, op-cost: 75.0, total-cost: 180.0]
-                -- BROADCAST_EXCHANGE  |PARTITIONED|
-                  join (eq($$56, $$58)) [cardinality: 25.0, op-cost: 35.0, total-cost: 105.0]
-                  -- HYBRID_HASH_JOIN [$$58][$$56]  |PARTITIONED|
-                    exchange [cardinality: 25.0, op-cost: 25.0, total-cost: 50.0]
-                    -- HASH_PARTITION_EXCHANGE [$$58]  |PARTITIONED|
-                      project ([$$58]) [cardinality: 25.0, op-cost: 0.0, total-cost: 25.0]
-                      -- STREAM_PROJECT  |PARTITIONED|
-                        exchange [cardinality: 25.0, op-cost: 25.0, total-cost: 50.0]
-                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                          data-scan []<-[$$58, $$n] <- tpch.Nation [cardinality: 25.0, op-cost: 25.0, total-cost: 25.0]
-                          -- DATASOURCE_SCAN  |PARTITIONED|
-                            exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
-                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
-                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                    exchange [cardinality: 10.0, op-cost: 10.0, total-cost: 20.0]
-                    -- HASH_PARTITION_EXCHANGE [$$56]  |PARTITIONED|
-                      project ([$$56]) [cardinality: 10.0, op-cost: 0.0, total-cost: 10.0]
-                      -- STREAM_PROJECT  |PARTITIONED|
-                        assign [$$56] <- [$$s.getField(3)] [cardinality: 10.0, op-cost: 0.0, total-cost: 10.0]
-                        -- ASSIGN  |PARTITIONED|
-                          project ([$$s]) [cardinality: 10.0, op-cost: 0.0, total-cost: 10.0]
-                          -- STREAM_PROJECT  |PARTITIONED|
-                            exchange [cardinality: 10.0, op-cost: 10.0, total-cost: 20.0]
-                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              data-scan []<-[$$59, $$s] <- tpch.Supplier [cardinality: 10.0, op-cost: 10.0, total-cost: 10.0]
-                              -- DATASOURCE_SCAN  |PARTITIONED|
+                              order (ASC, $$56) [cardinality: 10.26, op-cost: 50.26, total-cost: 90.26]
+                              -- STABLE_SORT [$$56(ASC)]  |PARTITIONED|
                                 exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
-                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                  empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
-                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                -- HASH_PARTITION_EXCHANGE [$$56]  |PARTITIONED|
+                                  project ([$$56]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    assign [$$56] <- [$$s.getField(3)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                    -- ASSIGN  |PARTITIONED|
+                                      project ([$$s]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          data-scan []<-[$$59, $$s] <- tpch.Supplier [cardinality: 10.0, op-cost: 10.0, total-cost: 10.0]
+                                          -- DATASOURCE_SCAN  |PARTITIONED|
+                                            exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/join/hash-join-with-redundant-variable/hash-join-with-redundant-variable.12.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/join/hash-join-with-redundant-variable/hash-join-with-redundant-variable.12.plan
index 65cdc79..09279a9 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/join/hash-join-with-redundant-variable/hash-join-with-redundant-variable.12.plan
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/join/hash-join-with-redundant-variable/hash-join-with-redundant-variable.12.plan
@@ -1,18 +1,18 @@
-distribute result [$$51] [cardinality: 150.0, op-cost: 0.0, total-cost: 1689.32]
+distribute result [$$51] [cardinality: 62.1, op-cost: 0.0, total-cost: 945.41]
 -- DISTRIBUTE_RESULT  |PARTITIONED|
-  exchange [cardinality: 150.0, op-cost: 0.0, total-cost: 1689.32]
+  exchange [cardinality: 62.1, op-cost: 0.0, total-cost: 945.41]
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-    project ([$$51]) [cardinality: 150.0, op-cost: 0.0, total-cost: 1689.32]
+    project ([$$51]) [cardinality: 62.1, op-cost: 0.0, total-cost: 945.41]
     -- STREAM_PROJECT  |PARTITIONED|
-      assign [$$51] <- [{"n_nationkey": $$58, "s_nationkey": $$56, "c_nationkey": $$55}] [cardinality: 150.0, op-cost: 0.0, total-cost: 1689.32]
+      assign [$$51] <- [{"n_nationkey": $$58, "s_nationkey": $$56, "c_nationkey": $$55}] [cardinality: 62.1, op-cost: 0.0, total-cost: 945.41]
       -- ASSIGN  |PARTITIONED|
-        exchange [cardinality: 150.0, op-cost: 0.0, total-cost: 1689.32]
+        exchange [cardinality: 62.1, op-cost: 0.0, total-cost: 945.41]
         -- SORT_MERGE_EXCHANGE [$$58(ASC), $$56(ASC), $$55(ASC) ]  |PARTITIONED|
-          order (ASC, $$58) (ASC, $$56) (ASC, $$55) [cardinality: 150.0, op-cost: 1084.32, total-cost: 1689.32]
+          order (ASC, $$58) (ASC, $$56) (ASC, $$55) [cardinality: 62.1, op-cost: 369.9, total-cost: 945.41]
           -- STABLE_SORT [$$58(ASC), $$56(ASC), $$55(ASC)]  |PARTITIONED|
-            exchange [cardinality: 150.0, op-cost: 0.0, total-cost: 605.0]
+            exchange [cardinality: 62.1, op-cost: 0.0, total-cost: 575.51]
             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-              join (eq($$55, $$58)) [cardinality: 150.0, op-cost: 175.0, total-cost: 605.0]
+              join (eq($$55, $$58)) [cardinality: 62.1, op-cost: 160.26, total-cost: 575.51]
               -- HYBRID_HASH_JOIN [$$55][$$58]  |PARTITIONED|
                 exchange [cardinality: 150.0, op-cost: 150.0, total-cost: 300.0]
                 -- HASH_PARTITION_EXCHANGE [$$55]  |PARTITIONED|
@@ -30,9 +30,9 @@
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                               empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                               -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                exchange [cardinality: 25.0, op-cost: 25.0, total-cost: 130.0]
+                exchange [cardinality: 10.26, op-cost: 10.26, total-cost: 115.26]
                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                  join (eq($$56, $$58)) [cardinality: 25.0, op-cost: 35.0, total-cost: 105.0]
+                  join (eq($$56, $$58)) [cardinality: 10.26, op-cost: 35.0, total-cost: 105.0]
                   -- HYBRID_HASH_JOIN [$$58][$$56]  |PARTITIONED|
                     exchange [cardinality: 25.0, op-cost: 25.0, total-cost: 50.0]
                     -- HASH_PARTITION_EXCHANGE [$$58]  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/join/hash-join-with-redundant-variable/hash-join-with-redundant-variable.14.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/join/hash-join-with-redundant-variable/hash-join-with-redundant-variable.14.plan
index dc1dd8c..e6d2791 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/join/hash-join-with-redundant-variable/hash-join-with-redundant-variable.14.plan
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/join/hash-join-with-redundant-variable/hash-join-with-redundant-variable.14.plan
@@ -1,25 +1,25 @@
-distribute result [$$51] [cardinality: 150.0, op-cost: 0.0, total-cost: 1699.32]
+distribute result [$$51] [cardinality: 62.1, op-cost: 0.0, total-cost: 852.21]
 -- DISTRIBUTE_RESULT  |PARTITIONED|
-  exchange [cardinality: 150.0, op-cost: 0.0, total-cost: 1699.32]
+  exchange [cardinality: 62.1, op-cost: 0.0, total-cost: 852.21]
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-    project ([$$51]) [cardinality: 150.0, op-cost: 0.0, total-cost: 1699.32]
+    project ([$$51]) [cardinality: 62.1, op-cost: 0.0, total-cost: 852.21]
     -- STREAM_PROJECT  |PARTITIONED|
-      assign [$$51] <- [{"n_nationkey": $$59, "s_nationkey": $$56, "c_nationkey": $$55}] [cardinality: 150.0, op-cost: 0.0, total-cost: 1699.32]
+      assign [$$51] <- [{"n_nationkey": $$59, "s_nationkey": $$56, "c_nationkey": $$55}] [cardinality: 62.1, op-cost: 0.0, total-cost: 852.21]
       -- ASSIGN  |PARTITIONED|
-        exchange [cardinality: 150.0, op-cost: 0.0, total-cost: 1699.32]
+        exchange [cardinality: 62.1, op-cost: 0.0, total-cost: 852.21]
         -- SORT_MERGE_EXCHANGE [$$59(ASC), $$56(ASC), $$55(ASC) ]  |PARTITIONED|
-          order (ASC, $$59) (ASC, $$56) (ASC, $$55) [cardinality: 150.0, op-cost: 1084.32, total-cost: 1699.32]
+          order (ASC, $$59) (ASC, $$56) (ASC, $$55) [cardinality: 62.1, op-cost: 369.9, total-cost: 852.21]
           -- STABLE_SORT [$$59(ASC), $$56(ASC), $$55(ASC)]  |PARTITIONED|
-            exchange [cardinality: 150.0, op-cost: 0.0, total-cost: 615.0]
+            exchange [cardinality: 62.1, op-cost: 0.0, total-cost: 482.31]
             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-              project ([$$59, $$56, $$55]) [cardinality: 150.0, op-cost: 0.0, total-cost: 615.0]
+              project ([$$59, $$56, $$55]) [cardinality: 62.1, op-cost: 0.0, total-cost: 482.31]
               -- STREAM_PROJECT  |PARTITIONED|
-                exchange [cardinality: 150.0, op-cost: 0.0, total-cost: 615.0]
+                exchange [cardinality: 62.1, op-cost: 0.0, total-cost: 482.31]
                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                  join (and(eq($$55, $$59), eq($$56, $$66))) [cardinality: 150.0, op-cost: 175.0, total-cost: 615.0]
+                  join (and(eq($$55, $$59), eq($$56, $$66))) [cardinality: 62.1, op-cost: 191.03, total-cost: 482.31]
                   -- HYBRID_HASH_JOIN [$$55, $$66][$$59, $$56]  |PARTITIONED|
-                    exchange [cardinality: 150.0, op-cost: 150.0, total-cost: 300.0]
-                    -- HASH_PARTITION_EXCHANGE [$$55, $$66]  |PARTITIONED|
+                    exchange [cardinality: 150.0, op-cost: 0.0, total-cost: 150.0]
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                       assign [$$66] <- [$$55] [cardinality: 150.0, op-cost: 0.0, total-cost: 150.0]
                       -- ASSIGN  |PARTITIONED|
                         project ([$$55]) [cardinality: 150.0, op-cost: 0.0, total-cost: 150.0]
@@ -28,7 +28,7 @@
                           -- ASSIGN  |PARTITIONED|
                             project ([$$c]) [cardinality: 150.0, op-cost: 0.0, total-cost: 150.0]
                             -- STREAM_PROJECT  |PARTITIONED|
-                              exchange [cardinality: 150.0, op-cost: 150.0, total-cost: 300.0]
+                              exchange [cardinality: 150.0, op-cost: 0.0, total-cost: 150.0]
                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                 data-scan []<-[$$60, $$c] <- tpch.Customer [cardinality: 150.0, op-cost: 150.0, total-cost: 150.0]
                                 -- DATASOURCE_SCAN  |PARTITIONED|
@@ -36,17 +36,17 @@
                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                     empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                     -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                    exchange [cardinality: 25.0, op-cost: 25.0, total-cost: 140.0]
-                    -- HASH_PARTITION_EXCHANGE [$$59, $$56]  |PARTITIONED|
-                      project ([$$56, $$59]) [cardinality: 25.0, op-cost: 0.0, total-cost: 115.0]
+                    exchange [cardinality: 10.26, op-cost: 41.03, total-cost: 141.29]
+                    -- BROADCAST_EXCHANGE  |PARTITIONED|
+                      project ([$$56, $$59]) [cardinality: 10.26, op-cost: 0.0, total-cost: 100.26]
                       -- STREAM_PROJECT  |PARTITIONED|
-                        exchange [cardinality: 25.0, op-cost: 25.0, total-cost: 140.0]
+                        exchange [cardinality: 10.26, op-cost: 41.03, total-cost: 141.29]
                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                          unnest-map [$$59, $$n] <- index-search("Nation", 0, "Default", "tpch", "Nation", true, true, 1, $$56, 1, $$56, true, true, true) [cardinality: 25.0, op-cost: 65.0, total-cost: 115.0]
+                          unnest-map [$$59, $$n] <- index-search("Nation", 0, "Default", "tpch", "Nation", true, true, 1, $$56, 1, $$56, true, true, true) [cardinality: 10.26, op-cost: 50.26, total-cost: 100.26]
                           -- BTREE_SEARCH  |PARTITIONED|
                             exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              order (ASC, $$56) [cardinality: 25.0, op-cost: 65.0, total-cost: 115.0]
+                              order (ASC, $$56) [cardinality: 10.26, op-cost: 50.26, total-cost: 100.26]
                               -- STABLE_SORT [$$56(ASC)]  |PARTITIONED|
                                 exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                 -- HASH_PARTITION_EXCHANGE [$$56]  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/join/hash-join-with-redundant-variable/hash-join-with-redundant-variable.16.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/join/hash-join-with-redundant-variable/hash-join-with-redundant-variable.16.plan
index 32ea43f..8491a35 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/join/hash-join-with-redundant-variable/hash-join-with-redundant-variable.16.plan
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/join/hash-join-with-redundant-variable/hash-join-with-redundant-variable.16.plan
@@ -1,22 +1,22 @@
-distribute result [$$51] [cardinality: 150.0, op-cost: 0.0, total-cost: 1639.32]
+distribute result [$$51] [cardinality: 62.1, op-cost: 0.0, total-cost: 821.69]
 -- DISTRIBUTE_RESULT  |PARTITIONED|
-  exchange [cardinality: 150.0, op-cost: 0.0, total-cost: 1639.32]
+  exchange [cardinality: 62.1, op-cost: 0.0, total-cost: 821.69]
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-    project ([$$51]) [cardinality: 150.0, op-cost: 0.0, total-cost: 1639.32]
+    project ([$$51]) [cardinality: 62.1, op-cost: 0.0, total-cost: 821.69]
     -- STREAM_PROJECT  |PARTITIONED|
-      assign [$$51] <- [{"n_nationkey": $$59, "s_nationkey": $$56, "c_nationkey": $$55}] [cardinality: 150.0, op-cost: 0.0, total-cost: 1639.32]
+      assign [$$51] <- [{"n_nationkey": $$59, "s_nationkey": $$56, "c_nationkey": $$55}] [cardinality: 62.1, op-cost: 0.0, total-cost: 821.69]
       -- ASSIGN  |PARTITIONED|
-        exchange [cardinality: 150.0, op-cost: 0.0, total-cost: 1639.32]
+        exchange [cardinality: 62.1, op-cost: 0.0, total-cost: 821.69]
         -- SORT_MERGE_EXCHANGE [$$59(ASC), $$56(ASC), $$55(ASC) ]  |PARTITIONED|
-          order (ASC, $$59) (ASC, $$56) (ASC, $$55) [cardinality: 150.0, op-cost: 1084.32, total-cost: 1639.32]
+          order (ASC, $$59) (ASC, $$56) (ASC, $$55) [cardinality: 62.1, op-cost: 369.9, total-cost: 821.69]
           -- STABLE_SORT [$$59(ASC), $$56(ASC), $$55(ASC)]  |PARTITIONED|
-            exchange [cardinality: 150.0, op-cost: 0.0, total-cost: 555.0]
+            exchange [cardinality: 62.1, op-cost: 0.0, total-cost: 451.79]
             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-              project ([$$59, $$56, $$55]) [cardinality: 150.0, op-cost: 0.0, total-cost: 555.0]
+              project ([$$59, $$56, $$55]) [cardinality: 62.1, op-cost: 0.0, total-cost: 451.79]
               -- STREAM_PROJECT  |PARTITIONED|
-                exchange [cardinality: 150.0, op-cost: 0.0, total-cost: 555.0]
+                exchange [cardinality: 62.1, op-cost: 0.0, total-cost: 451.79]
                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                  join (and(eq($$55, $$59), eq($$56, $$66))) [cardinality: 150.0, op-cost: 225.0, total-cost: 555.0]
+                  join (and(eq($$55, $$59), eq($$56, $$66))) [cardinality: 62.1, op-cost: 180.77, total-cost: 451.79]
                   -- HYBRID_HASH_JOIN [$$55, $$66][$$59, $$56]  |PARTITIONED|
                     exchange [cardinality: 150.0, op-cost: 0.0, total-cost: 150.0]
                     -- RANDOM_PARTITION_EXCHANGE  |PARTITIONED|
@@ -36,17 +36,17 @@
                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                     empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                     -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                    exchange [cardinality: 25.0, op-cost: 75.0, total-cost: 180.0]
+                    exchange [cardinality: 10.26, op-cost: 30.77, total-cost: 121.03]
                     -- BROADCAST_EXCHANGE  |PARTITIONED|
-                      project ([$$56, $$59]) [cardinality: 25.0, op-cost: 0.0, total-cost: 105.0]
+                      project ([$$56, $$59]) [cardinality: 10.26, op-cost: 0.0, total-cost: 90.26]
                       -- STREAM_PROJECT  |PARTITIONED|
-                        exchange [cardinality: 25.0, op-cost: 75.0, total-cost: 180.0]
+                        exchange [cardinality: 10.26, op-cost: 30.77, total-cost: 121.03]
                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                          unnest-map [$$59, $$n] <- index-search("Nation", 0, "Default", "tpch", "Nation", true, true, 1, $$56, 1, $$56, true, true, true) [cardinality: 25.0, op-cost: 65.0, total-cost: 105.0]
+                          unnest-map [$$59, $$n] <- index-search("Nation", 0, "Default", "tpch", "Nation", true, true, 1, $$56, 1, $$56, true, true, true) [cardinality: 10.26, op-cost: 50.26, total-cost: 90.26]
                           -- BTREE_SEARCH  |PARTITIONED|
                             exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              order (ASC, $$56) [cardinality: 25.0, op-cost: 65.0, total-cost: 105.0]
+                              order (ASC, $$56) [cardinality: 10.26, op-cost: 50.26, total-cost: 90.26]
                               -- STABLE_SORT [$$56(ASC)]  |PARTITIONED|
                                 exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                 -- HASH_PARTITION_EXCHANGE [$$56]  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/limit/push-limit-to-primary-scan-select/push-limit-to-primary-scan-select.5.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/limit/push-limit-to-primary-scan-select/push-limit-to-primary-scan-select.5.plan
index 44c16f4..eeb8286 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/limit/push-limit-to-primary-scan-select/push-limit-to-primary-scan-select.5.plan
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/limit/push-limit-to-primary-scan-select/push-limit-to-primary-scan-select.5.plan
@@ -1,22 +1,22 @@
-distribute result [$$37] [cardinality: 2.1, op-cost: 0.0, total-cost: 304.0]
+distribute result [$$37] [cardinality: 63.37, op-cost: 0.0, total-cost: 304.0]
 -- DISTRIBUTE_RESULT  |UNPARTITIONED|
-  exchange [cardinality: 2.1, op-cost: 0.0, total-cost: 304.0]
+  exchange [cardinality: 63.37, op-cost: 0.0, total-cost: 304.0]
   -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
-    limit 2 [cardinality: 2.1, op-cost: 0.0, total-cost: 304.0]
+    limit 2 [cardinality: 63.37, op-cost: 0.0, total-cost: 304.0]
     -- STREAM_LIMIT  |UNPARTITIONED|
-      exchange [cardinality: 2.1, op-cost: 0.0, total-cost: 304.0]
+      exchange [cardinality: 63.37, op-cost: 0.0, total-cost: 304.0]
       -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
-        project ([$$37]) [cardinality: 2.1, op-cost: 0.0, total-cost: 304.0]
+        project ([$$37]) [cardinality: 63.37, op-cost: 0.0, total-cost: 304.0]
         -- STREAM_PROJECT  |PARTITIONED|
-          assign [$$37] <- [{"dblpid": $$38}] [cardinality: 2.1, op-cost: 0.0, total-cost: 304.0]
+          assign [$$37] <- [{"dblpid": $$38}] [cardinality: 63.37, op-cost: 0.0, total-cost: 304.0]
           -- ASSIGN  |PARTITIONED|
-            limit 2 [cardinality: 2.1, op-cost: 0.0, total-cost: 304.0]
+            limit 2 [cardinality: 63.37, op-cost: 0.0, total-cost: 304.0]
             -- STREAM_LIMIT  |PARTITIONED|
-              project ([$$38]) [cardinality: 2.1, op-cost: 0.0, total-cost: 304.0]
+              project ([$$38]) [cardinality: 63.37, op-cost: 0.0, total-cost: 304.0]
               -- STREAM_PROJECT  |PARTITIONED|
-                exchange [cardinality: 2.1, op-cost: 0.0, total-cost: 304.0]
+                exchange [cardinality: 63.37, op-cost: 0.0, total-cost: 304.0]
                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                  join (eq($$38, $$41)) [cardinality: 2.1, op-cost: 100.0, total-cost: 304.0]
+                  join (eq($$38, $$41)) [cardinality: 63.37, op-cost: 100.0, total-cost: 304.0]
                   -- HYBRID_HASH_JOIN [$$38][$$41]  |PARTITIONED|
                     exchange [cardinality: 100.0, op-cost: 0.0, total-cost: 100.0]
                     -- HASH_PARTITION_EXCHANGE [$$38]  |PARTITIONED|