From ae28e068bb5af59b2cecd29ddf4cd2cf9d87ca84 Mon Sep 17 00:00:00 2001 From: Andres Freund Date: Thu, 26 Sep 2019 14:58:10 -0700 Subject: [PATCH v2 6/8] WIP: explain: Show per-phase information about aggregates in verbose mode. Author: Reviewed-By: Discussion: https://postgr.es/m/ Backpatch: --- src/backend/commands/explain.c | 520 +++++++++++++----- src/backend/executor/execExpr.c | 7 +- src/backend/executor/nodeAgg.c | 4 +- src/include/executor/executor.h | 3 +- src/include/executor/nodeAgg.h | 3 + src/test/regress/expected/aggregates.out | 32 +- src/test/regress/expected/groupingsets.out | 329 ++++++----- src/test/regress/expected/inherit.out | 9 +- src/test/regress/expected/join.out | 5 +- src/test/regress/expected/limit.out | 6 +- .../regress/expected/partition_aggregate.out | 102 ++-- src/test/regress/expected/select_distinct.out | 8 +- src/test/regress/expected/select_parallel.out | 5 +- src/test/regress/expected/subselect.out | 5 +- 14 files changed, 679 insertions(+), 359 deletions(-) diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 02455865d9f..2f3bd8a459a 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -84,14 +84,6 @@ static void show_sort_keys(SortState *sortstate, List *ancestors, ExplainState *es); static void show_merge_append_keys(MergeAppendState *mstate, List *ancestors, ExplainState *es); -static void show_agg_keys(AggState *astate, List *ancestors, - ExplainState *es); -static void show_grouping_sets(PlanState *planstate, Agg *agg, - List *ancestors, ExplainState *es); -static void show_grouping_set_keys(PlanState *planstate, - Agg *aggnode, Sort *sortnode, - List *context, bool useprefix, - List *ancestors, ExplainState *es); static void show_group_keys(GroupState *gstate, List *ancestors, ExplainState *es); static void show_sort_group_keys(PlanState *planstate, const char *qlabel, @@ -103,6 +95,7 @@ static void show_sortorder_options(StringInfo buf, Node *sortexpr, static void show_tablesample(TableSampleClause *tsc, PlanState *planstate, List *ancestors, ExplainState *es); static void show_sort_info(SortState *sortstate, ExplainState *es); +static void show_agg_info(AggState *aggstate, List *ancestors, ExplainState *es); static void show_hash_info(HashState *hashstate, ExplainState *es); static void show_tidbitmap_info(BitmapHeapScanState *planstate, ExplainState *es); @@ -1872,12 +1865,12 @@ ExplainNode(PlanState *planstate, List *ancestors, planstate, es); break; case T_Agg: - show_agg_keys(castNode(AggState, planstate), ancestors, es); show_upper_qual(plan->qual, planstate->qual, "Filter", planstate, ancestors, es); if (plan->qual) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); + show_agg_info((AggState *) planstate, ancestors, es); break; case T_Group: show_group_keys(castNode(GroupState, planstate), ancestors, es); @@ -2430,138 +2423,6 @@ show_merge_append_keys(MergeAppendState *mstate, List *ancestors, ancestors, es); } -/* - * Show the grouping keys for an Agg node. - */ -static void -show_agg_keys(AggState *astate, List *ancestors, - ExplainState *es) -{ - Agg *plan = (Agg *) astate->ss.ps.plan; - - if (plan->numCols > 0 || plan->groupingSets) - { - /* The key columns refer to the tlist of the child plan */ - ancestors = lcons(astate, ancestors); - - if (plan->groupingSets) - show_grouping_sets(outerPlanState(astate), plan, ancestors, es); - else - show_sort_group_keys(outerPlanState(astate), "Group Key", - plan->numCols, plan->grpColIdx, - NULL, NULL, NULL, - ancestors, es); - - ancestors = list_delete_first(ancestors); - } -} - -static void -show_grouping_sets(PlanState *planstate, Agg *agg, - List *ancestors, ExplainState *es) -{ - List *context; - bool useprefix; - ListCell *lc; - - /* Set up deparsing context */ - context = set_deparse_context_planstate(es->deparse_cxt, - (Node *) planstate, - ancestors); - useprefix = (list_length(es->rtable) > 1 || es->verbose); - - ExplainOpenGroup("Grouping Sets", "Grouping Sets", false, es); - - show_grouping_set_keys(planstate, agg, NULL, - context, useprefix, ancestors, es); - - foreach(lc, agg->chain) - { - Agg *aggnode = lfirst(lc); - Sort *sortnode = (Sort *) aggnode->plan.lefttree; - - show_grouping_set_keys(planstate, aggnode, sortnode, - context, useprefix, ancestors, es); - } - - ExplainCloseGroup("Grouping Sets", "Grouping Sets", false, es); -} - -static void -show_grouping_set_keys(PlanState *planstate, - Agg *aggnode, Sort *sortnode, - List *context, bool useprefix, - List *ancestors, ExplainState *es) -{ - Plan *plan = planstate->plan; - char *exprstr; - ListCell *lc; - List *gsets = aggnode->groupingSets; - AttrNumber *keycols = aggnode->grpColIdx; - const char *keyname; - const char *keysetname; - - if (aggnode->aggstrategy == AGG_HASHED || aggnode->aggstrategy == AGG_MIXED) - { - keyname = "Hash Key"; - keysetname = "Hash Keys"; - } - else - { - keyname = "Group Key"; - keysetname = "Group Keys"; - } - - ExplainOpenGroup("Grouping Set", NULL, true, es); - - if (sortnode) - { - show_sort_group_keys(planstate, "Sort Key", - sortnode->numCols, sortnode->sortColIdx, - sortnode->sortOperators, sortnode->collations, - sortnode->nullsFirst, - ancestors, es); - if (es->format == EXPLAIN_FORMAT_TEXT) - es->indent++; - } - - ExplainOpenGroup(keysetname, keysetname, false, es); - - foreach(lc, gsets) - { - List *result = NIL; - ListCell *lc2; - - foreach(lc2, (List *) lfirst(lc)) - { - Index i = lfirst_int(lc2); - AttrNumber keyresno = keycols[i]; - TargetEntry *target = get_tle_by_resno(plan->targetlist, - keyresno); - - if (!target) - elog(ERROR, "no tlist entry for key %d", keyresno); - /* Deparse the expression, showing any top-level cast */ - exprstr = deparse_expression((Node *) target->expr, context, - useprefix, true); - - result = lappend(result, exprstr); - } - - if (!result && es->format == EXPLAIN_FORMAT_TEXT) - ExplainPropertyText(keyname, "()", es); - else - ExplainPropertyListNested(keyname, result, es); - } - - ExplainCloseGroup(keysetname, keysetname, false, es); - - if (sortnode && es->format == EXPLAIN_FORMAT_TEXT) - es->indent--; - - ExplainCloseGroup("Grouping Set", NULL, true, es); -} - /* * Show the grouping keys for a Group node. */ @@ -2845,6 +2706,383 @@ show_sort_info(SortState *sortstate, ExplainState *es) } } +/* + * Generate an expression like string describing the computations for a + * phase's transition / combiner function. + */ +static char * +exprstr_for_agg_phase(AggState *aggstate, AggStatePerPhase perphase, List *ancestors, ExplainState *es) +{ + PlanState *planstate = &aggstate->ss.ps; + StringInfoData transbuf; + List *context; + bool useprefix; + bool isCombine = DO_AGGSPLIT_COMBINE(aggstate->aggsplit); + ListCell *lc; + + initStringInfo(&transbuf); + + /* Set up deparsing context */ + context = set_deparse_context_planstate(es->deparse_cxt, + (Node *) planstate, + ancestors); + useprefix = list_length(es->rtable) > 1; + + for (int transno = 0; transno < aggstate->numtrans; transno++) + { + AggStatePerTrans pertrans = &aggstate->pertrans[transno]; + int count = 0; + bool first; + + if (perphase->uses_sorting) + count += Max(perphase->numsets, 1); + + if (perphase->uses_hashing) + count += aggstate->num_hashes; + + if (transno != 0) + appendStringInfoString(&transbuf, ", "); + + if (pertrans->aggref->aggfilter && !isCombine) + { + appendStringInfo(&transbuf, "FILTER (%s) && ", + deparse_expression((Node *) pertrans->aggref->aggfilter, + context, useprefix, false)); + } + + /* + * XXX: should we instead somehow encode this as separate elements in + * non-text mode? + */ + /* simplify for text output */ + if (count > 1 || es->format != EXPLAIN_FORMAT_TEXT) + appendStringInfo(&transbuf, "%d * ", count); + + appendStringInfo(&transbuf, "%s(TRANS", + get_func_name(pertrans->transfn_oid)); + + if (isCombine && pertrans->deserialfn_oid) + { + first = true; + appendStringInfo(&transbuf, ", %s(", + get_func_name(pertrans->deserialfn_oid)); + } + else + first = false; + + foreach(lc, pertrans->aggref->args) + { + TargetEntry *tle = lfirst(lc); + + if (!first) + appendStringInfoString(&transbuf, ", "); + + first = false; + appendStringInfo(&transbuf, "%s", + deparse_expression((Node *) tle->expr, + context, useprefix, false)); + } + + if (isCombine && pertrans->deserialfn_oid) + appendStringInfoString(&transbuf, ")"); + appendStringInfoString(&transbuf, ")"); + } + + return transbuf.data; +} + +static void +show_agg_group_info(AggState *aggstate, AttrNumber *keycols, int length, + ExprState *expr, const char *label, + List *context, List *ancestors, ExplainState *es) +{ + bool useprefix = (list_length(es->rtable) > 1 || es->verbose); + List *result = NIL; + + for (int colno = 0; colno < length; colno++) + { + char *exprstr; + AttrNumber keyresno = keycols[colno]; + TargetEntry *target = get_tle_by_resno(outerPlanState(aggstate)->plan->targetlist, + keyresno); + + if (!target) + elog(ERROR, "no tlist entry for key %d", keyresno); + /* Deparse the expression, showing any top-level cast */ + exprstr = deparse_expression((Node *) target->expr, context, + useprefix, true); + + result = lappend(result, exprstr); + } + + if (es->format == EXPLAIN_FORMAT_TEXT) + { + ListCell *lc; + bool first = true; + + appendStringInfoSpaces(es->str, es->indent * 2); + + if (result != NIL) + { + appendStringInfo(es->str, "%s: ", label); + + foreach(lc, result) + { + if (!first) + appendStringInfoString(es->str, ", "); + appendStringInfoString(es->str, (const char *) lfirst(lc)); + first = false; + } + } + else + appendStringInfo(es->str, "%s", label); + + if (expr && es->jit_details) + { + appendStringInfoString(es->str, "; "); + show_jit_expr_details(expr, es); + } + + appendStringInfoChar(es->str, '\n'); + } + else + { + ExplainOpenGroup("Group", NULL, true, es); + ExplainPropertyText("Method", label, es); + ExplainPropertyList("Key", result, es); + ExplainCloseGroup("Group", NULL, true, es); + } + +} + +/* + * Show information about Agg ndoes. + */ +static void +show_agg_info(AggState *aggstate, List *ancestors, ExplainState *es) +{ + Agg *plan = (Agg *) aggstate->ss.ps.plan; + + if (!plan->groupingSets && + (!es->verbose && !es->jit_details && es->format == EXPLAIN_FORMAT_TEXT)) + { + /* The key columns refer to the tlist of the child plan */ + ancestors = lcons(aggstate, ancestors); + show_sort_group_keys(outerPlanState(aggstate), "Group Key", + plan->numCols, plan->grpColIdx, + NULL, NULL, NULL, + ancestors, es); + ancestors = list_delete_first(ancestors); + + return; + } + + ExplainOpenGroup("Phases", "Phases", false, es); + + for (int phaseno = aggstate->numphases - 1; phaseno >= 0; phaseno--) + { + AggStatePerPhase perphase = &aggstate->phases[phaseno]; + Sort *sortnode = perphase->sortnode; + char *exprstr; + bool has_zero_length = false; + List *context; + List *strategy = NIL; + char *plain_strategy; + + if (!perphase->evaltrans) + continue; + + for (int i = 0; i < perphase->numsets; i++) + { + if (perphase->gset_lengths[i] == 0) + has_zero_length = true; + } + + switch (perphase->aggstrategy) + { + case AGG_PLAIN: + strategy = lappend(strategy, "All"); + + if (aggstate->aggstrategy == AGG_MIXED && phaseno == 1) + strategy = lappend(strategy, "Hash"); + plain_strategy = "All Group"; + break; + case AGG_SORTED: + if (!perphase->sortnode) + { + strategy = lappend(strategy, "Sorted Input"); + plain_strategy = "Sorted Input Group"; + } + else + { + strategy = lappend(strategy, "Sort"); + plain_strategy = "Sort Group"; + } + + if (has_zero_length) + strategy = lappend(strategy, "All"); + + if (aggstate->aggstrategy == AGG_MIXED && phaseno == 1) + strategy = lappend(strategy, "Hash"); + + break; + case AGG_HASHED: + strategy = lappend(strategy, "Hash"); + plain_strategy = "Hash Group"; + break; + case AGG_MIXED: + if (has_zero_length) + strategy = lappend(strategy, "All"); + strategy = lappend(strategy, "Hash"); + plain_strategy = "???"; + break; + } + + exprstr = exprstr_for_agg_phase(aggstate, perphase, ancestors, es); + + ExplainOpenGroup("Phase", NULL, true, es); + + /* The key columns refer to the tlist of the child plan */ + ancestors = lcons(aggstate, ancestors); + context = set_deparse_context_planstate(es->deparse_cxt, + (Node *) outerPlanState(aggstate), + ancestors); + + if (es->format == EXPLAIN_FORMAT_TEXT) + { + ListCell *lc; + bool first = true; + + /* output phase data */ + appendStringInfoSpaces(es->str, es->indent * 2); + appendStringInfo(es->str, "Phase %d using strategy \"", + phaseno); + + foreach(lc, strategy) + { + if (!first) + appendStringInfoString(es->str, " & "); + first = false; + appendStringInfoString(es->str, (const char *) lfirst(lc)); + } + appendStringInfoString(es->str, "\":\n"); + es->indent++; + } + else + { + ExplainPropertyInteger("Phase-Number", NULL, phaseno, es); + ExplainPropertyList("Strategy", strategy, es); + } + + if (sortnode) + { + show_sort_group_keys(outerPlanState(aggstate), "Sort Key", + sortnode->numCols, sortnode->sortColIdx, + sortnode->sortOperators, sortnode->collations, + sortnode->nullsFirst, + ancestors, es); + } + + if (es->format == EXPLAIN_FORMAT_TEXT) + { + if (aggstate->numtrans > 0) + { + appendStringInfoSpaces(es->str, es->indent * 2); + appendStringInfo(es->str, "Transition Function: %s", + exprstr); + if (es->jit_details) + { + appendStringInfoString(es->str, "; "); + show_jit_expr_details(perphase->evaltrans, es); + } + appendStringInfoString(es->str, "\n"); + } + } + else + { + if (es->jit_details) + { + ExplainOpenGroup("Transition Function", "Transition Function", true, es); + ExplainPropertyText("Expr", exprstr, es); + if (es->jit_details && aggstate->numtrans > 0) + show_jit_expr_details(perphase->evaltrans, es); + ExplainCloseGroup("Transition Function", "Transition Function", true, es); + } + else + ExplainPropertyText("Transition Function", exprstr, es); + } + + ExplainOpenGroup("Groups", "Groups", false, es); + + /* output data about each group */ + + if (perphase->uses_sorting) + { + if (perphase->numsets == 0) + { + int length = perphase->aggnode->numCols; + ExprState *expr = NULL; + + if (length > 0) + expr = perphase->eqfunctions[perphase->aggnode->numCols - 1]; + + show_agg_group_info(aggstate, perphase->aggnode->grpColIdx, + length, expr, plain_strategy, context, + ancestors, es); + } + + for (int sortno = 0; sortno < perphase->numsets; sortno++) + { + int length = perphase->gset_lengths[sortno]; + ExprState *expr = NULL; + char *sort_strat; + + if (length == 0) + sort_strat = "All Group"; + else if (sortnode) + { + sort_strat = "Sorted Group"; + expr = perphase->eqfunctions[length - 1]; + } + else + { + sort_strat = "Sorted Input Group"; + expr = perphase->eqfunctions[length - 1]; + } + + show_agg_group_info(aggstate, perphase->aggnode->grpColIdx, length, + expr, sort_strat, context, ancestors, es); + } + } + + if (perphase->uses_hashing) + { + for (int hashno = 0; hashno < aggstate->num_hashes; hashno++) + { + AggStatePerHash perhash = &aggstate->perhash[hashno]; + + show_agg_group_info(aggstate, perhash->hashGrpColIdxInput, + perhash->numCols, + perhash->hashtable->tab_eq_func, + "Hash Group", context, ancestors, es); + } + + ancestors = list_delete_first(ancestors); + } + + ExplainCloseGroup("Groups", "Groups", false, es); + + if (es->format == EXPLAIN_FORMAT_TEXT) + es->indent--; + + /* TODO: should really show memory usage here */ + + ExplainCloseGroup("Phase", NULL, true, es); + } + + ExplainCloseGroup("Phases", "Phases", false, es); +} + /* * Show information on hash buckets/batches. */ diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c index 9005759cd06..e137be14979 100644 --- a/src/backend/executor/execExpr.c +++ b/src/backend/executor/execExpr.c @@ -2933,8 +2933,7 @@ ExecInitCoerceToDomain(ExprEvalStep *scratch, CoerceToDomain *ctest, * transition for each of the concurrently computed grouping sets. */ ExprState * -ExecBuildAggTrans(AggState *aggstate, AggStatePerPhase phase, - bool doSort, bool doHash) +ExecBuildAggTrans(AggState *aggstate, AggStatePerPhase phase) { ExprState *state = makeNode(ExprState); PlanState *parent = &aggstate->ss.ps; @@ -3160,7 +3159,7 @@ ExecBuildAggTrans(AggState *aggstate, AggStatePerPhase phase, * applicable. */ setoff = 0; - if (doSort) + if (phase->uses_sorting) { int processGroupingSets = Max(phase->numsets, 1); @@ -3172,7 +3171,7 @@ ExecBuildAggTrans(AggState *aggstate, AggStatePerPhase phase, } } - if (doHash) + if (phase->uses_hashing) { int numHashes = aggstate->num_hashes; diff --git a/src/backend/executor/nodeAgg.c b/src/backend/executor/nodeAgg.c index 58c376aeb74..d447009e002 100644 --- a/src/backend/executor/nodeAgg.c +++ b/src/backend/executor/nodeAgg.c @@ -2904,8 +2904,10 @@ ExecInitAgg(Agg *node, EState *estate, int eflags) else Assert(false); - phase->evaltrans = ExecBuildAggTrans(aggstate, phase, dosort, dohash); + phase->uses_hashing = dohash; + phase->uses_sorting = dosort; + phase->evaltrans = ExecBuildAggTrans(aggstate, phase); } return aggstate; diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h index 6298c7c8cad..6e2e7e14bac 100644 --- a/src/include/executor/executor.h +++ b/src/include/executor/executor.h @@ -249,8 +249,7 @@ extern ExprState *ExecInitExprWithParams(Expr *node, ParamListInfo ext_params); extern ExprState *ExecInitQual(List *qual, PlanState *parent); extern ExprState *ExecInitCheck(List *qual, PlanState *parent); extern List *ExecInitExprList(List *nodes, PlanState *parent); -extern ExprState *ExecBuildAggTrans(AggState *aggstate, struct AggStatePerPhaseData *phase, - bool doSort, bool doHash); +extern ExprState *ExecBuildAggTrans(AggState *aggstate, struct AggStatePerPhaseData *phase); extern ExprState *ExecBuildGroupingEqual(TupleDesc ldesc, TupleDesc rdesc, const TupleTableSlotOps *lops, const TupleTableSlotOps *rops, int numCols, diff --git a/src/include/executor/nodeAgg.h b/src/include/executor/nodeAgg.h index 68c9e5f5400..4f3e1377cdf 100644 --- a/src/include/executor/nodeAgg.h +++ b/src/include/executor/nodeAgg.h @@ -280,6 +280,9 @@ typedef struct AggStatePerPhaseData Sort *sortnode; /* Sort node for input ordering for phase */ ExprState *evaltrans; /* evaluation of transition functions */ + + bool uses_hashing; /* phase uses hashing */ + bool uses_sorting; /* phase uses sorting */ } AggStatePerPhaseData; /* diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index 683bcaedf5f..b3732b68d77 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -504,8 +504,8 @@ from generate_series(1, 3) s1, lateral (select s2, sum(s1 + s2) sm from generate_series(1, 3) s2 group by s2) ss order by 1, 2; - QUERY PLAN ------------------------------------------------------------------- + QUERY PLAN +----------------------------------------------------------------------- Sort Output: s1.s1, s2.s2, (sum((s1.s1 + s2.s2))) Sort Key: s1.s1, s2.s2 @@ -516,11 +516,13 @@ order by 1, 2; Function Call: generate_series(1, 3) -> HashAggregate Project: s2.s2, sum((s1.s1 + s2.s2)) - Group Key: s2.s2 + Phase 0 using strategy "Hash": + Transition Function: int4_sum(TRANS, (s1.s1 + s2.s2)) + Hash Group: s2.s2 -> Function Scan on pg_catalog.generate_series s2 Output: s2.s2 Function Call: generate_series(1, 3) -(14 rows) +(16 rows) select s1, s2, sm from generate_series(1, 3) s1, @@ -544,8 +546,8 @@ explain (verbose, costs off) select array(select sum(x+y) s from generate_series(1,3) y group by y order by s) from generate_series(1,3) x; - QUERY PLAN -------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------- Function Scan on pg_catalog.generate_series x Project: (SubPlan 1) Function Call: generate_series(1, 3) @@ -555,11 +557,13 @@ select array(select sum(x+y) s Sort Key: (sum((x.x + y.y))) -> HashAggregate Project: sum((x.x + y.y)), y.y - Group Key: y.y + Phase 0 using strategy "Hash": + Transition Function: int4_sum(TRANS, (x.x + y.y)) + Hash Group: y.y -> Function Scan on pg_catalog.generate_series y Output: y.y Function Call: generate_series(1, 3) -(13 rows) +(15 rows) select array(select sum(x+y) s from generate_series(1,3) y group by y order by s) @@ -2250,18 +2254,24 @@ SET enable_indexonlyscan = off; -- regr_count(float8, float8) covers int8inc_float8_float8 and aggregates with > 1 arg EXPLAIN (COSTS OFF, VERBOSE) SELECT variance(unique1::int4), sum(unique1::int8), regr_count(unique1::float8, unique1::float8) FROM tenk1; - QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate Project: variance(unique1), sum((unique1)::bigint), regr_count((unique1)::double precision, (unique1)::double precision) + Phase 1 using strategy "All": + Transition Function: numeric_poly_combine(TRANS, numeric_poly_deserialize((PARTIAL variance(unique1)))), int8_avg_combine(TRANS, int8_avg_deserialize((PARTIAL sum((unique1)::bigint)))), int8pl(TRANS, (PARTIAL regr_count((unique1)::double precision, (unique1)::double precision))) + All Group -> Gather Output: (PARTIAL variance(unique1)), (PARTIAL sum((unique1)::bigint)), (PARTIAL regr_count((unique1)::double precision, (unique1)::double precision)) Workers Planned: 4 -> Partial Aggregate Project: PARTIAL variance(unique1), PARTIAL sum((unique1)::bigint), PARTIAL regr_count((unique1)::double precision, (unique1)::double precision) + Phase 1 using strategy "All": + Transition Function: int4_accum(TRANS, unique1), int8_avg_accum(TRANS, (unique1)::bigint), int8inc_float8_float8(TRANS, (unique1)::double precision, (unique1)::double precision) + All Group -> Parallel Seq Scan on public.tenk1 Output: unique1, unique2, two, four, ten, twenty, hundred, thousand, twothousand, fivethous, tenthous, odd, even, stringu1, stringu2, string4 -(9 rows) +(15 rows) SELECT variance(unique1::int4), sum(unique1::int8), regr_count(unique1::float8, unique1::float8) FROM tenk1; variance | sum | regr_count diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out index c1f802c88a7..7bb052c568b 100644 --- a/src/test/regress/expected/groupingsets.out +++ b/src/test/regress/expected/groupingsets.out @@ -369,12 +369,13 @@ select g as alias1, g as alias2 QUERY PLAN ------------------------------------------------ GroupAggregate - Group Key: g, g - Group Key: g + Phase 1 using strategy "Sorted Input": + Sorted Input Group: g, g + Sorted Input Group: g -> Sort Sort Key: g -> Function Scan on generate_series g -(6 rows) +(7 rows) select g as alias1, g as alias2 from generate_series(1,3) g @@ -640,15 +641,16 @@ select a, b, sum(v.x) -- Test reordering of grouping sets explain (costs off) select * from gstest1 group by grouping sets((a,b,v),(v)) order by v,b,a; - QUERY PLAN ------------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------ GroupAggregate - Group Key: "*VALUES*".column3, "*VALUES*".column2, "*VALUES*".column1 - Group Key: "*VALUES*".column3 + Phase 1 using strategy "Sorted Input": + Sorted Input Group: "*VALUES*".column3, "*VALUES*".column2, "*VALUES*".column1 + Sorted Input Group: "*VALUES*".column3 -> Sort Sort Key: "*VALUES*".column3, "*VALUES*".column2, "*VALUES*".column1 -> Values Scan on "*VALUES*" -(6 rows) +(7 rows) -- Agg level check. This query should error out. select (select grouping(a,b) from gstest2) from gstest2 group by a,b; @@ -720,16 +722,18 @@ select a,count(*) from gstest2 group by rollup(a) having a is distinct from 1 or explain (costs off) select a,count(*) from gstest2 group by rollup(a) having a is distinct from 1 order by a; - QUERY PLAN ----------------------------------- + QUERY PLAN +------------------------------------------------ GroupAggregate - Group Key: a - Group Key: () Filter: (a IS DISTINCT FROM 1) + Phase 1 using strategy "Sorted Input & All": + Transition Function: 2 * int8inc(TRANS) + Sorted Input Group: a + All Group -> Sort Sort Key: a -> Seq Scan on gstest2 -(7 rows) +(9 rows) select v.c, (select count(*) from gstest2 group by () having v.c) from (values (false),(true)) v(c) order by v.c; @@ -749,12 +753,14 @@ explain (costs off) -> Values Scan on "*VALUES*" SubPlan 1 -> Aggregate - Group Key: () Filter: "*VALUES*".column1 + Phase 1 using strategy "All": + Transition Function: int8inc(TRANS) + All Group -> Result One-Time Filter: "*VALUES*".column1 -> Seq Scan on gstest2 -(10 rows) +(12 rows) -- HAVING with GROUPING queries select ten, grouping(ten) from onek @@ -968,15 +974,17 @@ select a, b, grouping(a,b), sum(v), count(*), max(v) explain (costs off) select a, b, grouping(a,b), sum(v), count(*), max(v) from gstest1 group by grouping sets ((a),(b)) order by 3,1,2; - QUERY PLAN --------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------- Sort Sort Key: (GROUPING("*VALUES*".column1, "*VALUES*".column2)), "*VALUES*".column1, "*VALUES*".column2 -> HashAggregate - Hash Key: "*VALUES*".column1 - Hash Key: "*VALUES*".column2 + Phase 0 using strategy "Hash": + Transition Function: 2 * int4_sum(TRANS, "*VALUES*".column3), 2 * int8inc(TRANS), 2 * int4larger(TRANS, "*VALUES*".column3) + Hash Group: "*VALUES*".column1 + Hash Group: "*VALUES*".column2 -> Values Scan on "*VALUES*" -(6 rows) +(8 rows) select a, b, grouping(a,b), sum(v), count(*), max(v) from gstest1 group by cube(a,b) order by 3,1,2; @@ -1002,34 +1010,40 @@ select a, b, grouping(a,b), sum(v), count(*), max(v) explain (costs off) select a, b, grouping(a,b), sum(v), count(*), max(v) from gstest1 group by cube(a,b) order by 3,1,2; - QUERY PLAN --------------------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------- Sort Sort Key: (GROUPING("*VALUES*".column1, "*VALUES*".column2)), "*VALUES*".column1, "*VALUES*".column2 -> MixedAggregate - Hash Key: "*VALUES*".column1, "*VALUES*".column2 - Hash Key: "*VALUES*".column1 - Hash Key: "*VALUES*".column2 - Group Key: () + Phase 1 using strategy "All & Hash": + Transition Function: 4 * int4_sum(TRANS, "*VALUES*".column3), 4 * int8inc(TRANS), 4 * int4larger(TRANS, "*VALUES*".column3) + All Group + Hash Group: "*VALUES*".column1, "*VALUES*".column2 + Hash Group: "*VALUES*".column1 + Hash Group: "*VALUES*".column2 -> Values Scan on "*VALUES*" -(8 rows) +(10 rows) -- shouldn't try and hash explain (costs off) select a, b, grouping(a,b), array_agg(v order by v) from gstest1 group by cube(a,b); - QUERY PLAN ----------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------- GroupAggregate - Group Key: "*VALUES*".column1, "*VALUES*".column2 - Group Key: "*VALUES*".column1 - Group Key: () - Sort Key: "*VALUES*".column2 - Group Key: "*VALUES*".column2 + Phase 2 using strategy "Sort": + Sort Key: "*VALUES*".column2 + Transition Function: array_agg_transfn(TRANS, "*VALUES*".column3) + Sorted Group: "*VALUES*".column2 + Phase 1 using strategy "Sorted Input & All": + Transition Function: 3 * array_agg_transfn(TRANS, "*VALUES*".column3) + Sorted Input Group: "*VALUES*".column1, "*VALUES*".column2 + Sorted Input Group: "*VALUES*".column1 + All Group -> Sort Sort Key: "*VALUES*".column1, "*VALUES*".column2 -> Values Scan on "*VALUES*" -(9 rows) +(13 rows) -- unsortable cases select unsortable_col, count(*) @@ -1065,17 +1079,19 @@ explain (costs off) count(*), sum(v) from gstest4 group by grouping sets ((unhashable_col),(unsortable_col)) order by 3,5; - QUERY PLAN ------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------- Sort Sort Key: (GROUPING(unhashable_col, unsortable_col)), (sum(v)) -> MixedAggregate - Hash Key: unsortable_col - Group Key: unhashable_col + Phase 1 using strategy "Sorted Input & Hash": + Transition Function: 2 * int8inc(TRANS), 2 * int4_sum(TRANS, v) + Sorted Input Group: unhashable_col + Hash Group: unsortable_col -> Sort Sort Key: unhashable_col -> Seq Scan on gstest4 -(8 rows) +(10 rows) select unhashable_col, unsortable_col, grouping(unhashable_col, unsortable_col), @@ -1108,17 +1124,19 @@ explain (costs off) count(*), sum(v) from gstest4 group by grouping sets ((v,unhashable_col),(v,unsortable_col)) order by 3,5; - QUERY PLAN ------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------- Sort Sort Key: (GROUPING(unhashable_col, unsortable_col)), (sum(v)) -> MixedAggregate - Hash Key: v, unsortable_col - Group Key: v, unhashable_col + Phase 1 using strategy "Sorted Input & Hash": + Transition Function: 2 * int8inc(TRANS), 2 * int4_sum(TRANS, v) + Sorted Input Group: v, unhashable_col + Hash Group: v, unsortable_col -> Sort Sort Key: v, unhashable_col -> Seq Scan on gstest4 -(8 rows) +(10 rows) -- empty input: first is 0 rows, second 1, third 3 etc. select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a); @@ -1128,13 +1146,15 @@ select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a) explain (costs off) select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),a); - QUERY PLAN --------------------------------- + QUERY PLAN +--------------------------------------------------------------------- HashAggregate - Hash Key: a, b - Hash Key: a + Phase 0 using strategy "Hash": + Transition Function: 2 * int4_sum(TRANS, v), 2 * int8inc(TRANS) + Hash Group: a, b + Hash Group: a -> Seq Scan on gstest_empty -(4 rows) +(6 rows) select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),()); a | b | sum | count @@ -1152,15 +1172,17 @@ select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),() explain (costs off) select a, b, sum(v), count(*) from gstest_empty group by grouping sets ((a,b),(),(),()); - QUERY PLAN --------------------------------- + QUERY PLAN +--------------------------------------------------------------------- MixedAggregate - Hash Key: a, b - Group Key: () - Group Key: () - Group Key: () + Phase 1 using strategy "All & Hash": + Transition Function: 4 * int4_sum(TRANS, v), 4 * int8inc(TRANS) + All Group + All Group + All Group + Hash Group: a, b -> Seq Scan on gstest_empty -(6 rows) +(8 rows) select sum(v), count(*) from gstest_empty group by grouping sets ((),(),()); sum | count @@ -1172,14 +1194,16 @@ select sum(v), count(*) from gstest_empty group by grouping sets ((),(),()); explain (costs off) select sum(v), count(*) from gstest_empty group by grouping sets ((),(),()); - QUERY PLAN --------------------------------- + QUERY PLAN +--------------------------------------------------------------------- Aggregate - Group Key: () - Group Key: () - Group Key: () + Phase 1 using strategy "All": + Transition Function: 3 * int4_sum(TRANS, v), 3 * int8inc(TRANS) + All Group + All Group + All Group -> Seq Scan on gstest_empty -(5 rows) +(7 rows) -- check that functionally dependent cols are not nulled select a, d, grouping(a,b,c) @@ -1197,13 +1221,14 @@ explain (costs off) select a, d, grouping(a,b,c) from gstest3 group by grouping sets ((a,b), (a,c)); - QUERY PLAN ---------------------------- + QUERY PLAN +---------------------------------- HashAggregate - Hash Key: a, b - Hash Key: a, c + Phase 0 using strategy "Hash": + Hash Group: a, b + Hash Group: a, c -> Seq Scan on gstest3 -(4 rows) +(5 rows) -- simple rescan tests select a, b, sum(v.x) @@ -1224,17 +1249,19 @@ explain (costs off) from (values (1),(2)) v(x), gstest_data(v.x) group by grouping sets (a,b) order by 3, 1, 2; - QUERY PLAN ---------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------ Sort Sort Key: (sum("*VALUES*".column1)), gstest_data.a, gstest_data.b -> HashAggregate - Hash Key: gstest_data.a - Hash Key: gstest_data.b + Phase 0 using strategy "Hash": + Transition Function: 2 * int4_sum(TRANS, "*VALUES*".column1) + Hash Group: gstest_data.a + Hash Group: gstest_data.b -> Nested Loop -> Values Scan on "*VALUES*" -> Function Scan on gstest_data -(8 rows) +(10 rows) select * from (values (1),(2)) v(x), @@ -1280,16 +1307,18 @@ select a, b, grouping(a,b), sum(v), count(*), max(v) explain (costs off) select a, b, grouping(a,b), sum(v), count(*), max(v) from gstest1 group by grouping sets ((a,b),(a+1,b+1),(a+2,b+2)) order by 3,6; - QUERY PLAN -------------------------------------------------------------------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------- Sort Sort Key: (GROUPING("*VALUES*".column1, "*VALUES*".column2)), (max("*VALUES*".column3)) -> HashAggregate - Hash Key: "*VALUES*".column1, "*VALUES*".column2 - Hash Key: ("*VALUES*".column1 + 1), ("*VALUES*".column2 + 1) - Hash Key: ("*VALUES*".column1 + 2), ("*VALUES*".column2 + 2) + Phase 0 using strategy "Hash": + Transition Function: 3 * int4_sum(TRANS, "*VALUES*".column3), 3 * int8inc(TRANS), 3 * int4larger(TRANS, "*VALUES*".column3) + Hash Group: "*VALUES*".column1, "*VALUES*".column2 + Hash Group: ("*VALUES*".column1 + 1), ("*VALUES*".column2 + 1) + Hash Group: ("*VALUES*".column1 + 2), ("*VALUES*".column2 + 2) -> Values Scan on "*VALUES*" -(7 rows) +(9 rows) select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum from gstest2 group by cube (a,b) order by rsum, a, b; @@ -1308,20 +1337,22 @@ select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum explain (costs off) select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum from gstest2 group by cube (a,b) order by rsum, a, b; - QUERY PLAN ---------------------------------------------- + QUERY PLAN +------------------------------------------------------------------- Sort Sort Key: (sum((sum(c))) OVER (?)), a, b -> WindowAgg -> Sort Sort Key: a, b -> MixedAggregate - Hash Key: a, b - Hash Key: a - Hash Key: b - Group Key: () + Phase 1 using strategy "All & Hash": + Transition Function: 4 * int4_sum(TRANS, c) + All Group + Hash Group: a, b + Hash Group: a + Hash Group: b -> Seq Scan on gstest2 -(11 rows) +(13 rows) select a, b, sum(v.x) from (values (1),(2)) v(x), gstest_data(v.x) @@ -1346,19 +1377,21 @@ explain (costs off) select a, b, sum(v.x) from (values (1),(2)) v(x), gstest_data(v.x) group by cube (a,b) order by a,b; - QUERY PLAN ------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------ Sort Sort Key: gstest_data.a, gstest_data.b -> MixedAggregate - Hash Key: gstest_data.a, gstest_data.b - Hash Key: gstest_data.a - Hash Key: gstest_data.b - Group Key: () + Phase 1 using strategy "All & Hash": + Transition Function: 4 * int4_sum(TRANS, "*VALUES*".column1) + All Group + Hash Group: gstest_data.a, gstest_data.b + Hash Group: gstest_data.a + Hash Group: gstest_data.b -> Nested Loop -> Values Scan on "*VALUES*" -> Function Scan on gstest_data -(10 rows) +(12 rows) -- Verify that we correctly handle the child node returning a -- non-minimal slot, which happens if the input is pre-sorted, @@ -1366,19 +1399,23 @@ explain (costs off) BEGIN; SET LOCAL enable_hashagg = false; EXPLAIN (COSTS OFF) SELECT a, b, count(*), max(a), max(b) FROM gstest3 GROUP BY GROUPING SETS(a, b,()) ORDER BY a, b; - QUERY PLAN ---------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------- Sort Sort Key: a, b -> GroupAggregate - Group Key: a - Group Key: () - Sort Key: b - Group Key: b + Phase 2 using strategy "Sort": + Sort Key: b + Transition Function: int8inc(TRANS), int4larger(TRANS, a), int4larger(TRANS, b) + Sorted Group: b + Phase 1 using strategy "Sorted Input & All": + Transition Function: 2 * int8inc(TRANS), 2 * int4larger(TRANS, a), 2 * int4larger(TRANS, b) + Sorted Input Group: a + All Group -> Sort Sort Key: a -> Seq Scan on gstest3 -(10 rows) +(14 rows) SELECT a, b, count(*), max(a), max(b) FROM gstest3 GROUP BY GROUPING SETS(a, b,()) ORDER BY a, b; a | b | count | max | max @@ -1392,17 +1429,21 @@ SELECT a, b, count(*), max(a), max(b) FROM gstest3 GROUP BY GROUPING SETS(a, b,( SET LOCAL enable_seqscan = false; EXPLAIN (COSTS OFF) SELECT a, b, count(*), max(a), max(b) FROM gstest3 GROUP BY GROUPING SETS(a, b,()) ORDER BY a, b; - QUERY PLAN ------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------------------------------------- Sort Sort Key: a, b -> GroupAggregate - Group Key: a - Group Key: () - Sort Key: b - Group Key: b + Phase 2 using strategy "Sort": + Sort Key: b + Transition Function: int8inc(TRANS), int4larger(TRANS, a), int4larger(TRANS, b) + Sorted Group: b + Phase 1 using strategy "Sorted Input & All": + Transition Function: 2 * int8inc(TRANS), 2 * int4larger(TRANS, a), 2 * int4larger(TRANS, b) + Sorted Input Group: a + All Group -> Index Scan using gstest3_pkey on gstest3 -(8 rows) +(12 rows) SELECT a, b, count(*), max(a), max(b) FROM gstest3 GROUP BY GROUPING SETS(a, b,()) ORDER BY a, b; a | b | count | max | max @@ -1549,22 +1590,28 @@ explain (costs off) count(hundred), count(thousand), count(twothousand), count(*) from tenk1 group by grouping sets (unique1,twothousand,thousand,hundred,ten,four,two); - QUERY PLAN -------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- MixedAggregate - Hash Key: two - Hash Key: four - Hash Key: ten - Hash Key: hundred - Group Key: unique1 - Sort Key: twothousand - Group Key: twothousand - Sort Key: thousand - Group Key: thousand + Phase 3 using strategy "Sort": + Sort Key: thousand + Transition Function: int8inc_any(TRANS, two), int8inc_any(TRANS, four), int8inc_any(TRANS, ten), int8inc_any(TRANS, hundred), int8inc_any(TRANS, thousand), int8inc_any(TRANS, twothousand), int8inc(TRANS) + Sorted Group: thousand + Phase 2 using strategy "Sort": + Sort Key: twothousand + Transition Function: int8inc_any(TRANS, two), int8inc_any(TRANS, four), int8inc_any(TRANS, ten), int8inc_any(TRANS, hundred), int8inc_any(TRANS, thousand), int8inc_any(TRANS, twothousand), int8inc(TRANS) + Sorted Group: twothousand + Phase 1 using strategy "Sorted Input & Hash": + Transition Function: 5 * int8inc_any(TRANS, two), 5 * int8inc_any(TRANS, four), 5 * int8inc_any(TRANS, ten), 5 * int8inc_any(TRANS, hundred), 5 * int8inc_any(TRANS, thousand), 5 * int8inc_any(TRANS, twothousand), 5 * int8inc(TRANS) + Sorted Input Group: unique1 + Hash Group: two + Hash Group: four + Hash Group: ten + Hash Group: hundred -> Sort Sort Key: unique1 -> Seq Scan on tenk1 -(13 rows) +(19 rows) explain (costs off) select unique1, @@ -1572,18 +1619,20 @@ explain (costs off) count(hundred), count(thousand), count(twothousand), count(*) from tenk1 group by grouping sets (unique1,hundred,ten,four,two); - QUERY PLAN -------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- MixedAggregate - Hash Key: two - Hash Key: four - Hash Key: ten - Hash Key: hundred - Group Key: unique1 + Phase 1 using strategy "Sorted Input & Hash": + Transition Function: 5 * int8inc_any(TRANS, two), 5 * int8inc_any(TRANS, four), 5 * int8inc_any(TRANS, ten), 5 * int8inc_any(TRANS, hundred), 5 * int8inc_any(TRANS, thousand), 5 * int8inc_any(TRANS, twothousand), 5 * int8inc(TRANS) + Sorted Input Group: unique1 + Hash Group: two + Hash Group: four + Hash Group: ten + Hash Group: hundred -> Sort Sort Key: unique1 -> Seq Scan on tenk1 -(9 rows) +(11 rows) set work_mem = '384kB'; explain (costs off) @@ -1592,21 +1641,25 @@ explain (costs off) count(hundred), count(thousand), count(twothousand), count(*) from tenk1 group by grouping sets (unique1,twothousand,thousand,hundred,ten,four,two); - QUERY PLAN -------------------------------- + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- MixedAggregate - Hash Key: two - Hash Key: four - Hash Key: ten - Hash Key: hundred - Hash Key: thousand - Group Key: unique1 - Sort Key: twothousand - Group Key: twothousand + Phase 2 using strategy "Sort": + Sort Key: twothousand + Transition Function: int8inc_any(TRANS, two), int8inc_any(TRANS, four), int8inc_any(TRANS, ten), int8inc_any(TRANS, hundred), int8inc_any(TRANS, thousand), int8inc_any(TRANS, twothousand), int8inc(TRANS) + Sorted Group: twothousand + Phase 1 using strategy "Sorted Input & Hash": + Transition Function: 6 * int8inc_any(TRANS, two), 6 * int8inc_any(TRANS, four), 6 * int8inc_any(TRANS, ten), 6 * int8inc_any(TRANS, hundred), 6 * int8inc_any(TRANS, thousand), 6 * int8inc_any(TRANS, twothousand), 6 * int8inc(TRANS) + Sorted Input Group: unique1 + Hash Group: two + Hash Group: four + Hash Group: ten + Hash Group: hundred + Hash Group: thousand -> Sort Sort Key: unique1 -> Seq Scan on tenk1 -(12 rows) +(16 rows) -- check collation-sensitive matching between grouping expressions -- (similar to a check for aggregates, but there are additional code diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index 4b8351839a8..48d16bcee55 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1435,10 +1435,13 @@ select * from matest0 order by 1-id; (6 rows) explain (verbose, costs off) select min(1-id) from matest0; - QUERY PLAN ----------------------------------------- + QUERY PLAN +--------------------------------------------------------------- Aggregate Project: min((1 - matest0.id)) + Phase 1 using strategy "All": + Transition Function: int4smaller(TRANS, (1 - matest0.id)) + All Group -> Append -> Seq Scan on public.matest0 Project: matest0.id @@ -1448,7 +1451,7 @@ explain (verbose, costs off) select min(1-id) from matest0; Project: matest2.id -> Seq Scan on public.matest3 Project: matest3.id -(11 rows) +(14 rows) select min(1-id) from matest0; min diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 7f319a79938..1ddc4423888 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -6172,7 +6172,8 @@ where exists (select 1 from tenk1 t3 Hash Cond: (t3.thousand = t1.unique1) -> HashAggregate Project: t3.thousand, t3.tenthous - Group Key: t3.thousand, t3.tenthous + Phase 0 using strategy "Hash": + Hash Group: t3.thousand, t3.tenthous -> Index Only Scan using tenk1_thous_tenthous on public.tenk1 t3 Output: t3.thousand, t3.tenthous -> Hash @@ -6183,7 +6184,7 @@ where exists (select 1 from tenk1 t3 -> Index Only Scan using tenk1_hundred on public.tenk1 t2 Output: t2.hundred Index Cond: (t2.hundred = t3.tenthous) -(18 rows) +(19 rows) -- ... unless it actually is unique create table j3 as select unique1, tenthous from onek; diff --git a/src/test/regress/expected/limit.out b/src/test/regress/expected/limit.out index 5b247e74b77..f9124feb866 100644 --- a/src/test/regress/expected/limit.out +++ b/src/test/regress/expected/limit.out @@ -489,10 +489,12 @@ select sum(tenthous) as s1, sum(tenthous) + random()*0 as s2 Output: (sum(tenthous)), (((sum(tenthous))::double precision + (random() * '0'::double precision))), thousand -> GroupAggregate Project: sum(tenthous), ((sum(tenthous))::double precision + (random() * '0'::double precision)), thousand - Group Key: tenk1.thousand + Phase 1 using strategy "Sorted Input": + Transition Function: int4_sum(TRANS, tenthous) + Sorted Input Group: tenk1.thousand -> Index Only Scan using tenk1_thous_tenthous on public.tenk1 Output: thousand, tenthous -(7 rows) +(9 rows) select sum(tenthous) as s1, sum(tenthous) + random()*0 as s2 from tenk1 group by thousand order by thousand limit 3; diff --git a/src/test/regress/expected/partition_aggregate.out b/src/test/regress/expected/partition_aggregate.out index 10349ec29c4..ca2c92a406a 100644 --- a/src/test/regress/expected/partition_aggregate.out +++ b/src/test/regress/expected/partition_aggregate.out @@ -26,16 +26,16 @@ SELECT c, sum(a), avg(b), count(*), min(a), max(b) FROM pagg_tab GROUP BY c HAVI Sort Key: pagg_tab_p1.c, (sum(pagg_tab_p1.a)), (avg(pagg_tab_p1.b)) -> Append -> HashAggregate - Group Key: pagg_tab_p1.c Filter: (avg(pagg_tab_p1.d) < '15'::numeric) + Group Key: pagg_tab_p1.c -> Seq Scan on pagg_tab_p1 -> HashAggregate - Group Key: pagg_tab_p2.c Filter: (avg(pagg_tab_p2.d) < '15'::numeric) + Group Key: pagg_tab_p2.c -> Seq Scan on pagg_tab_p2 -> HashAggregate - Group Key: pagg_tab_p3.c Filter: (avg(pagg_tab_p3.d) < '15'::numeric) + Group Key: pagg_tab_p3.c -> Seq Scan on pagg_tab_p3 (15 rows) @@ -58,8 +58,8 @@ SELECT a, sum(b), avg(b), count(*), min(a), max(b) FROM pagg_tab GROUP BY a HAVI Sort Sort Key: pagg_tab_p1.a, (sum(pagg_tab_p1.b)), (avg(pagg_tab_p1.b)) -> Finalize HashAggregate - Group Key: pagg_tab_p1.a Filter: (avg(pagg_tab_p1.d) < '15'::numeric) + Group Key: pagg_tab_p1.a -> Append -> Partial HashAggregate Group Key: pagg_tab_p1.a @@ -180,20 +180,20 @@ SELECT c, sum(a), avg(b), count(*) FROM pagg_tab GROUP BY 1 HAVING avg(d) < 15 O Sort Key: pagg_tab_p1.c, (sum(pagg_tab_p1.a)), (avg(pagg_tab_p1.b)) -> Append -> GroupAggregate - Group Key: pagg_tab_p1.c Filter: (avg(pagg_tab_p1.d) < '15'::numeric) + Group Key: pagg_tab_p1.c -> Sort Sort Key: pagg_tab_p1.c -> Seq Scan on pagg_tab_p1 -> GroupAggregate - Group Key: pagg_tab_p2.c Filter: (avg(pagg_tab_p2.d) < '15'::numeric) + Group Key: pagg_tab_p2.c -> Sort Sort Key: pagg_tab_p2.c -> Seq Scan on pagg_tab_p2 -> GroupAggregate - Group Key: pagg_tab_p3.c Filter: (avg(pagg_tab_p3.d) < '15'::numeric) + Group Key: pagg_tab_p3.c -> Sort Sort Key: pagg_tab_p3.c -> Seq Scan on pagg_tab_p3 @@ -218,8 +218,8 @@ SELECT a, sum(b), avg(b), count(*) FROM pagg_tab GROUP BY 1 HAVING avg(d) < 15 O Sort Sort Key: pagg_tab_p1.a, (sum(pagg_tab_p1.b)), (avg(pagg_tab_p1.b)) -> Finalize GroupAggregate - Group Key: pagg_tab_p1.a Filter: (avg(pagg_tab_p1.d) < '15'::numeric) + Group Key: pagg_tab_p1.a -> Merge Append Sort Key: pagg_tab_p1.a -> Partial GroupAggregate @@ -335,18 +335,20 @@ RESET enable_hashagg; -- ROLLUP, partitionwise aggregation does not apply EXPLAIN (COSTS OFF) SELECT c, sum(a) FROM pagg_tab GROUP BY rollup(c) ORDER BY 1, 2; - QUERY PLAN -------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------- Sort Sort Key: pagg_tab_p1.c, (sum(pagg_tab_p1.a)) -> MixedAggregate - Hash Key: pagg_tab_p1.c - Group Key: () + Phase 1 using strategy "All & Hash": + Transition Function: 2 * int4_sum(TRANS, pagg_tab_p1.a) + All Group + Hash Group: pagg_tab_p1.c -> Append -> Seq Scan on pagg_tab_p1 -> Seq Scan on pagg_tab_p2 -> Seq Scan on pagg_tab_p3 -(9 rows) +(11 rows) -- ORDERED SET within the aggregate. -- Full aggregation; since all the rows that belong to the same group come @@ -522,8 +524,8 @@ SELECT t1.y, sum(t1.x), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2 Sort Sort Key: t1.y, (sum(t1.x)), (count(*)) -> Finalize GroupAggregate - Group Key: t1.y Filter: (avg(t1.x) > '10'::numeric) + Group Key: t1.y -> Merge Append Sort Key: t1.y -> Partial GroupAggregate @@ -830,8 +832,8 @@ SELECT a, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY a HAVING avg(c) < 22 Sort Sort Key: pagg_tab_m_p1.a, (sum(pagg_tab_m_p1.b)), (avg(pagg_tab_m_p1.c)) -> Finalize HashAggregate - Group Key: pagg_tab_m_p1.a Filter: (avg(pagg_tab_m_p1.c) < '22'::numeric) + Group Key: pagg_tab_m_p1.a -> Append -> Partial HashAggregate Group Key: pagg_tab_m_p1.a @@ -864,16 +866,16 @@ SELECT a, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY a, (a+b)/2 HAVING su Sort Key: pagg_tab_m_p1.a, (sum(pagg_tab_m_p1.b)), (avg(pagg_tab_m_p1.c)) -> Append -> HashAggregate - Group Key: pagg_tab_m_p1.a, ((pagg_tab_m_p1.a + pagg_tab_m_p1.b) / 2) Filter: (sum(pagg_tab_m_p1.b) < 50) + Group Key: pagg_tab_m_p1.a, ((pagg_tab_m_p1.a + pagg_tab_m_p1.b) / 2) -> Seq Scan on pagg_tab_m_p1 -> HashAggregate - Group Key: pagg_tab_m_p2.a, ((pagg_tab_m_p2.a + pagg_tab_m_p2.b) / 2) Filter: (sum(pagg_tab_m_p2.b) < 50) + Group Key: pagg_tab_m_p2.a, ((pagg_tab_m_p2.a + pagg_tab_m_p2.b) / 2) -> Seq Scan on pagg_tab_m_p2 -> HashAggregate - Group Key: pagg_tab_m_p3.a, ((pagg_tab_m_p3.a + pagg_tab_m_p3.b) / 2) Filter: (sum(pagg_tab_m_p3.b) < 50) + Group Key: pagg_tab_m_p3.a, ((pagg_tab_m_p3.a + pagg_tab_m_p3.b) / 2) -> Seq Scan on pagg_tab_m_p3 (15 rows) @@ -897,16 +899,16 @@ SELECT a, c, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY (a+b)/2, 2, 1 HAV Sort Key: pagg_tab_m_p1.a, pagg_tab_m_p1.c, (sum(pagg_tab_m_p1.b)) -> Append -> HashAggregate - Group Key: ((pagg_tab_m_p1.a + pagg_tab_m_p1.b) / 2), pagg_tab_m_p1.c, pagg_tab_m_p1.a Filter: ((sum(pagg_tab_m_p1.b) = 50) AND (avg(pagg_tab_m_p1.c) > '25'::numeric)) + Group Key: ((pagg_tab_m_p1.a + pagg_tab_m_p1.b) / 2), pagg_tab_m_p1.c, pagg_tab_m_p1.a -> Seq Scan on pagg_tab_m_p1 -> HashAggregate - Group Key: ((pagg_tab_m_p2.a + pagg_tab_m_p2.b) / 2), pagg_tab_m_p2.c, pagg_tab_m_p2.a Filter: ((sum(pagg_tab_m_p2.b) = 50) AND (avg(pagg_tab_m_p2.c) > '25'::numeric)) + Group Key: ((pagg_tab_m_p2.a + pagg_tab_m_p2.b) / 2), pagg_tab_m_p2.c, pagg_tab_m_p2.a -> Seq Scan on pagg_tab_m_p2 -> HashAggregate - Group Key: ((pagg_tab_m_p3.a + pagg_tab_m_p3.b) / 2), pagg_tab_m_p3.c, pagg_tab_m_p3.a Filter: ((sum(pagg_tab_m_p3.b) = 50) AND (avg(pagg_tab_m_p3.c) > '25'::numeric)) + Group Key: ((pagg_tab_m_p3.a + pagg_tab_m_p3.b) / 2), pagg_tab_m_p3.c, pagg_tab_m_p3.a -> Seq Scan on pagg_tab_m_p3 (15 rows) @@ -951,24 +953,24 @@ SELECT a, sum(b), array_agg(distinct c), count(*) FROM pagg_tab_ml GROUP BY a HA Workers Planned: 2 -> Parallel Append -> GroupAggregate - Group Key: pagg_tab_ml_p2_s1.a Filter: (avg(pagg_tab_ml_p2_s1.b) < '3'::numeric) + Group Key: pagg_tab_ml_p2_s1.a -> Sort Sort Key: pagg_tab_ml_p2_s1.a -> Append -> Seq Scan on pagg_tab_ml_p2_s1 -> Seq Scan on pagg_tab_ml_p2_s2 -> GroupAggregate - Group Key: pagg_tab_ml_p3_s1.a Filter: (avg(pagg_tab_ml_p3_s1.b) < '3'::numeric) + Group Key: pagg_tab_ml_p3_s1.a -> Sort Sort Key: pagg_tab_ml_p3_s1.a -> Append -> Seq Scan on pagg_tab_ml_p3_s1 -> Seq Scan on pagg_tab_ml_p3_s2 -> GroupAggregate - Group Key: pagg_tab_ml_p1.a Filter: (avg(pagg_tab_ml_p1.b) < '3'::numeric) + Group Key: pagg_tab_ml_p1.a -> Sort Sort Key: pagg_tab_ml_p1.a -> Seq Scan on pagg_tab_ml_p1 @@ -997,24 +999,24 @@ SELECT a, sum(b), array_agg(distinct c), count(*) FROM pagg_tab_ml GROUP BY a HA Workers Planned: 2 -> Parallel Append -> GroupAggregate - Group Key: pagg_tab_ml_p2_s1.a Filter: (avg(pagg_tab_ml_p2_s1.b) < '3'::numeric) + Group Key: pagg_tab_ml_p2_s1.a -> Sort Sort Key: pagg_tab_ml_p2_s1.a -> Append -> Seq Scan on pagg_tab_ml_p2_s1 -> Seq Scan on pagg_tab_ml_p2_s2 -> GroupAggregate - Group Key: pagg_tab_ml_p3_s1.a Filter: (avg(pagg_tab_ml_p3_s1.b) < '3'::numeric) + Group Key: pagg_tab_ml_p3_s1.a -> Sort Sort Key: pagg_tab_ml_p3_s1.a -> Append -> Seq Scan on pagg_tab_ml_p3_s1 -> Seq Scan on pagg_tab_ml_p3_s2 -> GroupAggregate - Group Key: pagg_tab_ml_p1.a Filter: (avg(pagg_tab_ml_p1.b) < '3'::numeric) + Group Key: pagg_tab_ml_p1.a -> Sort Sort Key: pagg_tab_ml_p1.a -> Seq Scan on pagg_tab_ml_p1 @@ -1031,12 +1033,12 @@ SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER B Sort Key: pagg_tab_ml_p1.a, (sum(pagg_tab_ml_p1.b)), (count(*)) -> Append -> HashAggregate - Group Key: pagg_tab_ml_p1.a Filter: (avg(pagg_tab_ml_p1.b) < '3'::numeric) + Group Key: pagg_tab_ml_p1.a -> Seq Scan on pagg_tab_ml_p1 -> Finalize GroupAggregate - Group Key: pagg_tab_ml_p2_s1.a Filter: (avg(pagg_tab_ml_p2_s1.b) < '3'::numeric) + Group Key: pagg_tab_ml_p2_s1.a -> Sort Sort Key: pagg_tab_ml_p2_s1.a -> Append @@ -1047,8 +1049,8 @@ SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER B Group Key: pagg_tab_ml_p2_s2.a -> Seq Scan on pagg_tab_ml_p2_s2 -> Finalize GroupAggregate - Group Key: pagg_tab_ml_p3_s1.a Filter: (avg(pagg_tab_ml_p3_s1.b) < '3'::numeric) + Group Key: pagg_tab_ml_p3_s1.a -> Sort Sort Key: pagg_tab_ml_p3_s1.a -> Append @@ -1123,24 +1125,24 @@ SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a, b, c HAVING avg(b) > 7 O Sort Key: pagg_tab_ml_p1.a, (sum(pagg_tab_ml_p1.b)), (count(*)) -> Append -> HashAggregate - Group Key: pagg_tab_ml_p1.a, pagg_tab_ml_p1.b, pagg_tab_ml_p1.c Filter: (avg(pagg_tab_ml_p1.b) > '7'::numeric) + Group Key: pagg_tab_ml_p1.a, pagg_tab_ml_p1.b, pagg_tab_ml_p1.c -> Seq Scan on pagg_tab_ml_p1 -> HashAggregate - Group Key: pagg_tab_ml_p2_s1.a, pagg_tab_ml_p2_s1.b, pagg_tab_ml_p2_s1.c Filter: (avg(pagg_tab_ml_p2_s1.b) > '7'::numeric) + Group Key: pagg_tab_ml_p2_s1.a, pagg_tab_ml_p2_s1.b, pagg_tab_ml_p2_s1.c -> Seq Scan on pagg_tab_ml_p2_s1 -> HashAggregate - Group Key: pagg_tab_ml_p2_s2.a, pagg_tab_ml_p2_s2.b, pagg_tab_ml_p2_s2.c Filter: (avg(pagg_tab_ml_p2_s2.b) > '7'::numeric) + Group Key: pagg_tab_ml_p2_s2.a, pagg_tab_ml_p2_s2.b, pagg_tab_ml_p2_s2.c -> Seq Scan on pagg_tab_ml_p2_s2 -> HashAggregate - Group Key: pagg_tab_ml_p3_s1.a, pagg_tab_ml_p3_s1.b, pagg_tab_ml_p3_s1.c Filter: (avg(pagg_tab_ml_p3_s1.b) > '7'::numeric) + Group Key: pagg_tab_ml_p3_s1.a, pagg_tab_ml_p3_s1.b, pagg_tab_ml_p3_s1.c -> Seq Scan on pagg_tab_ml_p3_s1 -> HashAggregate - Group Key: pagg_tab_ml_p3_s2.a, pagg_tab_ml_p3_s2.b, pagg_tab_ml_p3_s2.c Filter: (avg(pagg_tab_ml_p3_s2.b) > '7'::numeric) + Group Key: pagg_tab_ml_p3_s2.a, pagg_tab_ml_p3_s2.b, pagg_tab_ml_p3_s2.c -> Seq Scan on pagg_tab_ml_p3_s2 (23 rows) @@ -1175,8 +1177,8 @@ SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER B Sort Key: pagg_tab_ml_p1.a, (sum(pagg_tab_ml_p1.b)), (count(*)) -> Append -> Finalize GroupAggregate - Group Key: pagg_tab_ml_p1.a Filter: (avg(pagg_tab_ml_p1.b) < '3'::numeric) + Group Key: pagg_tab_ml_p1.a -> Gather Merge Workers Planned: 2 -> Sort @@ -1185,8 +1187,8 @@ SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER B Group Key: pagg_tab_ml_p1.a -> Parallel Seq Scan on pagg_tab_ml_p1 -> Finalize GroupAggregate - Group Key: pagg_tab_ml_p2_s1.a Filter: (avg(pagg_tab_ml_p2_s1.b) < '3'::numeric) + Group Key: pagg_tab_ml_p2_s1.a -> Gather Merge Workers Planned: 2 -> Sort @@ -1199,8 +1201,8 @@ SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER B Group Key: pagg_tab_ml_p2_s2.a -> Parallel Seq Scan on pagg_tab_ml_p2_s2 -> Finalize GroupAggregate - Group Key: pagg_tab_ml_p3_s1.a Filter: (avg(pagg_tab_ml_p3_s1.b) < '3'::numeric) + Group Key: pagg_tab_ml_p3_s1.a -> Gather Merge Workers Planned: 2 -> Sort @@ -1281,24 +1283,24 @@ SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a, b, c HAVING avg(b) > 7 O Sort Key: pagg_tab_ml_p1.a, (sum(pagg_tab_ml_p1.b)), (count(*)) -> Parallel Append -> HashAggregate - Group Key: pagg_tab_ml_p1.a, pagg_tab_ml_p1.b, pagg_tab_ml_p1.c Filter: (avg(pagg_tab_ml_p1.b) > '7'::numeric) + Group Key: pagg_tab_ml_p1.a, pagg_tab_ml_p1.b, pagg_tab_ml_p1.c -> Seq Scan on pagg_tab_ml_p1 -> HashAggregate - Group Key: pagg_tab_ml_p2_s1.a, pagg_tab_ml_p2_s1.b, pagg_tab_ml_p2_s1.c Filter: (avg(pagg_tab_ml_p2_s1.b) > '7'::numeric) + Group Key: pagg_tab_ml_p2_s1.a, pagg_tab_ml_p2_s1.b, pagg_tab_ml_p2_s1.c -> Seq Scan on pagg_tab_ml_p2_s1 -> HashAggregate - Group Key: pagg_tab_ml_p2_s2.a, pagg_tab_ml_p2_s2.b, pagg_tab_ml_p2_s2.c Filter: (avg(pagg_tab_ml_p2_s2.b) > '7'::numeric) + Group Key: pagg_tab_ml_p2_s2.a, pagg_tab_ml_p2_s2.b, pagg_tab_ml_p2_s2.c -> Seq Scan on pagg_tab_ml_p2_s2 -> HashAggregate - Group Key: pagg_tab_ml_p3_s1.a, pagg_tab_ml_p3_s1.b, pagg_tab_ml_p3_s1.c Filter: (avg(pagg_tab_ml_p3_s1.b) > '7'::numeric) + Group Key: pagg_tab_ml_p3_s1.a, pagg_tab_ml_p3_s1.b, pagg_tab_ml_p3_s1.c -> Seq Scan on pagg_tab_ml_p3_s1 -> HashAggregate - Group Key: pagg_tab_ml_p3_s2.a, pagg_tab_ml_p3_s2.b, pagg_tab_ml_p3_s2.c Filter: (avg(pagg_tab_ml_p3_s2.b) > '7'::numeric) + Group Key: pagg_tab_ml_p3_s2.a, pagg_tab_ml_p3_s2.b, pagg_tab_ml_p3_s2.c -> Seq Scan on pagg_tab_ml_p3_s2 (25 rows) @@ -1342,8 +1344,8 @@ SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < Sort Sort Key: pagg_tab_para_p1.x, (sum(pagg_tab_para_p1.y)), (avg(pagg_tab_para_p1.y)) -> Finalize GroupAggregate - Group Key: pagg_tab_para_p1.x Filter: (avg(pagg_tab_para_p1.y) < '7'::numeric) + Group Key: pagg_tab_para_p1.x -> Gather Merge Workers Planned: 2 -> Sort @@ -1379,8 +1381,8 @@ SELECT y, sum(x), avg(x), count(*) FROM pagg_tab_para GROUP BY y HAVING avg(x) < Sort Sort Key: pagg_tab_para_p1.y, (sum(pagg_tab_para_p1.x)), (avg(pagg_tab_para_p1.x)) -> Finalize GroupAggregate - Group Key: pagg_tab_para_p1.y Filter: (avg(pagg_tab_para_p1.x) < '12'::numeric) + Group Key: pagg_tab_para_p1.y -> Gather Merge Workers Planned: 2 -> Sort @@ -1417,8 +1419,8 @@ SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < Sort Sort Key: pagg_tab_para_p1.x, (sum(pagg_tab_para_p1.y)), (avg(pagg_tab_para_p1.y)) -> Finalize GroupAggregate - Group Key: pagg_tab_para_p1.x Filter: (avg(pagg_tab_para_p1.y) < '7'::numeric) + Group Key: pagg_tab_para_p1.x -> Gather Merge Workers Planned: 2 -> Sort @@ -1451,8 +1453,8 @@ SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < Sort Sort Key: pagg_tab_para_p1.x, (sum(pagg_tab_para_p1.y)), (avg(pagg_tab_para_p1.y)) -> Finalize GroupAggregate - Group Key: pagg_tab_para_p1.x Filter: (avg(pagg_tab_para_p1.y) < '7'::numeric) + Group Key: pagg_tab_para_p1.x -> Gather Merge Workers Planned: 2 -> Sort @@ -1487,16 +1489,16 @@ SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < Sort Key: pagg_tab_para_p1.x, (sum(pagg_tab_para_p1.y)), (avg(pagg_tab_para_p1.y)) -> Append -> HashAggregate - Group Key: pagg_tab_para_p1.x Filter: (avg(pagg_tab_para_p1.y) < '7'::numeric) + Group Key: pagg_tab_para_p1.x -> Seq Scan on pagg_tab_para_p1 -> HashAggregate - Group Key: pagg_tab_para_p2.x Filter: (avg(pagg_tab_para_p2.y) < '7'::numeric) + Group Key: pagg_tab_para_p2.x -> Seq Scan on pagg_tab_para_p2 -> HashAggregate - Group Key: pagg_tab_para_p3.x Filter: (avg(pagg_tab_para_p3.y) < '7'::numeric) + Group Key: pagg_tab_para_p3.x -> Seq Scan on pagg_tab_para_p3 (15 rows) diff --git a/src/test/regress/expected/select_distinct.out b/src/test/regress/expected/select_distinct.out index fc93b33ee2b..e8e14292452 100644 --- a/src/test/regress/expected/select_distinct.out +++ b/src/test/regress/expected/select_distinct.out @@ -134,12 +134,16 @@ SELECT count(*) FROM --------------------------------------------------------- Aggregate Project: count(*) + Phase 1 using strategy "All": + Transition Function: int8inc(TRANS) + All Group -> HashAggregate Project: tenk1.two, tenk1.four, tenk1.two - Group Key: tenk1.two, tenk1.four, tenk1.two + Phase 0 using strategy "Hash": + Hash Group: tenk1.two, tenk1.four, tenk1.two -> Seq Scan on public.tenk1 Project: tenk1.two, tenk1.four, tenk1.two -(7 rows) +(11 rows) SELECT count(*) FROM (SELECT DISTINCT two, four, two FROM tenk1) ss; diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out index 3c03b171707..f561e41f6f3 100644 --- a/src/test/regress/expected/select_parallel.out +++ b/src/test/regress/expected/select_parallel.out @@ -966,6 +966,9 @@ explain (costs off, verbose) ---------------------------------------------------------------------------------------------- Aggregate Project: count(*) + Phase 1 using strategy "All": + Transition Function: int8inc(TRANS) + All Group -> Hash Semi Join Hash Cond: ((a.unique1 = b.unique1) AND (a.two = (row_number() OVER (?)))) -> Gather @@ -982,7 +985,7 @@ explain (costs off, verbose) Workers Planned: 4 -> Parallel Index Only Scan using tenk1_unique1 on public.tenk1 b Output: b.unique1 -(18 rows) +(21 rows) -- LIMIT/OFFSET within sub-selects can't be pushed to workers. explain (costs off) diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index 90fe9fe9802..a51086a0254 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -979,10 +979,11 @@ select * from int4_tbl o where (f1, f1) in Output: generate_series(1, 50), i.f1 -> HashAggregate Project: i.f1 - Group Key: i.f1 + Phase 0 using strategy "Hash": + Hash Group: i.f1 -> Seq Scan on public.int4_tbl i Output: i.f1 -(19 rows) +(20 rows) select * from int4_tbl o where (f1, f1) in (select f1, generate_series(1,50) / 10 g from int4_tbl i group by f1); -- 2.23.0.385.gbc12974a89