diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/cost/CostMethods.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/cost/CostMethods.java
index 71e9b3c..360bf23 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/cost/CostMethods.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/cost/CostMethods.java
@@ -148,7 +148,8 @@
     public Cost costCartesianProductJoin(JoinNode jn) {
         JoinNode leftJn = jn.getLeftJn();
         JoinNode rightJn = jn.getRightJn();
-        return new Cost(leftJn.getCardinality() * rightJn.getCardinality());
+        return new Cost(
+                Math.max(leftJn.getCardinality(), Cost.MIN_CARD) * Math.max(rightJn.getCardinality(), Cost.MIN_CARD));
     }
 
     public Cost computeCPRightExchangeCost(JoinNode jn) {
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/group-by/listify-3.1.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/group-by/listify-3.1.plan
index 85fa62d..ec40853 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/group-by/listify-3.1.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/group-by/listify-3.1.plan
@@ -8,27 +8,27 @@
               -- ASSIGN  |LOCAL|
                 -- ONE_TO_ONE_EXCHANGE  |LOCAL|
                   -- NESTED_LOOP  |LOCAL|
+                    -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+                      -- UNNEST  |UNPARTITIONED|
+                        -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
                     -- ONE_TO_ONE_EXCHANGE  |LOCAL|
                       -- NESTED_LOOP  |LOCAL|
-                        -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
-                          -- UNNEST  |UNPARTITIONED|
-                            -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
                         -- ONE_TO_ONE_EXCHANGE  |LOCAL|
                           -- STREAM_PROJECT  |LOCAL|
                             -- ASSIGN  |LOCAL|
-                              -- ONE_TO_ONE_EXCHANGE  |LOCAL|
-                                -- REPLICATE  |LOCAL|
+                              -- STREAM_PROJECT  |LOCAL|
+                                -- ASSIGN  |LOCAL|
                                   -- ONE_TO_ONE_EXCHANGE  |LOCAL|
-                                    -- AGGREGATE  |LOCAL|
-                                      -- STREAM_PROJECT  |LOCAL|
-                                        -- ASSIGN  |LOCAL|
-                                          -- AGGREGATE  |LOCAL|
-                                            -- AGGREGATE  |LOCAL|
-                                              -- UNNEST  |UNPARTITIONED|
-                                                -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
-                    -- ONE_TO_ONE_EXCHANGE  |LOCAL|
-                      -- STREAM_PROJECT  |LOCAL|
-                        -- ASSIGN  |LOCAL|
+                                    -- REPLICATE  |LOCAL|
+                                      -- ONE_TO_ONE_EXCHANGE  |LOCAL|
+                                        -- AGGREGATE  |LOCAL|
+                                          -- STREAM_PROJECT  |LOCAL|
+                                            -- ASSIGN  |LOCAL|
+                                              -- AGGREGATE  |LOCAL|
+                                                -- AGGREGATE  |LOCAL|
+                                                  -- UNNEST  |UNPARTITIONED|
+                                                    -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |LOCAL|
                           -- STREAM_PROJECT  |LOCAL|
                             -- ASSIGN  |LOCAL|
                               -- ONE_TO_ONE_EXCHANGE  |LOCAL|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/query_issue849-2.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/query_issue849-2.plan
index e23c4e5..21bb457 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/query_issue849-2.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/query_issue849-2.plan
@@ -21,11 +21,11 @@
         -- STREAM_PROJECT  |PARTITIONED|
           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
             -- HYBRID_HASH_JOIN [$$47][$$49]  |PARTITIONED|
-              -- HASH_PARTITION_EXCHANGE [$$47]  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                 -- DATASOURCE_SCAN (test.s)  |PARTITIONED|
                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                     -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-              -- HASH_PARTITION_EXCHANGE [$$49]  |PARTITIONED|
+              -- BROADCAST_EXCHANGE  |PARTITIONED|
                 -- STREAM_PROJECT  |UNPARTITIONED|
                   -- ASSIGN  |UNPARTITIONED|
                     -- UNNEST  |UNPARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/subquery/in_as_or_2.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/subquery/in_as_or_2.plan
index 6792ec5..d6e9fa9 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/subquery/in_as_or_2.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/subquery/in_as_or_2.plan
@@ -15,14 +15,14 @@
                   -- STREAM_PROJECT  |PARTITIONED|
                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                       -- HYBRID_HASH_JOIN [$$23][$#1]  |PARTITIONED|
-                        -- HASH_PARTITION_EXCHANGE [$$23]  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                           -- STREAM_PROJECT  |PARTITIONED|
                             -- ASSIGN  |PARTITIONED|
                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                 -- DATASOURCE_SCAN (tpch.Customer)  |PARTITIONED|
                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                     -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                        -- HASH_PARTITION_EXCHANGE [$#1]  |PARTITIONED|
+                        -- BROADCAST_EXCHANGE  |PARTITIONED|
                           -- ASSIGN  |UNPARTITIONED|
                             -- UNNEST  |UNPARTITIONED|
                               -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/subquery/in_as_or_2_ps.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/subquery/in_as_or_2_ps.plan
index 30c5399..de7e790 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/subquery/in_as_or_2_ps.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/subquery/in_as_or_2_ps.plan
@@ -21,14 +21,14 @@
                               -- STREAM_PROJECT  |PARTITIONED|
                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                   -- HYBRID_HASH_JOIN [$$23][$#1]  |PARTITIONED|
-                                    -- HASH_PARTITION_EXCHANGE [$$23]  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                       -- STREAM_PROJECT  |PARTITIONED|
                                         -- ASSIGN  |PARTITIONED|
                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                             -- DATASOURCE_SCAN (tpch.Customer)  |PARTITIONED|
                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                 -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                    -- HASH_PARTITION_EXCHANGE [$#1]  |PARTITIONED|
+                                    -- BROADCAST_EXCHANGE  |PARTITIONED|
                                       -- ASSIGN  |UNPARTITIONED|
                                         -- UNNEST  |UNPARTITIONED|
                                           -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
@@ -54,14 +54,14 @@
                                       -- STREAM_PROJECT  |PARTITIONED|
                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                           -- HYBRID_HASH_JOIN [$$23][$#1]  |PARTITIONED|
-                                            -- HASH_PARTITION_EXCHANGE [$$23]  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                               -- STREAM_PROJECT  |PARTITIONED|
                                                 -- ASSIGN  |PARTITIONED|
                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                     -- DATASOURCE_SCAN (tpch.Customer)  |PARTITIONED|
                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                         -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                            -- HASH_PARTITION_EXCHANGE [$#1]  |PARTITIONED|
+                                            -- BROADCAST_EXCHANGE  |PARTITIONED|
                                               -- ASSIGN  |UNPARTITIONED|
                                                 -- UNNEST  |UNPARTITIONED|
                                                   -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/subquery/in_as_or_4.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/subquery/in_as_or_4.plan
index 22ea58a..9ba625b 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/subquery/in_as_or_4.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/subquery/in_as_or_4.plan
@@ -15,14 +15,14 @@
                   -- STREAM_PROJECT  |PARTITIONED|
                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                       -- HYBRID_HASH_JOIN [$$22][$#1]  |PARTITIONED|
-                        -- HASH_PARTITION_EXCHANGE [$$22]  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                           -- STREAM_PROJECT  |PARTITIONED|
                             -- ASSIGN  |PARTITIONED|
                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                 -- DATASOURCE_SCAN (tpch.Customer)  |PARTITIONED|
                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                     -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                        -- HASH_PARTITION_EXCHANGE [$#1]  |PARTITIONED|
+                        -- BROADCAST_EXCHANGE  |PARTITIONED|
                           -- ASSIGN  |UNPARTITIONED|
                             -- UNNEST  |UNPARTITIONED|
                               -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/subquery/in_as_or_4_ps.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/subquery/in_as_or_4_ps.plan
index 84a9315..148d1b0 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/subquery/in_as_or_4_ps.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/subquery/in_as_or_4_ps.plan
@@ -21,14 +21,14 @@
                               -- STREAM_PROJECT  |PARTITIONED|
                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                   -- HYBRID_HASH_JOIN [$$22][$#1]  |PARTITIONED|
-                                    -- HASH_PARTITION_EXCHANGE [$$22]  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                       -- STREAM_PROJECT  |PARTITIONED|
                                         -- ASSIGN  |PARTITIONED|
                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                             -- DATASOURCE_SCAN (tpch.Customer)  |PARTITIONED|
                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                 -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                    -- HASH_PARTITION_EXCHANGE [$#1]  |PARTITIONED|
+                                    -- BROADCAST_EXCHANGE  |PARTITIONED|
                                       -- ASSIGN  |UNPARTITIONED|
                                         -- UNNEST  |UNPARTITIONED|
                                           -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
@@ -54,14 +54,14 @@
                                       -- STREAM_PROJECT  |PARTITIONED|
                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                           -- HYBRID_HASH_JOIN [$$22][$#1]  |PARTITIONED|
-                                            -- HASH_PARTITION_EXCHANGE [$$22]  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                               -- STREAM_PROJECT  |PARTITIONED|
                                                 -- ASSIGN  |PARTITIONED|
                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                     -- DATASOURCE_SCAN (tpch.Customer)  |PARTITIONED|
                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                         -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                            -- HASH_PARTITION_EXCHANGE [$#1]  |PARTITIONED|
+                                            -- BROADCAST_EXCHANGE  |PARTITIONED|
                                               -- ASSIGN  |UNPARTITIONED|
                                                 -- UNNEST  |UNPARTITIONED|
                                                   -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/subquery/in_as_or_6.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/subquery/in_as_or_6.plan
index 6792ec5..d6e9fa9 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/subquery/in_as_or_6.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/subquery/in_as_or_6.plan
@@ -15,14 +15,14 @@
                   -- STREAM_PROJECT  |PARTITIONED|
                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                       -- HYBRID_HASH_JOIN [$$23][$#1]  |PARTITIONED|
-                        -- HASH_PARTITION_EXCHANGE [$$23]  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                           -- STREAM_PROJECT  |PARTITIONED|
                             -- ASSIGN  |PARTITIONED|
                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                 -- DATASOURCE_SCAN (tpch.Customer)  |PARTITIONED|
                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                     -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                        -- HASH_PARTITION_EXCHANGE [$#1]  |PARTITIONED|
+                        -- BROADCAST_EXCHANGE  |PARTITIONED|
                           -- ASSIGN  |UNPARTITIONED|
                             -- UNNEST  |UNPARTITIONED|
                               -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/subquery/in_as_or_6_ps.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/subquery/in_as_or_6_ps.plan
index 30c5399..de7e790 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/subquery/in_as_or_6_ps.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/subquery/in_as_or_6_ps.plan
@@ -21,14 +21,14 @@
                               -- STREAM_PROJECT  |PARTITIONED|
                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                   -- HYBRID_HASH_JOIN [$$23][$#1]  |PARTITIONED|
-                                    -- HASH_PARTITION_EXCHANGE [$$23]  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                       -- STREAM_PROJECT  |PARTITIONED|
                                         -- ASSIGN  |PARTITIONED|
                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                             -- DATASOURCE_SCAN (tpch.Customer)  |PARTITIONED|
                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                 -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                    -- HASH_PARTITION_EXCHANGE [$#1]  |PARTITIONED|
+                                    -- BROADCAST_EXCHANGE  |PARTITIONED|
                                       -- ASSIGN  |UNPARTITIONED|
                                         -- UNNEST  |UNPARTITIONED|
                                           -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
@@ -54,14 +54,14 @@
                                       -- STREAM_PROJECT  |PARTITIONED|
                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                           -- HYBRID_HASH_JOIN [$$23][$#1]  |PARTITIONED|
-                                            -- HASH_PARTITION_EXCHANGE [$$23]  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                               -- STREAM_PROJECT  |PARTITIONED|
                                                 -- ASSIGN  |PARTITIONED|
                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                     -- DATASOURCE_SCAN (tpch.Customer)  |PARTITIONED|
                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                         -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                            -- HASH_PARTITION_EXCHANGE [$#1]  |PARTITIONED|
+                                            -- BROADCAST_EXCHANGE  |PARTITIONED|
                                               -- ASSIGN  |UNPARTITIONED|
                                                 -- UNNEST  |UNPARTITIONED|
                                                   -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/filter/not-in_every/not-in_every.201.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/filter/not-in_every/not-in_every.201.plan
index 31657c9..20294f4 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/filter/not-in_every/not-in_every.201.plan
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/filter/not-in_every/not-in_every.201.plan
@@ -31,7 +31,7 @@
                       -- STREAM_PROJECT  |PARTITIONED|
                         exchange [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                          left outer join (not(if-missing-or-null(neq($$21, $#1), false))) [cardinality: 3.0, op-cost: 6.0, total-cost: 17.0]
+                          left outer join (not(if-missing-or-null(neq($$21, $#1), false))) [cardinality: 3.0, op-cost: 12.6, total-cost: 23.6]
                           -- NESTED_LOOP  |PARTITIONED|
                             exchange [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/filter/subplan/subplan.032.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/filter/subplan/subplan.032.plan
index 41ca4c2..b691147 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/filter/subplan/subplan.032.plan
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/filter/subplan/subplan.032.plan
@@ -1,16 +1,16 @@
-distribute result [$$70] [cardinality: 2.1, op-cost: 0.0, total-cost: 12.0]
+distribute result [$$70] [cardinality: 2.1, op-cost: 0.0, total-cost: 15.41]
 -- DISTRIBUTE_RESULT  |UNPARTITIONED|
-  exchange [cardinality: 2.1, op-cost: 0.0, total-cost: 12.0]
+  exchange [cardinality: 2.1, op-cost: 0.0, total-cost: 15.41]
   -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
-    aggregate [$$70] <- [agg-sql-sum($$76)] [cardinality: 2.1, op-cost: 0.0, total-cost: 12.0]
+    aggregate [$$70] <- [agg-sql-sum($$76)] [cardinality: 2.1, op-cost: 0.0, total-cost: 15.41]
     -- AGGREGATE  |UNPARTITIONED|
-      exchange [cardinality: 2.1, op-cost: 0.0, total-cost: 12.0]
+      exchange [cardinality: 2.1, op-cost: 0.0, total-cost: 15.41]
       -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
-        aggregate [$$76] <- [agg-sql-count(1)] [cardinality: 2.1, op-cost: 0.0, total-cost: 12.0]
+        aggregate [$$76] <- [agg-sql-count(1)] [cardinality: 2.1, op-cost: 0.0, total-cost: 15.41]
         -- AGGREGATE  |PARTITIONED|
-          exchange [cardinality: 2.1, op-cost: 0.0, total-cost: 12.0]
+          exchange [cardinality: 2.1, op-cost: 0.0, total-cost: 15.41]
           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-            join (true) [cardinality: 2.1, op-cost: 1.0, total-cost: 12.0]
+            join (true) [cardinality: 2.1, op-cost: 4.41, total-cost: 15.41]
             -- NESTED_LOOP  |PARTITIONED|
               exchange [cardinality: 1.0, op-cost: 0.0, total-cost: 6.0]
               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/filter/subplan/subplan.042.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/filter/subplan/subplan.042.plan
index fd431a6..7fe9bcc 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/filter/subplan/subplan.042.plan
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/filter/subplan/subplan.042.plan
@@ -1,16 +1,16 @@
-distribute result [$$70] [cardinality: 6.0, op-cost: 0.0, total-cost: 22.0]
+distribute result [$$70] [cardinality: 6.0, op-cost: 0.0, total-cost: 28.6]
 -- DISTRIBUTE_RESULT  |UNPARTITIONED|
-  exchange [cardinality: 6.0, op-cost: 0.0, total-cost: 22.0]
+  exchange [cardinality: 6.0, op-cost: 0.0, total-cost: 28.6]
   -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
-    aggregate [$$70] <- [agg-sql-sum($$76)] [cardinality: 6.0, op-cost: 0.0, total-cost: 22.0]
+    aggregate [$$70] <- [agg-sql-sum($$76)] [cardinality: 6.0, op-cost: 0.0, total-cost: 28.6]
     -- AGGREGATE  |UNPARTITIONED|
-      exchange [cardinality: 6.0, op-cost: 0.0, total-cost: 22.0]
+      exchange [cardinality: 6.0, op-cost: 0.0, total-cost: 28.6]
       -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
-        aggregate [$$76] <- [agg-sql-count(1)] [cardinality: 6.0, op-cost: 0.0, total-cost: 22.0]
+        aggregate [$$76] <- [agg-sql-count(1)] [cardinality: 6.0, op-cost: 0.0, total-cost: 28.6]
         -- AGGREGATE  |PARTITIONED|
-          exchange [cardinality: 6.0, op-cost: 0.0, total-cost: 22.0]
+          exchange [cardinality: 6.0, op-cost: 0.0, total-cost: 28.6]
           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-            join (or(eq($$71, "7"), neq($$69, 0))) [cardinality: 6.0, op-cost: 6.0, total-cost: 22.0]
+            join (or(eq($$71, "7"), neq($$69, 0))) [cardinality: 6.0, op-cost: 12.6, total-cost: 28.6]
             -- NESTED_LOOP  |PARTITIONED|
               exchange [cardinality: 6.0, op-cost: 0.0, total-cost: 6.0]
               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/filter/subplan/subplan.062.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/filter/subplan/subplan.062.plan
index 0af0063..70f874e 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/filter/subplan/subplan.062.plan
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/column/filter/subplan/subplan.062.plan
@@ -1,8 +1,8 @@
-distribute result [$$63] [cardinality: 2.1, op-cost: 0.0, total-cost: 17.0]
+distribute result [$$63] [cardinality: 2.1, op-cost: 0.0, total-cost: 20.41]
 -- DISTRIBUTE_RESULT  |PARTITIONED|
-  exchange [cardinality: 2.1, op-cost: 0.0, total-cost: 17.0]
+  exchange [cardinality: 2.1, op-cost: 0.0, total-cost: 20.41]
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-    join (true) [cardinality: 2.1, op-cost: 1.0, total-cost: 17.0]
+    join (true) [cardinality: 2.1, op-cost: 4.41, total-cost: 20.41]
     -- NESTED_LOOP  |PARTITIONED|
       exchange [cardinality: 1.0, op-cost: 0.0, total-cost: 6.0]
       -- ONE_TO_ONE_EXCHANGE  |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 eeb8286..035ab6e 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,25 +1,25 @@
-distribute result [$$37] [cardinality: 63.37, op-cost: 0.0, total-cost: 304.0]
+distribute result [$$37] [cardinality: 63.37, op-cost: 0.0, total-cost: 308.0]
 -- DISTRIBUTE_RESULT  |UNPARTITIONED|
-  exchange [cardinality: 63.37, op-cost: 0.0, total-cost: 304.0]
+  exchange [cardinality: 63.37, op-cost: 0.0, total-cost: 308.0]
   -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
-    limit 2 [cardinality: 63.37, op-cost: 0.0, total-cost: 304.0]
+    limit 2 [cardinality: 63.37, op-cost: 0.0, total-cost: 308.0]
     -- STREAM_LIMIT  |UNPARTITIONED|
-      exchange [cardinality: 63.37, op-cost: 0.0, total-cost: 304.0]
+      exchange [cardinality: 63.37, op-cost: 0.0, total-cost: 308.0]
       -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
-        project ([$$37]) [cardinality: 63.37, op-cost: 0.0, total-cost: 304.0]
+        project ([$$37]) [cardinality: 63.37, op-cost: 0.0, total-cost: 308.0]
         -- STREAM_PROJECT  |PARTITIONED|
-          assign [$$37] <- [{"dblpid": $$38}] [cardinality: 63.37, op-cost: 0.0, total-cost: 304.0]
+          assign [$$37] <- [{"dblpid": $$38}] [cardinality: 63.37, op-cost: 0.0, total-cost: 308.0]
           -- ASSIGN  |PARTITIONED|
-            limit 2 [cardinality: 63.37, op-cost: 0.0, total-cost: 304.0]
+            limit 2 [cardinality: 63.37, op-cost: 0.0, total-cost: 308.0]
             -- STREAM_LIMIT  |PARTITIONED|
-              project ([$$38]) [cardinality: 63.37, op-cost: 0.0, total-cost: 304.0]
+              project ([$$38]) [cardinality: 63.37, op-cost: 0.0, total-cost: 308.0]
               -- STREAM_PROJECT  |PARTITIONED|
-                exchange [cardinality: 63.37, op-cost: 0.0, total-cost: 304.0]
+                exchange [cardinality: 63.37, op-cost: 0.0, total-cost: 308.0]
                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                  join (eq($$38, $$41)) [cardinality: 63.37, op-cost: 100.0, total-cost: 304.0]
+                  join (eq($$38, $$41)) [cardinality: 63.37, op-cost: 104.0, total-cost: 308.0]
                   -- HYBRID_HASH_JOIN [$$38][$$41]  |PARTITIONED|
                     exchange [cardinality: 100.0, op-cost: 0.0, total-cost: 100.0]
-                    -- HASH_PARTITION_EXCHANGE [$$38]  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                       project ([$$38]) [cardinality: 100.0, op-cost: 0.0, total-cost: 100.0]
                       -- STREAM_PROJECT  |PARTITIONED|
                         assign [$$38] <- [$$d.getField(1)] [cardinality: 100.0, op-cost: 0.0, total-cost: 100.0]
@@ -35,7 +35,7 @@
                                   empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                   -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                     exchange [cardinality: 1.0, op-cost: 4.0, total-cost: 104.0]
-                    -- HASH_PARTITION_EXCHANGE [$$41]  |PARTITIONED|
+                    -- BROADCAST_EXCHANGE  |PARTITIONED|
                       project ([$$41]) [cardinality: 1.0, op-cost: 0.0, total-cost: 100.0]
                       -- STREAM_PROJECT  |UNPARTITIONED|
                         assign [$$41] <- [get-item($$30, 0).getField(0).getField(1)] [cardinality: 1.0, op-cost: 0.0, total-cost: 100.0]
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.04.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.04.plan
index 1ed0841..8b34297 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.04.plan
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.04.plan
@@ -40,13 +40,13 @@
                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                         join (and(eq($$250, $$203), eq($$202, $$231))) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                         -- HYBRID_HASH_JOIN [$$250, $$231][$$203, $$202]  |PARTITIONED|
-                                          exchange [cardinality: 37.63, op-cost: 0.0, total-cost: 20000.0]
+                                          exchange [cardinality: 33.96, op-cost: 0.0, total-cost: 402536.81]
                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                            project ([$$250, $$244, $$231]) [cardinality: 37.63, op-cost: 0.0, total-cost: 20000.0]
+                                            project ([$$250, $$244, $$231]) [cardinality: 33.96, op-cost: 0.0, total-cost: 402536.81]
                                             -- STREAM_PROJECT  |PARTITIONED|
-                                              assign [$$231] <- [get-item($$181, 0)] [cardinality: 37.63, op-cost: 0.0, total-cost: 20000.0]
+                                              assign [$$231] <- [get-item($$181, 0)] [cardinality: 33.96, op-cost: 0.0, total-cost: 402536.81]
                                               -- ASSIGN  |PARTITIONED|
-                                                exchange [cardinality: 37.63, op-cost: 0.0, total-cost: 20000.0]
+                                                exchange [cardinality: 33.96, op-cost: 0.0, total-cost: 402536.81]
                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                   group by ([$$250 := $$200]) decor ([$$244]) {
                                                             aggregate [$$181] <- [listify($$215)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
@@ -55,146 +55,152 @@
                                                               -- AGGREGATE  |LOCAL|
                                                                 nested tuple source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                                                 -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                                         } [cardinality: 0.0, op-cost: 0.0, total-cost: 20000.0]
+                                                         } [cardinality: 0.0, op-cost: 0.0, total-cost: 402536.81]
                                                   -- PRE_CLUSTERED_GROUP_BY[$$200]  |PARTITIONED|
-                                                    exchange [cardinality: 37.63, op-cost: 0.0, total-cost: 20000.0]
+                                                    exchange [cardinality: 33.96, op-cost: 0.0, total-cost: 402536.81]
                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                      order (ASC, $$200) [cardinality: 37.63, op-cost: 0.0, total-cost: 20000.0]
+                                                      order (ASC, $$200) [cardinality: 33.96, op-cost: 0.0, total-cost: 402536.81]
                                                       -- STABLE_SORT [$$200(ASC)]  |PARTITIONED|
-                                                        exchange [cardinality: 37.63, op-cost: 0.0, total-cost: 20000.0]
-                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                          project ([$$244, $$208, $$200]) [cardinality: 37.63, op-cost: 0.0, total-cost: 20000.0]
+                                                        exchange [cardinality: 33.96, op-cost: 0.0, total-cost: 402536.81]
+                                                        -- HASH_PARTITION_EXCHANGE [$$200]  |PARTITIONED|
+                                                          project ([$$244, $$208, $$200]) [cardinality: 33.96, op-cost: 0.0, total-cost: 402536.81]
                                                           -- STREAM_PROJECT  |PARTITIONED|
-                                                            exchange [cardinality: 37.63, op-cost: 0.0, total-cost: 20000.0]
+                                                            exchange [cardinality: 33.96, op-cost: 0.0, total-cost: 402536.81]
                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                              left outer join (eq($$207, $$200)) [cardinality: 33.96, op-cost: 18085.52, total-cost: 401572.23]
-                                                              -- HYBRID_HASH_JOIN [$$200][$$207]  |PARTITIONED|
-                                                                exchange [cardinality: 37.63, op-cost: 0.0, total-cost: 20000.0]
-                                                                -- HASH_PARTITION_EXCHANGE [$$200]  |PARTITIONED|
-                                                                  project ([$$244, $$200]) [cardinality: 37.63, op-cost: 0.0, total-cost: 20000.0]
+                                                              join (eq($$218, $$213)) [cardinality: 33.96, op-cost: 156.81, total-cost: 402536.81]
+                                                              -- HYBRID_HASH_JOIN [$$218][$$213]  |PARTITIONED|
+                                                                exchange [cardinality: 152.81, op-cost: 0.0, total-cost: 402371.01]
+                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                  project ([$$244, $$208, $$200, $$218]) [cardinality: 152.81, op-cost: 0.0, total-cost: 402371.01]
                                                                   -- STREAM_PROJECT  |PARTITIONED|
-                                                                    select (and(eq($$p.getField(5), 37), like($$p.getField(4), "%COPPER"))) [cardinality: 37.63, op-cost: 0.0, total-cost: 20000.0]
-                                                                    -- STREAM_SELECT  |PARTITIONED|
-                                                                      assign [$$244] <- [$$p.getField(2)] [cardinality: 20000.0, op-cost: 0.0, total-cost: 20000.0]
-                                                                      -- ASSIGN  |PARTITIONED|
-                                                                        exchange [cardinality: 20000.0, op-cost: 0.0, total-cost: 20000.0]
-                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                          data-scan []<-[$$200, $$p] <- tpch.part [cardinality: 20000.0, op-cost: 20000.0, total-cost: 20000.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: 0.0, op-cost: 0.0, total-cost: 0.0]
-                                                                -- HASH_PARTITION_EXCHANGE [$$207]  |PARTITIONED|
-                                                                  project ([$$208, $$207]) [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]
+                                                                    exchange [cardinality: 152.81, op-cost: 0.0, total-cost: 402371.01]
                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                      join (eq($$207, $$209)) [cardinality: 18047.89, op-cost: 38049.55, total-cost: 345401.19]
-                                                                      -- HYBRID_HASH_JOIN [$$209][$$207]  |PARTITIONED|
-                                                                        exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
-                                                                        -- HASH_PARTITION_EXCHANGE [$$209]  |PARTITIONED|
-                                                                          project ([$$208, $$209]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                                                      join (eq($$223, $$212)) [cardinality: 152.81, op-cost: 173.99, total-cost: 402371.01]
+                                                                      -- HYBRID_HASH_JOIN [$$223][$$212]  |PARTITIONED|
+                                                                        exchange [cardinality: 987.0, op-cost: 148.99, total-cost: 1148.99]
+                                                                        -- HASH_PARTITION_EXCHANGE [$$223]  |PARTITIONED|
+                                                                          project ([$$244, $$208, $$200, $$223]) [cardinality: 987.0, op-cost: 0.0, total-cost: 1000.0]
                                                                           -- STREAM_PROJECT  |PARTITIONED|
-                                                                            exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
-                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                              join (and(eq($$218, $$213), eq($$223, $$212))) [cardinality: 18030.73, op-cost: 79209.84, total-cost: 249302.1]
-                                                                              -- HYBRID_HASH_JOIN [$$213, $$223][$$218, $$212]  |PARTITIONED|
-                                                                                exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                                                            assign [$$223] <- [$$s2.getField(3)] [cardinality: 987.0, op-cost: 0.0, total-cost: 1000.0]
+                                                                            -- ASSIGN  |PARTITIONED|
+                                                                              project ([$$208, $$244, $$200, $$s2]) [cardinality: 987.0, op-cost: 0.0, total-cost: 1000.0]
+                                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                                exchange [cardinality: 987.0, op-cost: 148.99, total-cost: 1148.99]
                                                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                  project ([$$208, $$209, $$213, $$223]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
-                                                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                                                  unnest-map [$$211, $$s2] <- index-search("supplier", 0, "Default", "tpch", "supplier", true, true, 1, $$210, 1, $$210, true, true, true) [cardinality: 987.0, op-cost: 1000.0, total-cost: 1000.0]
+                                                                                  -- BTREE_SEARCH  |PARTITIONED|
                                                                                     exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                      join (eq($$211, $$210)) [cardinality: 79109.84, op-cost: 84023.26, total-cost: 169967.26]
-                                                                                      -- HYBRID_HASH_JOIN [$$210][$$211]  |PARTITIONED|
+                                                                                      order (ASC, $$210) [cardinality: 987.0, op-cost: 1000.0, total-cost: 1000.0]
+                                                                                      -- STABLE_SORT [$$210(ASC)]  |PARTITIONED|
                                                                                         exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
-                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                          replicate [cardinality: 80000.0, op-cost: 0.0, total-cost: 80000.0]
-                                                                                          -- REPLICATE  |PARTITIONED|
-                                                                                            exchange [cardinality: 80000.0, op-cost: 0.0, total-cost: 80000.0]
+                                                                                        -- HASH_PARTITION_EXCHANGE [$$210]  |PARTITIONED|
+                                                                                          project ([$$208, $$210, $$244, $$200]) [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|
-                                                                                              project ([$$208, $$209, $$210]) [cardinality: 80000.0, op-cost: 0.0, total-cost: 80000.0]
-                                                                                              -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                exchange [cardinality: 80000.0, op-cost: 0.0, total-cost: 80000.0]
+                                                                                              left outer join (eq($$207, $$200)) [cardinality: 150.8, op-cost: 80301.99, total-cost: 400640.65]
+                                                                                              -- HYBRID_HASH_JOIN [$$207][$$200]  |PARTITIONED|
+                                                                                                exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                                                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                  data-scan []<-[$$208, $$209, $$210, $$ps2] <- tpch.partsupp [cardinality: 80000.0, op-cost: 80000.0, total-cost: 80000.0]
-                                                                                                  -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                                                  project ([$$208, $$210, $$207]) [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|
-                                                                                                      empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
-                                                                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                                        exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
-                                                                                        -- BROADCAST_EXCHANGE  |PARTITIONED|
-                                                                                          join (true) [cardinality: 987.0, op-cost: 987.0, total-cost: 1996.0]
-                                                                                          -- NESTED_LOOP  |PARTITIONED|
-                                                                                            exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
-                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                              project ([$$223, $$211]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
-                                                                                              -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                assign [$$223] <- [$$s2.getField(3)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
-                                                                                                -- ASSIGN  |PARTITIONED|
-                                                                                                  exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
-                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                    replicate [cardinality: 1000.0, op-cost: 0.0, total-cost: 1000.0]
-                                                                                                    -- REPLICATE  |PARTITIONED|
-                                                                                                      exchange [cardinality: 1000.0, op-cost: 3948.0, total-cost: 4948.0]
-                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                        data-scan []<-[$$211, $$s2] <- tpch.supplier [cardinality: 1000.0, op-cost: 1000.0, total-cost: 1000.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: 0.0, op-cost: 0.0, total-cost: 0.0]
-                                                                                            -- BROADCAST_EXCHANGE  |PARTITIONED|
-                                                                                              replicate [cardinality: 1.0, op-cost: 0.0, total-cost: 5.0]
-                                                                                              -- REPLICATE  |PARTITIONED|
-                                                                                                exchange [cardinality: 1.0, op-cost: 0.0, total-cost: 5.0]
-                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                  project ([$$213]) [cardinality: 1.0, op-cost: 0.0, total-cost: 5.0]
+                                                                                                      join (eq($$207, $$209)) [cardinality: 80151.47, op-cost: 100094.07, total-cost: 300188.15]
+                                                                                                      -- HYBRID_HASH_JOIN [$$209][$$207]  |PARTITIONED|
+                                                                                                        exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                                                                                        -- HASH_PARTITION_EXCHANGE [$$209]  |PARTITIONED|
+                                                                                                          replicate [cardinality: 80000.0, op-cost: 0.0, total-cost: 80000.0]
+                                                                                                          -- REPLICATE  |PARTITIONED|
+                                                                                                            exchange [cardinality: 80000.0, op-cost: 0.0, total-cost: 80000.0]
+                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                              project ([$$208, $$210, $$209]) [cardinality: 80000.0, op-cost: 0.0, total-cost: 80000.0]
+                                                                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                                exchange [cardinality: 80000.0, op-cost: 0.0, total-cost: 80000.0]
+                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                  data-scan []<-[$$208, $$209, $$210, $$ps2] <- tpch.partsupp [cardinality: 80000.0, op-cost: 80000.0, total-cost: 80000.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: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                                                                                        -- HASH_PARTITION_EXCHANGE [$$207]  |PARTITIONED|
+                                                                                                          project ([$$207]) [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|
+                                                                                                              replicate [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                                                                                              -- REPLICATE  |PARTITIONED|
+                                                                                                                exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                  data-scan []<-[$$207, $$p2] <- tpch.part [cardinality: 20000.0, op-cost: 20000.0, total-cost: 20000.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: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                                                                                -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                                                                  project ([$$244, $$200]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                                                                                   -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                    select (eq($$r2.getField(1), "EUROPE")) [cardinality: 1.0, op-cost: 0.0, total-cost: 5.0]
+                                                                                                    select (and(eq($$p.getField(5), 37), like($$p.getField(4), "%COPPER"))) [cardinality: 37.63, op-cost: 0.0, total-cost: 20000.0]
                                                                                                     -- STREAM_SELECT  |PARTITIONED|
-                                                                                                      exchange [cardinality: 5.0, op-cost: 0.0, total-cost: 5.0]
-                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                        data-scan []<-[$$213, $$r2] <- tpch.region [cardinality: 5.0, op-cost: 5.0, total-cost: 5.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: 0.0, op-cost: 0.0, total-cost: 0.0]
-                                                                                -- BROADCAST_EXCHANGE  |PARTITIONED|
-                                                                                  project ([$$218, $$212]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
-                                                                                  -- STREAM_PROJECT  |PARTITIONED|
-                                                                                    assign [$$218] <- [$$n2.getField(2)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
-                                                                                    -- ASSIGN  |PARTITIONED|
+                                                                                                      assign [$$244] <- [$$p.getField(2)] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                                                                                      -- ASSIGN  |PARTITIONED|
+                                                                                                        project ([$$200, $$p]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                                                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                          assign [$$200, $$p] <- [$$207, $$p2] [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                                                                                          -- ASSIGN  |PARTITIONED|
+                                                                                                            exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                              replicate [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                                                                                              -- REPLICATE  |PARTITIONED|
+                                                                                                                exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                  data-scan []<-[$$207, $$p2] <- tpch.part [cardinality: 20000.0, op-cost: 20000.0, total-cost: 20000.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: 50.0]
+                                                                        -- HASH_PARTITION_EXCHANGE [$$212]  |PARTITIONED|
+                                                                          project ([$$218, $$212]) [cardinality: 25.0, op-cost: 0.0, total-cost: 25.0]
+                                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                                            assign [$$218] <- [$$n2.getField(2)] [cardinality: 25.0, op-cost: 0.0, total-cost: 25.0]
+                                                                            -- ASSIGN  |PARTITIONED|
+                                                                              exchange [cardinality: 25.0, op-cost: 25.0, total-cost: 50.0]
+                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                replicate [cardinality: 25.0, op-cost: 0.0, total-cost: 25.0]
+                                                                                -- REPLICATE  |PARTITIONED|
+                                                                                  exchange [cardinality: 25.0, op-cost: 0.0, total-cost: 25.0]
+                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                    data-scan []<-[$$212, $$n2] <- 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|
-                                                                                        replicate [cardinality: 25.0, op-cost: 0.0, total-cost: 25.0]
-                                                                                        -- REPLICATE  |PARTITIONED|
-                                                                                          exchange [cardinality: 25.0, op-cost: 0.0, total-cost: 25.0]
-                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                            data-scan []<-[$$212, $$n2] <- 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: 0.0, op-cost: 0.0, total-cost: 0.0]
-                                                                        -- HASH_PARTITION_EXCHANGE [$$207]  |PARTITIONED|
-                                                                          project ([$$207]) [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 []<-[$$207, $$p2] <- tpch.part [cardinality: 20000.0, op-cost: 20000.0, total-cost: 20000.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|
+                                                                                        empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                exchange [cardinality: 1.0, op-cost: 4.0, total-cost: 9.0]
+                                                                -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                                  replicate [cardinality: 1.0, op-cost: 0.0, total-cost: 5.0]
+                                                                  -- REPLICATE  |PARTITIONED|
+                                                                    exchange [cardinality: 1.0, op-cost: 0.0, total-cost: 5.0]
+                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                      project ([$$213]) [cardinality: 1.0, op-cost: 0.0, total-cost: 5.0]
+                                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                                        select (eq($$r2.getField(1), "EUROPE")) [cardinality: 1.0, op-cost: 0.0, total-cost: 5.0]
+                                                                        -- STREAM_SELECT  |PARTITIONED|
+                                                                          exchange [cardinality: 5.0, op-cost: 0.0, total-cost: 5.0]
+                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                            data-scan []<-[$$213, $$r2] <- tpch.region [cardinality: 5.0, op-cost: 5.0, total-cost: 5.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: 79109.84, op-cost: 0.0, total-cost: 168971.26]
                                           -- HASH_PARTITION_EXCHANGE [$$203]  |PARTITIONED|
                                             project ([$$237, $$239, $$245, $$246, $$247, $$225, $$203, $$202]) [cardinality: 79109.84, op-cost: 0.0, total-cost: 168971.26]
@@ -205,9 +211,9 @@
                                                 -- HYBRID_HASH_JOIN [$$204][$$201]  |PARTITIONED|
                                                   exchange [cardinality: 80000.0, op-cost: 0.0, total-cost: 80000.0]
                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                    project ([$$202, $$203, $$204]) [cardinality: 80000.0, op-cost: 0.0, total-cost: 80000.0]
+                                                    project ([$$202, $$204, $$203]) [cardinality: 80000.0, op-cost: 0.0, total-cost: 80000.0]
                                                     -- STREAM_PROJECT  |PARTITIONED|
-                                                      assign [$$202, $$203, $$204] <- [$$208, $$209, $$210] [cardinality: 80000.0, op-cost: 0.0, total-cost: 80000.0]
+                                                      assign [$$202, $$204, $$203] <- [$$208, $$210, $$209] [cardinality: 80000.0, op-cost: 0.0, total-cost: 80000.0]
                                                       -- ASSIGN  |PARTITIONED|
                                                         exchange [cardinality: 80000.0, op-cost: 0.0, total-cost: 80000.0]
                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -215,7 +221,7 @@
                                                           -- REPLICATE  |PARTITIONED|
                                                             exchange [cardinality: 80000.0, op-cost: 0.0, total-cost: 80000.0]
                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                              project ([$$208, $$209, $$210]) [cardinality: 80000.0, op-cost: 0.0, total-cost: 80000.0]
+                                                              project ([$$208, $$210, $$209]) [cardinality: 80000.0, op-cost: 0.0, total-cost: 80000.0]
                                                               -- STREAM_PROJECT  |PARTITIONED|
                                                                 exchange [cardinality: 80000.0, op-cost: 0.0, total-cost: 80000.0]
                                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -231,22 +237,14 @@
                                                     -- STREAM_PROJECT  |PARTITIONED|
                                                       assign [$$247, $$246, $$245, $$237, $$239, $$225] <- [$$s.getField(6), $$s.getField(4), $$s.getField(2), $$s.getField(5), $$s.getField(1), $$s.getField(3)] [cardinality: 1000.0, op-cost: 0.0, total-cost: 1000.0]
                                                       -- ASSIGN  |PARTITIONED|
-                                                        project ([$$201, $$s]) [cardinality: 1000.0, op-cost: 0.0, total-cost: 1000.0]
-                                                        -- STREAM_PROJECT  |PARTITIONED|
-                                                          assign [$$201, $$s] <- [$$211, $$s2] [cardinality: 1000.0, op-cost: 0.0, total-cost: 1000.0]
-                                                          -- ASSIGN  |PARTITIONED|
-                                                            exchange [cardinality: 1000.0, op-cost: 3948.0, total-cost: 4948.0]
+                                                        exchange [cardinality: 1000.0, op-cost: 3948.0, total-cost: 4948.0]
+                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                          data-scan []<-[$$201, $$s] <- tpch.supplier [cardinality: 1000.0, op-cost: 1000.0, total-cost: 1000.0]
+                                                          -- DATASOURCE_SCAN  |PARTITIONED|
+                                                            exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                              replicate [cardinality: 1000.0, op-cost: 0.0, total-cost: 1000.0]
-                                                              -- REPLICATE  |PARTITIONED|
-                                                                exchange [cardinality: 1000.0, op-cost: 3948.0, total-cost: 4948.0]
-                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                  data-scan []<-[$$211, $$s2] <- tpch.supplier [cardinality: 1000.0, op-cost: 1000.0, total-cost: 1000.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|
+                                                              empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
+                                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                                   exchange [cardinality: 25.0, op-cost: 0.0, total-cost: 25.0]
                                   -- HASH_PARTITION_EXCHANGE [$$205]  |PARTITIONED|
                                     project ([$$238, $$221, $$205]) [cardinality: 25.0, op-cost: 0.0, total-cost: 25.0]
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.07.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.07.plan
index df1c653..4269fe6 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.07.plan
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.07.plan
@@ -1,48 +1,48 @@
-distribute result [$$149] [cardinality: 25.0, op-cost: 0.0, total-cost: 1574882.92]
+distribute result [$$149] [cardinality: 25.0, op-cost: 0.0, total-cost: 1574886.92]
 -- DISTRIBUTE_RESULT  |PARTITIONED|
-  exchange [cardinality: 25.0, op-cost: 0.0, total-cost: 1574882.92]
+  exchange [cardinality: 25.0, op-cost: 0.0, total-cost: 1574886.92]
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-    project ([$$149]) [cardinality: 25.0, op-cost: 0.0, total-cost: 1574882.92]
+    project ([$$149]) [cardinality: 25.0, op-cost: 0.0, total-cost: 1574886.92]
     -- STREAM_PROJECT  |PARTITIONED|
-      assign [$$149] <- [{"$1": 5, "n_name": $$n_name, "revenue": $$165}] [cardinality: 25.0, op-cost: 0.0, total-cost: 1574882.92]
+      assign [$$149] <- [{"$1": 5, "n_name": $$n_name, "revenue": $$165}] [cardinality: 25.0, op-cost: 0.0, total-cost: 1574886.92]
       -- ASSIGN  |PARTITIONED|
-        exchange [cardinality: 25.0, op-cost: 0.0, total-cost: 1574882.92]
+        exchange [cardinality: 25.0, op-cost: 0.0, total-cost: 1574886.92]
         -- SORT_MERGE_EXCHANGE [$$165(DESC) ]  |PARTITIONED|
-          order (DESC, $$165) [cardinality: 25.0, op-cost: 116.1, total-cost: 1574882.92]
+          order (DESC, $$165) [cardinality: 25.0, op-cost: 116.1, total-cost: 1574886.92]
           -- STABLE_SORT [$$165(DESC)]  |PARTITIONED|
-            exchange [cardinality: 25.0, op-cost: 0.0, total-cost: 1574766.82]
+            exchange [cardinality: 25.0, op-cost: 0.0, total-cost: 1574770.82]
             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
               group by ([$$n_name := $$176]) decor ([]) {
                         aggregate [$$165] <- [global-sql-sum-serial($$175)] [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: 38494.17, total-cost: 1574766.82]
+                     } [cardinality: 25.0, op-cost: 38494.17, total-cost: 1574770.82]
               -- EXTERNAL_GROUP_BY[$$176]  |PARTITIONED|
-                exchange [cardinality: 25.0, op-cost: 0.0, total-cost: 1536272.65]
+                exchange [cardinality: 25.0, op-cost: 0.0, total-cost: 1536276.65]
                 -- HASH_PARTITION_EXCHANGE [$$176]  |PARTITIONED|
                   group by ([$$176 := $$150]) decor ([]) {
                             aggregate [$$175] <- [local-sql-sum-serial(numeric-multiply($$173, numeric-subtract(1, $$174)))] [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: 38494.17, total-cost: 1536272.65]
+                         } [cardinality: 25.0, op-cost: 38494.17, total-cost: 1536276.65]
                   -- EXTERNAL_GROUP_BY[$$150]  |PARTITIONED|
-                    exchange [cardinality: 38494.17, op-cost: 0.0, total-cost: 1497778.48]
+                    exchange [cardinality: 38494.17, op-cost: 0.0, total-cost: 1497782.48]
                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                      project ([$$173, $$174, $$150]) [cardinality: 38494.17, op-cost: 0.0, total-cost: 1497778.48]
+                      project ([$$173, $$174, $$150]) [cardinality: 38494.17, op-cost: 0.0, total-cost: 1497782.48]
                       -- STREAM_PROJECT  |PARTITIONED|
-                        exchange [cardinality: 38494.17, op-cost: 0.0, total-cost: 1497778.48]
+                        exchange [cardinality: 38494.17, op-cost: 0.0, total-cost: 1497782.48]
                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                          join (and(eq($$154, $$160), eq($$170, $$159))) [cardinality: 38494.17, op-cost: 23429.97, total-cost: 1497778.48]
+                          join (and(eq($$154, $$160), eq($$170, $$159))) [cardinality: 38494.17, op-cost: 23429.97, total-cost: 1497782.48]
                           -- HYBRID_HASH_JOIN [$$160, $$170][$$154, $$159]  |PARTITIONED|
-                            exchange [cardinality: 19481.97, op-cost: 0.0, total-cost: 1469400.52]
+                            exchange [cardinality: 19481.97, op-cost: 0.0, total-cost: 1469404.52]
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              project ([$$173, $$174, $$150, $$160, $$170]) [cardinality: 19481.97, op-cost: 0.0, total-cost: 1469400.52]
+                              project ([$$173, $$174, $$150, $$160, $$170]) [cardinality: 19481.97, op-cost: 0.0, total-cost: 1469404.52]
                               -- STREAM_PROJECT  |PARTITIONED|
-                                exchange [cardinality: 19481.97, op-cost: 0.0, total-cost: 1469400.52]
+                                exchange [cardinality: 19481.97, op-cost: 0.0, total-cost: 1469404.52]
                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                  join (eq($$158, $$156)) [cardinality: 19481.97, op-cost: 620582.08, total-cost: 1469400.52]
+                                  join (eq($$158, $$156)) [cardinality: 19481.97, op-cost: 620582.08, total-cost: 1469404.52]
                                   -- HYBRID_HASH_JOIN [$$158][$$156]  |PARTITIONED|
                                     exchange [cardinality: 600572.0, op-cost: 0.0, total-cost: 600572.0]
                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -60,13 +60,13 @@
                                                 -- 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: 4861.28, op-cost: 19445.1, total-cost: 248246.43]
+                                    exchange [cardinality: 4861.28, op-cost: 19445.1, total-cost: 248250.43]
                                     -- BROADCAST_EXCHANGE  |PARTITIONED|
-                                      project ([$$150, $$160, $$156]) [cardinality: 4861.28, op-cost: 0.0, total-cost: 228801.33]
+                                      project ([$$150, $$160, $$156]) [cardinality: 4861.28, op-cost: 0.0, total-cost: 228805.33]
                                       -- STREAM_PROJECT  |PARTITIONED|
-                                        exchange [cardinality: 4861.28, op-cost: 19445.1, total-cost: 248246.43]
+                                        exchange [cardinality: 4861.28, op-cost: 19445.1, total-cost: 248250.43]
                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                          join (eq($$155, $$167)) [cardinality: 4861.28, op-cost: 34995.7, total-cost: 228801.33]
+                                          join (eq($$155, $$167)) [cardinality: 4861.28, op-cost: 34995.7, total-cost: 228805.33]
                                           -- HYBRID_HASH_JOIN [$$167][$$155]  |PARTITIONED|
                                             exchange [cardinality: 21307.62, op-cost: 0.0, total-cost: 150000.0]
                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -86,13 +86,13 @@
                                                           -- 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: 3422.02, op-cost: 13688.08, total-cost: 43805.64]
+                                            exchange [cardinality: 3422.02, op-cost: 13688.08, total-cost: 43809.64]
                                             -- BROADCAST_EXCHANGE  |PARTITIONED|
-                                              project ([$$150, $$160, $$155]) [cardinality: 3422.02, op-cost: 0.0, total-cost: 30117.56]
+                                              project ([$$150, $$160, $$155]) [cardinality: 3422.02, op-cost: 0.0, total-cost: 30121.56]
                                               -- STREAM_PROJECT  |PARTITIONED|
-                                                exchange [cardinality: 3422.02, op-cost: 13688.08, total-cost: 43805.64]
+                                                exchange [cardinality: 3422.02, op-cost: 13688.08, total-cost: 43809.64]
                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                  join (eq($$153, $$160)) [cardinality: 3422.02, op-cost: 15036.33, total-cost: 30117.56]
+                                                  join (eq($$153, $$160)) [cardinality: 3422.02, op-cost: 15036.33, total-cost: 30121.56]
                                                   -- HYBRID_HASH_JOIN [$$153][$$160]  |PARTITIONED|
                                                     exchange [cardinality: 15000.0, op-cost: 0.0, total-cost: 15000.0]
                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -108,16 +108,16 @@
                                                               -- 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: 5.56, op-cost: 22.22, total-cost: 81.22]
+                                                    exchange [cardinality: 5.56, op-cost: 22.22, total-cost: 85.22]
                                                     -- BROADCAST_EXCHANGE  |PARTITIONED|
-                                                      project ([$$150, $$160]) [cardinality: 5.56, op-cost: 0.0, total-cost: 59.0]
+                                                      project ([$$150, $$160]) [cardinality: 5.56, op-cost: 0.0, total-cost: 63.0]
                                                       -- STREAM_PROJECT  |PARTITIONED|
-                                                        exchange [cardinality: 5.56, op-cost: 22.22, total-cost: 81.22]
+                                                        exchange [cardinality: 5.56, op-cost: 22.22, total-cost: 85.22]
                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                          join (eq($$163, $$161)) [cardinality: 5.56, op-cost: 25.0, total-cost: 59.0]
+                                                          join (eq($$163, $$161)) [cardinality: 5.56, op-cost: 29.0, total-cost: 63.0]
                                                           -- HYBRID_HASH_JOIN [$$163][$$161]  |PARTITIONED|
                                                             exchange [cardinality: 25.0, op-cost: 0.0, total-cost: 25.0]
-                                                            -- HASH_PARTITION_EXCHANGE [$$163]  |PARTITIONED|
+                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                               project ([$$150, $$160, $$163]) [cardinality: 25.0, op-cost: 0.0, total-cost: 25.0]
                                                               -- STREAM_PROJECT  |PARTITIONED|
                                                                 assign [$$150, $$163] <- [$$n.getField(1), $$n.getField(2)] [cardinality: 25.0, op-cost: 0.0, total-cost: 25.0]
@@ -131,7 +131,7 @@
                                                                         empty-tuple-source [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                                                         -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                                                             exchange [cardinality: 1.0, op-cost: 4.0, total-cost: 9.0]
-                                                            -- HASH_PARTITION_EXCHANGE [$$161]  |PARTITIONED|
+                                                            -- BROADCAST_EXCHANGE  |PARTITIONED|
                                                               project ([$$161]) [cardinality: 1.0, op-cost: 0.0, total-cost: 5.0]
                                                               -- STREAM_PROJECT  |PARTITIONED|
                                                                 select (eq($$r.getField(1), "AFRICA")) [cardinality: 1.0, op-cost: 0.0, total-cost: 5.0]
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.09.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.09.plan
index f5c5738..1e2dd48 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.09.plan
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.09.plan
@@ -1,40 +1,40 @@
-distribute result [$$199] [cardinality: 2.0, op-cost: 0.0, total-cost: 1259139.68]
+distribute result [$$199] [cardinality: 2.0, op-cost: 0.0, total-cost: 1259243.16]
 -- DISTRIBUTE_RESULT  |PARTITIONED|
-  exchange [cardinality: 2.0, op-cost: 0.0, total-cost: 1259139.68]
+  exchange [cardinality: 2.0, op-cost: 0.0, total-cost: 1259243.16]
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-    project ([$$199]) [cardinality: 2.0, op-cost: 0.0, total-cost: 1259139.68]
+    project ([$$199]) [cardinality: 2.0, op-cost: 0.0, total-cost: 1259243.16]
     -- STREAM_PROJECT  |PARTITIONED|
-      assign [$$199] <- [{"$1": 8, "o_year": $$o_year, "mkt_share": numeric-divide($$214, $$215)}] [cardinality: 2.0, op-cost: 0.0, total-cost: 1259139.68]
+      assign [$$199] <- [{"$1": 8, "o_year": $$o_year, "mkt_share": numeric-divide($$214, $$215)}] [cardinality: 2.0, op-cost: 0.0, total-cost: 1259243.16]
       -- ASSIGN  |PARTITIONED|
-        exchange [cardinality: 2.0, op-cost: 0.0, total-cost: 1259139.68]
+        exchange [cardinality: 2.0, op-cost: 0.0, total-cost: 1259243.16]
         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
           group by ([$$o_year := $$238]) decor ([]) {
                     aggregate [$$214, $$215] <- [global-sql-sum-serial($$236), global-sql-sum-serial($$237)] [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: 2.0, op-cost: 193.7, total-cost: 1259139.68]
+                 } [cardinality: 2.0, op-cost: 193.7, total-cost: 1259243.16]
           -- EXTERNAL_GROUP_BY[$$238]  |PARTITIONED|
-            exchange [cardinality: 2.0, op-cost: 0.0, total-cost: 1258945.98]
+            exchange [cardinality: 2.0, op-cost: 0.0, total-cost: 1259049.46]
             -- HASH_PARTITION_EXCHANGE [$$238]  |PARTITIONED|
               group by ([$$238 := $$200]) decor ([]) {
                         aggregate [$$236, $$237] <- [local-sql-sum-serial(switch-case(true, eq($$232, "PERU"), numeric-multiply($$230, numeric-subtract(1, $$231)), 0)), local-sql-sum-serial(numeric-multiply($$230, numeric-subtract(1, $$231)))] [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: 2.0, op-cost: 193.7, total-cost: 1258945.98]
+                     } [cardinality: 2.0, op-cost: 193.7, total-cost: 1259049.46]
               -- EXTERNAL_GROUP_BY[$$200]  |PARTITIONED|
-                exchange [cardinality: 193.7, op-cost: 0.0, total-cost: 1258752.28]
+                exchange [cardinality: 193.7, op-cost: 0.0, total-cost: 1258855.76]
                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                  project ([$$232, $$230, $$231, $$200]) [cardinality: 193.7, op-cost: 0.0, total-cost: 1258752.28]
+                  project ([$$232, $$230, $$231, $$200]) [cardinality: 193.7, op-cost: 0.0, total-cost: 1258855.76]
                   -- STREAM_PROJECT  |PARTITIONED|
-                    assign [$$200] <- [get-year(date($$201))] [cardinality: 193.7, op-cost: 0.0, total-cost: 1258752.28]
+                    assign [$$200] <- [get-year(date($$201))] [cardinality: 193.7, op-cost: 0.0, total-cost: 1258855.76]
                     -- ASSIGN  |PARTITIONED|
-                      project ([$$230, $$231, $$201, $$232]) [cardinality: 193.7, op-cost: 0.0, total-cost: 1258752.28]
+                      project ([$$230, $$231, $$201, $$232]) [cardinality: 193.7, op-cost: 0.0, total-cost: 1258855.76]
                       -- STREAM_PROJECT  |PARTITIONED|
-                        exchange [cardinality: 193.7, op-cost: 0.0, total-cost: 1258752.28]
+                        exchange [cardinality: 193.7, op-cost: 0.0, total-cost: 1258855.76]
                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                          join (eq($$216, $$209)) [cardinality: 193.7, op-cost: 288.86, total-cost: 1258752.28]
+                          join (eq($$216, $$209)) [cardinality: 193.7, op-cost: 288.86, total-cost: 1258855.76]
                           -- HYBRID_HASH_JOIN [$$216][$$209]  |PARTITIONED|
                             exchange [cardinality: 987.0, op-cost: 0.0, total-cost: 1000.0]
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -58,13 +58,13 @@
                                               -- STREAM_PROJECT  |PARTITIONED|
                                                 exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                  join (and(eq($$212, $$210), eq($$218, $$208))) [cardinality: 191.16, op-cost: 938.74, total-cost: 1256617.76]
+                                                  join (and(eq($$212, $$210), eq($$218, $$208))) [cardinality: 191.16, op-cost: 938.74, total-cost: 1256721.24]
                                                   -- HYBRID_HASH_JOIN [$$210, $$218][$$212, $$208]  |PARTITIONED|
                                                     exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                       project ([$$230, $$231, $$201, $$223, $$210, $$218]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                                       -- STREAM_PROJECT  |PARTITIONED|
-                                                        assign [$$218] <- [$$c.getField(3)] [cardinality: 838.74, op-cost: 4189.55, total-cost: 1255554.02]
+                                                        assign [$$218] <- [$$c.getField(3)] [cardinality: 838.74, op-cost: 4189.55, total-cost: 1255657.5]
                                                         -- ASSIGN  |PARTITIONED|
                                                           project ([$$230, $$231, $$223, $$210, $$201, $$c]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                                           -- STREAM_PROJECT  |PARTITIONED|
@@ -78,7 +78,7 @@
                                                                   -- STABLE_SORT [$$226(ASC)]  |PARTITIONED|
                                                                     exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                                                     -- HASH_PARTITION_EXCHANGE [$$226]  |PARTITIONED|
-                                                                      select (and(ge($$201, "1995-01-01"), le($$201, "1996-12-31"))) [cardinality: 837.9, op-cost: 14137.91, total-cost: 1248012.87]
+                                                                      select (and(ge($$201, "1995-01-01"), le($$201, "1996-12-31"))) [cardinality: 837.9, op-cost: 14137.91, total-cost: 1248116.35]
                                                                       -- STREAM_SELECT  |PARTITIONED|
                                                                         project ([$$230, $$231, $$223, $$210, $$226, $$201]) [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                                                         -- STREAM_PROJECT  |PARTITIONED|
@@ -100,7 +100,7 @@
                                                                                         -- STREAM_PROJECT  |PARTITIONED|
                                                                                           exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                            join (eq($$202, $$221)) [cardinality: 2827.58, op-cost: 601513.27, total-cost: 1222564.64]
+                                                                                            join (eq($$202, $$221)) [cardinality: 2827.58, op-cost: 601513.27, total-cost: 1222668.12]
                                                                                             -- HYBRID_HASH_JOIN [$$221][$$202]  |PARTITIONED|
                                                                                               exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -120,7 +120,7 @@
                                                                                                             -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                                                                                               exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                                                                               -- BROADCAST_EXCHANGE  |PARTITIONED|
-                                                                                                join (true) [cardinality: 94.07, op-cost: 94.07, total-cost: 20103.07]
+                                                                                                join (true) [cardinality: 94.07, op-cost: 197.55, total-cost: 20206.55]
                                                                                                 -- NESTED_LOOP  |PARTITIONED|
                                                                                                   exchange [cardinality: 0.0, op-cost: 0.0, total-cost: 0.0]
                                                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|