This is an automated email from the ASF dual-hosted git repository. preetham02 pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/asterixdb.git
commit bb05962122d2725e11d2340d4bc85aafc029cc45 Author: murali4104 <[email protected]> AuthorDate: Fri Jun 27 15:18:54 2025 -0700 [ASTERIXDB-3600][COMP] cardinality improvements (GBY) Ext-ref: MB-66327 Change-Id: Ic8ea02415863ba0d4a9f154a213407b4e66466bc Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/19995 Tested-by: Jenkins <[email protected]> Integration-Tests: Jenkins <[email protected]> Reviewed-by: Preetham Poluparthi <[email protected]> --- .../apache/asterix/optimizer/rules/cbo/Stats.java | 126 +++++++++++---------- .../join-queries/join-queries.3.plan | 6 +- .../join-queries/join-queries.4.plan | 6 +- .../join-queries/join-queries.8.plan | 14 +-- .../single-collection-queries.3.plan | 16 +-- .../single-collection-queries.5.plan | 16 +-- .../single-collection-queries.6.plan | 16 +-- .../single-collection-queries.7.plan | 16 +-- .../tpch/query-plans/query-plans.03.plan | 30 ++--- .../tpch/query-plans/query-plans.05.plan | 24 ++-- .../tpch/query-plans/query-plans.06.plan | 28 ++--- .../tpch/query-plans/query-plans.07.plan | 18 +-- .../tpch/query-plans/query-plans.08.plan | 18 +-- .../tpch/query-plans/query-plans.09.plan | 14 +-- 14 files changed, 179 insertions(+), 169 deletions(-) diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/Stats.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/Stats.java index ab57d3ff8d..34584c9560 100644 --- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/Stats.java +++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/Stats.java @@ -68,6 +68,7 @@ import org.apache.hyracks.algebricks.core.algebra.operators.logical.AggregateOpe import org.apache.hyracks.algebricks.core.algebra.operators.logical.AssignOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.DataSourceScanOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.DistinctOperator; +import org.apache.hyracks.algebricks.core.algebra.operators.logical.GroupByOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.ProjectOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.SelectOperator; import org.apache.hyracks.algebricks.core.algebra.operators.logical.SubplanOperator; @@ -1005,7 +1006,7 @@ public class Stats { setTotalCardFromSample(sampleWithPredicates); } // get the estimated distinct cardinality for the dataset (i.e., D_est or D_est_f) - distinctCard = findEstDistinctWithPredicates(grpByDistinctOp, origDatasetCard, sampleDataSource); + distinctCard = findEstDistinctWithPredicates(grpByDistinctOp, origDatasetCard, sampleDataSource, index); } return distinctCard; } @@ -1022,26 +1023,89 @@ public class Stats { } private long findEstDistinctWithPredicates(ILogicalOperator grpByDistinctOp, double origDatasetCardinality, - SampleDataSource sampleDataSource) throws AlgebricksException { + SampleDataSource sampleDataSource, Index index) throws AlgebricksException { double estDistCardinalityFromSample = -1.0; double estDistCardinality = -1.0; LogicalOperatorTag tag = grpByDistinctOp.getOperatorTag(); - if (tag == LogicalOperatorTag.GROUP || tag == LogicalOperatorTag.DISTINCT) { + if (tag == LogicalOperatorTag.DISTINCT) { ILogicalOperator copyOfGrpByDistinctOp = OperatorManipulationUtil.bottomUpCopyOperators(grpByDistinctOp); if (setSampleDataSource(copyOfGrpByDistinctOp, sampleDataSource)) { // get distinct cardinality from the sampling source List<List<IAObject>> result = runSamplingQuery(optCtx, copyOfGrpByDistinctOp); estDistCardinalityFromSample = findPredicateCardinality(result, false); } + } else if (tag == LogicalOperatorTag.GROUP) { + ILogicalOperator copyOfGrpByDistinctOp = OperatorManipulationUtil.bottomUpCopyOperators(grpByDistinctOp); + if (setSampleDataSource(copyOfGrpByDistinctOp, sampleDataSource)) { + // get distinct cardinality from the sampling source + GroupByOperator gb = (GroupByOperator) copyOfGrpByDistinctOp; + int numFields = gb.getGroupByList().size(); + if (numFields == 1) { // This is the very simple case. So kept it. + List<List<IAObject>> result = runSamplingQuery(optCtx, copyOfGrpByDistinctOp); + estDistCardinalityFromSample = findPredicateCardinality(result, false); + if (estDistCardinalityFromSample != -1.0) { // estimate distinct cardinality for the dataset from the sampled cardinality + estDistCardinality = secondDistinctEstimator(estDistCardinalityFromSample, index); + } + } else { // now create one sample query with multiple count distincts + // Bypass the group by operator + // now add aggregate [$$49, $$50] <- [agg-sql-count-distinct($$39), agg-sql-count-distinct($$44)] + //List<Mutable<ILogicalExpression>> fields = new ArrayList<>(1); + List<Pair<LogicalVariable, Mutable<ILogicalExpression>>> groupByList = gb.getGroupByList(); + List<AggregateFunctionCallExpression> aggExprList = new ArrayList<>(); + List<BuiltinFunctionInfo> countFn = new ArrayList<>(); + // create a new var List [$$49, $$50] + List<LogicalVariable> varList = new ArrayList<>(); + + // create Mutable expressions + List<Mutable<ILogicalExpression>> aggExprMutableList = new ArrayList<>(1); + + for (int i = 0; i < numFields; i++) { + ILogicalExpression var = groupByList.get(i).second.getValue(); + Mutable<ILogicalExpression> mvar = new MutableObject<>(var); + List<Mutable<ILogicalExpression>> fields = new ArrayList<>(); + fields.add(mvar); + countFn.add(BuiltinFunctions.getBuiltinFunctionInfo(BuiltinFunctions.SQL_COUNTN_DISTINCT)); + aggExprList.add(new AggregateFunctionCallExpression(countFn.get(i), false, fields)); + //aggexprList is [agg-sql-count-distinct($$39), agg-sql-count-distinct($$44)] + varList.add(optCtx.newVar()); + aggExprMutableList.add(new MutableObject<>(aggExprList.get(i))); + } + AggregateOperator newAggOp = new AggregateOperator(varList, aggExprMutableList); + //connect newAggOp to the leafInput below bypassing the groupby + newAggOp.getInputs().add(new MutableObject<>(gb.getInputs().get(0).getValue())); + + List<List<IAObject>> result = helperFunction(joinEnum.optCtx, newAggOp); + List<Double> sampleEstimates = extractSampleEstimates(result, numFields); + estDistCardinality = 1; //change + for (int i = 0; i < numFields; i++) { + estDistCardinality *= secondDistinctEstimator(sampleEstimates.get(i), index); // any checks? zero? + } + if (estDistCardinality > origDatasetCardinality) { + estDistCardinality = origDatasetCardinality; // obviously cannot be higher than the dataset cardinality + } + } + } } + if (estDistCardinalityFromSample != -1.0) { // estimate distinct cardinality for the dataset from the sampled cardinality - estDistCardinality = distinctEstimator(estDistCardinalityFromSample, origDatasetCardinality); + estDistCardinality = secondDistinctEstimator(estDistCardinalityFromSample, index); } estDistCardinality = Math.max(0.0, estDistCardinality); return Math.round(estDistCardinality); } + private List<Double> extractSampleEstimates(List<List<IAObject>> result, int numFields) { + List<Double> sampleEstimates = new ArrayList<>(); + ARecord record = (ARecord) (result.get(0)).get(0); + for (int i = 0; i < numFields; i++) { + IAObject obj = record.getValueByPos(i); + double x = (double) ((AInt64) obj).getLongValue(); + sampleEstimates.add(x); + } + return sampleEstimates; + } + // Formula is d = D (1 - e^(-sampleCard/D)) double DistinctFormula(double sampleCard, double D) { double a, b, c; @@ -1083,60 +1147,6 @@ public class Stats { return D; } - // Use the Newton-Raphson method for distinct cardinality estimation. - private double distinctEstimator(double estDistinctCardinalityFromSample, double origDatasetCardinality) { - // initialize the estimate to be the number of distinct values from the sample. - double estDistinctCardinality = initNR(estDistinctCardinalityFromSample); - setDistinctCardFromSample(estDistinctCardinality); - - int itr_counter = 0, max_counter = 1000; // allow a maximum number of iterations - double denominator = derivativeFunctionForMMO(estDistinctCardinality); - if (denominator == 0.0) { // Newton-Raphson method requires it to be non-zero - return estDistinctCardinality; - } - double fraction = functionForMMO(estDistinctCardinality) / denominator; - while (Math.abs(fraction) >= 0.001 && itr_counter < max_counter) { - denominator = derivativeFunctionForMMO(estDistinctCardinality); - if (denominator == 0.0) { - break; - } - fraction = functionForMMO(estDistinctCardinality) / denominator; - estDistinctCardinality = estDistinctCardinality - fraction; - itr_counter++; - if (estDistinctCardinality > origDatasetCardinality) { - estDistinctCardinality = origDatasetCardinality; // for preventing infinite growth beyond N - break; - } - } - - // estimated cardinality cannot be less the initial one from samples - estDistinctCardinality = Math.max(estDistinctCardinality, estDistinctCardinalityFromSample); - - return estDistinctCardinality; - } - - double initNR(double estDistinctCardinalityFromSample) { - double estDistinctCardinality = estDistinctCardinalityFromSample; - - // Boundary condition checks for Newton-Raphson method. - if (totalCardFromSample <= MIN_TOTAL_SAMPLES) { - setTotalCardFromSample(totalCardFromSample + 2); - estDistinctCardinality = totalCardFromSample - 1; - } else if (estDistinctCardinality == totalCardFromSample) { - estDistinctCardinality--; - } - return estDistinctCardinality; - } - - private double functionForMMO(double x) { - return (x * (1.0 - Math.exp(-1.0 * (double) totalCardFromSample / x)) - distinctCardFromSample); - } - - private double derivativeFunctionForMMO(double x) { - double arg = ((double) totalCardFromSample / x); - return (1.0 - (arg + 1.0) * Math.exp(-1.0 * arg)); - } - private boolean setSampleDataSource(ILogicalOperator op, SampleDataSource sampleDataSource) { ILogicalOperator parent = joinEnum.findDataSourceScanOperatorParent(op); DataSourceScanOperator scanOp = (DataSourceScanOperator) parent.getInputs().get(0).getValue(); diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.3.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.3.plan index 0cd719931d..e06c9461c1 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.3.plan +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.3.plan @@ -1,8 +1,8 @@ -distribute result [$$35] [cardinality: 960.71, doc-size: 15.0, op-cost: 0.0, total-cost: 136471.34] +distribute result [$$35] [cardinality: 681.1, doc-size: 15.0, op-cost: 0.0, total-cost: 136471.34] -- DISTRIBUTE_RESULT |PARTITIONED| - exchange [cardinality: 960.71, doc-size: 15.0, op-cost: 0.0, total-cost: 136471.34] + exchange [cardinality: 681.1, doc-size: 15.0, op-cost: 0.0, total-cost: 136471.34] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - distinct ([$$35]) [cardinality: 960.71, doc-size: 15.0, op-cost: 0.0, total-cost: 136471.34] + distinct ([$$35]) [cardinality: 681.1, doc-size: 15.0, op-cost: 0.0, total-cost: 136471.34] -- PRE_SORTED_DISTINCT_BY |PARTITIONED| exchange [cardinality: 4783.64, doc-size: 15.0, op-cost: 0.0, total-cost: 77996.64] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.4.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.4.plan index d69e138a7b..4c120e1414 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.4.plan +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.4.plan @@ -1,8 +1,8 @@ -distribute result [$$34] [cardinality: 1400.38, doc-size: 5.0, op-cost: 0.0, total-cost: 166792.72] +distribute result [$$34] [cardinality: 1339.66, doc-size: 5.0, op-cost: 0.0, total-cost: 166792.72] -- DISTRIBUTE_RESULT |PARTITIONED| - exchange [cardinality: 1400.38, doc-size: 5.0, op-cost: 0.0, total-cost: 166792.72] + exchange [cardinality: 1339.66, doc-size: 5.0, op-cost: 0.0, total-cost: 166792.72] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - distinct ([$$34]) [cardinality: 1400.38, doc-size: 5.0, op-cost: 0.0, total-cost: 166792.72] + distinct ([$$34]) [cardinality: 1339.66, doc-size: 5.0, op-cost: 0.0, total-cost: 166792.72] -- PRE_SORTED_DISTINCT_BY |PARTITIONED| exchange [cardinality: 5880.6, doc-size: 5.0, op-cost: 0.0, total-cost: 93157.33] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.8.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.8.plan index ee29dcd26e..3eb913a973 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.8.plan +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/join-queries/join-queries.8.plan @@ -1,26 +1,26 @@ -distribute result [$$119] [cardinality: 25.0, doc-size: 45.0, op-cost: 0.0, total-cost: 6741.88] +distribute result [$$119] [cardinality: 24.0, doc-size: 45.0, op-cost: 0.0, total-cost: 6741.88] -- DISTRIBUTE_RESULT |PARTITIONED| - exchange [cardinality: 25.0, doc-size: 45.0, op-cost: 0.0, total-cost: 6741.88] + exchange [cardinality: 24.0, doc-size: 45.0, op-cost: 0.0, total-cost: 6741.88] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - assign [$$119] <- [{"n_name": $$n_name, "revenue": $$132}] project: [$$119] [cardinality: 25.0, doc-size: 45.0, op-cost: 0.0, total-cost: 6741.88] + assign [$$119] <- [{"n_name": $$n_name, "revenue": $$132}] project: [$$119] [cardinality: 24.0, doc-size: 45.0, op-cost: 0.0, total-cost: 6741.88] -- ASSIGN |PARTITIONED| - exchange [cardinality: 25.0, doc-size: 45.0, op-cost: 0.0, total-cost: 6741.88] + exchange [cardinality: 24.0, doc-size: 45.0, op-cost: 0.0, total-cost: 6741.88] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| group by ([$$n_name := $$142]) decor ([]) { aggregate [$$132] <- [global-sql-sum-serial($$141)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- AGGREGATE |LOCAL| nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- NESTED_TUPLE_SOURCE |LOCAL| - } [cardinality: 25.0, doc-size: 45.0, op-cost: 0.0, total-cost: 6741.88] + } [cardinality: 24.0, doc-size: 45.0, op-cost: 0.0, total-cost: 6741.88] -- EXTERNAL_GROUP_BY[$$142] |PARTITIONED| - exchange [cardinality: 25.0, doc-size: 45.0, op-cost: 0.0, total-cost: 6703.4] + exchange [cardinality: 24.0, doc-size: 45.0, op-cost: 0.0, total-cost: 6703.4] -- HASH_PARTITION_EXCHANGE [$$142] |PARTITIONED| group by ([$$142 := $$120]) decor ([]) { aggregate [$$141] <- [local-sql-sum-serial(numeric-multiply($$139, numeric-subtract(1, $$140)))] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- AGGREGATE |LOCAL| nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- NESTED_TUPLE_SOURCE |LOCAL| - } [cardinality: 25.0, doc-size: 45.0, op-cost: 0.0, total-cost: 6703.4] + } [cardinality: 24.0, doc-size: 45.0, op-cost: 0.0, total-cost: 6703.4] -- EXTERNAL_GROUP_BY[$$120] |PARTITIONED| exchange [cardinality: 38.48, doc-size: 45.0, op-cost: 0.0, total-cost: 6664.92] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/single-collection-queries/single-collection-queries.3.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/single-collection-queries/single-collection-queries.3.plan index 316bc6c890..c6c8f6f3b4 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/single-collection-queries/single-collection-queries.3.plan +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/single-collection-queries/single-collection-queries.3.plan @@ -1,28 +1,28 @@ -distribute result [$$48] [cardinality: 100.0, doc-size: 0.0, op-cost: 0.0, total-cost: 4495.77] +distribute result [$$48] [cardinality: 101.0, doc-size: 0.0, op-cost: 0.0, total-cost: 4495.77] -- DISTRIBUTE_RESULT |PARTITIONED| - exchange [cardinality: 100.0, doc-size: 0.0, op-cost: 0.0, total-cost: 4495.77] + exchange [cardinality: 101.0, doc-size: 0.0, op-cost: 0.0, total-cost: 4495.77] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - assign [$$48] <- [{"$1": $$51}] project: [$$48] [cardinality: 100.0, doc-size: 0.0, op-cost: 0.0, total-cost: 4495.77] + assign [$$48] <- [{"$1": $$51}] project: [$$48] [cardinality: 101.0, doc-size: 0.0, op-cost: 0.0, total-cost: 4495.77] -- ASSIGN |PARTITIONED| - project ([$$51]) [cardinality: 100.0, doc-size: 0.0, op-cost: 0.0, total-cost: 4495.77] + project ([$$51]) [cardinality: 101.0, doc-size: 0.0, op-cost: 0.0, total-cost: 4495.77] -- STREAM_PROJECT |PARTITIONED| - exchange [cardinality: 100.0, doc-size: 0.0, op-cost: 0.0, total-cost: 4495.77] + exchange [cardinality: 101.0, doc-size: 0.0, op-cost: 0.0, total-cost: 4495.77] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| group by ([$$o_custkey := $$54]) decor ([]) { aggregate [$$51] <- [sql-sum-serial($$53)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- AGGREGATE |LOCAL| nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- NESTED_TUPLE_SOURCE |LOCAL| - } [cardinality: 100.0, doc-size: 0.0, op-cost: 0.0, total-cost: 4495.77] + } [cardinality: 101.0, doc-size: 0.0, op-cost: 0.0, total-cost: 4495.77] -- EXTERNAL_GROUP_BY[$$54] |PARTITIONED| - exchange [cardinality: 100.0, doc-size: 0.0, op-cost: 0.0, total-cost: 2997.18] + exchange [cardinality: 101.0, doc-size: 0.0, op-cost: 0.0, total-cost: 2997.18] -- HASH_PARTITION_EXCHANGE [$$54] |PARTITIONED| group by ([$$54 := $$49]) decor ([]) { aggregate [$$53] <- [sql-count-serial(1)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- AGGREGATE |LOCAL| nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- NESTED_TUPLE_SOURCE |LOCAL| - } [cardinality: 100.0, doc-size: 0.0, op-cost: 0.0, total-cost: 2997.18] + } [cardinality: 101.0, doc-size: 0.0, op-cost: 0.0, total-cost: 2997.18] -- EXTERNAL_GROUP_BY[$$49] |PARTITIONED| exchange [cardinality: 1498.59, doc-size: 0.0, op-cost: 0.0, total-cost: 1498.59] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/single-collection-queries/single-collection-queries.5.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/single-collection-queries/single-collection-queries.5.plan index 125fec0a30..a285095c31 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/single-collection-queries/single-collection-queries.5.plan +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/single-collection-queries/single-collection-queries.5.plan @@ -1,28 +1,28 @@ -distribute result [$$51] [cardinality: 1298.0, doc-size: 0.0, op-cost: 0.0, total-cost: 15506.8] +distribute result [$$51] [cardinality: 887.0, doc-size: 0.0, op-cost: 0.0, total-cost: 15506.8] -- DISTRIBUTE_RESULT |PARTITIONED| - exchange [cardinality: 1298.0, doc-size: 0.0, op-cost: 0.0, total-cost: 15506.8] + exchange [cardinality: 887.0, doc-size: 0.0, op-cost: 0.0, total-cost: 15506.8] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - assign [$$51] <- [{"$1": $$55}] project: [$$51] [cardinality: 1298.0, doc-size: 0.0, op-cost: 0.0, total-cost: 15506.8] + assign [$$51] <- [{"$1": $$55}] project: [$$51] [cardinality: 887.0, doc-size: 0.0, op-cost: 0.0, total-cost: 15506.8] -- ASSIGN |PARTITIONED| - project ([$$55]) [cardinality: 1298.0, doc-size: 0.0, op-cost: 0.0, total-cost: 15506.8] + project ([$$55]) [cardinality: 887.0, doc-size: 0.0, op-cost: 0.0, total-cost: 15506.8] -- STREAM_PROJECT |PARTITIONED| - exchange [cardinality: 1298.0, doc-size: 0.0, op-cost: 0.0, total-cost: 15506.8] + exchange [cardinality: 887.0, doc-size: 0.0, op-cost: 0.0, total-cost: 15506.8] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| group by ([$$l_orderkey := $$59]) decor ([]) { aggregate [$$55] <- [sql-sum-serial($$58)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- AGGREGATE |LOCAL| nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- NESTED_TUPLE_SOURCE |LOCAL| - } [cardinality: 1298.0, doc-size: 0.0, op-cost: 0.0, total-cost: 15506.8] + } [cardinality: 887.0, doc-size: 0.0, op-cost: 0.0, total-cost: 15506.8] -- EXTERNAL_GROUP_BY[$$59] |PARTITIONED| - exchange [cardinality: 1298.0, doc-size: 0.0, op-cost: 0.0, total-cost: 10755.9] + exchange [cardinality: 887.0, doc-size: 0.0, op-cost: 0.0, total-cost: 10755.9] -- HASH_PARTITION_EXCHANGE [$$59] |PARTITIONED| group by ([$$59 := $$53]) decor ([]) { aggregate [$$58] <- [sql-count-serial(1)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- AGGREGATE |LOCAL| nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- NESTED_TUPLE_SOURCE |LOCAL| - } [cardinality: 1298.0, doc-size: 0.0, op-cost: 0.0, total-cost: 10755.9] + } [cardinality: 887.0, doc-size: 0.0, op-cost: 0.0, total-cost: 10755.9] -- EXTERNAL_GROUP_BY[$$53] |PARTITIONED| exchange [cardinality: 4750.9, doc-size: 0.0, op-cost: 0.0, total-cost: 6005.0] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/single-collection-queries/single-collection-queries.6.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/single-collection-queries/single-collection-queries.6.plan index 3abeddd130..b736105341 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/single-collection-queries/single-collection-queries.6.plan +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/single-collection-queries/single-collection-queries.6.plan @@ -1,28 +1,28 @@ -distribute result [$$51] [cardinality: 1543.0, doc-size: 0.0, op-cost: 0.0, total-cost: 13427.92] +distribute result [$$51] [cardinality: 673.0, doc-size: 0.0, op-cost: 0.0, total-cost: 13427.92] -- DISTRIBUTE_RESULT |PARTITIONED| - exchange [cardinality: 1543.0, doc-size: 0.0, op-cost: 0.0, total-cost: 13427.92] + exchange [cardinality: 673.0, doc-size: 0.0, op-cost: 0.0, total-cost: 13427.92] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - assign [$$51] <- [{"$1": $$55}] project: [$$51] [cardinality: 1543.0, doc-size: 0.0, op-cost: 0.0, total-cost: 13427.92] + assign [$$51] <- [{"$1": $$55}] project: [$$51] [cardinality: 673.0, doc-size: 0.0, op-cost: 0.0, total-cost: 13427.92] -- ASSIGN |PARTITIONED| - project ([$$55]) [cardinality: 1543.0, doc-size: 0.0, op-cost: 0.0, total-cost: 13427.92] + project ([$$55]) [cardinality: 673.0, doc-size: 0.0, op-cost: 0.0, total-cost: 13427.92] -- STREAM_PROJECT |PARTITIONED| - exchange [cardinality: 1543.0, doc-size: 0.0, op-cost: 0.0, total-cost: 13427.92] + exchange [cardinality: 673.0, doc-size: 0.0, op-cost: 0.0, total-cost: 13427.92] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| group by ([$$l_orderkey := $$59]) decor ([]) { aggregate [$$55] <- [sql-sum-serial($$58)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- AGGREGATE |LOCAL| nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- NESTED_TUPLE_SOURCE |LOCAL| - } [cardinality: 1543.0, doc-size: 0.0, op-cost: 0.0, total-cost: 13427.92] + } [cardinality: 673.0, doc-size: 0.0, op-cost: 0.0, total-cost: 13427.92] -- EXTERNAL_GROUP_BY[$$59] |PARTITIONED| - exchange [cardinality: 1543.0, doc-size: 0.0, op-cost: 0.0, total-cost: 9716.46] + exchange [cardinality: 673.0, doc-size: 0.0, op-cost: 0.0, total-cost: 9716.46] -- HASH_PARTITION_EXCHANGE [$$59] |PARTITIONED| group by ([$$59 := $$53]) decor ([]) { aggregate [$$58] <- [sql-count-serial(1)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- AGGREGATE |LOCAL| nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- NESTED_TUPLE_SOURCE |LOCAL| - } [cardinality: 1543.0, doc-size: 0.0, op-cost: 0.0, total-cost: 9716.46] + } [cardinality: 673.0, doc-size: 0.0, op-cost: 0.0, total-cost: 9716.46] -- EXTERNAL_GROUP_BY[$$53] |PARTITIONED| exchange [cardinality: 3711.46, doc-size: 0.0, op-cost: 0.0, total-cost: 6005.0] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/single-collection-queries/single-collection-queries.7.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/single-collection-queries/single-collection-queries.7.plan index b58d08d0b4..7ec6db3b3c 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/single-collection-queries/single-collection-queries.7.plan +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/cardinality-estimation/single-collection-queries/single-collection-queries.7.plan @@ -1,28 +1,28 @@ -distribute result [$$51] [cardinality: 191.0, doc-size: 0.0, op-cost: 0.0, total-cost: 3965.67] +distribute result [$$51] [cardinality: 136.0, doc-size: 0.0, op-cost: 0.0, total-cost: 3965.67] -- DISTRIBUTE_RESULT |PARTITIONED| - exchange [cardinality: 191.0, doc-size: 0.0, op-cost: 0.0, total-cost: 3965.67] + exchange [cardinality: 136.0, doc-size: 0.0, op-cost: 0.0, total-cost: 3965.67] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - assign [$$51] <- [{"$1": $$55}] project: [$$51] [cardinality: 191.0, doc-size: 0.0, op-cost: 0.0, total-cost: 3965.67] + assign [$$51] <- [{"$1": $$55}] project: [$$51] [cardinality: 136.0, doc-size: 0.0, op-cost: 0.0, total-cost: 3965.67] -- ASSIGN |PARTITIONED| - project ([$$55]) [cardinality: 191.0, doc-size: 0.0, op-cost: 0.0, total-cost: 3965.67] + project ([$$55]) [cardinality: 136.0, doc-size: 0.0, op-cost: 0.0, total-cost: 3965.67] -- STREAM_PROJECT |PARTITIONED| - exchange [cardinality: 191.0, doc-size: 0.0, op-cost: 0.0, total-cost: 3965.67] + exchange [cardinality: 136.0, doc-size: 0.0, op-cost: 0.0, total-cost: 3965.67] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| group by ([$$l_partkey := $$59]) decor ([]) { aggregate [$$55] <- [sql-sum-serial($$58)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- AGGREGATE |LOCAL| nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- NESTED_TUPLE_SOURCE |LOCAL| - } [cardinality: 191.0, doc-size: 0.0, op-cost: 0.0, total-cost: 3965.67] + } [cardinality: 136.0, doc-size: 0.0, op-cost: 0.0, total-cost: 3965.67] -- EXTERNAL_GROUP_BY[$$59] |PARTITIONED| - exchange [cardinality: 191.0, doc-size: 0.0, op-cost: 0.0, total-cost: 2643.78] + exchange [cardinality: 136.0, doc-size: 0.0, op-cost: 0.0, total-cost: 2643.78] -- HASH_PARTITION_EXCHANGE [$$59] |PARTITIONED| group by ([$$59 := $$52]) decor ([]) { aggregate [$$58] <- [sql-count-serial(1)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- AGGREGATE |LOCAL| nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- NESTED_TUPLE_SOURCE |LOCAL| - } [cardinality: 191.0, doc-size: 0.0, op-cost: 0.0, total-cost: 2643.78] + } [cardinality: 136.0, doc-size: 0.0, op-cost: 0.0, total-cost: 2643.78] -- EXTERNAL_GROUP_BY[$$52] |PARTITIONED| exchange [cardinality: 1321.89, doc-size: 0.0, op-cost: 0.0, total-cost: 1321.89] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.03.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.03.plan index 2f3fe45140..8fe8adb6e4 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.03.plan +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.03.plan @@ -1,30 +1,30 @@ -distribute result [$$134] [cardinality: 4.0, doc-size: 0.0, op-cost: 0.0, total-cost: 1794944.26] +distribute result [$$134] [cardinality: 1.0, doc-size: 0.0, op-cost: 0.0, total-cost: 1794936.26] -- DISTRIBUTE_RESULT |PARTITIONED| - exchange [cardinality: 4.0, doc-size: 0.0, op-cost: 0.0, total-cost: 1794944.26] + exchange [cardinality: 1.0, doc-size: 0.0, op-cost: 0.0, total-cost: 1794936.26] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - assign [$$134] <- [{"$1": 1, "l_returnflag": $$l_returnflag, "l_linestatus": $$l_linestatus, "sum_qty": $$140, "sum_base_price": $$141, "sum_disc_price": $$142, "sum_charge": $$143, "avg_qty": $$144, "avg_price": $$145, "avg_disc": $$146, "count_order": $$147}] project: [$$134] [cardinality: 4.0, doc-size: 0.0, op-cost: 0.0, total-cost: 1794944.26] + assign [$$134] <- [{"$1": 1, "l_returnflag": $$l_returnflag, "l_linestatus": $$l_linestatus, "sum_qty": $$140, "sum_base_price": $$141, "sum_disc_price": $$142, "sum_charge": $$143, "avg_qty": $$144, "avg_price": $$145, "avg_disc": $$146, "count_order": $$147}] project: [$$134] [cardinality: 1.0, doc-size: 0.0, op-cost: 0.0, total-cost: 1794936.26] -- ASSIGN |PARTITIONED| - exchange [cardinality: 4.0, doc-size: 0.0, op-cost: 0.0, total-cost: 1794944.26] + exchange [cardinality: 1.0, doc-size: 0.0, op-cost: 0.0, total-cost: 1794936.26] -- SORT_MERGE_EXCHANGE [$$l_returnflag(ASC), $$l_linestatus(ASC) ] |PARTITIONED| - order (ASC, $$l_returnflag) (ASC, $$l_linestatus) [cardinality: 4.0, doc-size: 0.0, op-cost: 0.0, total-cost: 1794944.26] + order (ASC, $$l_returnflag) (ASC, $$l_linestatus) [cardinality: 1.0, doc-size: 0.0, op-cost: 0.0, total-cost: 1794936.26] -- STABLE_SORT [$$l_returnflag(ASC), $$l_linestatus(ASC)] |PARTITIONED| - exchange [cardinality: 4.0, doc-size: 0.0, op-cost: 0.0, total-cost: 1794936.26] + exchange [cardinality: 1.0, doc-size: 0.0, op-cost: 0.0, total-cost: 1794936.26] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - group by ([$$l_returnflag := $$160; $$l_linestatus := $$161]) decor ([]) { - aggregate [$$140, $$141, $$142, $$143, $$144, $$145, $$146, $$147] <- [global-sql-sum-serial($$152), global-sql-sum-serial($$153), global-sql-sum-serial($$154), global-sql-sum-serial($$155), global-sql-avg-serial($$156), global-sql-avg-serial($$157), global-sql-avg-serial($$158), sql-sum-serial($$159)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] + group by ([$$l_returnflag := $$175; $$l_linestatus := $$176]) decor ([]) { + aggregate [$$140, $$141, $$142, $$143, $$144, $$145, $$146, $$147] <- [global-sql-sum-serial($$167), global-sql-sum-serial($$168), global-sql-sum-serial($$169), global-sql-sum-serial($$170), global-sql-avg-serial($$171), global-sql-avg-serial($$172), global-sql-avg-serial($$173), sql-sum-serial($$174)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- AGGREGATE |LOCAL| nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- NESTED_TUPLE_SOURCE |LOCAL| - } [cardinality: 4.0, doc-size: 0.0, op-cost: 0.0, total-cost: 1794936.26] - -- EXTERNAL_GROUP_BY[$$160, $$161] |PARTITIONED| - exchange [cardinality: 4.0, doc-size: 0.0, op-cost: 0.0, total-cost: 1197754.13] - -- HASH_PARTITION_EXCHANGE [$$160, $$161] |PARTITIONED| - group by ([$$160 := $$135; $$161 := $$136]) decor ([]) { - aggregate [$$152, $$153, $$154, $$155, $$156, $$157, $$158, $$159] <- [local-sql-sum-serial($$88), local-sql-sum-serial($$93), local-sql-sum-serial(numeric-multiply($$93, numeric-subtract(1, $$149))), local-sql-sum-serial(numeric-multiply(numeric-multiply($$93, numeric-subtract(1, $$149)), numeric-add(1, $$151))), local-sql-avg-serial($$88), local-sql-avg-serial($$93), local-sql-avg-serial($$149), sql-count-serial(1)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0 [...] + } [cardinality: 1.0, doc-size: 0.0, op-cost: 0.0, total-cost: 1794936.26] + -- EXTERNAL_GROUP_BY[$$175, $$176] |PARTITIONED| + exchange [cardinality: 1.0, doc-size: 0.0, op-cost: 0.0, total-cost: 1197754.13] + -- HASH_PARTITION_EXCHANGE [$$175, $$176] |PARTITIONED| + group by ([$$175 := $$135; $$176 := $$136]) decor ([]) { + aggregate [$$167, $$168, $$169, $$170, $$171, $$172, $$173, $$174] <- [local-sql-sum-serial($$88), local-sql-sum-serial($$93), local-sql-sum-serial(numeric-multiply($$93, numeric-subtract(1, $$149))), local-sql-sum-serial(numeric-multiply(numeric-multiply($$93, numeric-subtract(1, $$149)), numeric-add(1, $$151))), local-sql-avg-serial($$88), local-sql-avg-serial($$93), local-sql-avg-serial($$149), sql-count-serial(1)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0 [...] -- AGGREGATE |LOCAL| nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- NESTED_TUPLE_SOURCE |LOCAL| - } [cardinality: 4.0, doc-size: 0.0, op-cost: 0.0, total-cost: 1197754.13] + } [cardinality: 1.0, doc-size: 0.0, op-cost: 0.0, total-cost: 1197754.13] -- EXTERNAL_GROUP_BY[$$135, $$136] |PARTITIONED| exchange [cardinality: 597182.13, doc-size: 0.0, op-cost: 0.0, total-cost: 600572.0] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.05.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.05.plan index 225fdf9fec..fb870146d0 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.05.plan +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.05.plan @@ -1,36 +1,36 @@ -distribute result [$$122] [cardinality: 30575.82, doc-size: 20.0, op-cost: 0.0, total-cost: 1353243.22] +distribute result [$$122] [cardinality: 178.87, doc-size: 20.0, op-cost: 0.0, total-cost: 1353243.22] -- DISTRIBUTE_RESULT |UNPARTITIONED| - exchange [cardinality: 30575.82, doc-size: 20.0, op-cost: 0.0, total-cost: 1353243.22] + exchange [cardinality: 178.87, doc-size: 20.0, op-cost: 0.0, total-cost: 1353243.22] -- ONE_TO_ONE_EXCHANGE |UNPARTITIONED| - limit 10 [cardinality: 30575.82, doc-size: 20.0, op-cost: 0.0, total-cost: 1353243.22] + limit 10 [cardinality: 178.87, doc-size: 20.0, op-cost: 0.0, total-cost: 1353243.22] -- STREAM_LIMIT |UNPARTITIONED| - assign [$$122] <- [{"$1": 3, "l_orderkey": $$l_orderkey, "revenue": $$134, "o_orderdate": $$o_orderdate, "o_shippriority": $$o_shippriority}] project: [$$122] [cardinality: 30575.82, doc-size: 20.0, op-cost: 0.0, total-cost: 1353243.22] + assign [$$122] <- [{"$1": 3, "l_orderkey": $$l_orderkey, "revenue": $$134, "o_orderdate": $$o_orderdate, "o_shippriority": $$o_shippriority}] project: [$$122] [cardinality: 178.87, doc-size: 20.0, op-cost: 0.0, total-cost: 1353243.22] -- ASSIGN |PARTITIONED| - exchange [cardinality: 30575.82, doc-size: 20.0, op-cost: 0.0, total-cost: 1353243.22] + exchange [cardinality: 178.87, doc-size: 20.0, op-cost: 0.0, total-cost: 1353243.22] -- SORT_MERGE_EXCHANGE [$$134(DESC), $$o_orderdate(ASC) ] |PARTITIONED| - limit 10 [cardinality: 30575.82, doc-size: 20.0, op-cost: 0.0, total-cost: 1353243.22] + limit 10 [cardinality: 178.87, doc-size: 20.0, op-cost: 0.0, total-cost: 1353243.22] -- STREAM_LIMIT |PARTITIONED| - exchange [cardinality: 30575.82, doc-size: 20.0, op-cost: 0.0, total-cost: 1353243.22] + exchange [cardinality: 178.87, doc-size: 20.0, op-cost: 0.0, total-cost: 1353243.22] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - order (topK: 10) (DESC, $$134) (ASC, $$o_orderdate) [cardinality: 30575.82, doc-size: 20.0, op-cost: 0.0, total-cost: 1353243.22] + order (topK: 10) (DESC, $$134) (ASC, $$o_orderdate) [cardinality: 178.87, doc-size: 20.0, op-cost: 0.0, total-cost: 1353243.22] -- STABLE_SORT [topK: 10] [$$134(DESC), $$o_orderdate(ASC)] |PARTITIONED| - exchange [cardinality: 30575.82, doc-size: 20.0, op-cost: 0.0, total-cost: 1353243.22] + exchange [cardinality: 178.87, doc-size: 20.0, op-cost: 0.0, total-cost: 1353243.22] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| group by ([$$l_orderkey := $$142; $$o_orderdate := $$143; $$o_shippriority := $$144]) decor ([]) { aggregate [$$134] <- [global-sql-sum-serial($$141)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- AGGREGATE |LOCAL| nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- NESTED_TUPLE_SOURCE |LOCAL| - } [cardinality: 30575.82, doc-size: 20.0, op-cost: 0.0, total-cost: 1353243.22] + } [cardinality: 178.87, doc-size: 20.0, op-cost: 0.0, total-cost: 1353243.22] -- EXTERNAL_GROUP_BY[$$142, $$143, $$144] |PARTITIONED| - exchange [cardinality: 30575.82, doc-size: 20.0, op-cost: 0.0, total-cost: 1322667.4] + exchange [cardinality: 178.87, doc-size: 20.0, op-cost: 0.0, total-cost: 1322667.4] -- HASH_PARTITION_EXCHANGE [$$142, $$143, $$144] |PARTITIONED| group by ([$$142 := $$131; $$143 := $$127; $$144 := $$125]) decor ([]) { aggregate [$$141] <- [local-sql-sum-serial(numeric-multiply($$139, numeric-subtract(1, $$140)))] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- AGGREGATE |LOCAL| nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- NESTED_TUPLE_SOURCE |LOCAL| - } [cardinality: 30575.82, doc-size: 20.0, op-cost: 0.0, total-cost: 1322667.4] + } [cardinality: 178.87, doc-size: 20.0, op-cost: 0.0, total-cost: 1322667.4] -- EXTERNAL_GROUP_BY[$$131, $$127, $$125] |PARTITIONED| exchange [cardinality: 30575.82, doc-size: 20.0, op-cost: 0.0, total-cost: 1292091.58] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.06.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.06.plan index 47182232d2..a7238940b8 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.06.plan +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.06.plan @@ -1,43 +1,43 @@ -distribute result [$$74] [cardinality: 997.0, doc-size: 5.0, op-cost: 0.0, total-cost: 610643.36] +distribute result [$$74] [cardinality: 45.0, doc-size: 5.0, op-cost: 0.0, total-cost: 610643.36] -- DISTRIBUTE_RESULT |PARTITIONED| - exchange [cardinality: 997.0, doc-size: 5.0, op-cost: 0.0, total-cost: 610643.36] + exchange [cardinality: 45.0, doc-size: 5.0, op-cost: 0.0, total-cost: 610643.36] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - assign [$$74] <- [{"$1": 4, "o_orderpriority": $$o_orderpriority, "order_count": $$81}] project: [$$74] [cardinality: 997.0, doc-size: 5.0, op-cost: 0.0, total-cost: 610643.36] + assign [$$74] <- [{"$1": 4, "o_orderpriority": $$o_orderpriority, "order_count": $$81}] project: [$$74] [cardinality: 45.0, doc-size: 5.0, op-cost: 0.0, total-cost: 610643.36] -- ASSIGN |PARTITIONED| - exchange [cardinality: 997.0, doc-size: 5.0, op-cost: 0.0, total-cost: 610643.36] + exchange [cardinality: 45.0, doc-size: 5.0, op-cost: 0.0, total-cost: 610643.36] -- SORT_MERGE_EXCHANGE [$$o_orderpriority(ASC) ] |PARTITIONED| group by ([$$o_orderpriority := $$91]) decor ([]) { aggregate [$$81] <- [agg-sql-sum($$90)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- AGGREGATE |LOCAL| nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- NESTED_TUPLE_SOURCE |LOCAL| - } [cardinality: 997.0, doc-size: 5.0, op-cost: 0.0, total-cost: 610643.36] + } [cardinality: 45.0, doc-size: 5.0, op-cost: 0.0, total-cost: 610643.36] -- SORT_GROUP_BY[$$91] |PARTITIONED| - exchange [cardinality: 997.0, doc-size: 5.0, op-cost: 0.0, total-cost: 610643.36] + exchange [cardinality: 45.0, doc-size: 5.0, op-cost: 0.0, total-cost: 610643.36] -- HASH_PARTITION_EXCHANGE [$$91] |PARTITIONED| group by ([$$91 := $$75]) decor ([]) { aggregate [$$90] <- [agg-sql-count(1)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- AGGREGATE |LOCAL| nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- NESTED_TUPLE_SOURCE |LOCAL| - } [cardinality: 997.0, doc-size: 5.0, op-cost: 0.0, total-cost: 610643.36] + } [cardinality: 45.0, doc-size: 5.0, op-cost: 0.0, total-cost: 610643.36] -- SORT_GROUP_BY[$$75] |PARTITIONED| - exchange [cardinality: 997.0, doc-size: 5.0, op-cost: 0.0, total-cost: 610643.36] + exchange [cardinality: 45.0, doc-size: 5.0, op-cost: 0.0, total-cost: 610643.36] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - select (neq($$80, 0)) project: [$$75] [cardinality: 997.0, doc-size: 5.0, op-cost: 0.0, total-cost: 610643.36] + select (neq($$80, 0)) project: [$$75] [cardinality: 45.0, doc-size: 5.0, op-cost: 0.0, total-cost: 610643.36] -- STREAM_SELECT |PARTITIONED| - project ([$$80, $$75]) [cardinality: 997.0, doc-size: 5.0, op-cost: 0.0, total-cost: 610643.36] + project ([$$80, $$75]) [cardinality: 45.0, doc-size: 5.0, op-cost: 0.0, total-cost: 610643.36] -- STREAM_PROJECT |PARTITIONED| - exchange [cardinality: 997.0, doc-size: 5.0, op-cost: 0.0, total-cost: 610643.36] + exchange [cardinality: 45.0, doc-size: 5.0, op-cost: 0.0, total-cost: 610643.36] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| group by ([$$87 := $$89]) decor ([$$75]) { aggregate [$$80] <- [sum-serial($$88)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- AGGREGATE |LOCAL| nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- NESTED_TUPLE_SOURCE |LOCAL| - } [cardinality: 997.0, doc-size: 5.0, op-cost: 0.0, total-cost: 610643.36] + } [cardinality: 45.0, doc-size: 5.0, op-cost: 0.0, total-cost: 610643.36] -- EXTERNAL_GROUP_BY[$$89] |PARTITIONED| - exchange [cardinality: 997.0, doc-size: 5.0, op-cost: 0.0, total-cost: 594714.46] + exchange [cardinality: 45.0, doc-size: 5.0, op-cost: 0.0, total-cost: 594714.46] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| group by ([$$89 := $$77]) decor ([$$75]) { aggregate [$$88] <- [agg-count({"l": $$l})] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] @@ -48,7 +48,7 @@ distribute result [$$74] [cardinality: 997.0, doc-size: 5.0, op-cost: 0.0, total -- STREAM_PROJECT |LOCAL| nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- NESTED_TUPLE_SOURCE |LOCAL| - } [cardinality: 997.0, doc-size: 5.0, op-cost: 0.0, total-cost: 594714.46] + } [cardinality: 45.0, doc-size: 5.0, op-cost: 0.0, total-cost: 594714.46] -- PRE_CLUSTERED_GROUP_BY[$$77] |PARTITIONED| exchange [cardinality: 6349.95, doc-size: 5.0, op-cost: 0.0, total-cost: 372357.23] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.07.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.07.plan index e281064974..cc1b04e62b 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.07.plan +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.07.plan @@ -1,30 +1,30 @@ -distribute result [$$149] [cardinality: 25.0, doc-size: 55.0, op-cost: 0.0, total-cost: 1554876.05] +distribute result [$$149] [cardinality: 24.0, doc-size: 55.0, op-cost: 0.0, total-cost: 1554869.99] -- DISTRIBUTE_RESULT |PARTITIONED| - exchange [cardinality: 25.0, doc-size: 55.0, op-cost: 0.0, total-cost: 1554876.05] + exchange [cardinality: 24.0, doc-size: 55.0, op-cost: 0.0, total-cost: 1554869.99] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - assign [$$149] <- [{"$1": 5, "n_name": $$n_name, "revenue": $$165}] project: [$$149] [cardinality: 25.0, doc-size: 55.0, op-cost: 0.0, total-cost: 1554876.05] + assign [$$149] <- [{"$1": 5, "n_name": $$n_name, "revenue": $$165}] project: [$$149] [cardinality: 24.0, doc-size: 55.0, op-cost: 0.0, total-cost: 1554869.99] -- ASSIGN |PARTITIONED| - exchange [cardinality: 25.0, doc-size: 55.0, op-cost: 0.0, total-cost: 1554876.05] + exchange [cardinality: 24.0, doc-size: 55.0, op-cost: 0.0, total-cost: 1554869.99] -- SORT_MERGE_EXCHANGE [$$165(DESC) ] |PARTITIONED| - order (DESC, $$165) [cardinality: 25.0, doc-size: 55.0, op-cost: 0.0, total-cost: 1554876.05] + order (DESC, $$165) [cardinality: 24.0, doc-size: 55.0, op-cost: 0.0, total-cost: 1554869.99] -- STABLE_SORT [$$165(DESC)] |PARTITIONED| - exchange [cardinality: 25.0, doc-size: 55.0, op-cost: 0.0, total-cost: 1554759.95] + exchange [cardinality: 24.0, doc-size: 55.0, op-cost: 0.0, total-cost: 1554759.95] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| group by ([$$n_name := $$176]) decor ([]) { aggregate [$$165] <- [global-sql-sum-serial($$175)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- AGGREGATE |LOCAL| nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- NESTED_TUPLE_SOURCE |LOCAL| - } [cardinality: 25.0, doc-size: 55.0, op-cost: 0.0, total-cost: 1554759.95] + } [cardinality: 24.0, doc-size: 55.0, op-cost: 0.0, total-cost: 1554759.95] -- EXTERNAL_GROUP_BY[$$176] |PARTITIONED| - exchange [cardinality: 25.0, doc-size: 55.0, op-cost: 0.0, total-cost: 1521015.48] + exchange [cardinality: 24.0, doc-size: 55.0, op-cost: 0.0, total-cost: 1521015.48] -- HASH_PARTITION_EXCHANGE [$$176] |PARTITIONED| group by ([$$176 := $$150]) decor ([]) { aggregate [$$175] <- [local-sql-sum-serial(numeric-multiply($$173, numeric-subtract(1, $$174)))] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- AGGREGATE |LOCAL| nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- NESTED_TUPLE_SOURCE |LOCAL| - } [cardinality: 25.0, doc-size: 55.0, op-cost: 0.0, total-cost: 1521015.48] + } [cardinality: 24.0, doc-size: 55.0, op-cost: 0.0, total-cost: 1521015.48] -- EXTERNAL_GROUP_BY[$$150] |PARTITIONED| exchange [cardinality: 33744.47, doc-size: 55.0, op-cost: 0.0, total-cost: 1487271.01] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.08.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.08.plan index 6fa60d8d0a..55d4b57cb1 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.08.plan +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.08.plan @@ -1,30 +1,30 @@ -distribute result [$$186] [cardinality: 2.52, doc-size: 68.16, op-cost: 0.0, total-cost: 986821.79] +distribute result [$$186] [cardinality: 2.43, doc-size: 68.16, op-cost: 0.0, total-cost: 986821.54] -- DISTRIBUTE_RESULT |PARTITIONED| - exchange [cardinality: 2.52, doc-size: 68.16, op-cost: 0.0, total-cost: 986821.79] + exchange [cardinality: 2.43, doc-size: 68.16, op-cost: 0.0, total-cost: 986821.54] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - assign [$$186] <- [{"$1": 7, "supp_nation": $$supp_nation, "cust_nation": $$cust_nation, "l_year": $$l_year, "revenue": $$200}] project: [$$186] [cardinality: 2.52, doc-size: 68.16, op-cost: 0.0, total-cost: 986821.79] + assign [$$186] <- [{"$1": 7, "supp_nation": $$supp_nation, "cust_nation": $$cust_nation, "l_year": $$l_year, "revenue": $$200}] project: [$$186] [cardinality: 2.43, doc-size: 68.16, op-cost: 0.0, total-cost: 986821.54] -- ASSIGN |PARTITIONED| - exchange [cardinality: 2.52, doc-size: 68.16, op-cost: 0.0, total-cost: 986821.79] + exchange [cardinality: 2.43, doc-size: 68.16, op-cost: 0.0, total-cost: 986821.54] -- SORT_MERGE_EXCHANGE [$$supp_nation(ASC), $$cust_nation(ASC), $$l_year(ASC) ] |PARTITIONED| - order (ASC, $$supp_nation) (ASC, $$cust_nation) (ASC, $$l_year) [cardinality: 2.52, doc-size: 68.16, op-cost: 0.0, total-cost: 986821.79] + order (ASC, $$supp_nation) (ASC, $$cust_nation) (ASC, $$l_year) [cardinality: 2.43, doc-size: 68.16, op-cost: 0.0, total-cost: 986821.54] -- STABLE_SORT [$$supp_nation(ASC), $$cust_nation(ASC), $$l_year(ASC)] |PARTITIONED| - exchange [cardinality: 2.52, doc-size: 68.16, op-cost: 0.0, total-cost: 986818.43] + exchange [cardinality: 2.43, doc-size: 68.16, op-cost: 0.0, total-cost: 986818.43] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| group by ([$$supp_nation := $$217; $$cust_nation := $$218; $$l_year := $$219]) decor ([]) { aggregate [$$200] <- [global-sql-sum-serial($$216)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- AGGREGATE |LOCAL| nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- NESTED_TUPLE_SOURCE |LOCAL| - } [cardinality: 2.52, doc-size: 68.16, op-cost: 0.0, total-cost: 986818.43] + } [cardinality: 2.43, doc-size: 68.16, op-cost: 0.0, total-cost: 986818.43] -- EXTERNAL_GROUP_BY[$$217, $$218, $$219] |PARTITIONED| - exchange [cardinality: 2.52, doc-size: 68.16, op-cost: 0.0, total-cost: 986248.58] + exchange [cardinality: 2.43, doc-size: 68.16, op-cost: 0.0, total-cost: 986248.58] -- HASH_PARTITION_EXCHANGE [$$217, $$218, $$219] |PARTITIONED| group by ([$$217 := $$191; $$218 := $$192; $$219 := $$214]) decor ([]) { aggregate [$$216] <- [local-sql-sum-serial($$184)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- AGGREGATE |LOCAL| nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- NESTED_TUPLE_SOURCE |LOCAL| - } [cardinality: 2.52, doc-size: 68.16, op-cost: 0.0, total-cost: 986248.58] + } [cardinality: 2.43, doc-size: 68.16, op-cost: 0.0, total-cost: 986248.58] -- EXTERNAL_GROUP_BY[$$191, $$192, $$214] |PARTITIONED| exchange [cardinality: 569.85, doc-size: 68.16, op-cost: 0.0, total-cost: 985678.73] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.09.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.09.plan index efb7af8a9b..3675874099 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.09.plan +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_cbo/tpch/query-plans/query-plans.09.plan @@ -1,26 +1,26 @@ -distribute result [$$199] [cardinality: 2.0, doc-size: 70.0, op-cost: 0.0, total-cost: 1258952.06] +distribute result [$$199] [cardinality: 1.0, doc-size: 70.0, op-cost: 0.0, total-cost: 1258952.06] -- DISTRIBUTE_RESULT |PARTITIONED| - exchange [cardinality: 2.0, doc-size: 70.0, op-cost: 0.0, total-cost: 1258952.06] + exchange [cardinality: 1.0, doc-size: 70.0, op-cost: 0.0, total-cost: 1258952.06] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| - assign [$$199] <- [{"$1": 8, "o_year": $$o_year, "mkt_share": numeric-divide($$214, $$215)}] project: [$$199] [cardinality: 2.0, doc-size: 70.0, op-cost: 0.0, total-cost: 1258952.06] + assign [$$199] <- [{"$1": 8, "o_year": $$o_year, "mkt_share": numeric-divide($$214, $$215)}] project: [$$199] [cardinality: 1.0, doc-size: 70.0, op-cost: 0.0, total-cost: 1258952.06] -- ASSIGN |PARTITIONED| - exchange [cardinality: 2.0, doc-size: 70.0, op-cost: 0.0, total-cost: 1258952.06] + exchange [cardinality: 1.0, doc-size: 70.0, op-cost: 0.0, total-cost: 1258952.06] -- ONE_TO_ONE_EXCHANGE |PARTITIONED| group by ([$$o_year := $$238]) decor ([]) { aggregate [$$214, $$215] <- [global-sql-sum-serial($$236), global-sql-sum-serial($$237)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- AGGREGATE |LOCAL| nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- NESTED_TUPLE_SOURCE |LOCAL| - } [cardinality: 2.0, doc-size: 70.0, op-cost: 0.0, total-cost: 1258952.06] + } [cardinality: 1.0, doc-size: 70.0, op-cost: 0.0, total-cost: 1258952.06] -- EXTERNAL_GROUP_BY[$$238] |PARTITIONED| - exchange [cardinality: 2.0, doc-size: 70.0, op-cost: 0.0, total-cost: 1258786.5] + exchange [cardinality: 1.0, doc-size: 70.0, op-cost: 0.0, total-cost: 1258786.5] -- HASH_PARTITION_EXCHANGE [$$238] |PARTITIONED| group by ([$$238 := $$200]) decor ([]) { aggregate [$$236, $$237] <- [local-sql-sum-serial(switch-case(true, eq($$232, "PERU"), numeric-multiply($$230, numeric-subtract(1, $$231)), 0)), local-sql-sum-serial(numeric-multiply($$230, numeric-subtract(1, $$231)))] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- AGGREGATE |LOCAL| nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0] -- NESTED_TUPLE_SOURCE |LOCAL| - } [cardinality: 2.0, doc-size: 70.0, op-cost: 0.0, total-cost: 1258786.5] + } [cardinality: 1.0, doc-size: 70.0, op-cost: 0.0, total-cost: 1258786.5] -- EXTERNAL_GROUP_BY[$$200] |PARTITIONED| exchange [cardinality: 165.56, doc-size: 70.0, op-cost: 0.0, total-cost: 1258620.94] -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
