You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@impala.apache.org by jo...@apache.org on 2024/12/13 23:58:24 UTC
(impala) branch master updated: IMPALA-13480: VALIDATE_CARDINALITY in some aggregation tests
This is an automated email from the ASF dual-hosted git repository.
joemcdonnell pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/impala.git
The following commit(s) were added to refs/heads/master by this push:
new 2828e4737 IMPALA-13480: VALIDATE_CARDINALITY in some aggregation tests
2828e4737 is described below
commit 2828e473710cc246dbdeb9e1da772c45881cfddb
Author: Riza Suminto <ri...@cloudera.com>
AuthorDate: Wed Nov 6 20:09:28 2024 -0800
IMPALA-13480: VALIDATE_CARDINALITY in some aggregation tests
This patch enable VALIDATE_CARDINALITY test options in several planner
tests that touch aggregation node. Enabling it has revealed three bugs.
First, in IMPALA-13405, cardinality estimate of MERGE phase aggregation
is not capped against the output cardinality of the EXCHANGE node. This
patch fix it by adding such capping.
Second, tuple-based optimization IMPALA-13405 can cause cardinality
underestimation if HAVING predicate exist. This is due to the default
selectivity of 10% applied for each HAVING predicate. This patch skip
tuple-based optimization if AggregationNode.conjuncts_ is ever not
empty. It will stay skipped on stats recompute, even if conjuncts_ is
transfered into the next Merge AggregationNode above the plan. The
optimization skip causes following PlannerTest (under
testdata/workloads/functional-planner/queries/PlannerTest/) to revert
their cardinality estimation to their state pior to IMPALA-13405:
- tpcds/tpcds-q39a.test
- tpcds/tpcds-q39b.test
- tpcds_cpu_cost/tpcds-q39a.test
- tpcds_cpu_cost/tpcds-q39b.test
In the future, we should consider raising the default selectivity for
HAVING predicate and undo this skipping logic (IMPALA-13542).
Third, is missing stats recompute after conjunct transfer in multi-phase
aggregation. This will be fixed separately by IMPALA-13526.
Testing:
- Enable cardinality validation in testMultipleDistinct*
- Update aggregation.test to reflect current PlannerTest output.
Added some test cases in aggregation.test.
- Run and pass TpcdsPlannerTest and TpcdsCpuPlannerTest.
- Selectively run some more planner tests that touch AggregationNode and
pass them.
Change-Id: Iadb4af9fd65fdb85b66fae1e403ccec8ca5eb102
Reviewed-on: http://gerrit.cloudera.org:8080/22184
Reviewed-by: Impala Public Jenkins <im...@cloudera.com>
Tested-by: Impala Public Jenkins <im...@cloudera.com>
---
.../org/apache/impala/planner/AggregationNode.java | 58 +-
.../org/apache/impala/planner/PlannerTest.java | 24 +-
.../queries/PlannerTest/aggregation.test | 1013 ++++++++++++++++++--
.../multiple-distinct-materialization.test | 54 +-
.../PlannerTest/multiple-distinct-predicates.test | 36 +-
.../queries/PlannerTest/tpcds/tpcds-q39a.test | 122 +--
.../queries/PlannerTest/tpcds/tpcds-q39b.test | 122 +--
.../PlannerTest/tpcds_cpu_cost/tpcds-q39a.test | 176 ++--
.../PlannerTest/tpcds_cpu_cost/tpcds-q39b.test | 176 ++--
9 files changed, 1324 insertions(+), 457 deletions(-)
diff --git a/fe/src/main/java/org/apache/impala/planner/AggregationNode.java b/fe/src/main/java/org/apache/impala/planner/AggregationNode.java
index 8cd9981a3..eda9d1a8f 100644
--- a/fe/src/main/java/org/apache/impala/planner/AggregationNode.java
+++ b/fe/src/main/java/org/apache/impala/planner/AggregationNode.java
@@ -114,6 +114,14 @@ public class AggregationNode extends PlanNode {
// can not be estimated.
private List<Long> aggClassNumGroups_;
+ // Determine whether to do tuple-based cardinality estimation or skip it.
+ // This flag is here to avoid severe cardinality and memory estimation after
+ // introduction of tuple-based analysis (IMPALA-13405).
+ // May set to true in computeStats() and will stay true during lifetime of this
+ // AggregationNode.
+ // TODO: IMPALA-13542
+ private boolean skipTupleBasedAnalysis_ = false;
+
public AggregationNode(
PlanNodeId id, PlanNode input, MultiAggregateInfo multiAggInfo, AggPhase aggPhase) {
super(id, "AGGREGATE");
@@ -259,14 +267,22 @@ public class AggregationNode extends PlanNode {
getId(), preaggNode.getId(), aggInfos_.size(), preaggNode.aggInfos_.size());
}
+ // DistributedPlanner.java may transfer conjunct to merge phase aggregation later.
+ // Keep skipping tuple-based analysis to maintain same number as single node plan.
+ // TODO: IMPALA-13542
+ skipTupleBasedAnalysis_ |= !conjuncts_.isEmpty();
+
boolean unknownEstimate = false;
aggClassNumGroups_ = Lists.newArrayList();
int aggIdx = 0;
for (AggregateInfo aggInfo : aggInfos_) {
// Compute the cardinality for this set of grouping exprs.
long numGroups = -1;
+ long preaggNumgroup = -1;
if (preaggNode != null) {
- numGroups = preaggNode.aggClassNumGroups_.get(aggIdx);
+ preaggNumgroup = preaggNode.aggClassNumGroups_.get(aggIdx);
+ numGroups = estimateNumGroups(
+ aggInfo.getGroupingExprs(), aggInputCardinality_, preaggNumgroup);
} else {
// TODO: This numGroups is a global estimate accross all data. If this is
// a preaggregation node with N instances, actual number can be as high as
@@ -278,10 +294,10 @@ public class AggregationNode extends PlanNode {
Preconditions.checkState(numGroups >= -1, numGroups);
if (LOG.isTraceEnabled()) {
- LOG.trace(
- "{} aggPhase={} aggInputCardinality={} aggIdx={} numGroups={} aggInfo={}",
+ LOG.trace("{} aggPhase={} aggInputCardinality={} aggIdx={} numGroups={} "
+ + "preaggNumGroup={} aggInfo={}",
getDisplayLabel(), aggPhase_, aggInputCardinality_, aggIdx, numGroups,
- aggInfo.debugString());
+ preaggNumgroup, aggInfo.debugString());
}
aggClassNumGroups_.add(numGroups);
@@ -308,6 +324,20 @@ public class AggregationNode extends PlanNode {
}
}
+ /**
+ * Estimate the number of groups that will be present for the provided grouping
+ * expressions, input cardinality, and num group estimate from preaggregation phase.
+ */
+ public static long estimateNumGroups(
+ List<Expr> groupingExprs, long aggInputCardinality, long preaggNumGroup) {
+ Preconditions.checkArgument(aggInputCardinality >= -1, aggInputCardinality);
+ if (groupingExprs.isEmpty()) {
+ return NON_GROUPING_AGG_NUM_GROUPS;
+ } else {
+ return lowerNumGroupsByInputCardinality(preaggNumGroup, aggInputCardinality);
+ }
+ }
+
/**
* Estimate the number of groups that will be present for the provided grouping
* expressions and input cardinality.
@@ -316,7 +346,14 @@ public class AggregationNode extends PlanNode {
public static long estimateNumGroups(
List<Expr> groupingExprs, long aggInputCardinality, PlanNode planNode) {
Preconditions.checkArgument(aggInputCardinality >= -1, aggInputCardinality);
- if (groupingExprs.isEmpty()) { return NON_GROUPING_AGG_NUM_GROUPS; }
+ if (groupingExprs.isEmpty()) return NON_GROUPING_AGG_NUM_GROUPS;
+ if (planNode instanceof AggregationNode
+ && ((AggregationNode) planNode).skipTupleBasedAnalysis_) {
+ // This AggregationNode has been planned with non-empty conjunct before.
+ // Skip tuple based to avoid severe underestimation.
+ // TODO: IMPALA-13542
+ return estimateNumGroups(groupingExprs, aggInputCardinality);
+ }
// This is prone to overestimation, because we keep multiplying cardinalities,
// even if the grouping exprs are functionally dependent (example:
@@ -424,7 +461,16 @@ public class AggregationNode extends PlanNode {
if (aggInputCardinality >= 0) {
numGroups = Math.min(aggInputCardinality, numGroups);
}
- return numGroups;
+ return lowerNumGroupsByInputCardinality(numGroups, aggInputCardinality);
+ }
+
+ private static long lowerNumGroupsByInputCardinality(
+ long numGroups, long aggInputCardinality) {
+ if (aggInputCardinality >= 0) {
+ return Math.min(numGroups, aggInputCardinality);
+ } else {
+ return numGroups;
+ }
}
/**
diff --git a/fe/src/test/java/org/apache/impala/planner/PlannerTest.java b/fe/src/test/java/org/apache/impala/planner/PlannerTest.java
index 756025de4..54f0ffbf3 100644
--- a/fe/src/test/java/org/apache/impala/planner/PlannerTest.java
+++ b/fe/src/test/java/org/apache/impala/planner/PlannerTest.java
@@ -69,6 +69,13 @@ public class PlannerTest extends PlannerTestBase {
assignment.close();
}
+ private void runCardinalityVerifyingTest(String test_file) {
+ TQueryOptions options = defaultQueryOptions();
+ options.setDisable_hdfs_num_rows_estimate(true); // Needed for deterministic estimate.
+ runPlannerTestFile(
+ test_file, options, ImmutableSet.of(PlannerTestOption.VALIDATE_CARDINALITY));
+ }
+
/**
* Scan node cardinality test
*/
@@ -157,32 +164,32 @@ public class PlannerTest extends PlannerTestBase {
// TODO: Multiple distinct in subqueries.
// TODO: Multiple distinct lineage tests.
// TODO: Multiple distinct and SHUFFLE_DISTINCT_EXPRS tests
- runPlannerTestFile("multiple-distinct");
+ runCardinalityVerifyingTest("multiple-distinct");
}
@Test
public void testMultipleDistinctMaterialization() {
- runPlannerTestFile("multiple-distinct-materialization");
+ runCardinalityVerifyingTest("multiple-distinct-materialization");
}
@Test
public void testMultipleDistinctPredicates() {
- runPlannerTestFile("multiple-distinct-predicates");
+ runCardinalityVerifyingTest("multiple-distinct-predicates");
}
@Test
public void testMultipleDistinctLimit() {
- runPlannerTestFile("multiple-distinct-limit");
+ runCardinalityVerifyingTest("multiple-distinct-limit");
}
@Test
public void testShuffleByDistinctExprs() {
- runPlannerTestFile("shuffle-by-distinct-exprs");
+ runCardinalityVerifyingTest("shuffle-by-distinct-exprs");
}
@Test
public void testAggregation() {
- runPlannerTestFile("aggregation");
+ runCardinalityVerifyingTest("aggregation");
}
@Test
@@ -376,10 +383,7 @@ public class PlannerTest extends PlannerTestBase {
@Test
public void testJoinsWithHDFSNumRowsEstDisabled() {
- TQueryOptions options = defaultQueryOptions();
- options.setDisable_hdfs_num_rows_estimate(true);
- runPlannerTestFile("joins", options,
- ImmutableSet.of(PlannerTestOption.VALIDATE_CARDINALITY));
+ runCardinalityVerifyingTest("joins");
}
@Test
diff --git a/testdata/workloads/functional-planner/queries/PlannerTest/aggregation.test b/testdata/workloads/functional-planner/queries/PlannerTest/aggregation.test
index ad48a6bc2..a602b161f 100644
--- a/testdata/workloads/functional-planner/queries/PlannerTest/aggregation.test
+++ b/testdata/workloads/functional-planner/queries/PlannerTest/aggregation.test
@@ -10,7 +10,7 @@ PLAN-ROOT SINK
| row-size=34B cardinality=1
|
00:SCAN HDFS [functional.alltypesagg]
- partitions=11/11 files=11 size=814.73KB
+ HDFS partitions=11/11 files=11 size=814.73KB
row-size=1B cardinality=11.00K
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
@@ -26,7 +26,7 @@ PLAN-ROOT SINK
| row-size=34B cardinality=1
|
00:SCAN HDFS [functional.alltypesagg]
- partitions=11/11 files=11 size=814.73KB
+ HDFS partitions=11/11 files=11 size=814.73KB
row-size=1B cardinality=11.00K
====
# with grouping
@@ -43,7 +43,7 @@ PLAN-ROOT SINK
| row-size=35B cardinality=9.07K
|
00:SCAN HDFS [functional.alltypesagg]
- partitions=11/11 files=11 size=814.73KB
+ HDFS partitions=11/11 files=11 size=814.73KB
row-size=9B cardinality=11.00K
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
@@ -63,7 +63,7 @@ PLAN-ROOT SINK
| row-size=35B cardinality=9.07K
|
00:SCAN HDFS [functional.alltypesagg]
- partitions=11/11 files=11 size=814.73KB
+ HDFS partitions=11/11 files=11 size=814.73KB
row-size=9B cardinality=11.00K
====
# avg substitution
@@ -76,36 +76,36 @@ PLAN-ROOT SINK
|
02:TOP-N [LIMIT=10]
| order by: avg(zip) ASC
-| row-size=16B cardinality=0
+| row-size=16B cardinality=1
|
01:AGGREGATE [FINALIZE]
| output: avg(id), count(id), avg(zip)
| having: count(id) > 0
-| row-size=24B cardinality=0
+| row-size=24B cardinality=1
|
00:SCAN HDFS [functional.testtbl]
- partitions=1/1 files=0 size=0B
+ HDFS partitions=1/1 files=0 size=0B
row-size=12B cardinality=0
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
|
02:TOP-N [LIMIT=10]
| order by: avg(zip) ASC
-| row-size=16B cardinality=0
+| row-size=16B cardinality=1
|
04:AGGREGATE [FINALIZE]
| output: avg:merge(id), count:merge(id), avg:merge(zip)
| having: count(id) > 0
-| row-size=24B cardinality=0
+| row-size=24B cardinality=1
|
03:EXCHANGE [UNPARTITIONED]
|
01:AGGREGATE
| output: avg(id), count(id), avg(zip)
-| row-size=24B cardinality=0
+| row-size=24B cardinality=1
|
00:SCAN HDFS [functional.testtbl]
- partitions=1/1 files=0 size=0B
+ HDFS partitions=1/1 files=0 size=0B
row-size=12B cardinality=0
====
# Test correct removal of redundant group-by expressions (IMPALA-817)
@@ -123,7 +123,7 @@ PLAN-ROOT SINK
| row-size=16B cardinality=10
|
00:SCAN HDFS [functional.alltypesagg]
- partitions=11/11 files=11 size=814.73KB
+ HDFS partitions=11/11 files=11 size=814.73KB
row-size=4B cardinality=11.00K
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
@@ -144,7 +144,7 @@ PLAN-ROOT SINK
| row-size=16B cardinality=11.00K
|
00:SCAN HDFS [functional.alltypesagg]
- partitions=11/11 files=11 size=814.73KB
+ HDFS partitions=11/11 files=11 size=814.73KB
row-size=4B cardinality=11.00K
====
# Tests that a having predicate triggers slot materialization (IMPALA-846).
@@ -168,11 +168,11 @@ PLAN-ROOT SINK
| row-size=17B cardinality=5.84K
|
|--01:SCAN HDFS [functional.alltypestiny t2]
-| partitions=4/4 files=4 size=460B
+| HDFS partitions=4/4 files=4 size=460B
| row-size=6B cardinality=8
|
00:SCAN HDFS [functional.alltypes t1]
- partitions=24/24 files=24 size=478.45KB
+ HDFS partitions=24/24 files=24 size=478.45KB
runtime filters: RF000 -> t1.smallint_col
row-size=11B cardinality=7.30K
====
@@ -194,7 +194,7 @@ PLAN-ROOT SINK
| row-size=12B cardinality=1
|
00:SCAN HDFS [functional.alltypes]
- partitions=24/24 files=24 size=478.45KB
+ HDFS partitions=24/24 files=24 size=478.45KB
row-size=12B cardinality=7.30K
====
# test distributed aggregation over unions (IMPALA-831)
@@ -217,11 +217,11 @@ PLAN-ROOT SINK
| row-size=0B cardinality=7.40K
|
|--02:SCAN HDFS [functional.alltypessmall]
-| partitions=4/4 files=4 size=6.32KB
+| HDFS partitions=4/4 files=4 size=6.32KB
| row-size=0B cardinality=100
|
01:SCAN HDFS [functional.alltypes]
- partitions=24/24 files=24 size=478.45KB
+ HDFS partitions=24/24 files=24 size=478.45KB
row-size=0B cardinality=7.30K
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
@@ -242,11 +242,11 @@ PLAN-ROOT SINK
| row-size=0B cardinality=7.40K
|
|--02:SCAN HDFS [functional.alltypessmall]
-| partitions=4/4 files=4 size=6.32KB
+| HDFS partitions=4/4 files=4 size=6.32KB
| row-size=0B cardinality=100
|
01:SCAN HDFS [functional.alltypes]
- partitions=24/24 files=24 size=478.45KB
+ HDFS partitions=24/24 files=24 size=478.45KB
row-size=0B cardinality=7.30K
====
# non-distinct agg with grouping over a union
@@ -270,11 +270,11 @@ PLAN-ROOT SINK
| row-size=8B cardinality=7.40K
|
|--02:SCAN HDFS [functional.alltypessmall]
-| partitions=4/4 files=4 size=6.32KB
+| HDFS partitions=4/4 files=4 size=6.32KB
| row-size=8B cardinality=100
|
01:SCAN HDFS [functional.alltypes]
- partitions=24/24 files=24 size=478.45KB
+ HDFS partitions=24/24 files=24 size=478.45KB
row-size=8B cardinality=7.30K
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
@@ -300,11 +300,11 @@ PLAN-ROOT SINK
| row-size=8B cardinality=7.40K
|
|--02:SCAN HDFS [functional.alltypessmall]
-| partitions=4/4 files=4 size=6.32KB
+| HDFS partitions=4/4 files=4 size=6.32KB
| row-size=8B cardinality=100
|
01:SCAN HDFS [functional.alltypes]
- partitions=24/24 files=24 size=478.45KB
+ HDFS partitions=24/24 files=24 size=478.45KB
row-size=8B cardinality=7.30K
====
# distinct agg without grouping over a union
@@ -331,11 +331,11 @@ PLAN-ROOT SINK
| row-size=4B cardinality=7.40K
|
|--02:SCAN HDFS [functional.alltypessmall]
-| partitions=4/4 files=4 size=6.32KB
+| HDFS partitions=4/4 files=4 size=6.32KB
| row-size=4B cardinality=100
|
01:SCAN HDFS [functional.alltypes]
- partitions=24/24 files=24 size=478.45KB
+ HDFS partitions=24/24 files=24 size=478.45KB
row-size=4B cardinality=7.30K
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
@@ -366,11 +366,11 @@ PLAN-ROOT SINK
| row-size=4B cardinality=7.40K
|
|--02:SCAN HDFS [functional.alltypessmall]
-| partitions=4/4 files=4 size=6.32KB
+| HDFS partitions=4/4 files=4 size=6.32KB
| row-size=4B cardinality=100
|
01:SCAN HDFS [functional.alltypes]
- partitions=24/24 files=24 size=478.45KB
+ HDFS partitions=24/24 files=24 size=478.45KB
row-size=4B cardinality=7.30K
====
# distinct agg with grouping over a union
@@ -399,11 +399,11 @@ PLAN-ROOT SINK
| row-size=12B cardinality=7.40K
|
|--02:SCAN HDFS [functional.alltypessmall]
-| partitions=4/4 files=4 size=6.32KB
+| HDFS partitions=4/4 files=4 size=6.32KB
| row-size=12B cardinality=100
|
01:SCAN HDFS [functional.alltypes]
- partitions=24/24 files=24 size=478.45KB
+ HDFS partitions=24/24 files=24 size=478.45KB
row-size=12B cardinality=7.30K
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
@@ -439,11 +439,11 @@ PLAN-ROOT SINK
| row-size=12B cardinality=7.40K
|
|--02:SCAN HDFS [functional.alltypessmall]
-| partitions=4/4 files=4 size=6.32KB
+| HDFS partitions=4/4 files=4 size=6.32KB
| row-size=12B cardinality=100
|
01:SCAN HDFS [functional.alltypes]
- partitions=24/24 files=24 size=478.45KB
+ HDFS partitions=24/24 files=24 size=478.45KB
row-size=12B cardinality=7.30K
====
# mixed distinct and non-distinct agg without grouping over a union
@@ -471,11 +471,11 @@ PLAN-ROOT SINK
| row-size=6B cardinality=7.40K
|
|--02:SCAN HDFS [functional.alltypessmall]
-| partitions=4/4 files=4 size=6.32KB
+| HDFS partitions=4/4 files=4 size=6.32KB
| row-size=6B cardinality=100
|
01:SCAN HDFS [functional.alltypes]
- partitions=24/24 files=24 size=478.45KB
+ HDFS partitions=24/24 files=24 size=478.45KB
row-size=6B cardinality=7.30K
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
@@ -508,11 +508,11 @@ PLAN-ROOT SINK
| row-size=6B cardinality=7.40K
|
|--02:SCAN HDFS [functional.alltypessmall]
-| partitions=4/4 files=4 size=6.32KB
+| HDFS partitions=4/4 files=4 size=6.32KB
| row-size=6B cardinality=100
|
01:SCAN HDFS [functional.alltypes]
- partitions=24/24 files=24 size=478.45KB
+ HDFS partitions=24/24 files=24 size=478.45KB
row-size=6B cardinality=7.30K
====
# mixed distinct and non-distinct agg with grouping over a union
@@ -542,11 +542,11 @@ PLAN-ROOT SINK
| row-size=14B cardinality=7.40K
|
|--02:SCAN HDFS [functional.alltypessmall]
-| partitions=4/4 files=4 size=6.32KB
+| HDFS partitions=4/4 files=4 size=6.32KB
| row-size=14B cardinality=100
|
01:SCAN HDFS [functional.alltypes]
- partitions=24/24 files=24 size=478.45KB
+ HDFS partitions=24/24 files=24 size=478.45KB
row-size=14B cardinality=7.30K
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
@@ -584,11 +584,11 @@ PLAN-ROOT SINK
| row-size=14B cardinality=7.40K
|
|--02:SCAN HDFS [functional.alltypessmall]
-| partitions=4/4 files=4 size=6.32KB
+| HDFS partitions=4/4 files=4 size=6.32KB
| row-size=14B cardinality=100
|
01:SCAN HDFS [functional.alltypes]
- partitions=24/24 files=24 size=478.45KB
+ HDFS partitions=24/24 files=24 size=478.45KB
row-size=14B cardinality=7.30K
====
# mixed distinct and non-distinct agg with grouping over a union distinct
@@ -622,11 +622,11 @@ PLAN-ROOT SINK
| row-size=89B cardinality=7.40K
|
|--02:SCAN HDFS [functional.alltypessmall]
-| partitions=4/4 files=4 size=6.32KB
+| HDFS partitions=4/4 files=4 size=6.32KB
| row-size=89B cardinality=100
|
01:SCAN HDFS [functional.alltypes]
- partitions=24/24 files=24 size=478.45KB
+ HDFS partitions=24/24 files=24 size=478.45KB
row-size=89B cardinality=7.30K
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
@@ -674,11 +674,11 @@ PLAN-ROOT SINK
| row-size=89B cardinality=7.40K
|
|--02:SCAN HDFS [functional.alltypessmall]
-| partitions=4/4 files=4 size=6.32KB
+| HDFS partitions=4/4 files=4 size=6.32KB
| row-size=89B cardinality=100
|
01:SCAN HDFS [functional.alltypes]
- partitions=24/24 files=24 size=478.45KB
+ HDFS partitions=24/24 files=24 size=478.45KB
row-size=89B cardinality=7.30K
====
# Mixed distinct and non-distinct agg with intermediate type different from input type
@@ -698,7 +698,7 @@ PLAN-ROOT SINK
| row-size=24B cardinality=200.52K
|
00:SCAN HDFS [tpch_parquet.lineitem]
- partitions=1/1 files=3 size=193.60MB
+ HDFS partitions=1/1 files=3 size=193.98MB
row-size=24B cardinality=6.00M
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
@@ -726,7 +726,7 @@ PLAN-ROOT SINK
| row-size=24B cardinality=200.52K
|
00:SCAN HDFS [tpch_parquet.lineitem]
- partitions=1/1 files=3 size=193.60MB
+ HDFS partitions=1/1 files=3 size=193.98MB
row-size=24B cardinality=6.00M
====
# test that aggregations are not placed below an unpartitioned exchange with a limit
@@ -739,7 +739,7 @@ PLAN-ROOT SINK
| row-size=8B cardinality=1
|
00:SCAN HDFS [functional.alltypes]
- partitions=24/24 files=24 size=478.45KB
+ HDFS partitions=24/24 files=24 size=478.45KB
limit: 10
row-size=0B cardinality=10
---- DISTRIBUTEDPLAN
@@ -753,7 +753,7 @@ PLAN-ROOT SINK
| limit: 10
|
00:SCAN HDFS [functional.alltypes]
- partitions=24/24 files=24 size=478.45KB
+ HDFS partitions=24/24 files=24 size=478.45KB
limit: 10
row-size=0B cardinality=10
====
@@ -775,11 +775,11 @@ PLAN-ROOT SINK
| row-size=0B cardinality=10
|
|--02:SCAN HDFS [functional.alltypessmall]
-| partitions=4/4 files=4 size=6.32KB
+| HDFS partitions=4/4 files=4 size=6.32KB
| row-size=0B cardinality=100
|
01:SCAN HDFS [functional.alltypes]
- partitions=24/24 files=24 size=478.45KB
+ HDFS partitions=24/24 files=24 size=478.45KB
row-size=0B cardinality=7.30K
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
@@ -797,11 +797,11 @@ PLAN-ROOT SINK
| row-size=0B cardinality=10
|
|--02:SCAN HDFS [functional.alltypessmall]
-| partitions=4/4 files=4 size=6.32KB
+| HDFS partitions=4/4 files=4 size=6.32KB
| row-size=0B cardinality=100
|
01:SCAN HDFS [functional.alltypes]
- partitions=24/24 files=24 size=478.45KB
+ HDFS partitions=24/24 files=24 size=478.45KB
row-size=0B cardinality=7.30K
====
# test that limits are applied at the top-level merge aggregation node for non-grouping
@@ -840,11 +840,11 @@ PLAN-ROOT SINK
| row-size=8B cardinality=9
|
|--01:SCAN HDFS [functional.alltypestiny t2]
-| partitions=4/4 files=4 size=460B
+| HDFS partitions=4/4 files=4 size=460B
| row-size=4B cardinality=8
|
00:SCAN HDFS [functional.alltypesagg t1]
- partitions=11/11 files=11 size=814.73KB
+ HDFS partitions=11/11 files=11 size=814.73KB
runtime filters: RF000 -> t1.id
row-size=4B cardinality=11.00K
---- DISTRIBUTEDPLAN
@@ -888,11 +888,11 @@ PLAN-ROOT SINK
|--07:EXCHANGE [BROADCAST]
| |
| 01:SCAN HDFS [functional.alltypestiny t2]
-| partitions=4/4 files=4 size=460B
+| HDFS partitions=4/4 files=4 size=460B
| row-size=4B cardinality=8
|
00:SCAN HDFS [functional.alltypesagg t1]
- partitions=11/11 files=11 size=814.73KB
+ HDFS partitions=11/11 files=11 size=814.73KB
runtime filters: RF000 -> t1.id
row-size=4B cardinality=11.00K
====
@@ -913,10 +913,10 @@ PLAN-ROOT SINK
01:AGGREGATE [FINALIZE]
| group by: tinyint_col, smallint_col, int_col + int_col, coalesce(bigint_col, `year`)
| having: int_col + int_col = coalesce(bigint_col, `year`), smallint_col = int_col + int_col
-| row-size=19B cardinality=730
+| row-size=19B cardinality=73
|
00:SCAN HDFS [functional.alltypes]
- partitions=24/24 files=24 size=478.45KB
+ HDFS partitions=24/24 files=24 size=478.45KB
predicates: functional.alltypes.tinyint_col = functional.alltypes.smallint_col
row-size=19B cardinality=730
====
@@ -934,7 +934,7 @@ PLAN-ROOT SINK
| row-size=13B cardinality=2
|
00:SCAN HDFS [functional.alltypestiny]
- partitions=4/4 files=4 size=460B
+ HDFS partitions=4/4 files=4 size=460B
row-size=1B cardinality=8
====
# IMPALA-1917: Test NULL literals inside inline view with grouping aggregation.
@@ -955,7 +955,7 @@ PLAN-ROOT SINK
| row-size=6B cardinality=4
|
00:SCAN HDFS [functional.alltypestiny]
- partitions=4/4 files=4 size=460B
+ HDFS partitions=4/4 files=4 size=460B
row-size=5B cardinality=8
====
# test simple group_concat with distinct
@@ -972,7 +972,7 @@ PLAN-ROOT SINK
| row-size=15B cardinality=963
|
00:SCAN HDFS [functional.alltypesagg]
- partitions=11/11 files=11 size=814.73KB
+ HDFS partitions=11/11 files=11 size=814.73KB
row-size=15B cardinality=11.00K
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
@@ -998,7 +998,7 @@ PLAN-ROOT SINK
| row-size=15B cardinality=963
|
00:SCAN HDFS [functional.alltypesagg]
- partitions=11/11 files=11 size=814.73KB
+ HDFS partitions=11/11 files=11 size=814.73KB
row-size=15B cardinality=11.00K
====
# test group_concat and a group by
@@ -1022,7 +1022,7 @@ PLAN-ROOT SINK
| row-size=23B cardinality=1.10K
|
00:SCAN HDFS [functional.alltypesagg]
- partitions=11/11 files=11 size=814.73KB
+ HDFS partitions=11/11 files=11 size=814.73KB
predicates: `day` = id % 100
row-size=23B cardinality=1.10K
---- DISTRIBUTEDPLAN
@@ -1046,7 +1046,7 @@ PLAN-ROOT SINK
| row-size=23B cardinality=1.10K
|
00:SCAN HDFS [functional.alltypesagg]
- partitions=11/11 files=11 size=814.73KB
+ HDFS partitions=11/11 files=11 size=814.73KB
predicates: `day` = id % 100
row-size=23B cardinality=1.10K
====
@@ -1067,7 +1067,7 @@ PLAN-ROOT SINK
| row-size=20B cardinality=10.21K
|
00:SCAN HDFS [functional.alltypesagg]
- partitions=11/11 files=11 size=814.73KB
+ HDFS partitions=11/11 files=11 size=814.73KB
row-size=20B cardinality=11.00K
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
@@ -1097,7 +1097,7 @@ PLAN-ROOT SINK
| row-size=20B cardinality=10.21K
|
00:SCAN HDFS [functional.alltypesagg]
- partitions=11/11 files=11 size=814.73KB
+ HDFS partitions=11/11 files=11 size=814.73KB
row-size=20B cardinality=11.00K
====
# test group_concat distinct with other non-distinct aggregate functions
@@ -1115,7 +1115,7 @@ PLAN-ROOT SINK
| row-size=23B cardinality=963
|
00:SCAN HDFS [functional.alltypesagg]
- partitions=11/11 files=11 size=814.73KB
+ HDFS partitions=11/11 files=11 size=814.73KB
row-size=15B cardinality=11.00K
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
@@ -1143,7 +1143,7 @@ PLAN-ROOT SINK
| row-size=23B cardinality=963
|
00:SCAN HDFS [functional.alltypesagg]
- partitions=11/11 files=11 size=814.73KB
+ HDFS partitions=11/11 files=11 size=814.73KB
row-size=15B cardinality=11.00K
====
# test group_concat distinct with other aggregate functions, with custom separator
@@ -1162,7 +1162,7 @@ PLAN-ROOT SINK
| row-size=23B cardinality=963
|
00:SCAN HDFS [functional.alltypesagg]
- partitions=11/11 files=11 size=814.73KB
+ HDFS partitions=11/11 files=11 size=814.73KB
row-size=19B cardinality=11.00K
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
@@ -1190,7 +1190,7 @@ PLAN-ROOT SINK
| row-size=23B cardinality=963
|
00:SCAN HDFS [functional.alltypesagg]
- partitions=11/11 files=11 size=814.73KB
+ HDFS partitions=11/11 files=11 size=814.73KB
row-size=19B cardinality=11.00K
====
# test group_concat distinct with other aggregate functions, with custom separator
@@ -1212,7 +1212,7 @@ PLAN-ROOT SINK
| row-size=36B cardinality=10
|
00:SCAN HDFS [functional.alltypesagg]
- partitions=11/11 files=11 size=814.73KB
+ HDFS partitions=11/11 files=11 size=814.73KB
row-size=28B cardinality=11.00K
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
@@ -1244,7 +1244,7 @@ PLAN-ROOT SINK
| row-size=36B cardinality=10
|
00:SCAN HDFS [functional.alltypesagg]
- partitions=11/11 files=11 size=814.73KB
+ HDFS partitions=11/11 files=11 size=814.73KB
row-size=28B cardinality=11.00K
====
# test multiple group_concat distinct, each with a different separator
@@ -1262,7 +1262,7 @@ PLAN-ROOT SINK
| row-size=15B cardinality=963
|
00:SCAN HDFS [functional.alltypesagg]
- partitions=11/11 files=11 size=814.73KB
+ HDFS partitions=11/11 files=11 size=814.73KB
row-size=15B cardinality=11.00K
---- DISTRIBUTEDPLAN
PLAN-ROOT SINK
@@ -1288,7 +1288,7 @@ PLAN-ROOT SINK
| row-size=15B cardinality=963
|
00:SCAN HDFS [functional.alltypesagg]
- partitions=11/11 files=11 size=814.73KB
+ HDFS partitions=11/11 files=11 size=814.73KB
row-size=15B cardinality=11.00K
====
# IMPALA-852: Aggregation only in the HAVING clause.
@@ -1299,10 +1299,10 @@ PLAN-ROOT SINK
01:AGGREGATE [FINALIZE]
| output: count(*)
| having: count(*) > 0
-| row-size=8B cardinality=0
+| row-size=8B cardinality=1
|
00:SCAN HDFS [functional.alltypestiny]
- partitions=4/4 files=4 size=460B
+ HDFS partitions=4/4 files=4 size=460B
row-size=0B cardinality=8
====
# Grouping aggregation where input is partitioned on grouping expr.
@@ -1325,7 +1325,7 @@ PLAN-ROOT SINK
| group by: c_custkey
| having: count(*) < 150000
| limit: 1000000
-| row-size=16B cardinality=15.00K
+| row-size=16B cardinality=9.15K
|
02:HASH JOIN [INNER JOIN, PARTITIONED]
| hash predicates: c_custkey = o_custkey
@@ -1335,13 +1335,13 @@ PLAN-ROOT SINK
|--05:EXCHANGE [HASH(o_custkey)]
| |
| 01:SCAN HDFS [tpch_parquet.orders]
-| partitions=1/1 files=2 size=54.07MB
+| HDFS partitions=1/1 files=2 size=54.21MB
| row-size=8B cardinality=1.50M
|
04:EXCHANGE [HASH(c_custkey)]
|
00:SCAN HDFS [tpch_parquet.customer]
- partitions=1/1 files=1 size=12.31MB
+ HDFS partitions=1/1 files=1 size=12.34MB
predicates: c_nationkey = 16
runtime filters: RF000 -> c_custkey
row-size=10B cardinality=6.00K
@@ -1361,13 +1361,13 @@ PLAN-ROOT SINK
| output: count:merge(c_custkey)
| having: count(c_custkey) > 50
| limit: 50
-| row-size=8B cardinality=0
+| row-size=8B cardinality=1
|
07:EXCHANGE [UNPARTITIONED]
|
04:AGGREGATE
| output: count(c_custkey)
-| row-size=8B cardinality=0
+| row-size=8B cardinality=1
|
03:AGGREGATE
| group by: c_custkey
@@ -1381,13 +1381,13 @@ PLAN-ROOT SINK
|--06:EXCHANGE [HASH(c_custkey)]
| |
| 01:SCAN HDFS [tpch_parquet.customer]
-| partitions=1/1 files=1 size=12.31MB
+| HDFS partitions=1/1 files=1 size=12.34MB
| row-size=8B cardinality=150.00K
|
05:EXCHANGE [HASH(o_custkey)]
|
00:SCAN HDFS [tpch_parquet.orders]
- partitions=1/1 files=2 size=54.07MB
+ HDFS partitions=1/1 files=2 size=54.21MB
runtime filters: RF000 -> o_custkey
row-size=8B cardinality=1.50M
====
@@ -1416,7 +1416,7 @@ PLAN-ROOT SINK
|
03:AGGREGATE
| group by: c_custkey, c_custkey
-| row-size=16B cardinality=1.50M
+| row-size=16B cardinality=150.00K
|
02:HASH JOIN [INNER JOIN, PARTITIONED]
| hash predicates: o_custkey = c_custkey
@@ -1426,13 +1426,13 @@ PLAN-ROOT SINK
|--06:EXCHANGE [HASH(c_custkey)]
| |
| 01:SCAN HDFS [tpch_parquet.customer]
-| partitions=1/1 files=1 size=12.31MB
+| HDFS partitions=1/1 files=1 size=12.34MB
| row-size=8B cardinality=150.00K
|
05:EXCHANGE [HASH(o_custkey)]
|
00:SCAN HDFS [tpch_parquet.orders]
- partitions=1/1 files=2 size=54.07MB
+ HDFS partitions=1/1 files=2 size=54.21MB
runtime filters: RF000 -> o_custkey
row-size=8B cardinality=1.50M
====
@@ -1469,7 +1469,7 @@ PLAN-ROOT SINK
|--08:EXCHANGE [BROADCAST]
| |
| 02:SCAN HDFS [tpch_parquet.customer]
-| partitions=1/1 files=1 size=12.31MB
+| HDFS partitions=1/1 files=1 size=12.34MB
| row-size=35B cardinality=150.00K
|
03:HASH JOIN [INNER JOIN, PARTITIONED]
@@ -1480,14 +1480,14 @@ PLAN-ROOT SINK
|--07:EXCHANGE [HASH(o_orderkey,o_clerk)]
| |
| 01:SCAN HDFS [tpch_parquet.orders]
-| partitions=1/1 files=2 size=54.07MB
+| HDFS partitions=1/1 files=2 size=54.21MB
| runtime filters: RF000 -> o_custkey, RF001 -> o_comment
| row-size=104B cardinality=1.50M
|
06:EXCHANGE [HASH(l_orderkey,l_returnflag)]
|
00:SCAN HDFS [tpch_parquet.lineitem]
- partitions=1/1 files=3 size=193.60MB
+ HDFS partitions=1/1 files=3 size=193.98MB
runtime filters: RF004 -> l_orderkey, RF005 -> l_returnflag
row-size=21B cardinality=5.76M(filtered from 6.00M)
====
@@ -1522,13 +1522,13 @@ PLAN-ROOT SINK
|--05:EXCHANGE [HASH(t2.id)]
| |
| 01:SCAN HDFS [functional.alltypessmall t2]
-| partitions=4/4 files=4 size=6.32KB
+| HDFS partitions=4/4 files=4 size=6.32KB
| row-size=4B cardinality=100
|
04:EXCHANGE [HASH(t1.id)]
|
00:SCAN HDFS [functional.alltypes t1]
- partitions=24/24 files=24 size=478.45KB
+ HDFS partitions=24/24 files=24 size=478.45KB
row-size=4B cardinality=7.30K
====
# IMPALA-4263: Grouping agg is placed in the join fragment and has no merge step.
@@ -1554,13 +1554,13 @@ PLAN-ROOT SINK
|--05:EXCHANGE [HASH(t2.id)]
| |
| 01:SCAN HDFS [functional.alltypessmall t2]
-| partitions=4/4 files=4 size=6.32KB
+| HDFS partitions=4/4 files=4 size=6.32KB
| row-size=4B cardinality=100
|
04:EXCHANGE [HASH(t1.id)]
|
00:SCAN HDFS [functional.alltypes t1]
- partitions=24/24 files=24 size=478.45KB
+ HDFS partitions=24/24 files=24 size=478.45KB
row-size=4B cardinality=7.30K
====
# IMPALA-4263: Grouping agg is placed in the second join fragment and has no merge step.
@@ -1591,7 +1591,7 @@ PLAN-ROOT SINK
|--09:EXCHANGE [HASH(t3.id)]
| |
| 02:SCAN HDFS [functional.alltypestiny t3]
-| partitions=4/4 files=4 size=460B
+| HDFS partitions=4/4 files=4 size=460B
| row-size=4B cardinality=8
|
08:EXCHANGE [HASH(t2.id)]
@@ -1603,13 +1603,13 @@ PLAN-ROOT SINK
|--07:EXCHANGE [HASH(t2.int_col)]
| |
| 01:SCAN HDFS [functional.alltypessmall t2]
-| partitions=4/4 files=4 size=6.32KB
+| HDFS partitions=4/4 files=4 size=6.32KB
| row-size=8B cardinality=100
|
06:EXCHANGE [HASH(t1.int_col)]
|
00:SCAN HDFS [functional.alltypes t1]
- partitions=24/24 files=24 size=478.45KB
+ HDFS partitions=24/24 files=24 size=478.45KB
row-size=4B cardinality=7.30K
====
# IMPALA-10096: use the original ordinal if the group by ordinal reference is a
@@ -1666,7 +1666,7 @@ PLAN-ROOT SINK
01:AGGREGATE [FINALIZE]
| output: count(*)
| group by: 3, id
-| row-size=18B cardinality=10
+| row-size=17B cardinality=10
|
00:SCAN HDFS [functional.dimtbl]
HDFS partitions=1/1 files=1 size=171B
@@ -1690,6 +1690,823 @@ PLAN-ROOT SINK
|
00:SCAN HDFS [tpcds.store_sales a]
partition predicates: ss_sold_date_sk > 245263
- HDFS partitions=1823/1824 files=1823 size=195.68MB
+ HDFS partitions=1823/1824 files=1823 size=336.51MB
row-size=16B cardinality=2.75M
+---- PARALLELPLANS
+PLAN-ROOT SINK
+|
+04:EXCHANGE [UNPARTITIONED]
+|
+03:AGGREGATE [FINALIZE]
+| output: count:merge(ss_ticket_number)
+| group by: ss_item_sk, ss_item_sk + 300
+| row-size=24B cardinality=2.75M
+|
+02:EXCHANGE [HASH(ss_item_sk,ss_item_sk + 300)]
+|
+01:AGGREGATE [STREAMING]
+| output: count(ss_ticket_number)
+| group by: ss_item_sk, ss_item_sk + 300
+| row-size=24B cardinality=2.75M
+|
+00:SCAN HDFS [tpcds.store_sales a]
+ partition predicates: ss_sold_date_sk > 245263
+ HDFS partitions=1823/1824 files=1823 size=336.51MB
+ row-size=16B cardinality=2.75M
+====
+# Baseline for next similar test cases below that do 2-phase aggregation.
+# The query output is:
+# +----+----+----+
+# | bc | c1 | c2 |
+# +----+----+----+
+# | 0 | 12 | 1 |
+# | 10 | 12 | 1 |
+# | 20 | 12 | 1 |
+# | 30 | 12 | 1 |
+# | 40 | 12 | 1 |
+# | 50 | 8 | 1 |
+# | 60 | 8 | 1 |
+# | 70 | 8 | 1 |
+# | 80 | 8 | 1 |
+# | 90 | 8 | 1 |
+# +----+----+----+
+select
+ bigint_col bc, count(smallint_col) c1, count(distinct int_col) c2
+from functional.alltypessmall
+group by bigint_col
+order by bc;
+---- PLAN
+PLAN-ROOT SINK
+|
+03:SORT
+| order by: bigint_col ASC
+| row-size=24B cardinality=10
+|
+02:AGGREGATE [FINALIZE]
+| output: count(int_col), count:merge(smallint_col)
+| group by: bigint_col
+| row-size=24B cardinality=10
+|
+01:AGGREGATE
+| output: count(smallint_col)
+| group by: bigint_col, int_col
+| row-size=20B cardinality=100
+|
+00:SCAN HDFS [functional.alltypessmall]
+ HDFS partitions=4/4 files=4 size=6.32KB
+ row-size=14B cardinality=100
+---- PARALLELPLANS
+PLAN-ROOT SINK
+|
+08:MERGING-EXCHANGE [UNPARTITIONED]
+| order by: bigint_col ASC
+|
+03:SORT
+| order by: bigint_col ASC
+| row-size=24B cardinality=10
+|
+07:AGGREGATE [FINALIZE]
+| output: count:merge(int_col), count:merge(smallint_col)
+| group by: bigint_col
+| row-size=24B cardinality=10
+|
+06:EXCHANGE [HASH(bigint_col)]
+|
+02:AGGREGATE [STREAMING]
+| output: count(int_col), count:merge(smallint_col)
+| group by: bigint_col
+| row-size=24B cardinality=10
+|
+05:AGGREGATE
+| output: count:merge(smallint_col)
+| group by: bigint_col, int_col
+| row-size=20B cardinality=100
+|
+04:EXCHANGE [HASH(bigint_col,int_col)]
+|
+01:AGGREGATE [STREAMING]
+| output: count(smallint_col)
+| group by: bigint_col, int_col
+| row-size=20B cardinality=100
+|
+00:SCAN HDFS [functional.alltypessmall]
+ HDFS partitions=4/4 files=4 size=6.32KB
+ row-size=14B cardinality=100
+====
+# Add limit 7.
+select
+ bigint_col bc, count(smallint_col) c1, count(distinct int_col) c2
+from functional.alltypessmall
+group by bigint_col
+order by bc
+limit 7;
+---- PLAN
+PLAN-ROOT SINK
+|
+03:TOP-N [LIMIT=7]
+| order by: bigint_col ASC
+| row-size=24B cardinality=7
+|
+02:AGGREGATE [FINALIZE]
+| output: count(int_col), count:merge(smallint_col)
+| group by: bigint_col
+| row-size=24B cardinality=10
+|
+01:AGGREGATE
+| output: count(smallint_col)
+| group by: bigint_col, int_col
+| row-size=20B cardinality=100
+|
+00:SCAN HDFS [functional.alltypessmall]
+ HDFS partitions=4/4 files=4 size=6.32KB
+ row-size=14B cardinality=100
+---- PARALLELPLANS
+PLAN-ROOT SINK
+|
+08:MERGING-EXCHANGE [UNPARTITIONED]
+| order by: bigint_col ASC
+| limit: 7
+|
+03:TOP-N [LIMIT=7]
+| order by: bigint_col ASC
+| row-size=24B cardinality=7
+|
+07:AGGREGATE [FINALIZE]
+| output: count:merge(int_col), count:merge(smallint_col)
+| group by: bigint_col
+| row-size=24B cardinality=10
+|
+06:EXCHANGE [HASH(bigint_col)]
+|
+02:AGGREGATE [STREAMING]
+| output: count(int_col), count:merge(smallint_col)
+| group by: bigint_col
+| row-size=24B cardinality=10
+|
+05:AGGREGATE
+| output: count:merge(smallint_col)
+| group by: bigint_col, int_col
+| row-size=20B cardinality=100
+|
+04:EXCHANGE [HASH(bigint_col,int_col)]
+|
+01:AGGREGATE [STREAMING]
+| output: count(smallint_col)
+| group by: bigint_col, int_col
+| row-size=20B cardinality=100
+|
+00:SCAN HDFS [functional.alltypessmall]
+ HDFS partitions=4/4 files=4 size=6.32KB
+ row-size=14B cardinality=100
+====
+# Add equality HAVING predicate over grouping column.
+# That HAVING predicate should be pushed down to scan.
+select
+ bigint_col bc, count(smallint_col) c1, count(distinct int_col) c2
+from functional.alltypessmall
+group by bigint_col
+having bigint_col = 0
+order by bc;
+---- PLAN
+PLAN-ROOT SINK
+|
+03:SORT
+| order by: bigint_col ASC
+| row-size=24B cardinality=10
+|
+02:AGGREGATE [FINALIZE]
+| output: count(int_col), count:merge(smallint_col)
+| group by: bigint_col
+| row-size=24B cardinality=10
+|
+01:AGGREGATE
+| output: count(smallint_col)
+| group by: bigint_col, int_col
+| row-size=20B cardinality=10
+|
+00:SCAN HDFS [functional.alltypessmall]
+ HDFS partitions=4/4 files=4 size=6.32KB
+ predicates: functional.alltypessmall.bigint_col = 0
+ row-size=14B cardinality=10
+---- PARALLELPLANS
+PLAN-ROOT SINK
+|
+08:MERGING-EXCHANGE [UNPARTITIONED]
+| order by: bigint_col ASC
+|
+03:SORT
+| order by: bigint_col ASC
+| row-size=24B cardinality=10
+|
+07:AGGREGATE [FINALIZE]
+| output: count:merge(int_col), count:merge(smallint_col)
+| group by: bigint_col
+| row-size=24B cardinality=10
+|
+06:EXCHANGE [HASH(bigint_col)]
+|
+02:AGGREGATE [STREAMING]
+| output: count(int_col), count:merge(smallint_col)
+| group by: bigint_col
+| row-size=24B cardinality=10
+|
+05:AGGREGATE
+| output: count:merge(smallint_col)
+| group by: bigint_col, int_col
+| row-size=20B cardinality=10
+|
+04:EXCHANGE [HASH(bigint_col,int_col)]
+|
+01:AGGREGATE [STREAMING]
+| output: count(smallint_col)
+| group by: bigint_col, int_col
+| row-size=20B cardinality=10
+|
+00:SCAN HDFS [functional.alltypessmall]
+ HDFS partitions=4/4 files=4 size=6.32KB
+ predicates: functional.alltypessmall.bigint_col = 0
+ row-size=14B cardinality=10
+====
+# Add equality HAVING predicate over output expression.
+# Actual cardinality of 02:AGGREGATE is 5.
+# TODO: Apply the 10% default selectivity instead.
+select
+ bigint_col bc, count(smallint_col) c1, count(distinct int_col) c2
+from functional.alltypessmall
+group by bigint_col
+having count(smallint_col) = 8
+order by bc;
+---- PLAN
+PLAN-ROOT SINK
+|
+03:SORT
+| order by: bigint_col ASC
+| row-size=24B cardinality=1
+|
+02:AGGREGATE [FINALIZE]
+| output: count(int_col), count:merge(smallint_col)
+| group by: bigint_col
+| having: count(smallint_col) = 8
+| row-size=24B cardinality=1
+|
+01:AGGREGATE
+| output: count(smallint_col)
+| group by: bigint_col, int_col
+| row-size=20B cardinality=100
+|
+00:SCAN HDFS [functional.alltypessmall]
+ HDFS partitions=4/4 files=4 size=6.32KB
+ row-size=14B cardinality=100
+---- PARALLELPLANS
+PLAN-ROOT SINK
+|
+08:MERGING-EXCHANGE [UNPARTITIONED]
+| order by: bigint_col ASC
+|
+03:SORT
+| order by: bigint_col ASC
+| row-size=24B cardinality=1
+|
+07:AGGREGATE [FINALIZE]
+| output: count:merge(int_col), count:merge(smallint_col)
+| group by: bigint_col
+| having: count(smallint_col) = 8
+| row-size=24B cardinality=1
+|
+06:EXCHANGE [HASH(bigint_col)]
+|
+02:AGGREGATE [STREAMING]
+| output: count(int_col), count:merge(smallint_col)
+| group by: bigint_col
+| row-size=24B cardinality=1
+|
+05:AGGREGATE
+| output: count:merge(smallint_col)
+| group by: bigint_col, int_col
+| row-size=20B cardinality=100
+|
+04:EXCHANGE [HASH(bigint_col,int_col)]
+|
+01:AGGREGATE [STREAMING]
+| output: count(smallint_col)
+| group by: bigint_col, int_col
+| row-size=20B cardinality=100
+|
+00:SCAN HDFS [functional.alltypessmall]
+ HDFS partitions=4/4 files=4 size=6.32KB
+ row-size=14B cardinality=100
+====
+# Add in-list HAVING predicate over output expression.
+# Actual cardinality of 02:AGGREGATE is 5.
+# TODO: Apply the 10% default selectivity instead.
+select
+ bigint_col bc, count(smallint_col) c1, count(distinct int_col) c2
+from functional.alltypessmall
+group by bigint_col
+having count(smallint_col) in (7, 8, 9)
+order by bc;
+---- PLAN
+PLAN-ROOT SINK
+|
+03:SORT
+| order by: bigint_col ASC
+| row-size=24B cardinality=3
+|
+02:AGGREGATE [FINALIZE]
+| output: count(int_col), count:merge(smallint_col)
+| group by: bigint_col
+| having: count(smallint_col) IN (7, 8, 9)
+| row-size=24B cardinality=3
+|
+01:AGGREGATE
+| output: count(smallint_col)
+| group by: bigint_col, int_col
+| row-size=20B cardinality=100
+|
+00:SCAN HDFS [functional.alltypessmall]
+ HDFS partitions=4/4 files=4 size=6.32KB
+ row-size=14B cardinality=100
+---- PARALLELPLANS
+PLAN-ROOT SINK
+|
+08:MERGING-EXCHANGE [UNPARTITIONED]
+| order by: bigint_col ASC
+|
+03:SORT
+| order by: bigint_col ASC
+| row-size=24B cardinality=3
+|
+07:AGGREGATE [FINALIZE]
+| output: count:merge(int_col), count:merge(smallint_col)
+| group by: bigint_col
+| having: count(smallint_col) IN (7, 8, 9)
+| row-size=24B cardinality=3
+|
+06:EXCHANGE [HASH(bigint_col)]
+|
+02:AGGREGATE [STREAMING]
+| output: count(int_col), count:merge(smallint_col)
+| group by: bigint_col
+| row-size=24B cardinality=3
+|
+05:AGGREGATE
+| output: count:merge(smallint_col)
+| group by: bigint_col, int_col
+| row-size=20B cardinality=100
+|
+04:EXCHANGE [HASH(bigint_col,int_col)]
+|
+01:AGGREGATE [STREAMING]
+| output: count(smallint_col)
+| group by: bigint_col, int_col
+| row-size=20B cardinality=100
+|
+00:SCAN HDFS [functional.alltypessmall]
+ HDFS partitions=4/4 files=4 size=6.32KB
+ row-size=14B cardinality=100
+====
+# Aggregation across nested views. The NDV of aggregation columns are following:
+# ss_item_sk = 17975
+# ss_customer_sk = 90632
+# d_month_seq = 2421
+# numRows(store_sales) = 2880404 & numRows(date_dim) = 73049
+with v1 as (
+ select ss_item_sk, ss_customer_sk, d_month_seq, count(*)
+ from tpcds.store_sales ss
+ inner join tpcds.date_dim dd on ss.ss_sold_date_sk = dd.d_date_sk
+ group by ss_item_sk, ss_customer_sk, d_month_seq),
+ v2 as (
+ select ss_item_sk, ss_customer_sk, count(*)
+ from v1 group by ss_item_sk, ss_customer_sk)
+select ss_item_sk, count(*) from v2 group by ss_item_sk;
+---- PLAN
+PLAN-ROOT SINK
+|
+05:AGGREGATE [FINALIZE]
+| output: count(*)
+| group by: ss_item_sk
+| row-size=16B cardinality=17.98K
+|
+04:AGGREGATE [FINALIZE]
+| group by: ss_item_sk, ss_customer_sk
+| row-size=12B cardinality=2.88M
+|
+03:AGGREGATE [FINALIZE]
+| group by: ss_item_sk, ss_customer_sk, d_month_seq
+| row-size=16B cardinality=2.88M
+|
+02:HASH JOIN [INNER JOIN]
+| hash predicates: ss.ss_sold_date_sk = dd.d_date_sk
+| runtime filters: RF000 <- dd.d_date_sk
+| row-size=24B cardinality=2.88M
+|
+|--01:SCAN HDFS [tpcds.date_dim dd]
+| HDFS partitions=1/1 files=1 size=9.84MB
+| row-size=8B cardinality=73.05K
+|
+00:SCAN HDFS [tpcds.store_sales ss]
+ HDFS partitions=1824/1824 files=1824 size=346.60MB
+ runtime filters: RF000 -> ss.ss_sold_date_sk
+ row-size=16B cardinality=2.88M
+---- PARALLELPLANS
+PLAN-ROOT SINK
+|
+13:EXCHANGE [UNPARTITIONED]
+|
+12:AGGREGATE [FINALIZE]
+| output: count:merge(*)
+| group by: ss_item_sk
+| row-size=16B cardinality=17.98K
+|
+11:EXCHANGE [HASH(ss_item_sk)]
+|
+05:AGGREGATE [STREAMING]
+| output: count(*)
+| group by: ss_item_sk
+| row-size=16B cardinality=17.98K
+|
+10:AGGREGATE [FINALIZE]
+| group by: ss_item_sk, ss_customer_sk
+| row-size=12B cardinality=2.88M
+|
+09:EXCHANGE [HASH(ss_item_sk,ss_customer_sk)]
+|
+04:AGGREGATE [STREAMING]
+| group by: ss_item_sk, ss_customer_sk
+| row-size=12B cardinality=2.88M
+|
+08:AGGREGATE [FINALIZE]
+| group by: ss_item_sk, ss_customer_sk, d_month_seq
+| row-size=16B cardinality=2.88M
+|
+07:EXCHANGE [HASH(ss_item_sk,ss_customer_sk,d_month_seq)]
+|
+03:AGGREGATE [STREAMING]
+| group by: ss_item_sk, ss_customer_sk, d_month_seq
+| row-size=16B cardinality=2.88M
+|
+02:HASH JOIN [INNER JOIN, BROADCAST]
+| hash predicates: ss.ss_sold_date_sk = dd.d_date_sk
+| row-size=24B cardinality=2.88M
+|
+|--JOIN BUILD
+| | join-table-id=00 plan-id=01 cohort-id=01
+| | build expressions: dd.d_date_sk
+| | runtime filters: RF000 <- dd.d_date_sk
+| |
+| 06:EXCHANGE [BROADCAST]
+| |
+| 01:SCAN HDFS [tpcds.date_dim dd]
+| HDFS partitions=1/1 files=1 size=9.84MB
+| row-size=8B cardinality=73.05K
+|
+00:SCAN HDFS [tpcds.store_sales ss]
+ HDFS partitions=1824/1824 files=1824 size=346.60MB
+ runtime filters: RF000 -> ss.ss_sold_date_sk
+ row-size=16B cardinality=2.88M
+====
+# Aggregation across nested views where the grouping columns do not change.
+# The NDV of aggregation columns are following:
+# c_nationkey = 25
+# c_custkey = 150000
+with v1 as (
+ select c_nationkey, c_custkey, count(*)
+ from tpch.customer
+ group by c_nationkey, c_custkey),
+ v2 as (
+ select c_nationkey, c_custkey, count(*)
+ from v1, tpch.orders
+ where c_custkey = o_custkey
+ group by c_nationkey, c_custkey)
+select c_nationkey, count(*) from v2 group by c_nationkey;
+---- PLAN
+PLAN-ROOT SINK
+|
+05:AGGREGATE [FINALIZE]
+| output: count(*)
+| group by: c_nationkey
+| row-size=10B cardinality=25
+|
+04:AGGREGATE [FINALIZE]
+| group by: c_nationkey, c_custkey
+| row-size=10B cardinality=1.50M
+|
+03:HASH JOIN [INNER JOIN]
+| hash predicates: o_custkey = c_custkey
+| runtime filters: RF000 <- c_custkey
+| row-size=18B cardinality=1.50M
+|
+|--01:AGGREGATE [FINALIZE]
+| | group by: c_nationkey, c_custkey
+| | row-size=10B cardinality=150.00K
+| |
+| 00:SCAN HDFS [tpch.customer]
+| HDFS partitions=1/1 files=1 size=23.08MB
+| row-size=10B cardinality=150.00K
+|
+02:SCAN HDFS [tpch.orders]
+ HDFS partitions=1/1 files=1 size=162.56MB
+ runtime filters: RF000 -> o_custkey
+ row-size=8B cardinality=1.50M
+---- PARALLELPLANS
+PLAN-ROOT SINK
+|
+13:EXCHANGE [UNPARTITIONED]
+|
+12:AGGREGATE [FINALIZE]
+| output: count:merge(*)
+| group by: c_nationkey
+| row-size=10B cardinality=25
+|
+11:EXCHANGE [HASH(c_nationkey)]
+|
+05:AGGREGATE [STREAMING]
+| output: count(*)
+| group by: c_nationkey
+| row-size=10B cardinality=25
+|
+10:AGGREGATE [FINALIZE]
+| group by: c_nationkey, c_custkey
+| row-size=10B cardinality=1.50M
+|
+09:EXCHANGE [HASH(c_nationkey,c_custkey)]
+|
+04:AGGREGATE [STREAMING]
+| group by: c_nationkey, c_custkey
+| row-size=10B cardinality=1.50M
+|
+03:HASH JOIN [INNER JOIN, BROADCAST]
+| hash predicates: o_custkey = c_custkey
+| row-size=18B cardinality=1.50M
+|
+|--JOIN BUILD
+| | join-table-id=00 plan-id=01 cohort-id=01
+| | build expressions: c_custkey
+| | runtime filters: RF000 <- c_custkey
+| |
+| 08:EXCHANGE [BROADCAST]
+| |
+| 07:AGGREGATE [FINALIZE]
+| | group by: c_nationkey, c_custkey
+| | row-size=10B cardinality=150.00K
+| |
+| 06:EXCHANGE [HASH(c_nationkey,c_custkey)]
+| |
+| 01:AGGREGATE [STREAMING]
+| | group by: c_nationkey, c_custkey
+| | row-size=10B cardinality=150.00K
+| |
+| 00:SCAN HDFS [tpch.customer]
+| HDFS partitions=1/1 files=1 size=23.08MB
+| row-size=10B cardinality=150.00K
+|
+02:SCAN HDFS [tpch.orders]
+ HDFS partitions=1/1 files=1 size=162.56MB
+ runtime filters: RF000 -> o_custkey
+ row-size=8B cardinality=1.50M
+====
+# Aggregation across nested views where the grouping columns do not change,
+# and there is a UnionNode in plan tree.
+# The NDV of aggregation columns are following:
+# c_nationkey = 25
+# c_custkey = 150000
+with v0 as (
+ select * from tpch.customer
+ union all
+ select * from tpch.customer
+ ),
+ v1 as (
+ select c_nationkey, c_custkey, count(*)
+ from v0
+ group by c_nationkey, c_custkey),
+ v2 as (
+ select c_nationkey, c_custkey, count(*)
+ from v1, tpch.orders
+ where c_custkey = o_custkey
+ group by c_nationkey, c_custkey)
+select c_nationkey, count(*) from v2 group by c_nationkey;
+---- PLAN
+PLAN-ROOT SINK
+|
+07:AGGREGATE [FINALIZE]
+| output: count(*)
+| group by: c_nationkey
+| row-size=10B cardinality=25
+|
+06:AGGREGATE [FINALIZE]
+| group by: c_nationkey, c_custkey
+| row-size=10B cardinality=3.00M
+|
+05:HASH JOIN [INNER JOIN]
+| hash predicates: o_custkey = c_custkey
+| runtime filters: RF000 <- c_custkey
+| row-size=18B cardinality=3.00M
+|
+|--03:AGGREGATE [FINALIZE]
+| | group by: c_nationkey, c_custkey
+| | row-size=10B cardinality=300.00K
+| |
+| 00:UNION
+| | pass-through-operands: all
+| | row-size=10B cardinality=300.00K
+| |
+| |--02:SCAN HDFS [tpch.customer]
+| | HDFS partitions=1/1 files=1 size=23.08MB
+| | row-size=10B cardinality=150.00K
+| |
+| 01:SCAN HDFS [tpch.customer]
+| HDFS partitions=1/1 files=1 size=23.08MB
+| row-size=10B cardinality=150.00K
+|
+04:SCAN HDFS [tpch.orders]
+ HDFS partitions=1/1 files=1 size=162.56MB
+ runtime filters: RF000 -> o_custkey
+ row-size=8B cardinality=1.50M
+---- PARALLELPLANS
+PLAN-ROOT SINK
+|
+15:EXCHANGE [UNPARTITIONED]
+|
+14:AGGREGATE [FINALIZE]
+| output: count:merge(*)
+| group by: c_nationkey
+| row-size=10B cardinality=25
+|
+13:EXCHANGE [HASH(c_nationkey)]
+|
+07:AGGREGATE [STREAMING]
+| output: count(*)
+| group by: c_nationkey
+| row-size=10B cardinality=25
+|
+12:AGGREGATE [FINALIZE]
+| group by: c_nationkey, c_custkey
+| row-size=10B cardinality=3.00M
+|
+11:EXCHANGE [HASH(c_nationkey,c_custkey)]
+|
+06:AGGREGATE [STREAMING]
+| group by: c_nationkey, c_custkey
+| row-size=10B cardinality=3.00M
+|
+05:HASH JOIN [INNER JOIN, BROADCAST]
+| hash predicates: o_custkey = c_custkey
+| row-size=18B cardinality=3.00M
+|
+|--JOIN BUILD
+| | join-table-id=00 plan-id=01 cohort-id=01
+| | build expressions: c_custkey
+| | runtime filters: RF000 <- c_custkey
+| |
+| 10:EXCHANGE [BROADCAST]
+| |
+| 09:AGGREGATE [FINALIZE]
+| | group by: c_nationkey, c_custkey
+| | row-size=10B cardinality=300.00K
+| |
+| 08:EXCHANGE [HASH(c_nationkey,c_custkey)]
+| |
+| 03:AGGREGATE [STREAMING]
+| | group by: c_nationkey, c_custkey
+| | row-size=10B cardinality=300.00K
+| |
+| 00:UNION
+| | pass-through-operands: all
+| | row-size=10B cardinality=300.00K
+| |
+| |--02:SCAN HDFS [tpch.customer]
+| | HDFS partitions=1/1 files=1 size=23.08MB
+| | row-size=10B cardinality=150.00K
+| |
+| 01:SCAN HDFS [tpch.customer]
+| HDFS partitions=1/1 files=1 size=23.08MB
+| row-size=10B cardinality=150.00K
+|
+04:SCAN HDFS [tpch.orders]
+ HDFS partitions=1/1 files=1 size=162.56MB
+ runtime filters: RF000 -> o_custkey
+ row-size=8B cardinality=1.50M
+====
+# Aggregation across nested views where the grouping columns do not change,
+# and there is a UnionNode with limit in plan tree.
+# The NDV of aggregation columns are following:
+# c_nationkey = 25
+# c_custkey = 150000
+with v0 as (
+ select * from (
+ select * from tpch.customer
+ union all
+ select * from tpch.customer
+ ) u limit 100
+ ),
+ v1 as (
+ select c_nationkey, c_custkey, count(*)
+ from v0
+ group by c_nationkey, c_custkey),
+ v2 as (
+ select c_nationkey, c_custkey, count(*)
+ from v1, tpch.orders
+ where c_custkey = o_custkey
+ group by c_nationkey, c_custkey)
+select c_nationkey, count(*) from v2 group by c_nationkey;
+---- PLAN
+PLAN-ROOT SINK
+|
+07:AGGREGATE [FINALIZE]
+| output: count(*)
+| group by: c_nationkey
+| row-size=10B cardinality=25
+|
+06:AGGREGATE [FINALIZE]
+| group by: c_nationkey, c_custkey
+| row-size=10B cardinality=1.52K
+|
+05:HASH JOIN [INNER JOIN]
+| hash predicates: o_custkey = c_custkey
+| runtime filters: RF000 <- c_custkey
+| row-size=18B cardinality=1.52K
+|
+|--03:AGGREGATE [FINALIZE]
+| | group by: c_nationkey, c_custkey
+| | row-size=10B cardinality=100
+| |
+| 00:UNION
+| | pass-through-operands: all
+| | limit: 100
+| | row-size=10B cardinality=100
+| |
+| |--02:SCAN HDFS [tpch.customer]
+| | HDFS partitions=1/1 files=1 size=23.08MB
+| | row-size=10B cardinality=150.00K
+| |
+| 01:SCAN HDFS [tpch.customer]
+| HDFS partitions=1/1 files=1 size=23.08MB
+| row-size=10B cardinality=150.00K
+|
+04:SCAN HDFS [tpch.orders]
+ HDFS partitions=1/1 files=1 size=162.56MB
+ runtime filters: RF000 -> o_custkey
+ row-size=8B cardinality=1.50M
+---- PARALLELPLANS
+PLAN-ROOT SINK
+|
+14:EXCHANGE [UNPARTITIONED]
+|
+13:AGGREGATE [FINALIZE]
+| output: count:merge(*)
+| group by: c_nationkey
+| row-size=10B cardinality=25
+|
+12:EXCHANGE [HASH(c_nationkey)]
+|
+07:AGGREGATE [STREAMING]
+| output: count(*)
+| group by: c_nationkey
+| row-size=10B cardinality=25
+|
+11:AGGREGATE [FINALIZE]
+| group by: c_nationkey, c_custkey
+| row-size=10B cardinality=1.52K
+|
+10:EXCHANGE [HASH(c_nationkey,c_custkey)]
+|
+06:AGGREGATE [STREAMING]
+| group by: c_nationkey, c_custkey
+| row-size=10B cardinality=1.52K
+|
+05:HASH JOIN [INNER JOIN, BROADCAST]
+| hash predicates: o_custkey = c_custkey
+| row-size=18B cardinality=1.52K
+|
+|--JOIN BUILD
+| | join-table-id=00 plan-id=01 cohort-id=01
+| | build expressions: c_custkey
+| | runtime filters: RF000 <- c_custkey
+| |
+| 09:EXCHANGE [BROADCAST]
+| |
+| 03:AGGREGATE [FINALIZE]
+| | group by: c_nationkey, c_custkey
+| | row-size=10B cardinality=100
+| |
+| 08:EXCHANGE [UNPARTITIONED]
+| | limit: 100
+| |
+| 00:UNION
+| | pass-through-operands: all
+| | limit: 100
+| | row-size=10B cardinality=100
+| |
+| |--02:SCAN HDFS [tpch.customer]
+| | HDFS partitions=1/1 files=1 size=23.08MB
+| | row-size=10B cardinality=150.00K
+| |
+| 01:SCAN HDFS [tpch.customer]
+| HDFS partitions=1/1 files=1 size=23.08MB
+| row-size=10B cardinality=150.00K
+|
+04:SCAN HDFS [tpch.orders]
+ HDFS partitions=1/1 files=1 size=162.56MB
+ runtime filters: RF000 -> o_custkey
+ row-size=8B cardinality=1.50M
====
diff --git a/testdata/workloads/functional-planner/queries/PlannerTest/multiple-distinct-materialization.test b/testdata/workloads/functional-planner/queries/PlannerTest/multiple-distinct-materialization.test
index c43005464..d17267e2b 100644
--- a/testdata/workloads/functional-planner/queries/PlannerTest/multiple-distinct-materialization.test
+++ b/testdata/workloads/functional-planner/queries/PlannerTest/multiple-distinct-materialization.test
@@ -185,7 +185,7 @@ PLAN-ROOT SINK
03:AGGREGATE [FINALIZE]
| output: aggif(valid_tid(2,5,7,10,11) = 2, count(tinyint_col)), aggif(valid_tid(2,5,7,10,11) = 7, count(int_col)), aggif(valid_tid(2,5,7,10,11) = 11, min(float_col)), aggif(valid_tid(2,5,7,10,11) = 11, max(float_col)), aggif(valid_tid(2,5,7,10,11) = 11, sum(double_col))
| group by: CASE valid_tid(2,7,11) WHEN 2 THEN string_col WHEN 7 THEN string_col WHEN 11 THEN string_col END, CASE valid_tid(2,7,11) WHEN 2 THEN date_string_col WHEN 7 THEN date_string_col WHEN 11 THEN date_string_col END
-| row-size=56B cardinality=8.11K
+| row-size=56B cardinality=7.30K
|
02:AGGREGATE [FINALIZE]
| Class 0
@@ -197,7 +197,7 @@ PLAN-ROOT SINK
| Class 2
| output: min:merge(float_col), max:merge(float_col), sum:merge(double_col)
| group by: string_col, date_string_col
-| row-size=131B cardinality=22.08K
+| row-size=131B cardinality=21.90K
|
01:AGGREGATE
| Class 0
@@ -220,7 +220,7 @@ PLAN-ROOT SINK
03:AGGREGATE [FINALIZE]
| output: aggif(valid_tid(2,5,7,10,11) = 2, count(tinyint_col)), aggif(valid_tid(2,5,7,10,11) = 7, count(int_col)), aggif(valid_tid(2,5,7,10,11) = 11, min(float_col)), aggif(valid_tid(2,5,7,10,11) = 11, max(float_col)), aggif(valid_tid(2,5,7,10,11) = 11, sum(double_col))
| group by: CASE valid_tid(2,7,11) WHEN 2 THEN string_col WHEN 7 THEN string_col WHEN 11 THEN string_col END, CASE valid_tid(2,7,11) WHEN 2 THEN date_string_col WHEN 7 THEN date_string_col WHEN 11 THEN date_string_col END
-| row-size=56B cardinality=8.11K
+| row-size=56B cardinality=7.30K
|
07:AGGREGATE [FINALIZE]
| Class 0
@@ -232,7 +232,7 @@ PLAN-ROOT SINK
| Class 2
| output: min:merge(float_col), max:merge(float_col), sum:merge(double_col)
| group by: string_col, date_string_col
-| row-size=131B cardinality=22.08K
+| row-size=131B cardinality=21.90K
|
06:EXCHANGE [HASH(CASE valid_tid(2,7,11) WHEN 2 THEN murmur_hash(string_col) WHEN 7 THEN murmur_hash(string_col) WHEN 11 THEN murmur_hash(string_col) END,CASE valid_tid(2,7,11) WHEN 2 THEN murmur_hash(date_string_col) WHEN 7 THEN murmur_hash(date_string_col) WHEN 11 THEN murmur_hash(date_string_col) END)]
|
@@ -246,7 +246,7 @@ PLAN-ROOT SINK
| Class 2
| output: min:merge(float_col), max:merge(float_col), sum:merge(double_col)
| group by: string_col, date_string_col
-| row-size=131B cardinality=22.08K
+| row-size=131B cardinality=21.90K
|
05:AGGREGATE
| Class 0
@@ -291,7 +291,7 @@ PLAN-ROOT SINK
03:AGGREGATE [FINALIZE]
| output: aggif(valid_tid(3,6,9,12,13) = 3, count(tinyint_col)), aggif(valid_tid(3,6,9,12,13) = 9, avg(int_col)), aggif(valid_tid(3,6,9,12,13) = 13, max(float_col))
| group by: CASE valid_tid(3,9,13) WHEN 3 THEN string_col WHEN 9 THEN string_col WHEN 13 THEN string_col END, CASE valid_tid(3,9,13) WHEN 3 THEN date_string_col WHEN 9 THEN date_string_col WHEN 13 THEN date_string_col END
-| row-size=44B cardinality=8.11K
+| row-size=44B cardinality=7.30K
|
02:AGGREGATE [FINALIZE]
| Class 0
@@ -303,7 +303,7 @@ PLAN-ROOT SINK
| Class 2
| output: max:merge(float_col)
| group by: string_col, date_string_col
-| row-size=119B cardinality=22.08K
+| row-size=119B cardinality=21.90K
|
01:AGGREGATE
| Class 0
@@ -326,7 +326,7 @@ PLAN-ROOT SINK
03:AGGREGATE [FINALIZE]
| output: aggif(valid_tid(3,6,9,12,13) = 3, count(tinyint_col)), aggif(valid_tid(3,6,9,12,13) = 9, avg(int_col)), aggif(valid_tid(3,6,9,12,13) = 13, max(float_col))
| group by: CASE valid_tid(3,9,13) WHEN 3 THEN string_col WHEN 9 THEN string_col WHEN 13 THEN string_col END, CASE valid_tid(3,9,13) WHEN 3 THEN date_string_col WHEN 9 THEN date_string_col WHEN 13 THEN date_string_col END
-| row-size=44B cardinality=8.11K
+| row-size=44B cardinality=7.30K
|
07:AGGREGATE [FINALIZE]
| Class 0
@@ -338,7 +338,7 @@ PLAN-ROOT SINK
| Class 2
| output: max:merge(float_col)
| group by: string_col, date_string_col
-| row-size=119B cardinality=22.08K
+| row-size=119B cardinality=21.90K
|
06:EXCHANGE [HASH(CASE valid_tid(2,8,13) WHEN 2 THEN murmur_hash(string_col) WHEN 8 THEN murmur_hash(string_col) WHEN 13 THEN murmur_hash(string_col) END,CASE valid_tid(2,8,13) WHEN 2 THEN murmur_hash(date_string_col) WHEN 8 THEN murmur_hash(date_string_col) WHEN 13 THEN murmur_hash(date_string_col) END)]
|
@@ -352,7 +352,7 @@ PLAN-ROOT SINK
| Class 2
| output: max:merge(float_col)
| group by: string_col, date_string_col
-| row-size=119B cardinality=22.08K
+| row-size=119B cardinality=21.90K
|
05:AGGREGATE
| Class 0
@@ -479,7 +479,7 @@ PLAN-ROOT SINK
03:AGGREGATE [FINALIZE]
| output: aggif(valid_tid(2,4,5) = 2, count(tinyint_col)), aggif(valid_tid(2,4,5) = 4, count(smallint_col)), aggif(valid_tid(2,4,5) = 5, min(timestamp_col)), aggif(valid_tid(2,4,5) = 5, max(date_string_col))
| having: aggif(valid_tid(2,4,5) = 4, count(smallint_col)) < 10, aggif(valid_tid(2,4,5) = 5, max(date_string_col)) = 'test'
-| row-size=44B cardinality=0
+| row-size=44B cardinality=1
|
02:AGGREGATE [FINALIZE]
| Class 0
@@ -508,7 +508,7 @@ PLAN-ROOT SINK
03:AGGREGATE [FINALIZE]
| output: aggif(valid_tid(2,4,5) = 2, count(tinyint_col)), aggif(valid_tid(2,4,5) = 4, count(smallint_col)), aggif(valid_tid(2,4,5) = 5, min(timestamp_col)), aggif(valid_tid(2,4,5) = 5, max(date_string_col))
| having: aggif(valid_tid(2,4,5) = 4, count(smallint_col)) < 10, aggif(valid_tid(2,4,5) = 5, max(date_string_col)) = 'test'
-| row-size=44B cardinality=0
+| row-size=44B cardinality=1
|
07:AGGREGATE [FINALIZE]
| Class 0
@@ -748,7 +748,7 @@ PLAN-ROOT SINK
02:AGGREGATE [FINALIZE]
| output: count(tinyint_col)
| having: count(tinyint_col) < 9
-| row-size=8B cardinality=0
+| row-size=8B cardinality=1
|
01:AGGREGATE
| group by: tinyint_col
@@ -769,7 +769,7 @@ PLAN-ROOT SINK
|
02:AGGREGATE
| output: count(tinyint_col)
-| row-size=8B cardinality=0
+| row-size=8B cardinality=1
|
05:AGGREGATE
| group by: tinyint_col
@@ -798,7 +798,7 @@ PLAN-ROOT SINK
02:AGGREGATE [FINALIZE]
| output: count(smallint_col)
| having: count(smallint_col) < 9
-| row-size=8B cardinality=0
+| row-size=8B cardinality=1
|
01:AGGREGATE
| group by: smallint_col
@@ -819,7 +819,7 @@ PLAN-ROOT SINK
|
02:AGGREGATE
| output: count(smallint_col)
-| row-size=8B cardinality=0
+| row-size=8B cardinality=1
|
05:AGGREGATE
| group by: smallint_col
@@ -849,7 +849,7 @@ PLAN-ROOT SINK
01:AGGREGATE [FINALIZE]
| output: min(string_col), max(string_col)
| having: max(string_col) > '0', min(string_col) < '9', max(string_col) < min(string_col)
-| row-size=24B cardinality=0
+| row-size=24B cardinality=1
|
00:SCAN HDFS [functional.alltypes]
HDFS partitions=24/24 files=24 size=478.45KB
@@ -860,7 +860,7 @@ PLAN-ROOT SINK
04:AGGREGATE [FINALIZE]
| output: min:merge(string_col), max:merge(string_col)
| having: max(string_col) > '0', min(string_col) < '9', max(string_col) < min(string_col)
-| row-size=24B cardinality=0
+| row-size=24B cardinality=1
|
03:EXCHANGE [UNPARTITIONED]
|
@@ -886,7 +886,7 @@ PLAN-ROOT SINK
02:AGGREGATE [FINALIZE]
| output: count(tinyint_col), min:merge(string_col), max:merge(string_col)
| having: count(tinyint_col) = 10, max(string_col) > '0', min(string_col) < '9', max(string_col) < min(string_col)
-| row-size=32B cardinality=0
+| row-size=32B cardinality=1
|
01:AGGREGATE
| output: min(string_col), max(string_col)
@@ -908,7 +908,7 @@ PLAN-ROOT SINK
|
02:AGGREGATE
| output: count(tinyint_col), min:merge(string_col), max:merge(string_col)
-| row-size=32B cardinality=0
+| row-size=32B cardinality=1
|
05:AGGREGATE
| output: min:merge(string_col), max:merge(string_col)
@@ -962,7 +962,7 @@ PLAN-ROOT SINK
| output: count:merge(tinyint_col)
| group by: date_string_col, timestamp_col
| having: count(tinyint_col) < 10
-| row-size=44B cardinality=10
+| row-size=44B cardinality=1
|
06:EXCHANGE [HASH(date_string_col,timestamp_col)]
|
@@ -1003,7 +1003,7 @@ PLAN-ROOT SINK
| output: count(tinyint_col), min:merge(string_col), max:merge(string_col)
| group by: date_string_col, timestamp_col
| having: count(tinyint_col) < 10, date_string_col < timestamp_col
-| row-size=68B cardinality=0
+| row-size=68B cardinality=1
|
01:AGGREGATE
| output: min(string_col), max(string_col)
@@ -1023,14 +1023,14 @@ PLAN-ROOT SINK
| output: count:merge(tinyint_col), min:merge(string_col), max:merge(string_col)
| group by: date_string_col, timestamp_col
| having: count(tinyint_col) < 10, date_string_col < timestamp_col
-| row-size=68B cardinality=3
+| row-size=68B cardinality=1
|
06:EXCHANGE [HASH(date_string_col,timestamp_col)]
|
02:AGGREGATE [STREAMING]
| output: count(tinyint_col), min:merge(string_col), max:merge(string_col)
| group by: date_string_col, timestamp_col
-| row-size=68B cardinality=0
+| row-size=68B cardinality=1
|
05:AGGREGATE
| output: min:merge(string_col), max:merge(string_col)
@@ -1066,7 +1066,7 @@ PLAN-ROOT SINK
| output: count(smallint_col), max:merge(string_col)
| group by: date_string_col, timestamp_col
| having: count(smallint_col) < 20, date_string_col < timestamp_col
-| row-size=56B cardinality=0
+| row-size=56B cardinality=1
|
01:AGGREGATE
| output: max(string_col)
@@ -1086,14 +1086,14 @@ PLAN-ROOT SINK
| output: count:merge(smallint_col), max:merge(string_col)
| group by: date_string_col, timestamp_col
| having: count(smallint_col) < 20, date_string_col < timestamp_col
-| row-size=56B cardinality=3
+| row-size=56B cardinality=1
|
06:EXCHANGE [HASH(date_string_col,timestamp_col)]
|
02:AGGREGATE [STREAMING]
| output: count(smallint_col), max:merge(string_col)
| group by: date_string_col, timestamp_col
-| row-size=56B cardinality=0
+| row-size=56B cardinality=1
|
05:AGGREGATE
| output: max:merge(string_col)
diff --git a/testdata/workloads/functional-planner/queries/PlannerTest/multiple-distinct-predicates.test b/testdata/workloads/functional-planner/queries/PlannerTest/multiple-distinct-predicates.test
index 8cb72f4e2..a5769be81 100644
--- a/testdata/workloads/functional-planner/queries/PlannerTest/multiple-distinct-predicates.test
+++ b/testdata/workloads/functional-planner/queries/PlannerTest/multiple-distinct-predicates.test
@@ -10,7 +10,7 @@ PLAN-ROOT SINK
03:AGGREGATE [FINALIZE]
| output: aggif(valid_tid(2,4,5) = 2, count(tinyint_col)), aggif(valid_tid(2,4,5) = 4, count(smallint_col)), aggif(valid_tid(2,4,5) = 5, max(string_col))
| having: aggif(valid_tid(2,4,5) = 2, count(tinyint_col)) < 10, aggif(valid_tid(2,4,5) = 4, count(smallint_col)) <= 30, aggif(valid_tid(2,4,5) = 4, count(smallint_col)) >= 20, aggif(valid_tid(2,4,5) = 5, max(string_col)) = 'test'
-| row-size=28B cardinality=0
+| row-size=28B cardinality=1
|
02:AGGREGATE [FINALIZE]
| Class 0
@@ -39,7 +39,7 @@ PLAN-ROOT SINK
03:AGGREGATE [FINALIZE]
| output: aggif(valid_tid(2,4,5) = 2, count(tinyint_col)), aggif(valid_tid(2,4,5) = 4, count(smallint_col)), aggif(valid_tid(2,4,5) = 5, max(string_col))
| having: aggif(valid_tid(2,4,5) = 2, count(tinyint_col)) < 10, aggif(valid_tid(2,4,5) = 4, count(smallint_col)) <= 30, aggif(valid_tid(2,4,5) = 4, count(smallint_col)) >= 20, aggif(valid_tid(2,4,5) = 5, max(string_col)) = 'test'
-| row-size=28B cardinality=0
+| row-size=28B cardinality=1
|
07:AGGREGATE [FINALIZE]
| Class 0
@@ -95,7 +95,7 @@ PLAN-ROOT SINK
03:AGGREGATE [FINALIZE]
| output: aggif(valid_tid(2,4,5) = 2, count(tinyint_col)), aggif(valid_tid(2,4,5) = 4, count(smallint_col)), aggif(valid_tid(2,4,5) = 5, max(string_col))
| having: aggif(valid_tid(2,4,5) = 2, count(tinyint_col)) < aggif(valid_tid(2,4,5) = 4, count(smallint_col))
-| row-size=28B cardinality=0
+| row-size=28B cardinality=1
|
02:AGGREGATE [FINALIZE]
| Class 0
@@ -124,7 +124,7 @@ PLAN-ROOT SINK
03:AGGREGATE [FINALIZE]
| output: aggif(valid_tid(2,4,5) = 2, count(tinyint_col)), aggif(valid_tid(2,4,5) = 4, count(smallint_col)), aggif(valid_tid(2,4,5) = 5, max(string_col))
| having: aggif(valid_tid(2,4,5) = 2, count(tinyint_col)) < aggif(valid_tid(2,4,5) = 4, count(smallint_col))
-| row-size=28B cardinality=0
+| row-size=28B cardinality=1
|
07:AGGREGATE [FINALIZE]
| Class 0
@@ -181,7 +181,7 @@ PLAN-ROOT SINK
03:AGGREGATE [FINALIZE]
| output: aggif(valid_tid(2,4,5) = 2, count(tinyint_col)), aggif(valid_tid(2,4,5) = 4, count(smallint_col)), aggif(valid_tid(2,4,5) = 5, max(string_col))
| having: aggif(valid_tid(2,4,5) = 2, count(tinyint_col)) < aggif(valid_tid(2,4,5) = 4, count(smallint_col)), aggif(valid_tid(2,4,5) = 4, count(smallint_col)) <= 30, aggif(valid_tid(2,4,5) = 4, count(smallint_col)) >= 20, aggif(valid_tid(2,4,5) = 5, max(string_col)) = 'test'
-| row-size=28B cardinality=0
+| row-size=28B cardinality=1
|
02:AGGREGATE [FINALIZE]
| Class 0
@@ -210,7 +210,7 @@ PLAN-ROOT SINK
03:AGGREGATE [FINALIZE]
| output: aggif(valid_tid(2,4,5) = 2, count(tinyint_col)), aggif(valid_tid(2,4,5) = 4, count(smallint_col)), aggif(valid_tid(2,4,5) = 5, max(string_col))
| having: aggif(valid_tid(2,4,5) = 2, count(tinyint_col)) < aggif(valid_tid(2,4,5) = 4, count(smallint_col)), aggif(valid_tid(2,4,5) = 4, count(smallint_col)) <= 30, aggif(valid_tid(2,4,5) = 4, count(smallint_col)) >= 20, aggif(valid_tid(2,4,5) = 5, max(string_col)) = 'test'
-| row-size=28B cardinality=0
+| row-size=28B cardinality=1
|
07:AGGREGATE [FINALIZE]
| Class 0
@@ -267,7 +267,7 @@ PLAN-ROOT SINK
03:AGGREGATE [FINALIZE]
| output: aggif(valid_tid(2,4,5) = 2, count(tinyint_col)), aggif(valid_tid(2,4,5) = 4, count(smallint_col)), aggif(valid_tid(2,4,5) = 5, max(string_col))
| having: aggif(valid_tid(2,4,5) = 2, count(tinyint_col)) < aggif(valid_tid(2,4,5) = 4, count(smallint_col)), aggif(valid_tid(2,4,5) = 4, count(smallint_col)) <= 30, aggif(valid_tid(2,4,5) = 4, count(smallint_col)) >= 20, aggif(valid_tid(2,4,5) = 5, max(string_col)) = 'test'
-| row-size=28B cardinality=0
+| row-size=28B cardinality=1
|
02:AGGREGATE [FINALIZE]
| Class 0
@@ -296,7 +296,7 @@ PLAN-ROOT SINK
03:AGGREGATE [FINALIZE]
| output: aggif(valid_tid(2,4,5) = 2, count(tinyint_col)), aggif(valid_tid(2,4,5) = 4, count(smallint_col)), aggif(valid_tid(2,4,5) = 5, max(string_col))
| having: aggif(valid_tid(2,4,5) = 2, count(tinyint_col)) < aggif(valid_tid(2,4,5) = 4, count(smallint_col)), aggif(valid_tid(2,4,5) = 4, count(smallint_col)) <= 30, aggif(valid_tid(2,4,5) = 4, count(smallint_col)) >= 20, aggif(valid_tid(2,4,5) = 5, max(string_col)) = 'test'
-| row-size=28B cardinality=0
+| row-size=28B cardinality=1
|
07:AGGREGATE [FINALIZE]
| Class 0
@@ -357,7 +357,7 @@ PLAN-ROOT SINK
| output: aggif(valid_tid(2,4,5) = 2, count(tinyint_col)), aggif(valid_tid(2,4,5) = 4, count(smallint_col)), aggif(valid_tid(2,4,5) = 5, max(string_col))
| group by: CASE valid_tid(2,4,5) WHEN 2 THEN date_string_col WHEN 4 THEN date_string_col WHEN 5 THEN date_string_col END, CASE valid_tid(2,4,5) WHEN 2 THEN timestamp_col WHEN 4 THEN timestamp_col WHEN 5 THEN timestamp_col END
| having: aggif(valid_tid(2,4,5) = 2, count(tinyint_col)) < 10, CAST(CASE valid_tid(2,4,5) WHEN 2 THEN timestamp_col WHEN 4 THEN timestamp_col WHEN 5 THEN timestamp_col END AS BIGINT) = aggif(valid_tid(2,4,5) = 2, count(tinyint_col)), CAST(CASE valid_tid(2,4,5) WHEN 2 THEN timestamp_col WHEN 4 THEN timestamp_col WHEN 5 THEN timestamp_col END AS STRING) < CASE valid_tid(2,4,5) WHEN 2 THEN date_string_col WHEN 4 THEN date_string_col WHEN 5 THEN date_string_col END
-| row-size=56B cardinality=657
+| row-size=56B cardinality=73
|
02:AGGREGATE [FINALIZE]
| Class 0
@@ -369,7 +369,7 @@ PLAN-ROOT SINK
| Class 2
| output: max:merge(string_col)
| group by: date_string_col, timestamp_col
-| row-size=136B cardinality=6.57K
+| row-size=136B cardinality=2.19K
|
01:AGGREGATE
| Class 0
@@ -394,7 +394,7 @@ PLAN-ROOT SINK
| output: aggif(valid_tid(2,4,5) = 2, count(tinyint_col)), aggif(valid_tid(2,4,5) = 4, count(smallint_col)), aggif(valid_tid(2,4,5) = 5, max(string_col))
| group by: CASE valid_tid(2,4,5) WHEN 2 THEN date_string_col WHEN 4 THEN date_string_col WHEN 5 THEN date_string_col END, CASE valid_tid(2,4,5) WHEN 2 THEN timestamp_col WHEN 4 THEN timestamp_col WHEN 5 THEN timestamp_col END
| having: aggif(valid_tid(2,4,5) = 2, count(tinyint_col)) < 10, CAST(CASE valid_tid(2,4,5) WHEN 2 THEN timestamp_col WHEN 4 THEN timestamp_col WHEN 5 THEN timestamp_col END AS BIGINT) = aggif(valid_tid(2,4,5) = 2, count(tinyint_col)), CAST(CASE valid_tid(2,4,5) WHEN 2 THEN timestamp_col WHEN 4 THEN timestamp_col WHEN 5 THEN timestamp_col END AS STRING) < CASE valid_tid(2,4,5) WHEN 2 THEN date_string_col WHEN 4 THEN date_string_col WHEN 5 THEN date_string_col END
-| row-size=56B cardinality=657
+| row-size=56B cardinality=73
|
07:AGGREGATE [FINALIZE]
| Class 0
@@ -406,7 +406,7 @@ PLAN-ROOT SINK
| Class 2
| output: max:merge(string_col)
| group by: date_string_col, timestamp_col
-| row-size=136B cardinality=6.57K
+| row-size=136B cardinality=2.19K
|
06:EXCHANGE [HASH(CASE valid_tid(2,4,5) WHEN 2 THEN murmur_hash(date_string_col) WHEN 4 THEN murmur_hash(date_string_col) WHEN 5 THEN murmur_hash(date_string_col) END,CASE valid_tid(2,4,5) WHEN 2 THEN murmur_hash(timestamp_col) WHEN 4 THEN murmur_hash(timestamp_col) WHEN 5 THEN murmur_hash(timestamp_col) END)]
|
@@ -420,7 +420,7 @@ PLAN-ROOT SINK
| Class 2
| output: max:merge(string_col)
| group by: date_string_col, timestamp_col
-| row-size=136B cardinality=6.57K
+| row-size=136B cardinality=2.19K
|
05:AGGREGATE
| Class 0
@@ -464,7 +464,7 @@ PLAN-ROOT SINK
| output: aggif(valid_tid(2,4,5) = 2, count(tinyint_col)), aggif(valid_tid(2,4,5) = 4, count(smallint_col)), aggif(valid_tid(2,4,5) = 5, max(string_col))
| group by: CASE valid_tid(2,4,5) WHEN 2 THEN date_string_col WHEN 4 THEN date_string_col WHEN 5 THEN date_string_col END, CASE valid_tid(2,4,5) WHEN 2 THEN timestamp_col WHEN 4 THEN timestamp_col WHEN 5 THEN timestamp_col END
| having: aggif(valid_tid(2,4,5) = 2, count(tinyint_col)) < 10, CAST(CASE valid_tid(2,4,5) WHEN 2 THEN timestamp_col WHEN 4 THEN timestamp_col WHEN 5 THEN timestamp_col END AS BIGINT) = aggif(valid_tid(2,4,5) = 2, count(tinyint_col)), CAST(CASE valid_tid(2,4,5) WHEN 2 THEN timestamp_col WHEN 4 THEN timestamp_col WHEN 5 THEN timestamp_col END AS STRING) < CASE valid_tid(2,4,5) WHEN 2 THEN date_string_col WHEN 4 THEN date_string_col WHEN 5 THEN date_string_col END
-| row-size=56B cardinality=657
+| row-size=56B cardinality=73
|
02:AGGREGATE [FINALIZE]
| Class 0
@@ -476,7 +476,7 @@ PLAN-ROOT SINK
| Class 2
| output: max:merge(string_col)
| group by: date_string_col, timestamp_col
-| row-size=136B cardinality=6.57K
+| row-size=136B cardinality=2.19K
|
01:AGGREGATE
| Class 0
@@ -501,7 +501,7 @@ PLAN-ROOT SINK
| output: aggif(valid_tid(2,4,5) = 2, count(tinyint_col)), aggif(valid_tid(2,4,5) = 4, count(smallint_col)), aggif(valid_tid(2,4,5) = 5, max(string_col))
| group by: CASE valid_tid(2,4,5) WHEN 2 THEN date_string_col WHEN 4 THEN date_string_col WHEN 5 THEN date_string_col END, CASE valid_tid(2,4,5) WHEN 2 THEN timestamp_col WHEN 4 THEN timestamp_col WHEN 5 THEN timestamp_col END
| having: aggif(valid_tid(2,4,5) = 2, count(tinyint_col)) < 10, CAST(CASE valid_tid(2,4,5) WHEN 2 THEN timestamp_col WHEN 4 THEN timestamp_col WHEN 5 THEN timestamp_col END AS BIGINT) = aggif(valid_tid(2,4,5) = 2, count(tinyint_col)), CAST(CASE valid_tid(2,4,5) WHEN 2 THEN timestamp_col WHEN 4 THEN timestamp_col WHEN 5 THEN timestamp_col END AS STRING) < CASE valid_tid(2,4,5) WHEN 2 THEN date_string_col WHEN 4 THEN date_string_col WHEN 5 THEN date_string_col END
-| row-size=56B cardinality=657
+| row-size=56B cardinality=73
|
07:AGGREGATE [FINALIZE]
| Class 0
@@ -513,7 +513,7 @@ PLAN-ROOT SINK
| Class 2
| output: max:merge(string_col)
| group by: date_string_col, timestamp_col
-| row-size=136B cardinality=6.57K
+| row-size=136B cardinality=2.19K
|
06:EXCHANGE [HASH(CASE valid_tid(2,4,5) WHEN 2 THEN murmur_hash(date_string_col) WHEN 4 THEN murmur_hash(date_string_col) WHEN 5 THEN murmur_hash(date_string_col) END,CASE valid_tid(2,4,5) WHEN 2 THEN murmur_hash(timestamp_col) WHEN 4 THEN murmur_hash(timestamp_col) WHEN 5 THEN murmur_hash(timestamp_col) END)]
|
@@ -527,7 +527,7 @@ PLAN-ROOT SINK
| Class 2
| output: max:merge(string_col)
| group by: date_string_col, timestamp_col
-| row-size=136B cardinality=6.57K
+| row-size=136B cardinality=2.19K
|
05:AGGREGATE
| Class 0
diff --git a/testdata/workloads/functional-planner/queries/PlannerTest/tpcds/tpcds-q39a.test b/testdata/workloads/functional-planner/queries/PlannerTest/tpcds/tpcds-q39a.test
index 5ff2c6a1b..810e75584 100644
--- a/testdata/workloads/functional-planner/queries/PlannerTest/tpcds/tpcds-q39a.test
+++ b/testdata/workloads/functional-planner/queries/PlannerTest/tpcds/tpcds-q39a.test
@@ -58,26 +58,26 @@ ORDER BY inv1.w_warehouse_sk,
inv2.mean,
inv2.cov ;
---- PLAN
-Max Per-Host Resource Reservation: Memory=119.62MB Threads=9
+Max Per-Host Resource Reservation: Memory=131.81MB Threads=9
Per-Host Resource Estimates: Memory=880MB
F00:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
-| Per-Host Resources: mem-estimate=880.41MB mem-reservation=119.62MB thread-reservation=9 runtime-filters-memory=8.00MB
+| Per-Host Resources: mem-estimate=880.41MB mem-reservation=131.81MB thread-reservation=9 runtime-filters-memory=8.00MB
PLAN-ROOT SINK
| output exprs: w_warehouse_sk, i_item_sk, d_moy, mean, cov, w_warehouse_sk, i_item_sk, d_moy, mean, cov
-| mem-estimate=6.58MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0
+| mem-estimate=26.33MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0
|
17:SORT
| order by: w_warehouse_sk ASC, i_item_sk ASC, d_moy ASC, mean ASC, cov ASC, d_moy ASC, mean ASC, cov ASC
-| mem-estimate=6.58MB mem-reservation=6.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=16 row-size=64B cardinality=107.85K
+| mem-estimate=26.33MB mem-reservation=6.00MB spill-buffer=2.00MB thread-reservation=0
+| tuple-ids=16 row-size=64B cardinality=431.40K
| in pipelines: 17(GETNEXT), 07(OPEN)
|
16:HASH JOIN [INNER JOIN]
| hash predicates: i_item_sk = i_item_sk, w_warehouse_sk = w_warehouse_sk
| fk/pk conjuncts: i_item_sk = i_item_sk, w_warehouse_sk = w_warehouse_sk
| runtime filters: RF000[bloom] <- i_item_sk, RF001[bloom] <- w_warehouse_sk, RF002[min_max] <- i_item_sk, RF003[min_max] <- w_warehouse_sk
-| mem-estimate=17.00MB mem-reservation=17.00MB spill-buffer=1.00MB thread-reservation=0
-| tuple-ids=5,13 row-size=123B cardinality=107.85K
+| mem-estimate=42.51MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
+| tuple-ids=5,13 row-size=123B cardinality=431.40K
| in pipelines: 07(GETNEXT), 15(OPEN)
|
|--15:AGGREGATE [FINALIZE]
@@ -85,7 +85,7 @@ PLAN-ROOT SINK
| | group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| | having: CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN CAST(0 AS DOUBLE) ELSE stddev_samp(inv_quantity_on_hand) * CAST(1.000 AS DOUBLE) / avg(inv_quantity_on_hand) END > CAST(1 AS DOUBLE)
| | mem-estimate=302.39MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| | tuple-ids=13 row-size=62B cardinality=107.85K
+| | tuple-ids=13 row-size=62B cardinality=431.40K
| | in pipelines: 15(GETNEXT), 08(OPEN)
| |
| 14:HASH JOIN [INNER JOIN]
@@ -161,7 +161,7 @@ PLAN-ROOT SINK
| group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| having: CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN CAST(0 AS DOUBLE) ELSE stddev_samp(inv_quantity_on_hand) * CAST(1.000 AS DOUBLE) / avg(inv_quantity_on_hand) END > CAST(1 AS DOUBLE)
| mem-estimate=302.39MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=5 row-size=62B cardinality=107.85K
+| tuple-ids=5 row-size=62B cardinality=431.40K
| in pipelines: 07(GETNEXT), 00(OPEN)
|
06:HASH JOIN [INNER JOIN]
@@ -234,54 +234,54 @@ PLAN-ROOT SINK
tuple-ids=0 row-size=20B cardinality=4.82M(filtered from 11.74M)
in pipelines: 00(GETNEXT)
---- DISTRIBUTEDPLAN
-Max Per-Host Resource Reservation: Memory=211.66MB Threads=20
-Per-Host Resource Estimates: Memory=850MB
+Max Per-Host Resource Reservation: Memory=237.16MB Threads=20
+Per-Host Resource Estimates: Memory=1.12GB
F11:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
-| Per-Host Resources: mem-estimate=10.01MB mem-reservation=4.00MB thread-reservation=1
+| Per-Host Resources: mem-estimate=39.63MB mem-reservation=4.00MB thread-reservation=1
PLAN-ROOT SINK
| output exprs: w_warehouse_sk, i_item_sk, d_moy, mean, cov, w_warehouse_sk, i_item_sk, d_moy, mean, cov
-| mem-estimate=6.58MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0
+| mem-estimate=26.33MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0
|
30:MERGING-EXCHANGE [UNPARTITIONED]
| order by: w_warehouse_sk ASC, i_item_sk ASC, d_moy ASC, mean ASC, cov ASC, d_moy ASC, mean ASC, cov ASC
-| mem-estimate=3.42MB mem-reservation=0B thread-reservation=0
-| tuple-ids=16 row-size=64B cardinality=107.85K
+| mem-estimate=13.30MB mem-reservation=0B thread-reservation=0
+| tuple-ids=16 row-size=64B cardinality=431.40K
| in pipelines: 17(GETNEXT)
|
F10:PLAN FRAGMENT [HASH(i_item_sk,w_warehouse_sk)] hosts=2 instances=2
-Per-Host Resources: mem-estimate=19.79MB mem-reservation=16.50MB thread-reservation=1 runtime-filters-memory=2.00MB
+Per-Host Resources: mem-estimate=59.29MB mem-reservation=42.00MB thread-reservation=1 runtime-filters-memory=2.00MB
17:SORT
| order by: w_warehouse_sk ASC, i_item_sk ASC, d_moy ASC, mean ASC, cov ASC, d_moy ASC, mean ASC, cov ASC
-| mem-estimate=6.00MB mem-reservation=6.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=16 row-size=64B cardinality=107.85K
+| mem-estimate=13.17MB mem-reservation=6.00MB spill-buffer=2.00MB thread-reservation=0
+| tuple-ids=16 row-size=64B cardinality=431.40K
| in pipelines: 17(GETNEXT), 22(OPEN)
|
16:HASH JOIN [INNER JOIN, PARTITIONED]
| hash predicates: i_item_sk = i_item_sk, w_warehouse_sk = w_warehouse_sk
| fk/pk conjuncts: i_item_sk = i_item_sk, w_warehouse_sk = w_warehouse_sk
| runtime filters: RF000[bloom] <- i_item_sk, RF001[bloom] <- w_warehouse_sk, RF002[min_max] <- i_item_sk, RF003[min_max] <- w_warehouse_sk
-| mem-estimate=8.50MB mem-reservation=8.50MB spill-buffer=512.00KB thread-reservation=0
-| tuple-ids=5,13 row-size=123B cardinality=107.85K
+| mem-estimate=34.00MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
+| tuple-ids=5,13 row-size=123B cardinality=431.40K
| in pipelines: 22(GETNEXT), 27(OPEN)
|
|--29:EXCHANGE [HASH(i_item_sk,w_warehouse_sk)]
-| | mem-estimate=3.29MB mem-reservation=0B thread-reservation=0
-| | tuple-ids=13 row-size=62B cardinality=107.85K
+| | mem-estimate=10.13MB mem-reservation=0B thread-reservation=0
+| | tuple-ids=13 row-size=62B cardinality=431.40K
| | in pipelines: 27(GETNEXT)
| |
| F09:PLAN FRAGMENT [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)] hosts=2 instances=2
-| Per-Host Resources: mem-estimate=47.93MB mem-reservation=34.00MB thread-reservation=1
+| Per-Host Resources: mem-estimate=161.32MB mem-reservation=34.00MB thread-reservation=1
| 27:AGGREGATE [FINALIZE]
| | output: stddev_samp:merge(inv_quantity_on_hand), avg:merge(inv_quantity_on_hand)
| | group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| | having: CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN CAST(0 AS DOUBLE) ELSE stddev_samp(inv_quantity_on_hand) * CAST(1.000 AS DOUBLE) / avg(inv_quantity_on_hand) END > CAST(1 AS DOUBLE)
-| | mem-estimate=37.80MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| | tuple-ids=13 row-size=62B cardinality=107.85K
+| | mem-estimate=151.20MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
+| | tuple-ids=13 row-size=62B cardinality=431.40K
| | in pipelines: 27(GETNEXT), 08(OPEN)
| |
| 26:EXCHANGE [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)]
| | mem-estimate=10.13MB mem-reservation=0B thread-reservation=0
-| | tuple-ids=12 row-size=62B cardinality=1.08M
+| | tuple-ids=12 row-size=62B cardinality=4.31M
| | in pipelines: 08(GETNEXT)
| |
| F05:PLAN FRAGMENT [RANDOM] hosts=2 instances=2
@@ -290,7 +290,7 @@ Per-Host Resources: mem-estimate=19.79MB mem-reservation=16.50MB thread-reservat
| | output: stddev_samp(inv_quantity_on_hand), avg(CAST(inv_quantity_on_hand AS BIGINT))
| | group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| | mem-estimate=126.78MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| | tuple-ids=12 row-size=62B cardinality=1.08M
+| | tuple-ids=12 row-size=62B cardinality=4.31M
| | in pipelines: 08(GETNEXT)
| |
| 14:HASH JOIN [INNER JOIN, BROADCAST]
@@ -383,23 +383,23 @@ Per-Host Resources: mem-estimate=19.79MB mem-reservation=16.50MB thread-reservat
| in pipelines: 08(GETNEXT)
|
28:EXCHANGE [HASH(i_item_sk,w_warehouse_sk)]
-| mem-estimate=3.29MB mem-reservation=0B thread-reservation=0
-| tuple-ids=5 row-size=62B cardinality=107.85K
+| mem-estimate=10.13MB mem-reservation=0B thread-reservation=0
+| tuple-ids=5 row-size=62B cardinality=431.40K
| in pipelines: 22(GETNEXT)
|
F04:PLAN FRAGMENT [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)] hosts=2 instances=2
-Per-Host Resources: mem-estimate=47.93MB mem-reservation=34.00MB thread-reservation=1
+Per-Host Resources: mem-estimate=161.32MB mem-reservation=34.00MB thread-reservation=1
22:AGGREGATE [FINALIZE]
| output: stddev_samp:merge(inv_quantity_on_hand), avg:merge(inv_quantity_on_hand)
| group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| having: CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN CAST(0 AS DOUBLE) ELSE stddev_samp(inv_quantity_on_hand) * CAST(1.000 AS DOUBLE) / avg(inv_quantity_on_hand) END > CAST(1 AS DOUBLE)
-| mem-estimate=37.80MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=5 row-size=62B cardinality=107.85K
+| mem-estimate=151.20MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
+| tuple-ids=5 row-size=62B cardinality=431.40K
| in pipelines: 22(GETNEXT), 00(OPEN)
|
21:EXCHANGE [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)]
| mem-estimate=10.13MB mem-reservation=0B thread-reservation=0
-| tuple-ids=4 row-size=62B cardinality=1.08M
+| tuple-ids=4 row-size=62B cardinality=4.31M
| in pipelines: 00(GETNEXT)
|
F00:PLAN FRAGMENT [RANDOM] hosts=2 instances=2
@@ -408,7 +408,7 @@ Per-Host Resources: mem-estimate=266.10MB mem-reservation=60.81MB thread-reserva
| output: stddev_samp(inv_quantity_on_hand), avg(CAST(inv_quantity_on_hand AS BIGINT))
| group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| mem-estimate=126.78MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=4 row-size=62B cardinality=1.08M
+| tuple-ids=4 row-size=62B cardinality=4.31M
| in pipelines: 00(GETNEXT)
|
06:HASH JOIN [INNER JOIN, BROADCAST]
@@ -502,62 +502,62 @@ Per-Host Resources: mem-estimate=266.10MB mem-reservation=60.81MB thread-reserva
tuple-ids=0 row-size=20B cardinality=4.82M(filtered from 11.74M)
in pipelines: 00(GETNEXT)
---- PARALLELPLANS
-Max Per-Host Resource Reservation: Memory=229.28MB Threads=19
-Per-Host Resource Estimates: Memory=584MB
+Max Per-Host Resource Reservation: Memory=254.78MB Threads=19
+Per-Host Resource Estimates: Memory=886MB
F11:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
-| Per-Instance Resources: mem-estimate=10.01MB mem-reservation=4.00MB thread-reservation=1
+| Per-Instance Resources: mem-estimate=39.63MB mem-reservation=4.00MB thread-reservation=1
PLAN-ROOT SINK
| output exprs: w_warehouse_sk, i_item_sk, d_moy, mean, cov, w_warehouse_sk, i_item_sk, d_moy, mean, cov
-| mem-estimate=6.58MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0
+| mem-estimate=26.33MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0
|
30:MERGING-EXCHANGE [UNPARTITIONED]
| order by: w_warehouse_sk ASC, i_item_sk ASC, d_moy ASC, mean ASC, cov ASC, d_moy ASC, mean ASC, cov ASC
-| mem-estimate=3.42MB mem-reservation=0B thread-reservation=0
-| tuple-ids=16 row-size=64B cardinality=107.85K
+| mem-estimate=13.30MB mem-reservation=0B thread-reservation=0
+| tuple-ids=16 row-size=64B cardinality=431.40K
| in pipelines: 17(GETNEXT)
|
F10:PLAN FRAGMENT [HASH(i_item_sk,w_warehouse_sk)] hosts=2 instances=2
-Per-Instance Resources: mem-estimate=9.29MB mem-reservation=6.00MB thread-reservation=1
+Per-Instance Resources: mem-estimate=23.29MB mem-reservation=6.00MB thread-reservation=1
17:SORT
| order by: w_warehouse_sk ASC, i_item_sk ASC, d_moy ASC, mean ASC, cov ASC, d_moy ASC, mean ASC, cov ASC
-| mem-estimate=6.00MB mem-reservation=6.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=16 row-size=64B cardinality=107.85K
+| mem-estimate=13.17MB mem-reservation=6.00MB spill-buffer=2.00MB thread-reservation=0
+| tuple-ids=16 row-size=64B cardinality=431.40K
| in pipelines: 17(GETNEXT), 22(OPEN)
|
16:HASH JOIN [INNER JOIN, PARTITIONED]
| hash-table-id=00
| hash predicates: i_item_sk = i_item_sk, w_warehouse_sk = w_warehouse_sk
| fk/pk conjuncts: i_item_sk = i_item_sk, w_warehouse_sk = w_warehouse_sk
-| mem-estimate=0B mem-reservation=0B spill-buffer=512.00KB thread-reservation=0
-| tuple-ids=5,13 row-size=123B cardinality=107.85K
+| mem-estimate=0B mem-reservation=0B spill-buffer=2.00MB thread-reservation=0
+| tuple-ids=5,13 row-size=123B cardinality=431.40K
| in pipelines: 22(GETNEXT), 27(OPEN)
|
|--F12:PLAN FRAGMENT [HASH(i_item_sk,w_warehouse_sk)] hosts=2 instances=2
-| | Per-Instance Resources: mem-estimate=13.79MB mem-reservation=10.50MB thread-reservation=1 runtime-filters-memory=2.00MB
+| | Per-Instance Resources: mem-estimate=46.13MB mem-reservation=36.00MB thread-reservation=1 runtime-filters-memory=2.00MB
| JOIN BUILD
| | join-table-id=00 plan-id=01 cohort-id=01
| | build expressions: i_item_sk, w_warehouse_sk
| | runtime filters: RF000[bloom] <- i_item_sk, RF001[bloom] <- w_warehouse_sk, RF002[min_max] <- i_item_sk, RF003[min_max] <- w_warehouse_sk
-| | mem-estimate=8.50MB mem-reservation=8.50MB spill-buffer=512.00KB thread-reservation=0
+| | mem-estimate=34.00MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
| |
| 29:EXCHANGE [HASH(i_item_sk,w_warehouse_sk)]
-| | mem-estimate=3.29MB mem-reservation=0B thread-reservation=0
-| | tuple-ids=13 row-size=62B cardinality=107.85K
+| | mem-estimate=10.13MB mem-reservation=0B thread-reservation=0
+| | tuple-ids=13 row-size=62B cardinality=431.40K
| | in pipelines: 27(GETNEXT)
| |
| F09:PLAN FRAGMENT [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)] hosts=2 instances=2
-| Per-Instance Resources: mem-estimate=47.93MB mem-reservation=34.00MB thread-reservation=1
+| Per-Instance Resources: mem-estimate=161.32MB mem-reservation=34.00MB thread-reservation=1
| 27:AGGREGATE [FINALIZE]
| | output: stddev_samp:merge(inv_quantity_on_hand), avg:merge(inv_quantity_on_hand)
| | group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| | having: CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN CAST(0 AS DOUBLE) ELSE stddev_samp(inv_quantity_on_hand) * CAST(1.000 AS DOUBLE) / avg(inv_quantity_on_hand) END > CAST(1 AS DOUBLE)
-| | mem-estimate=37.80MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| | tuple-ids=13 row-size=62B cardinality=107.85K
+| | mem-estimate=151.20MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
+| | tuple-ids=13 row-size=62B cardinality=431.40K
| | in pipelines: 27(GETNEXT), 08(OPEN)
| |
| 26:EXCHANGE [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)]
| | mem-estimate=10.13MB mem-reservation=0B thread-reservation=0
-| | tuple-ids=12 row-size=62B cardinality=1.08M
+| | tuple-ids=12 row-size=62B cardinality=4.31M
| | in pipelines: 08(GETNEXT)
| |
| F05:PLAN FRAGMENT [RANDOM] hosts=2 instances=2
@@ -567,7 +567,7 @@ Per-Instance Resources: mem-estimate=9.29MB mem-reservation=6.00MB thread-reserv
| | output: stddev_samp(inv_quantity_on_hand), avg(CAST(inv_quantity_on_hand AS BIGINT))
| | group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| | mem-estimate=126.78MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| | tuple-ids=12 row-size=62B cardinality=1.08M
+| | tuple-ids=12 row-size=62B cardinality=4.31M
| | in pipelines: 08(GETNEXT)
| |
| 14:HASH JOIN [INNER JOIN, BROADCAST]
@@ -684,23 +684,23 @@ Per-Instance Resources: mem-estimate=9.29MB mem-reservation=6.00MB thread-reserv
| in pipelines: 08(GETNEXT)
|
28:EXCHANGE [HASH(i_item_sk,w_warehouse_sk)]
-| mem-estimate=3.29MB mem-reservation=0B thread-reservation=0
-| tuple-ids=5 row-size=62B cardinality=107.85K
+| mem-estimate=10.13MB mem-reservation=0B thread-reservation=0
+| tuple-ids=5 row-size=62B cardinality=431.40K
| in pipelines: 22(GETNEXT)
|
F04:PLAN FRAGMENT [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)] hosts=2 instances=2
-Per-Instance Resources: mem-estimate=47.93MB mem-reservation=34.00MB thread-reservation=1
+Per-Instance Resources: mem-estimate=161.32MB mem-reservation=34.00MB thread-reservation=1
22:AGGREGATE [FINALIZE]
| output: stddev_samp:merge(inv_quantity_on_hand), avg:merge(inv_quantity_on_hand)
| group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| having: CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN CAST(0 AS DOUBLE) ELSE stddev_samp(inv_quantity_on_hand) * CAST(1.000 AS DOUBLE) / avg(inv_quantity_on_hand) END > CAST(1 AS DOUBLE)
-| mem-estimate=37.80MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=5 row-size=62B cardinality=107.85K
+| mem-estimate=151.20MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
+| tuple-ids=5 row-size=62B cardinality=431.40K
| in pipelines: 22(GETNEXT), 00(OPEN)
|
21:EXCHANGE [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)]
| mem-estimate=10.13MB mem-reservation=0B thread-reservation=0
-| tuple-ids=4 row-size=62B cardinality=1.08M
+| tuple-ids=4 row-size=62B cardinality=4.31M
| in pipelines: 00(GETNEXT)
|
F00:PLAN FRAGMENT [RANDOM] hosts=2 instances=2
@@ -710,7 +710,7 @@ Per-Instance Resources: mem-estimate=159.29MB mem-reservation=50.00MB thread-res
| output: stddev_samp(inv_quantity_on_hand), avg(CAST(inv_quantity_on_hand AS BIGINT))
| group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| mem-estimate=126.78MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=4 row-size=62B cardinality=1.08M
+| tuple-ids=4 row-size=62B cardinality=4.31M
| in pipelines: 00(GETNEXT)
|
06:HASH JOIN [INNER JOIN, BROADCAST]
diff --git a/testdata/workloads/functional-planner/queries/PlannerTest/tpcds/tpcds-q39b.test b/testdata/workloads/functional-planner/queries/PlannerTest/tpcds/tpcds-q39b.test
index 9f3807cd6..b14a39121 100644
--- a/testdata/workloads/functional-planner/queries/PlannerTest/tpcds/tpcds-q39b.test
+++ b/testdata/workloads/functional-planner/queries/PlannerTest/tpcds/tpcds-q39b.test
@@ -59,26 +59,26 @@ ORDER BY inv1.w_warehouse_sk,
inv2.mean,
inv2.cov ;
---- PLAN
-Max Per-Host Resource Reservation: Memory=119.62MB Threads=9
+Max Per-Host Resource Reservation: Memory=131.81MB Threads=9
Per-Host Resource Estimates: Memory=880MB
F00:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
-| Per-Host Resources: mem-estimate=880.41MB mem-reservation=119.62MB thread-reservation=9 runtime-filters-memory=8.00MB
+| Per-Host Resources: mem-estimate=880.41MB mem-reservation=131.81MB thread-reservation=9 runtime-filters-memory=8.00MB
PLAN-ROOT SINK
| output exprs: w_warehouse_sk, i_item_sk, d_moy, mean, cov, w_warehouse_sk, i_item_sk, d_moy, mean, cov
-| mem-estimate=6.58MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0
+| mem-estimate=26.33MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0
|
17:SORT
| order by: w_warehouse_sk ASC, i_item_sk ASC, d_moy ASC, mean ASC, cov ASC, d_moy ASC, mean ASC, cov ASC
-| mem-estimate=6.58MB mem-reservation=6.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=16 row-size=64B cardinality=107.85K
+| mem-estimate=26.33MB mem-reservation=6.00MB spill-buffer=2.00MB thread-reservation=0
+| tuple-ids=16 row-size=64B cardinality=431.40K
| in pipelines: 17(GETNEXT), 07(OPEN)
|
16:HASH JOIN [INNER JOIN]
| hash predicates: i_item_sk = i_item_sk, w_warehouse_sk = w_warehouse_sk
| fk/pk conjuncts: i_item_sk = i_item_sk, w_warehouse_sk = w_warehouse_sk
| runtime filters: RF000[bloom] <- i_item_sk, RF001[bloom] <- w_warehouse_sk, RF002[min_max] <- i_item_sk, RF003[min_max] <- w_warehouse_sk
-| mem-estimate=17.00MB mem-reservation=17.00MB spill-buffer=1.00MB thread-reservation=0
-| tuple-ids=5,13 row-size=123B cardinality=107.85K
+| mem-estimate=42.51MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
+| tuple-ids=5,13 row-size=123B cardinality=431.40K
| in pipelines: 07(GETNEXT), 15(OPEN)
|
|--15:AGGREGATE [FINALIZE]
@@ -86,7 +86,7 @@ PLAN-ROOT SINK
| | group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| | having: CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN CAST(0 AS DOUBLE) ELSE stddev_samp(inv_quantity_on_hand) / avg(inv_quantity_on_hand) END > CAST(1 AS DOUBLE)
| | mem-estimate=302.39MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| | tuple-ids=13 row-size=62B cardinality=107.85K
+| | tuple-ids=13 row-size=62B cardinality=431.40K
| | in pipelines: 15(GETNEXT), 08(OPEN)
| |
| 14:HASH JOIN [INNER JOIN]
@@ -162,7 +162,7 @@ PLAN-ROOT SINK
| group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| having: CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN CAST(0 AS DOUBLE) ELSE stddev_samp(inv_quantity_on_hand) / avg(inv_quantity_on_hand) END > CAST(1 AS DOUBLE), CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN NULL ELSE stddev_samp(inv_quantity_on_hand) / avg(inv_quantity_on_hand) END > CAST(1.5 AS DOUBLE)
| mem-estimate=302.39MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=5 row-size=62B cardinality=107.85K
+| tuple-ids=5 row-size=62B cardinality=431.40K
| in pipelines: 07(GETNEXT), 00(OPEN)
|
06:HASH JOIN [INNER JOIN]
@@ -235,54 +235,54 @@ PLAN-ROOT SINK
tuple-ids=0 row-size=20B cardinality=4.82M(filtered from 11.74M)
in pipelines: 00(GETNEXT)
---- DISTRIBUTEDPLAN
-Max Per-Host Resource Reservation: Memory=211.66MB Threads=20
-Per-Host Resource Estimates: Memory=850MB
+Max Per-Host Resource Reservation: Memory=237.16MB Threads=20
+Per-Host Resource Estimates: Memory=1.12GB
F11:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
-| Per-Host Resources: mem-estimate=10.01MB mem-reservation=4.00MB thread-reservation=1
+| Per-Host Resources: mem-estimate=39.63MB mem-reservation=4.00MB thread-reservation=1
PLAN-ROOT SINK
| output exprs: w_warehouse_sk, i_item_sk, d_moy, mean, cov, w_warehouse_sk, i_item_sk, d_moy, mean, cov
-| mem-estimate=6.58MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0
+| mem-estimate=26.33MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0
|
30:MERGING-EXCHANGE [UNPARTITIONED]
| order by: w_warehouse_sk ASC, i_item_sk ASC, d_moy ASC, mean ASC, cov ASC, d_moy ASC, mean ASC, cov ASC
-| mem-estimate=3.42MB mem-reservation=0B thread-reservation=0
-| tuple-ids=16 row-size=64B cardinality=107.85K
+| mem-estimate=13.30MB mem-reservation=0B thread-reservation=0
+| tuple-ids=16 row-size=64B cardinality=431.40K
| in pipelines: 17(GETNEXT)
|
F10:PLAN FRAGMENT [HASH(i_item_sk,w_warehouse_sk)] hosts=2 instances=2
-Per-Host Resources: mem-estimate=19.79MB mem-reservation=16.50MB thread-reservation=1 runtime-filters-memory=2.00MB
+Per-Host Resources: mem-estimate=59.29MB mem-reservation=42.00MB thread-reservation=1 runtime-filters-memory=2.00MB
17:SORT
| order by: w_warehouse_sk ASC, i_item_sk ASC, d_moy ASC, mean ASC, cov ASC, d_moy ASC, mean ASC, cov ASC
-| mem-estimate=6.00MB mem-reservation=6.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=16 row-size=64B cardinality=107.85K
+| mem-estimate=13.17MB mem-reservation=6.00MB spill-buffer=2.00MB thread-reservation=0
+| tuple-ids=16 row-size=64B cardinality=431.40K
| in pipelines: 17(GETNEXT), 22(OPEN)
|
16:HASH JOIN [INNER JOIN, PARTITIONED]
| hash predicates: i_item_sk = i_item_sk, w_warehouse_sk = w_warehouse_sk
| fk/pk conjuncts: i_item_sk = i_item_sk, w_warehouse_sk = w_warehouse_sk
| runtime filters: RF000[bloom] <- i_item_sk, RF001[bloom] <- w_warehouse_sk, RF002[min_max] <- i_item_sk, RF003[min_max] <- w_warehouse_sk
-| mem-estimate=8.50MB mem-reservation=8.50MB spill-buffer=512.00KB thread-reservation=0
-| tuple-ids=5,13 row-size=123B cardinality=107.85K
+| mem-estimate=34.00MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
+| tuple-ids=5,13 row-size=123B cardinality=431.40K
| in pipelines: 22(GETNEXT), 27(OPEN)
|
|--29:EXCHANGE [HASH(i_item_sk,w_warehouse_sk)]
-| | mem-estimate=3.29MB mem-reservation=0B thread-reservation=0
-| | tuple-ids=13 row-size=62B cardinality=107.85K
+| | mem-estimate=10.13MB mem-reservation=0B thread-reservation=0
+| | tuple-ids=13 row-size=62B cardinality=431.40K
| | in pipelines: 27(GETNEXT)
| |
| F09:PLAN FRAGMENT [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)] hosts=2 instances=2
-| Per-Host Resources: mem-estimate=47.93MB mem-reservation=34.00MB thread-reservation=1
+| Per-Host Resources: mem-estimate=161.32MB mem-reservation=34.00MB thread-reservation=1
| 27:AGGREGATE [FINALIZE]
| | output: stddev_samp:merge(inv_quantity_on_hand), avg:merge(inv_quantity_on_hand)
| | group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| | having: CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN CAST(0 AS DOUBLE) ELSE stddev_samp(inv_quantity_on_hand) / avg(inv_quantity_on_hand) END > CAST(1 AS DOUBLE)
-| | mem-estimate=37.80MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| | tuple-ids=13 row-size=62B cardinality=107.85K
+| | mem-estimate=151.20MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
+| | tuple-ids=13 row-size=62B cardinality=431.40K
| | in pipelines: 27(GETNEXT), 08(OPEN)
| |
| 26:EXCHANGE [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)]
| | mem-estimate=10.13MB mem-reservation=0B thread-reservation=0
-| | tuple-ids=12 row-size=62B cardinality=1.08M
+| | tuple-ids=12 row-size=62B cardinality=4.31M
| | in pipelines: 08(GETNEXT)
| |
| F05:PLAN FRAGMENT [RANDOM] hosts=2 instances=2
@@ -291,7 +291,7 @@ Per-Host Resources: mem-estimate=19.79MB mem-reservation=16.50MB thread-reservat
| | output: stddev_samp(inv_quantity_on_hand), avg(CAST(inv_quantity_on_hand AS BIGINT))
| | group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| | mem-estimate=126.78MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| | tuple-ids=12 row-size=62B cardinality=1.08M
+| | tuple-ids=12 row-size=62B cardinality=4.31M
| | in pipelines: 08(GETNEXT)
| |
| 14:HASH JOIN [INNER JOIN, BROADCAST]
@@ -384,23 +384,23 @@ Per-Host Resources: mem-estimate=19.79MB mem-reservation=16.50MB thread-reservat
| in pipelines: 08(GETNEXT)
|
28:EXCHANGE [HASH(i_item_sk,w_warehouse_sk)]
-| mem-estimate=3.29MB mem-reservation=0B thread-reservation=0
-| tuple-ids=5 row-size=62B cardinality=107.85K
+| mem-estimate=10.13MB mem-reservation=0B thread-reservation=0
+| tuple-ids=5 row-size=62B cardinality=431.40K
| in pipelines: 22(GETNEXT)
|
F04:PLAN FRAGMENT [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)] hosts=2 instances=2
-Per-Host Resources: mem-estimate=47.93MB mem-reservation=34.00MB thread-reservation=1
+Per-Host Resources: mem-estimate=161.32MB mem-reservation=34.00MB thread-reservation=1
22:AGGREGATE [FINALIZE]
| output: stddev_samp:merge(inv_quantity_on_hand), avg:merge(inv_quantity_on_hand)
| group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| having: CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN CAST(0 AS DOUBLE) ELSE stddev_samp(inv_quantity_on_hand) / avg(inv_quantity_on_hand) END > CAST(1 AS DOUBLE), CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN NULL ELSE stddev_samp(inv_quantity_on_hand) / avg(inv_quantity_on_hand) END > CAST(1.5 AS DOUBLE)
-| mem-estimate=37.80MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=5 row-size=62B cardinality=107.85K
+| mem-estimate=151.20MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
+| tuple-ids=5 row-size=62B cardinality=431.40K
| in pipelines: 22(GETNEXT), 00(OPEN)
|
21:EXCHANGE [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)]
| mem-estimate=10.13MB mem-reservation=0B thread-reservation=0
-| tuple-ids=4 row-size=62B cardinality=1.08M
+| tuple-ids=4 row-size=62B cardinality=4.31M
| in pipelines: 00(GETNEXT)
|
F00:PLAN FRAGMENT [RANDOM] hosts=2 instances=2
@@ -409,7 +409,7 @@ Per-Host Resources: mem-estimate=266.10MB mem-reservation=60.81MB thread-reserva
| output: stddev_samp(inv_quantity_on_hand), avg(CAST(inv_quantity_on_hand AS BIGINT))
| group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| mem-estimate=126.78MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=4 row-size=62B cardinality=1.08M
+| tuple-ids=4 row-size=62B cardinality=4.31M
| in pipelines: 00(GETNEXT)
|
06:HASH JOIN [INNER JOIN, BROADCAST]
@@ -503,62 +503,62 @@ Per-Host Resources: mem-estimate=266.10MB mem-reservation=60.81MB thread-reserva
tuple-ids=0 row-size=20B cardinality=4.82M(filtered from 11.74M)
in pipelines: 00(GETNEXT)
---- PARALLELPLANS
-Max Per-Host Resource Reservation: Memory=229.28MB Threads=19
-Per-Host Resource Estimates: Memory=584MB
+Max Per-Host Resource Reservation: Memory=254.78MB Threads=19
+Per-Host Resource Estimates: Memory=886MB
F11:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
-| Per-Instance Resources: mem-estimate=10.01MB mem-reservation=4.00MB thread-reservation=1
+| Per-Instance Resources: mem-estimate=39.63MB mem-reservation=4.00MB thread-reservation=1
PLAN-ROOT SINK
| output exprs: w_warehouse_sk, i_item_sk, d_moy, mean, cov, w_warehouse_sk, i_item_sk, d_moy, mean, cov
-| mem-estimate=6.58MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0
+| mem-estimate=26.33MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0
|
30:MERGING-EXCHANGE [UNPARTITIONED]
| order by: w_warehouse_sk ASC, i_item_sk ASC, d_moy ASC, mean ASC, cov ASC, d_moy ASC, mean ASC, cov ASC
-| mem-estimate=3.42MB mem-reservation=0B thread-reservation=0
-| tuple-ids=16 row-size=64B cardinality=107.85K
+| mem-estimate=13.30MB mem-reservation=0B thread-reservation=0
+| tuple-ids=16 row-size=64B cardinality=431.40K
| in pipelines: 17(GETNEXT)
|
F10:PLAN FRAGMENT [HASH(i_item_sk,w_warehouse_sk)] hosts=2 instances=2
-Per-Instance Resources: mem-estimate=9.29MB mem-reservation=6.00MB thread-reservation=1
+Per-Instance Resources: mem-estimate=23.29MB mem-reservation=6.00MB thread-reservation=1
17:SORT
| order by: w_warehouse_sk ASC, i_item_sk ASC, d_moy ASC, mean ASC, cov ASC, d_moy ASC, mean ASC, cov ASC
-| mem-estimate=6.00MB mem-reservation=6.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=16 row-size=64B cardinality=107.85K
+| mem-estimate=13.17MB mem-reservation=6.00MB spill-buffer=2.00MB thread-reservation=0
+| tuple-ids=16 row-size=64B cardinality=431.40K
| in pipelines: 17(GETNEXT), 22(OPEN)
|
16:HASH JOIN [INNER JOIN, PARTITIONED]
| hash-table-id=00
| hash predicates: i_item_sk = i_item_sk, w_warehouse_sk = w_warehouse_sk
| fk/pk conjuncts: i_item_sk = i_item_sk, w_warehouse_sk = w_warehouse_sk
-| mem-estimate=0B mem-reservation=0B spill-buffer=512.00KB thread-reservation=0
-| tuple-ids=5,13 row-size=123B cardinality=107.85K
+| mem-estimate=0B mem-reservation=0B spill-buffer=2.00MB thread-reservation=0
+| tuple-ids=5,13 row-size=123B cardinality=431.40K
| in pipelines: 22(GETNEXT), 27(OPEN)
|
|--F12:PLAN FRAGMENT [HASH(i_item_sk,w_warehouse_sk)] hosts=2 instances=2
-| | Per-Instance Resources: mem-estimate=13.79MB mem-reservation=10.50MB thread-reservation=1 runtime-filters-memory=2.00MB
+| | Per-Instance Resources: mem-estimate=46.13MB mem-reservation=36.00MB thread-reservation=1 runtime-filters-memory=2.00MB
| JOIN BUILD
| | join-table-id=00 plan-id=01 cohort-id=01
| | build expressions: i_item_sk, w_warehouse_sk
| | runtime filters: RF000[bloom] <- i_item_sk, RF001[bloom] <- w_warehouse_sk, RF002[min_max] <- i_item_sk, RF003[min_max] <- w_warehouse_sk
-| | mem-estimate=8.50MB mem-reservation=8.50MB spill-buffer=512.00KB thread-reservation=0
+| | mem-estimate=34.00MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
| |
| 29:EXCHANGE [HASH(i_item_sk,w_warehouse_sk)]
-| | mem-estimate=3.29MB mem-reservation=0B thread-reservation=0
-| | tuple-ids=13 row-size=62B cardinality=107.85K
+| | mem-estimate=10.13MB mem-reservation=0B thread-reservation=0
+| | tuple-ids=13 row-size=62B cardinality=431.40K
| | in pipelines: 27(GETNEXT)
| |
| F09:PLAN FRAGMENT [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)] hosts=2 instances=2
-| Per-Instance Resources: mem-estimate=47.93MB mem-reservation=34.00MB thread-reservation=1
+| Per-Instance Resources: mem-estimate=161.32MB mem-reservation=34.00MB thread-reservation=1
| 27:AGGREGATE [FINALIZE]
| | output: stddev_samp:merge(inv_quantity_on_hand), avg:merge(inv_quantity_on_hand)
| | group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| | having: CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN CAST(0 AS DOUBLE) ELSE stddev_samp(inv_quantity_on_hand) / avg(inv_quantity_on_hand) END > CAST(1 AS DOUBLE)
-| | mem-estimate=37.80MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| | tuple-ids=13 row-size=62B cardinality=107.85K
+| | mem-estimate=151.20MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
+| | tuple-ids=13 row-size=62B cardinality=431.40K
| | in pipelines: 27(GETNEXT), 08(OPEN)
| |
| 26:EXCHANGE [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)]
| | mem-estimate=10.13MB mem-reservation=0B thread-reservation=0
-| | tuple-ids=12 row-size=62B cardinality=1.08M
+| | tuple-ids=12 row-size=62B cardinality=4.31M
| | in pipelines: 08(GETNEXT)
| |
| F05:PLAN FRAGMENT [RANDOM] hosts=2 instances=2
@@ -568,7 +568,7 @@ Per-Instance Resources: mem-estimate=9.29MB mem-reservation=6.00MB thread-reserv
| | output: stddev_samp(inv_quantity_on_hand), avg(CAST(inv_quantity_on_hand AS BIGINT))
| | group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| | mem-estimate=126.78MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| | tuple-ids=12 row-size=62B cardinality=1.08M
+| | tuple-ids=12 row-size=62B cardinality=4.31M
| | in pipelines: 08(GETNEXT)
| |
| 14:HASH JOIN [INNER JOIN, BROADCAST]
@@ -685,23 +685,23 @@ Per-Instance Resources: mem-estimate=9.29MB mem-reservation=6.00MB thread-reserv
| in pipelines: 08(GETNEXT)
|
28:EXCHANGE [HASH(i_item_sk,w_warehouse_sk)]
-| mem-estimate=3.29MB mem-reservation=0B thread-reservation=0
-| tuple-ids=5 row-size=62B cardinality=107.85K
+| mem-estimate=10.13MB mem-reservation=0B thread-reservation=0
+| tuple-ids=5 row-size=62B cardinality=431.40K
| in pipelines: 22(GETNEXT)
|
F04:PLAN FRAGMENT [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)] hosts=2 instances=2
-Per-Instance Resources: mem-estimate=47.93MB mem-reservation=34.00MB thread-reservation=1
+Per-Instance Resources: mem-estimate=161.32MB mem-reservation=34.00MB thread-reservation=1
22:AGGREGATE [FINALIZE]
| output: stddev_samp:merge(inv_quantity_on_hand), avg:merge(inv_quantity_on_hand)
| group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| having: CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN CAST(0 AS DOUBLE) ELSE stddev_samp(inv_quantity_on_hand) / avg(inv_quantity_on_hand) END > CAST(1 AS DOUBLE), CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN NULL ELSE stddev_samp(inv_quantity_on_hand) / avg(inv_quantity_on_hand) END > CAST(1.5 AS DOUBLE)
-| mem-estimate=37.80MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=5 row-size=62B cardinality=107.85K
+| mem-estimate=151.20MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
+| tuple-ids=5 row-size=62B cardinality=431.40K
| in pipelines: 22(GETNEXT), 00(OPEN)
|
21:EXCHANGE [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)]
| mem-estimate=10.13MB mem-reservation=0B thread-reservation=0
-| tuple-ids=4 row-size=62B cardinality=1.08M
+| tuple-ids=4 row-size=62B cardinality=4.31M
| in pipelines: 00(GETNEXT)
|
F00:PLAN FRAGMENT [RANDOM] hosts=2 instances=2
@@ -711,7 +711,7 @@ Per-Instance Resources: mem-estimate=159.29MB mem-reservation=50.00MB thread-res
| output: stddev_samp(inv_quantity_on_hand), avg(CAST(inv_quantity_on_hand AS BIGINT))
| group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| mem-estimate=126.78MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=4 row-size=62B cardinality=1.08M
+| tuple-ids=4 row-size=62B cardinality=4.31M
| in pipelines: 00(GETNEXT)
|
06:HASH JOIN [INNER JOIN, BROADCAST]
diff --git a/testdata/workloads/functional-planner/queries/PlannerTest/tpcds_cpu_cost/tpcds-q39a.test b/testdata/workloads/functional-planner/queries/PlannerTest/tpcds_cpu_cost/tpcds-q39a.test
index 6e3b9a425..c2e28b014 100644
--- a/testdata/workloads/functional-planner/queries/PlannerTest/tpcds_cpu_cost/tpcds-q39a.test
+++ b/testdata/workloads/functional-planner/queries/PlannerTest/tpcds_cpu_cost/tpcds-q39a.test
@@ -27,26 +27,26 @@ order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov
;
---- PLAN
Max Per-Host Resource Reservation: Memory=131.12MB Threads=1
-Per-Host Resource Estimates: Memory=8.40GB
+Per-Host Resource Estimates: Memory=11.19GB
F00:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
-| Per-Instance Resources: mem-estimate=8.40GB mem-reservation=131.12MB thread-reservation=1 runtime-filters-memory=8.00MB
-| max-parallelism=1 segment-costs=[3213474426, 3213474426, 40684722, 95040000]
+| Per-Instance Resources: mem-estimate=11.19GB mem-reservation=131.12MB thread-reservation=1 runtime-filters-memory=8.00MB
+| max-parallelism=1 segment-costs=[3213474426, 3213474426, 182989764, 427466272]
PLAN-ROOT SINK
| output exprs: w_warehouse_sk, i_item_sk, d_moy, mean, cov, w_warehouse_sk, i_item_sk, d_moy, mean, cov
-| mem-estimate=100.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 cost=95040000
+| mem-estimate=100.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 cost=427466272
|
17:SORT
| order by: w_warehouse_sk ASC, i_item_sk ASC, d_moy ASC, mean ASC, cov ASC, d_moy ASC, mean ASC, cov ASC
-| mem-estimate=507.57MB mem-reservation=6.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=16 row-size=56B cardinality=9.50M cost=27020822
+| mem-estimate=2.23GB mem-reservation=6.00MB spill-buffer=2.00MB thread-reservation=0
+| tuple-ids=16 row-size=56B cardinality=42.75M cost=121532939
| in pipelines: 17(GETNEXT), 07(OPEN)
|
16:HASH JOIN [INNER JOIN]
| hash predicates: i_item_sk = i_item_sk, w_warehouse_sk = w_warehouse_sk
| fk/pk conjuncts: i_item_sk = i_item_sk, w_warehouse_sk = w_warehouse_sk
| runtime filters: RF000[bloom] <- i_item_sk, RF001[bloom] <- w_warehouse_sk, RF002[min_max] <- i_item_sk, RF003[min_max] <- w_warehouse_sk
-| mem-estimate=724.17MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=5,13 row-size=112B cardinality=9.50M cost=13663900
+| mem-estimate=3.50GB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
+| tuple-ids=5,13 row-size=112B cardinality=42.75M cost=61456825
| in pipelines: 07(GETNEXT), 15(OPEN)
|
|--15:AGGREGATE [FINALIZE]
@@ -54,7 +54,7 @@ PLAN-ROOT SINK
| | group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| | having: CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN CAST(0 AS DOUBLE) ELSE stddev_samp(inv_quantity_on_hand) / avg(inv_quantity_on_hand) END > CAST(1 AS DOUBLE)
| | mem-estimate=3.82GB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| | tuple-ids=13 row-size=56B cardinality=9.50M cost=2553085026
+| | tuple-ids=13 row-size=56B cardinality=42.75M cost=2553085026
| | in pipelines: 15(GETNEXT), 08(OPEN)
| |
| 14:HASH JOIN [INNER JOIN]
@@ -131,7 +131,7 @@ PLAN-ROOT SINK
| group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| having: CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN CAST(0 AS DOUBLE) ELSE stddev_samp(inv_quantity_on_hand) / avg(inv_quantity_on_hand) END > CAST(1 AS DOUBLE)
| mem-estimate=3.82GB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=5 row-size=56B cardinality=9.50M cost=2553085026
+| tuple-ids=5 row-size=56B cardinality=42.75M cost=2553085026
| in pipelines: 07(GETNEXT), 00(OPEN)
|
06:HASH JOIN [INNER JOIN]
@@ -205,28 +205,28 @@ PLAN-ROOT SINK
tuple-ids=0 row-size=16B cardinality=427.68M(filtered from 1.03G) cost=98537472
in pipelines: 00(GETNEXT)
---- DISTRIBUTEDPLAN
-Max Per-Host Resource Reservation: Memory=1.71GB Threads=51
-Per-Host Resource Estimates: Memory=7.12GB
+Max Per-Host Resource Reservation: Memory=2.08GB Threads=63
+Per-Host Resource Estimates: Memory=12.39GB
F11:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
-| Per-Instance Resources: mem-estimate=151.34MB mem-reservation=4.00MB thread-reservation=1
-| max-parallelism=1 segment-costs=[98508009] cpu-comparison-result=216 [max(1 (self) vs 216 (sum children))]
+| Per-Instance Resources: mem-estimate=301.17MB mem-reservation=4.00MB thread-reservation=1
+| max-parallelism=1 segment-costs=[443064516] cpu-comparison-result=240 [max(1 (self) vs 240 (sum children))]
PLAN-ROOT SINK
| output exprs: w_warehouse_sk, i_item_sk, d_moy, mean, cov, w_warehouse_sk, i_item_sk, d_moy, mean, cov
-| mem-estimate=100.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 cost=95040000
+| mem-estimate=100.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 cost=427466272
|
30:MERGING-EXCHANGE [UNPARTITIONED]
| order by: w_warehouse_sk ASC, i_item_sk ASC, d_moy ASC, mean ASC, cov ASC, d_moy ASC, mean ASC, cov ASC
-| mem-estimate=51.34MB mem-reservation=0B thread-reservation=0
-| tuple-ids=16 row-size=56B cardinality=9.50M cost=3468009
+| mem-estimate=201.17MB mem-reservation=0B thread-reservation=0
+| tuple-ids=16 row-size=56B cardinality=42.75M cost=15598244
| in pipelines: 17(GETNEXT)
|
-F10:PLAN FRAGMENT [HASH(i_item_sk,w_warehouse_sk)] hosts=10 instances=10 (adjusted from 120)
-Per-Instance Resources: mem-estimate=64.86MB mem-reservation=6.00MB thread-reservation=1
-max-parallelism=10 segment-costs=[34686928, 16931376] cpu-comparison-result=216 [max(10 (self) vs 216 (sum children))]
+F10:PLAN FRAGMENT [HASH(i_item_sk,w_warehouse_sk)] hosts=10 instances=20 (adjusted from 120)
+Per-Instance Resources: mem-estimate=131.18MB mem-reservation=6.00MB thread-reservation=1
+max-parallelism=20 segment-costs=[156013181, 76153116] cpu-comparison-result=240 [max(20 (self) vs 240 (sum children))]
17:SORT
| order by: w_warehouse_sk ASC, i_item_sk ASC, d_moy ASC, mean ASC, cov ASC, d_moy ASC, mean ASC, cov ASC
-| mem-estimate=50.76MB mem-reservation=6.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=16 row-size=56B cardinality=9.50M cost=27020822
+| mem-estimate=114.15MB mem-reservation=6.00MB spill-buffer=2.00MB thread-reservation=0
+| tuple-ids=16 row-size=56B cardinality=42.75M cost=121532939
| in pipelines: 17(GETNEXT), 22(OPEN)
|
16:HASH JOIN [INNER JOIN, PARTITIONED]
@@ -234,48 +234,48 @@ max-parallelism=10 segment-costs=[34686928, 16931376] cpu-comparison-result=216
| hash predicates: i_item_sk = i_item_sk, w_warehouse_sk = w_warehouse_sk
| fk/pk conjuncts: i_item_sk = i_item_sk, w_warehouse_sk = w_warehouse_sk
| mem-estimate=0B mem-reservation=0B spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=5,13 row-size=112B cardinality=9.50M cost=4159900
+| tuple-ids=5,13 row-size=112B cardinality=42.75M cost=18710198
| in pipelines: 22(GETNEXT), 27(OPEN)
|
-|--F12:PLAN FRAGMENT [HASH(i_item_sk,w_warehouse_sk)] hosts=10 instances=10 (adjusted from 120)
-| | Per-Instance Resources: mem-estimate=88.52MB mem-reservation=36.00MB thread-reservation=1 runtime-filters-memory=2.00MB
-| | max-parallelism=10 segment-costs=[13010206] cpu-comparison-result=108 [max(10 (self) vs 108 (sum children))]
+|--F12:PLAN FRAGMENT [HASH(i_item_sk,w_warehouse_sk)] hosts=10 instances=20 (adjusted from 120)
+| | Per-Instance Resources: mem-estimate=198.25MB mem-reservation=36.00MB thread-reservation=1 runtime-filters-memory=2.00MB
+| | max-parallelism=20 segment-costs=[58516671] cpu-comparison-result=120 [max(20 (self) vs 120 (sum children))]
| JOIN BUILD
| | join-table-id=00 plan-id=01 cohort-id=01
| | build expressions: i_item_sk, w_warehouse_sk
| | runtime filters: RF000[bloom] <- i_item_sk, RF001[bloom] <- w_warehouse_sk, RF002[min_max] <- i_item_sk, RF003[min_max] <- w_warehouse_sk
-| | mem-estimate=72.42MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0 cost=9504000
+| | mem-estimate=179.21MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0 cost=42746627
| |
| 29:EXCHANGE [HASH(i_item_sk,w_warehouse_sk)]
-| | mem-estimate=14.10MB mem-reservation=0B thread-reservation=0
-| | tuple-ids=13 row-size=56B cardinality=9.50M cost=3506206
+| | mem-estimate=17.04MB mem-reservation=0B thread-reservation=0
+| | tuple-ids=13 row-size=56B cardinality=42.75M cost=15770044
| | in pipelines: 27(GETNEXT)
| |
-| F09:PLAN FRAGMENT [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)] hosts=10 instances=70 (adjusted from 120)
-| Per-Instance Resources: mem-estimate=104.44MB mem-reservation=34.00MB thread-reservation=1
-| max-parallelism=70 segment-costs=[602697970, 39200831] cpu-comparison-result=108 [max(70 (self) vs 108 (sum children))]
+| F09:PLAN FRAGMENT [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)] hosts=10 instances=120
+| Per-Instance Resources: mem-estimate=247.50MB mem-reservation=34.00MB thread-reservation=1
+| max-parallelism=280 segment-costs=[2710785473, 176315586] cpu-comparison-result=120 [max(120 (self) vs 108 (sum children))]
| 27:AGGREGATE [FINALIZE]
| | output: stddev_samp:merge(inv_quantity_on_hand), avg:merge(inv_quantity_on_hand)
| | group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| | having: CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN CAST(0 AS DOUBLE) ELSE stddev_samp(inv_quantity_on_hand) / avg(inv_quantity_on_hand) END > CAST(1 AS DOUBLE)
-| | mem-estimate=88.11MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| | tuple-ids=13 row-size=56B cardinality=9.50M cost=567635904
+| | mem-estimate=231.17MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
+| | tuple-ids=13 row-size=56B cardinality=42.75M cost=2553085026
| | in pipelines: 27(GETNEXT), 08(OPEN)
| |
| 26:EXCHANGE [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)]
| | mem-estimate=16.33MB mem-reservation=0B thread-reservation=0
-| | tuple-ids=12 row-size=56B cardinality=95.04M cost=35062066
+| | tuple-ids=12 row-size=56B cardinality=427.47M cost=157700447
| | in pipelines: 08(GETNEXT)
| |
| F05:PLAN FRAGMENT [RANDOM] hosts=10 instances=108 (adjusted from 120)
| Per-Host Shared Resources: mem-estimate=3.00MB mem-reservation=3.00MB thread-reservation=0 runtime-filters-memory=3.00MB
-| Per-Instance Resources: mem-estimate=225.06MB mem-reservation=34.25MB thread-reservation=1
-| max-parallelism=108 segment-costs=[3213076826, 392008318] cpu-comparison-result=108 [max(108 (self) vs 36 (sum children))]
+| Per-Instance Resources: mem-estimate=236.79MB mem-reservation=34.25MB thread-reservation=1
+| max-parallelism=108 segment-costs=[3213076826, 1763155857] cpu-comparison-result=108 [max(108 (self) vs 36 (sum children))]
| 15:AGGREGATE [STREAMING]
| | output: stddev_samp(inv_quantity_on_hand), avg(CAST(inv_quantity_on_hand AS BIGINT))
| | group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| | mem-estimate=192.64MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| | tuple-ids=12 row-size=56B cardinality=95.04M cost=2553085026
+| | tuple-ids=12 row-size=56B cardinality=427.47M cost=2553085026
| | in pipelines: 08(GETNEXT)
| |
| 14:HASH JOIN [INNER JOIN, BROADCAST]
@@ -399,35 +399,35 @@ max-parallelism=10 segment-costs=[34686928, 16931376] cpu-comparison-result=216
| in pipelines: 08(GETNEXT)
|
28:EXCHANGE [HASH(i_item_sk,w_warehouse_sk)]
-| mem-estimate=14.10MB mem-reservation=0B thread-reservation=0
-| tuple-ids=5 row-size=56B cardinality=9.50M cost=3506206
+| mem-estimate=17.04MB mem-reservation=0B thread-reservation=0
+| tuple-ids=5 row-size=56B cardinality=42.75M cost=15770044
| in pipelines: 22(GETNEXT)
|
-F04:PLAN FRAGMENT [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)] hosts=10 instances=70 (adjusted from 120)
-Per-Instance Resources: mem-estimate=104.44MB mem-reservation=34.00MB thread-reservation=1
-max-parallelism=70 segment-costs=[602697970, 39200831] cpu-comparison-result=108 [max(70 (self) vs 108 (sum children))]
+F04:PLAN FRAGMENT [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)] hosts=10 instances=120
+Per-Instance Resources: mem-estimate=247.50MB mem-reservation=34.00MB thread-reservation=1
+max-parallelism=280 segment-costs=[2710785473, 176315586] cpu-comparison-result=120 [max(120 (self) vs 108 (sum children))]
22:AGGREGATE [FINALIZE]
| output: stddev_samp:merge(inv_quantity_on_hand), avg:merge(inv_quantity_on_hand)
| group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| having: CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN CAST(0 AS DOUBLE) ELSE stddev_samp(inv_quantity_on_hand) / avg(inv_quantity_on_hand) END > CAST(1 AS DOUBLE)
-| mem-estimate=88.11MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=5 row-size=56B cardinality=9.50M cost=567635904
+| mem-estimate=231.17MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
+| tuple-ids=5 row-size=56B cardinality=42.75M cost=2553085026
| in pipelines: 22(GETNEXT), 00(OPEN)
|
21:EXCHANGE [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)]
| mem-estimate=16.33MB mem-reservation=0B thread-reservation=0
-| tuple-ids=4 row-size=56B cardinality=95.04M cost=35062066
+| tuple-ids=4 row-size=56B cardinality=427.47M cost=157700447
| in pipelines: 00(GETNEXT)
|
F00:PLAN FRAGMENT [RANDOM] hosts=10 instances=108 (adjusted from 120)
Per-Host Shared Resources: mem-estimate=5.00MB mem-reservation=5.00MB thread-reservation=0 runtime-filters-memory=5.00MB
-Per-Instance Resources: mem-estimate=225.06MB mem-reservation=34.25MB thread-reservation=1
-max-parallelism=108 segment-costs=[3213076826, 392008318] cpu-comparison-result=108 [max(108 (self) vs 36 (sum children))]
+Per-Instance Resources: mem-estimate=236.79MB mem-reservation=34.25MB thread-reservation=1
+max-parallelism=108 segment-costs=[3213076826, 1763155857] cpu-comparison-result=108 [max(108 (self) vs 36 (sum children))]
07:AGGREGATE [STREAMING]
| output: stddev_samp(inv_quantity_on_hand), avg(CAST(inv_quantity_on_hand AS BIGINT))
| group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| mem-estimate=192.64MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=4 row-size=56B cardinality=95.04M cost=2553085026
+| tuple-ids=4 row-size=56B cardinality=427.47M cost=2553085026
| in pipelines: 00(GETNEXT)
|
06:HASH JOIN [INNER JOIN, BROADCAST]
@@ -554,28 +554,28 @@ max-parallelism=108 segment-costs=[3213076826, 392008318] cpu-comparison-result=
tuple-ids=0 row-size=16B cardinality=427.68M(filtered from 1.03G) cost=98537472
in pipelines: 00(GETNEXT)
---- PARALLELPLANS
-Max Per-Host Resource Reservation: Memory=1.71GB Threads=51
-Per-Host Resource Estimates: Memory=7.12GB
+Max Per-Host Resource Reservation: Memory=2.08GB Threads=63
+Per-Host Resource Estimates: Memory=12.39GB
F11:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
-| Per-Instance Resources: mem-estimate=151.34MB mem-reservation=4.00MB thread-reservation=1
-| max-parallelism=1 segment-costs=[98508009] cpu-comparison-result=216 [max(1 (self) vs 216 (sum children))]
+| Per-Instance Resources: mem-estimate=301.17MB mem-reservation=4.00MB thread-reservation=1
+| max-parallelism=1 segment-costs=[443064516] cpu-comparison-result=240 [max(1 (self) vs 240 (sum children))]
PLAN-ROOT SINK
| output exprs: w_warehouse_sk, i_item_sk, d_moy, mean, cov, w_warehouse_sk, i_item_sk, d_moy, mean, cov
-| mem-estimate=100.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 cost=95040000
+| mem-estimate=100.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 cost=427466272
|
30:MERGING-EXCHANGE [UNPARTITIONED]
| order by: w_warehouse_sk ASC, i_item_sk ASC, d_moy ASC, mean ASC, cov ASC, d_moy ASC, mean ASC, cov ASC
-| mem-estimate=51.34MB mem-reservation=0B thread-reservation=0
-| tuple-ids=16 row-size=56B cardinality=9.50M cost=3468009
+| mem-estimate=201.17MB mem-reservation=0B thread-reservation=0
+| tuple-ids=16 row-size=56B cardinality=42.75M cost=15598244
| in pipelines: 17(GETNEXT)
|
-F10:PLAN FRAGMENT [HASH(i_item_sk,w_warehouse_sk)] hosts=10 instances=10 (adjusted from 120)
-Per-Instance Resources: mem-estimate=64.86MB mem-reservation=6.00MB thread-reservation=1
-max-parallelism=10 segment-costs=[34686928, 16931376] cpu-comparison-result=216 [max(10 (self) vs 216 (sum children))]
+F10:PLAN FRAGMENT [HASH(i_item_sk,w_warehouse_sk)] hosts=10 instances=20 (adjusted from 120)
+Per-Instance Resources: mem-estimate=131.18MB mem-reservation=6.00MB thread-reservation=1
+max-parallelism=20 segment-costs=[156013181, 76153116] cpu-comparison-result=240 [max(20 (self) vs 240 (sum children))]
17:SORT
| order by: w_warehouse_sk ASC, i_item_sk ASC, d_moy ASC, mean ASC, cov ASC, d_moy ASC, mean ASC, cov ASC
-| mem-estimate=50.76MB mem-reservation=6.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=16 row-size=56B cardinality=9.50M cost=27020822
+| mem-estimate=114.15MB mem-reservation=6.00MB spill-buffer=2.00MB thread-reservation=0
+| tuple-ids=16 row-size=56B cardinality=42.75M cost=121532939
| in pipelines: 17(GETNEXT), 22(OPEN)
|
16:HASH JOIN [INNER JOIN, PARTITIONED]
@@ -583,48 +583,48 @@ max-parallelism=10 segment-costs=[34686928, 16931376] cpu-comparison-result=216
| hash predicates: i_item_sk = i_item_sk, w_warehouse_sk = w_warehouse_sk
| fk/pk conjuncts: i_item_sk = i_item_sk, w_warehouse_sk = w_warehouse_sk
| mem-estimate=0B mem-reservation=0B spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=5,13 row-size=112B cardinality=9.50M cost=4159900
+| tuple-ids=5,13 row-size=112B cardinality=42.75M cost=18710198
| in pipelines: 22(GETNEXT), 27(OPEN)
|
-|--F12:PLAN FRAGMENT [HASH(i_item_sk,w_warehouse_sk)] hosts=10 instances=10 (adjusted from 120)
-| | Per-Instance Resources: mem-estimate=88.52MB mem-reservation=36.00MB thread-reservation=1 runtime-filters-memory=2.00MB
-| | max-parallelism=10 segment-costs=[13010206] cpu-comparison-result=108 [max(10 (self) vs 108 (sum children))]
+|--F12:PLAN FRAGMENT [HASH(i_item_sk,w_warehouse_sk)] hosts=10 instances=20 (adjusted from 120)
+| | Per-Instance Resources: mem-estimate=198.25MB mem-reservation=36.00MB thread-reservation=1 runtime-filters-memory=2.00MB
+| | max-parallelism=20 segment-costs=[58516671] cpu-comparison-result=120 [max(20 (self) vs 120 (sum children))]
| JOIN BUILD
| | join-table-id=00 plan-id=01 cohort-id=01
| | build expressions: i_item_sk, w_warehouse_sk
| | runtime filters: RF000[bloom] <- i_item_sk, RF001[bloom] <- w_warehouse_sk, RF002[min_max] <- i_item_sk, RF003[min_max] <- w_warehouse_sk
-| | mem-estimate=72.42MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0 cost=9504000
+| | mem-estimate=179.21MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0 cost=42746627
| |
| 29:EXCHANGE [HASH(i_item_sk,w_warehouse_sk)]
-| | mem-estimate=14.10MB mem-reservation=0B thread-reservation=0
-| | tuple-ids=13 row-size=56B cardinality=9.50M cost=3506206
+| | mem-estimate=17.04MB mem-reservation=0B thread-reservation=0
+| | tuple-ids=13 row-size=56B cardinality=42.75M cost=15770044
| | in pipelines: 27(GETNEXT)
| |
-| F09:PLAN FRAGMENT [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)] hosts=10 instances=70 (adjusted from 120)
-| Per-Instance Resources: mem-estimate=104.44MB mem-reservation=34.00MB thread-reservation=1
-| max-parallelism=70 segment-costs=[602697970, 39200831] cpu-comparison-result=108 [max(70 (self) vs 108 (sum children))]
+| F09:PLAN FRAGMENT [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)] hosts=10 instances=120
+| Per-Instance Resources: mem-estimate=247.50MB mem-reservation=34.00MB thread-reservation=1
+| max-parallelism=280 segment-costs=[2710785473, 176315586] cpu-comparison-result=120 [max(120 (self) vs 108 (sum children))]
| 27:AGGREGATE [FINALIZE]
| | output: stddev_samp:merge(inv_quantity_on_hand), avg:merge(inv_quantity_on_hand)
| | group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| | having: CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN CAST(0 AS DOUBLE) ELSE stddev_samp(inv_quantity_on_hand) / avg(inv_quantity_on_hand) END > CAST(1 AS DOUBLE)
-| | mem-estimate=88.11MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| | tuple-ids=13 row-size=56B cardinality=9.50M cost=567635904
+| | mem-estimate=231.17MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
+| | tuple-ids=13 row-size=56B cardinality=42.75M cost=2553085026
| | in pipelines: 27(GETNEXT), 08(OPEN)
| |
| 26:EXCHANGE [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)]
| | mem-estimate=16.33MB mem-reservation=0B thread-reservation=0
-| | tuple-ids=12 row-size=56B cardinality=95.04M cost=35062066
+| | tuple-ids=12 row-size=56B cardinality=427.47M cost=157700447
| | in pipelines: 08(GETNEXT)
| |
| F05:PLAN FRAGMENT [RANDOM] hosts=10 instances=108 (adjusted from 120)
| Per-Host Shared Resources: mem-estimate=3.00MB mem-reservation=3.00MB thread-reservation=0 runtime-filters-memory=3.00MB
-| Per-Instance Resources: mem-estimate=225.06MB mem-reservation=34.25MB thread-reservation=1
-| max-parallelism=108 segment-costs=[3213076826, 392008318] cpu-comparison-result=108 [max(108 (self) vs 36 (sum children))]
+| Per-Instance Resources: mem-estimate=236.79MB mem-reservation=34.25MB thread-reservation=1
+| max-parallelism=108 segment-costs=[3213076826, 1763155857] cpu-comparison-result=108 [max(108 (self) vs 36 (sum children))]
| 15:AGGREGATE [STREAMING]
| | output: stddev_samp(inv_quantity_on_hand), avg(CAST(inv_quantity_on_hand AS BIGINT))
| | group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| | mem-estimate=192.64MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| | tuple-ids=12 row-size=56B cardinality=95.04M cost=2553085026
+| | tuple-ids=12 row-size=56B cardinality=427.47M cost=2553085026
| | in pipelines: 08(GETNEXT)
| |
| 14:HASH JOIN [INNER JOIN, BROADCAST]
@@ -748,35 +748,35 @@ max-parallelism=10 segment-costs=[34686928, 16931376] cpu-comparison-result=216
| in pipelines: 08(GETNEXT)
|
28:EXCHANGE [HASH(i_item_sk,w_warehouse_sk)]
-| mem-estimate=14.10MB mem-reservation=0B thread-reservation=0
-| tuple-ids=5 row-size=56B cardinality=9.50M cost=3506206
+| mem-estimate=17.04MB mem-reservation=0B thread-reservation=0
+| tuple-ids=5 row-size=56B cardinality=42.75M cost=15770044
| in pipelines: 22(GETNEXT)
|
-F04:PLAN FRAGMENT [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)] hosts=10 instances=70 (adjusted from 120)
-Per-Instance Resources: mem-estimate=104.44MB mem-reservation=34.00MB thread-reservation=1
-max-parallelism=70 segment-costs=[602697970, 39200831] cpu-comparison-result=108 [max(70 (self) vs 108 (sum children))]
+F04:PLAN FRAGMENT [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)] hosts=10 instances=120
+Per-Instance Resources: mem-estimate=247.50MB mem-reservation=34.00MB thread-reservation=1
+max-parallelism=280 segment-costs=[2710785473, 176315586] cpu-comparison-result=120 [max(120 (self) vs 108 (sum children))]
22:AGGREGATE [FINALIZE]
| output: stddev_samp:merge(inv_quantity_on_hand), avg:merge(inv_quantity_on_hand)
| group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| having: CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN CAST(0 AS DOUBLE) ELSE stddev_samp(inv_quantity_on_hand) / avg(inv_quantity_on_hand) END > CAST(1 AS DOUBLE)
-| mem-estimate=88.11MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=5 row-size=56B cardinality=9.50M cost=567635904
+| mem-estimate=231.17MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
+| tuple-ids=5 row-size=56B cardinality=42.75M cost=2553085026
| in pipelines: 22(GETNEXT), 00(OPEN)
|
21:EXCHANGE [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)]
| mem-estimate=16.33MB mem-reservation=0B thread-reservation=0
-| tuple-ids=4 row-size=56B cardinality=95.04M cost=35062066
+| tuple-ids=4 row-size=56B cardinality=427.47M cost=157700447
| in pipelines: 00(GETNEXT)
|
F00:PLAN FRAGMENT [RANDOM] hosts=10 instances=108 (adjusted from 120)
Per-Host Shared Resources: mem-estimate=5.00MB mem-reservation=5.00MB thread-reservation=0 runtime-filters-memory=5.00MB
-Per-Instance Resources: mem-estimate=225.06MB mem-reservation=34.25MB thread-reservation=1
-max-parallelism=108 segment-costs=[3213076826, 392008318] cpu-comparison-result=108 [max(108 (self) vs 36 (sum children))]
+Per-Instance Resources: mem-estimate=236.79MB mem-reservation=34.25MB thread-reservation=1
+max-parallelism=108 segment-costs=[3213076826, 1763155857] cpu-comparison-result=108 [max(108 (self) vs 36 (sum children))]
07:AGGREGATE [STREAMING]
| output: stddev_samp(inv_quantity_on_hand), avg(CAST(inv_quantity_on_hand AS BIGINT))
| group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| mem-estimate=192.64MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=4 row-size=56B cardinality=95.04M cost=2553085026
+| tuple-ids=4 row-size=56B cardinality=427.47M cost=2553085026
| in pipelines: 00(GETNEXT)
|
06:HASH JOIN [INNER JOIN, BROADCAST]
diff --git a/testdata/workloads/functional-planner/queries/PlannerTest/tpcds_cpu_cost/tpcds-q39b.test b/testdata/workloads/functional-planner/queries/PlannerTest/tpcds_cpu_cost/tpcds-q39b.test
index 943017ec7..bedefdbcc 100644
--- a/testdata/workloads/functional-planner/queries/PlannerTest/tpcds_cpu_cost/tpcds-q39b.test
+++ b/testdata/workloads/functional-planner/queries/PlannerTest/tpcds_cpu_cost/tpcds-q39b.test
@@ -29,26 +29,26 @@ order by inv1.w_warehouse_sk,inv1.i_item_sk,inv1.d_moy,inv1.mean,inv1.cov
# end query 39 in stream 0 using template query39.tpl
---- PLAN
Max Per-Host Resource Reservation: Memory=131.12MB Threads=1
-Per-Host Resource Estimates: Memory=8.40GB
+Per-Host Resource Estimates: Memory=11.19GB
F00:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
-| Per-Instance Resources: mem-estimate=8.40GB mem-reservation=131.12MB thread-reservation=1 runtime-filters-memory=8.00MB
-| max-parallelism=1 segment-costs=[3213474426, 3213474426, 40684722, 95040000]
+| Per-Instance Resources: mem-estimate=11.19GB mem-reservation=131.12MB thread-reservation=1 runtime-filters-memory=8.00MB
+| max-parallelism=1 segment-costs=[3213474426, 3213474426, 182989764, 427466272]
PLAN-ROOT SINK
| output exprs: w_warehouse_sk, i_item_sk, d_moy, mean, cov, w_warehouse_sk, i_item_sk, d_moy, mean, cov
-| mem-estimate=100.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 cost=95040000
+| mem-estimate=100.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 cost=427466272
|
17:SORT
| order by: w_warehouse_sk ASC, i_item_sk ASC, d_moy ASC, mean ASC, cov ASC, d_moy ASC, mean ASC, cov ASC
-| mem-estimate=507.57MB mem-reservation=6.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=16 row-size=56B cardinality=9.50M cost=27020822
+| mem-estimate=2.23GB mem-reservation=6.00MB spill-buffer=2.00MB thread-reservation=0
+| tuple-ids=16 row-size=56B cardinality=42.75M cost=121532939
| in pipelines: 17(GETNEXT), 07(OPEN)
|
16:HASH JOIN [INNER JOIN]
| hash predicates: i_item_sk = i_item_sk, w_warehouse_sk = w_warehouse_sk
| fk/pk conjuncts: i_item_sk = i_item_sk, w_warehouse_sk = w_warehouse_sk
| runtime filters: RF000[bloom] <- i_item_sk, RF001[bloom] <- w_warehouse_sk, RF002[min_max] <- i_item_sk, RF003[min_max] <- w_warehouse_sk
-| mem-estimate=724.17MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=5,13 row-size=112B cardinality=9.50M cost=13663900
+| mem-estimate=3.50GB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
+| tuple-ids=5,13 row-size=112B cardinality=42.75M cost=61456825
| in pipelines: 07(GETNEXT), 15(OPEN)
|
|--15:AGGREGATE [FINALIZE]
@@ -56,7 +56,7 @@ PLAN-ROOT SINK
| | group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| | having: CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN CAST(0 AS DOUBLE) ELSE stddev_samp(inv_quantity_on_hand) / avg(inv_quantity_on_hand) END > CAST(1 AS DOUBLE)
| | mem-estimate=3.82GB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| | tuple-ids=13 row-size=56B cardinality=9.50M cost=2553085026
+| | tuple-ids=13 row-size=56B cardinality=42.75M cost=2553085026
| | in pipelines: 15(GETNEXT), 08(OPEN)
| |
| 14:HASH JOIN [INNER JOIN]
@@ -133,7 +133,7 @@ PLAN-ROOT SINK
| group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| having: CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN CAST(0 AS DOUBLE) ELSE stddev_samp(inv_quantity_on_hand) / avg(inv_quantity_on_hand) END > CAST(1 AS DOUBLE), CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN NULL ELSE stddev_samp(inv_quantity_on_hand) / avg(inv_quantity_on_hand) END > CAST(1.5 AS DOUBLE)
| mem-estimate=3.82GB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=5 row-size=56B cardinality=9.50M cost=2553085026
+| tuple-ids=5 row-size=56B cardinality=42.75M cost=2553085026
| in pipelines: 07(GETNEXT), 00(OPEN)
|
06:HASH JOIN [INNER JOIN]
@@ -207,28 +207,28 @@ PLAN-ROOT SINK
tuple-ids=0 row-size=16B cardinality=427.68M(filtered from 1.03G) cost=98537472
in pipelines: 00(GETNEXT)
---- DISTRIBUTEDPLAN
-Max Per-Host Resource Reservation: Memory=1.71GB Threads=51
-Per-Host Resource Estimates: Memory=7.12GB
+Max Per-Host Resource Reservation: Memory=2.08GB Threads=63
+Per-Host Resource Estimates: Memory=12.39GB
F11:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
-| Per-Instance Resources: mem-estimate=151.34MB mem-reservation=4.00MB thread-reservation=1
-| max-parallelism=1 segment-costs=[98508009] cpu-comparison-result=216 [max(1 (self) vs 216 (sum children))]
+| Per-Instance Resources: mem-estimate=301.17MB mem-reservation=4.00MB thread-reservation=1
+| max-parallelism=1 segment-costs=[443064516] cpu-comparison-result=240 [max(1 (self) vs 240 (sum children))]
PLAN-ROOT SINK
| output exprs: w_warehouse_sk, i_item_sk, d_moy, mean, cov, w_warehouse_sk, i_item_sk, d_moy, mean, cov
-| mem-estimate=100.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 cost=95040000
+| mem-estimate=100.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 cost=427466272
|
30:MERGING-EXCHANGE [UNPARTITIONED]
| order by: w_warehouse_sk ASC, i_item_sk ASC, d_moy ASC, mean ASC, cov ASC, d_moy ASC, mean ASC, cov ASC
-| mem-estimate=51.34MB mem-reservation=0B thread-reservation=0
-| tuple-ids=16 row-size=56B cardinality=9.50M cost=3468009
+| mem-estimate=201.17MB mem-reservation=0B thread-reservation=0
+| tuple-ids=16 row-size=56B cardinality=42.75M cost=15598244
| in pipelines: 17(GETNEXT)
|
-F10:PLAN FRAGMENT [HASH(i_item_sk,w_warehouse_sk)] hosts=10 instances=10 (adjusted from 120)
-Per-Instance Resources: mem-estimate=64.86MB mem-reservation=6.00MB thread-reservation=1
-max-parallelism=10 segment-costs=[34686928, 16931376] cpu-comparison-result=216 [max(10 (self) vs 216 (sum children))]
+F10:PLAN FRAGMENT [HASH(i_item_sk,w_warehouse_sk)] hosts=10 instances=20 (adjusted from 120)
+Per-Instance Resources: mem-estimate=131.18MB mem-reservation=6.00MB thread-reservation=1
+max-parallelism=20 segment-costs=[156013181, 76153116] cpu-comparison-result=240 [max(20 (self) vs 240 (sum children))]
17:SORT
| order by: w_warehouse_sk ASC, i_item_sk ASC, d_moy ASC, mean ASC, cov ASC, d_moy ASC, mean ASC, cov ASC
-| mem-estimate=50.76MB mem-reservation=6.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=16 row-size=56B cardinality=9.50M cost=27020822
+| mem-estimate=114.15MB mem-reservation=6.00MB spill-buffer=2.00MB thread-reservation=0
+| tuple-ids=16 row-size=56B cardinality=42.75M cost=121532939
| in pipelines: 17(GETNEXT), 22(OPEN)
|
16:HASH JOIN [INNER JOIN, PARTITIONED]
@@ -236,48 +236,48 @@ max-parallelism=10 segment-costs=[34686928, 16931376] cpu-comparison-result=216
| hash predicates: i_item_sk = i_item_sk, w_warehouse_sk = w_warehouse_sk
| fk/pk conjuncts: i_item_sk = i_item_sk, w_warehouse_sk = w_warehouse_sk
| mem-estimate=0B mem-reservation=0B spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=5,13 row-size=112B cardinality=9.50M cost=4159900
+| tuple-ids=5,13 row-size=112B cardinality=42.75M cost=18710198
| in pipelines: 22(GETNEXT), 27(OPEN)
|
-|--F12:PLAN FRAGMENT [HASH(i_item_sk,w_warehouse_sk)] hosts=10 instances=10 (adjusted from 120)
-| | Per-Instance Resources: mem-estimate=88.52MB mem-reservation=36.00MB thread-reservation=1 runtime-filters-memory=2.00MB
-| | max-parallelism=10 segment-costs=[13010206] cpu-comparison-result=108 [max(10 (self) vs 108 (sum children))]
+|--F12:PLAN FRAGMENT [HASH(i_item_sk,w_warehouse_sk)] hosts=10 instances=20 (adjusted from 120)
+| | Per-Instance Resources: mem-estimate=198.25MB mem-reservation=36.00MB thread-reservation=1 runtime-filters-memory=2.00MB
+| | max-parallelism=20 segment-costs=[58516671] cpu-comparison-result=120 [max(20 (self) vs 120 (sum children))]
| JOIN BUILD
| | join-table-id=00 plan-id=01 cohort-id=01
| | build expressions: i_item_sk, w_warehouse_sk
| | runtime filters: RF000[bloom] <- i_item_sk, RF001[bloom] <- w_warehouse_sk, RF002[min_max] <- i_item_sk, RF003[min_max] <- w_warehouse_sk
-| | mem-estimate=72.42MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0 cost=9504000
+| | mem-estimate=179.21MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0 cost=42746627
| |
| 29:EXCHANGE [HASH(i_item_sk,w_warehouse_sk)]
-| | mem-estimate=14.10MB mem-reservation=0B thread-reservation=0
-| | tuple-ids=13 row-size=56B cardinality=9.50M cost=3506206
+| | mem-estimate=17.04MB mem-reservation=0B thread-reservation=0
+| | tuple-ids=13 row-size=56B cardinality=42.75M cost=15770044
| | in pipelines: 27(GETNEXT)
| |
-| F09:PLAN FRAGMENT [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)] hosts=10 instances=70 (adjusted from 120)
-| Per-Instance Resources: mem-estimate=104.44MB mem-reservation=34.00MB thread-reservation=1
-| max-parallelism=70 segment-costs=[602697970, 39200831] cpu-comparison-result=108 [max(70 (self) vs 108 (sum children))]
+| F09:PLAN FRAGMENT [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)] hosts=10 instances=120
+| Per-Instance Resources: mem-estimate=247.50MB mem-reservation=34.00MB thread-reservation=1
+| max-parallelism=280 segment-costs=[2710785473, 176315586] cpu-comparison-result=120 [max(120 (self) vs 108 (sum children))]
| 27:AGGREGATE [FINALIZE]
| | output: stddev_samp:merge(inv_quantity_on_hand), avg:merge(inv_quantity_on_hand)
| | group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| | having: CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN CAST(0 AS DOUBLE) ELSE stddev_samp(inv_quantity_on_hand) / avg(inv_quantity_on_hand) END > CAST(1 AS DOUBLE)
-| | mem-estimate=88.11MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| | tuple-ids=13 row-size=56B cardinality=9.50M cost=567635904
+| | mem-estimate=231.17MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
+| | tuple-ids=13 row-size=56B cardinality=42.75M cost=2553085026
| | in pipelines: 27(GETNEXT), 08(OPEN)
| |
| 26:EXCHANGE [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)]
| | mem-estimate=16.33MB mem-reservation=0B thread-reservation=0
-| | tuple-ids=12 row-size=56B cardinality=95.04M cost=35062066
+| | tuple-ids=12 row-size=56B cardinality=427.47M cost=157700447
| | in pipelines: 08(GETNEXT)
| |
| F05:PLAN FRAGMENT [RANDOM] hosts=10 instances=108 (adjusted from 120)
| Per-Host Shared Resources: mem-estimate=3.00MB mem-reservation=3.00MB thread-reservation=0 runtime-filters-memory=3.00MB
-| Per-Instance Resources: mem-estimate=225.06MB mem-reservation=34.25MB thread-reservation=1
-| max-parallelism=108 segment-costs=[3213076826, 392008318] cpu-comparison-result=108 [max(108 (self) vs 36 (sum children))]
+| Per-Instance Resources: mem-estimate=236.79MB mem-reservation=34.25MB thread-reservation=1
+| max-parallelism=108 segment-costs=[3213076826, 1763155857] cpu-comparison-result=108 [max(108 (self) vs 36 (sum children))]
| 15:AGGREGATE [STREAMING]
| | output: stddev_samp(inv_quantity_on_hand), avg(CAST(inv_quantity_on_hand AS BIGINT))
| | group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| | mem-estimate=192.64MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| | tuple-ids=12 row-size=56B cardinality=95.04M cost=2553085026
+| | tuple-ids=12 row-size=56B cardinality=427.47M cost=2553085026
| | in pipelines: 08(GETNEXT)
| |
| 14:HASH JOIN [INNER JOIN, BROADCAST]
@@ -401,35 +401,35 @@ max-parallelism=10 segment-costs=[34686928, 16931376] cpu-comparison-result=216
| in pipelines: 08(GETNEXT)
|
28:EXCHANGE [HASH(i_item_sk,w_warehouse_sk)]
-| mem-estimate=14.10MB mem-reservation=0B thread-reservation=0
-| tuple-ids=5 row-size=56B cardinality=9.50M cost=3506206
+| mem-estimate=17.04MB mem-reservation=0B thread-reservation=0
+| tuple-ids=5 row-size=56B cardinality=42.75M cost=15770044
| in pipelines: 22(GETNEXT)
|
-F04:PLAN FRAGMENT [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)] hosts=10 instances=70 (adjusted from 120)
-Per-Instance Resources: mem-estimate=104.44MB mem-reservation=34.00MB thread-reservation=1
-max-parallelism=70 segment-costs=[602697970, 39200831] cpu-comparison-result=108 [max(70 (self) vs 108 (sum children))]
+F04:PLAN FRAGMENT [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)] hosts=10 instances=120
+Per-Instance Resources: mem-estimate=247.50MB mem-reservation=34.00MB thread-reservation=1
+max-parallelism=280 segment-costs=[2710785473, 176315586] cpu-comparison-result=120 [max(120 (self) vs 108 (sum children))]
22:AGGREGATE [FINALIZE]
| output: stddev_samp:merge(inv_quantity_on_hand), avg:merge(inv_quantity_on_hand)
| group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| having: CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN CAST(0 AS DOUBLE) ELSE stddev_samp(inv_quantity_on_hand) / avg(inv_quantity_on_hand) END > CAST(1 AS DOUBLE), CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN NULL ELSE stddev_samp(inv_quantity_on_hand) / avg(inv_quantity_on_hand) END > CAST(1.5 AS DOUBLE)
-| mem-estimate=88.11MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=5 row-size=56B cardinality=9.50M cost=567635904
+| mem-estimate=231.17MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
+| tuple-ids=5 row-size=56B cardinality=42.75M cost=2553085026
| in pipelines: 22(GETNEXT), 00(OPEN)
|
21:EXCHANGE [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)]
| mem-estimate=16.33MB mem-reservation=0B thread-reservation=0
-| tuple-ids=4 row-size=56B cardinality=95.04M cost=35062066
+| tuple-ids=4 row-size=56B cardinality=427.47M cost=157700447
| in pipelines: 00(GETNEXT)
|
F00:PLAN FRAGMENT [RANDOM] hosts=10 instances=108 (adjusted from 120)
Per-Host Shared Resources: mem-estimate=5.00MB mem-reservation=5.00MB thread-reservation=0 runtime-filters-memory=5.00MB
-Per-Instance Resources: mem-estimate=225.06MB mem-reservation=34.25MB thread-reservation=1
-max-parallelism=108 segment-costs=[3213076826, 392008318] cpu-comparison-result=108 [max(108 (self) vs 36 (sum children))]
+Per-Instance Resources: mem-estimate=236.79MB mem-reservation=34.25MB thread-reservation=1
+max-parallelism=108 segment-costs=[3213076826, 1763155857] cpu-comparison-result=108 [max(108 (self) vs 36 (sum children))]
07:AGGREGATE [STREAMING]
| output: stddev_samp(inv_quantity_on_hand), avg(CAST(inv_quantity_on_hand AS BIGINT))
| group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| mem-estimate=192.64MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=4 row-size=56B cardinality=95.04M cost=2553085026
+| tuple-ids=4 row-size=56B cardinality=427.47M cost=2553085026
| in pipelines: 00(GETNEXT)
|
06:HASH JOIN [INNER JOIN, BROADCAST]
@@ -556,28 +556,28 @@ max-parallelism=108 segment-costs=[3213076826, 392008318] cpu-comparison-result=
tuple-ids=0 row-size=16B cardinality=427.68M(filtered from 1.03G) cost=98537472
in pipelines: 00(GETNEXT)
---- PARALLELPLANS
-Max Per-Host Resource Reservation: Memory=1.71GB Threads=51
-Per-Host Resource Estimates: Memory=7.12GB
+Max Per-Host Resource Reservation: Memory=2.08GB Threads=63
+Per-Host Resource Estimates: Memory=12.39GB
F11:PLAN FRAGMENT [UNPARTITIONED] hosts=1 instances=1
-| Per-Instance Resources: mem-estimate=151.34MB mem-reservation=4.00MB thread-reservation=1
-| max-parallelism=1 segment-costs=[98508009] cpu-comparison-result=216 [max(1 (self) vs 216 (sum children))]
+| Per-Instance Resources: mem-estimate=301.17MB mem-reservation=4.00MB thread-reservation=1
+| max-parallelism=1 segment-costs=[443064516] cpu-comparison-result=240 [max(1 (self) vs 240 (sum children))]
PLAN-ROOT SINK
| output exprs: w_warehouse_sk, i_item_sk, d_moy, mean, cov, w_warehouse_sk, i_item_sk, d_moy, mean, cov
-| mem-estimate=100.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 cost=95040000
+| mem-estimate=100.00MB mem-reservation=4.00MB spill-buffer=2.00MB thread-reservation=0 cost=427466272
|
30:MERGING-EXCHANGE [UNPARTITIONED]
| order by: w_warehouse_sk ASC, i_item_sk ASC, d_moy ASC, mean ASC, cov ASC, d_moy ASC, mean ASC, cov ASC
-| mem-estimate=51.34MB mem-reservation=0B thread-reservation=0
-| tuple-ids=16 row-size=56B cardinality=9.50M cost=3468009
+| mem-estimate=201.17MB mem-reservation=0B thread-reservation=0
+| tuple-ids=16 row-size=56B cardinality=42.75M cost=15598244
| in pipelines: 17(GETNEXT)
|
-F10:PLAN FRAGMENT [HASH(i_item_sk,w_warehouse_sk)] hosts=10 instances=10 (adjusted from 120)
-Per-Instance Resources: mem-estimate=64.86MB mem-reservation=6.00MB thread-reservation=1
-max-parallelism=10 segment-costs=[34686928, 16931376] cpu-comparison-result=216 [max(10 (self) vs 216 (sum children))]
+F10:PLAN FRAGMENT [HASH(i_item_sk,w_warehouse_sk)] hosts=10 instances=20 (adjusted from 120)
+Per-Instance Resources: mem-estimate=131.18MB mem-reservation=6.00MB thread-reservation=1
+max-parallelism=20 segment-costs=[156013181, 76153116] cpu-comparison-result=240 [max(20 (self) vs 240 (sum children))]
17:SORT
| order by: w_warehouse_sk ASC, i_item_sk ASC, d_moy ASC, mean ASC, cov ASC, d_moy ASC, mean ASC, cov ASC
-| mem-estimate=50.76MB mem-reservation=6.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=16 row-size=56B cardinality=9.50M cost=27020822
+| mem-estimate=114.15MB mem-reservation=6.00MB spill-buffer=2.00MB thread-reservation=0
+| tuple-ids=16 row-size=56B cardinality=42.75M cost=121532939
| in pipelines: 17(GETNEXT), 22(OPEN)
|
16:HASH JOIN [INNER JOIN, PARTITIONED]
@@ -585,48 +585,48 @@ max-parallelism=10 segment-costs=[34686928, 16931376] cpu-comparison-result=216
| hash predicates: i_item_sk = i_item_sk, w_warehouse_sk = w_warehouse_sk
| fk/pk conjuncts: i_item_sk = i_item_sk, w_warehouse_sk = w_warehouse_sk
| mem-estimate=0B mem-reservation=0B spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=5,13 row-size=112B cardinality=9.50M cost=4159900
+| tuple-ids=5,13 row-size=112B cardinality=42.75M cost=18710198
| in pipelines: 22(GETNEXT), 27(OPEN)
|
-|--F12:PLAN FRAGMENT [HASH(i_item_sk,w_warehouse_sk)] hosts=10 instances=10 (adjusted from 120)
-| | Per-Instance Resources: mem-estimate=88.52MB mem-reservation=36.00MB thread-reservation=1 runtime-filters-memory=2.00MB
-| | max-parallelism=10 segment-costs=[13010206] cpu-comparison-result=108 [max(10 (self) vs 108 (sum children))]
+|--F12:PLAN FRAGMENT [HASH(i_item_sk,w_warehouse_sk)] hosts=10 instances=20 (adjusted from 120)
+| | Per-Instance Resources: mem-estimate=198.25MB mem-reservation=36.00MB thread-reservation=1 runtime-filters-memory=2.00MB
+| | max-parallelism=20 segment-costs=[58516671] cpu-comparison-result=120 [max(20 (self) vs 120 (sum children))]
| JOIN BUILD
| | join-table-id=00 plan-id=01 cohort-id=01
| | build expressions: i_item_sk, w_warehouse_sk
| | runtime filters: RF000[bloom] <- i_item_sk, RF001[bloom] <- w_warehouse_sk, RF002[min_max] <- i_item_sk, RF003[min_max] <- w_warehouse_sk
-| | mem-estimate=72.42MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0 cost=9504000
+| | mem-estimate=179.21MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0 cost=42746627
| |
| 29:EXCHANGE [HASH(i_item_sk,w_warehouse_sk)]
-| | mem-estimate=14.10MB mem-reservation=0B thread-reservation=0
-| | tuple-ids=13 row-size=56B cardinality=9.50M cost=3506206
+| | mem-estimate=17.04MB mem-reservation=0B thread-reservation=0
+| | tuple-ids=13 row-size=56B cardinality=42.75M cost=15770044
| | in pipelines: 27(GETNEXT)
| |
-| F09:PLAN FRAGMENT [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)] hosts=10 instances=70 (adjusted from 120)
-| Per-Instance Resources: mem-estimate=104.44MB mem-reservation=34.00MB thread-reservation=1
-| max-parallelism=70 segment-costs=[602697970, 39200831] cpu-comparison-result=108 [max(70 (self) vs 108 (sum children))]
+| F09:PLAN FRAGMENT [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)] hosts=10 instances=120
+| Per-Instance Resources: mem-estimate=247.50MB mem-reservation=34.00MB thread-reservation=1
+| max-parallelism=280 segment-costs=[2710785473, 176315586] cpu-comparison-result=120 [max(120 (self) vs 108 (sum children))]
| 27:AGGREGATE [FINALIZE]
| | output: stddev_samp:merge(inv_quantity_on_hand), avg:merge(inv_quantity_on_hand)
| | group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| | having: CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN CAST(0 AS DOUBLE) ELSE stddev_samp(inv_quantity_on_hand) / avg(inv_quantity_on_hand) END > CAST(1 AS DOUBLE)
-| | mem-estimate=88.11MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| | tuple-ids=13 row-size=56B cardinality=9.50M cost=567635904
+| | mem-estimate=231.17MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
+| | tuple-ids=13 row-size=56B cardinality=42.75M cost=2553085026
| | in pipelines: 27(GETNEXT), 08(OPEN)
| |
| 26:EXCHANGE [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)]
| | mem-estimate=16.33MB mem-reservation=0B thread-reservation=0
-| | tuple-ids=12 row-size=56B cardinality=95.04M cost=35062066
+| | tuple-ids=12 row-size=56B cardinality=427.47M cost=157700447
| | in pipelines: 08(GETNEXT)
| |
| F05:PLAN FRAGMENT [RANDOM] hosts=10 instances=108 (adjusted from 120)
| Per-Host Shared Resources: mem-estimate=3.00MB mem-reservation=3.00MB thread-reservation=0 runtime-filters-memory=3.00MB
-| Per-Instance Resources: mem-estimate=225.06MB mem-reservation=34.25MB thread-reservation=1
-| max-parallelism=108 segment-costs=[3213076826, 392008318] cpu-comparison-result=108 [max(108 (self) vs 36 (sum children))]
+| Per-Instance Resources: mem-estimate=236.79MB mem-reservation=34.25MB thread-reservation=1
+| max-parallelism=108 segment-costs=[3213076826, 1763155857] cpu-comparison-result=108 [max(108 (self) vs 36 (sum children))]
| 15:AGGREGATE [STREAMING]
| | output: stddev_samp(inv_quantity_on_hand), avg(CAST(inv_quantity_on_hand AS BIGINT))
| | group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| | mem-estimate=192.64MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| | tuple-ids=12 row-size=56B cardinality=95.04M cost=2553085026
+| | tuple-ids=12 row-size=56B cardinality=427.47M cost=2553085026
| | in pipelines: 08(GETNEXT)
| |
| 14:HASH JOIN [INNER JOIN, BROADCAST]
@@ -750,35 +750,35 @@ max-parallelism=10 segment-costs=[34686928, 16931376] cpu-comparison-result=216
| in pipelines: 08(GETNEXT)
|
28:EXCHANGE [HASH(i_item_sk,w_warehouse_sk)]
-| mem-estimate=14.10MB mem-reservation=0B thread-reservation=0
-| tuple-ids=5 row-size=56B cardinality=9.50M cost=3506206
+| mem-estimate=17.04MB mem-reservation=0B thread-reservation=0
+| tuple-ids=5 row-size=56B cardinality=42.75M cost=15770044
| in pipelines: 22(GETNEXT)
|
-F04:PLAN FRAGMENT [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)] hosts=10 instances=70 (adjusted from 120)
-Per-Instance Resources: mem-estimate=104.44MB mem-reservation=34.00MB thread-reservation=1
-max-parallelism=70 segment-costs=[602697970, 39200831] cpu-comparison-result=108 [max(70 (self) vs 108 (sum children))]
+F04:PLAN FRAGMENT [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)] hosts=10 instances=120
+Per-Instance Resources: mem-estimate=247.50MB mem-reservation=34.00MB thread-reservation=1
+max-parallelism=280 segment-costs=[2710785473, 176315586] cpu-comparison-result=120 [max(120 (self) vs 108 (sum children))]
22:AGGREGATE [FINALIZE]
| output: stddev_samp:merge(inv_quantity_on_hand), avg:merge(inv_quantity_on_hand)
| group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| having: CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN CAST(0 AS DOUBLE) ELSE stddev_samp(inv_quantity_on_hand) / avg(inv_quantity_on_hand) END > CAST(1 AS DOUBLE), CASE avg(inv_quantity_on_hand) WHEN CAST(0 AS DOUBLE) THEN NULL ELSE stddev_samp(inv_quantity_on_hand) / avg(inv_quantity_on_hand) END > CAST(1.5 AS DOUBLE)
-| mem-estimate=88.11MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=5 row-size=56B cardinality=9.50M cost=567635904
+| mem-estimate=231.17MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
+| tuple-ids=5 row-size=56B cardinality=42.75M cost=2553085026
| in pipelines: 22(GETNEXT), 00(OPEN)
|
21:EXCHANGE [HASH(w_warehouse_name,w_warehouse_sk,i_item_sk,d_moy)]
| mem-estimate=16.33MB mem-reservation=0B thread-reservation=0
-| tuple-ids=4 row-size=56B cardinality=95.04M cost=35062066
+| tuple-ids=4 row-size=56B cardinality=427.47M cost=157700447
| in pipelines: 00(GETNEXT)
|
F00:PLAN FRAGMENT [RANDOM] hosts=10 instances=108 (adjusted from 120)
Per-Host Shared Resources: mem-estimate=5.00MB mem-reservation=5.00MB thread-reservation=0 runtime-filters-memory=5.00MB
-Per-Instance Resources: mem-estimate=225.06MB mem-reservation=34.25MB thread-reservation=1
-max-parallelism=108 segment-costs=[3213076826, 392008318] cpu-comparison-result=108 [max(108 (self) vs 36 (sum children))]
+Per-Instance Resources: mem-estimate=236.79MB mem-reservation=34.25MB thread-reservation=1
+max-parallelism=108 segment-costs=[3213076826, 1763155857] cpu-comparison-result=108 [max(108 (self) vs 36 (sum children))]
07:AGGREGATE [STREAMING]
| output: stddev_samp(inv_quantity_on_hand), avg(CAST(inv_quantity_on_hand AS BIGINT))
| group by: w_warehouse_name, w_warehouse_sk, i_item_sk, d_moy
| mem-estimate=192.64MB mem-reservation=34.00MB spill-buffer=2.00MB thread-reservation=0
-| tuple-ids=4 row-size=56B cardinality=95.04M cost=2553085026
+| tuple-ids=4 row-size=56B cardinality=427.47M cost=2553085026
| in pipelines: 00(GETNEXT)
|
06:HASH JOIN [INNER JOIN, BROADCAST]