This is an automated email from the ASF dual-hosted git repository.
maxyang pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
The following commit(s) were added to refs/heads/main by this push:
new 17a6d1c5a57 Fix: wrong aggno and aggtransno generated in ORCA
17a6d1c5a57 is described below
commit 17a6d1c5a572dacdafb376fb8abc42410bdb7423
Author: zhoujiaqi <[email protected]>
AuthorDate: Wed Jun 18 16:56:18 2025 +0800
Fix: wrong aggno and aggtransno generated in ORCA
This issue caused `AggRef` in the `targetlist` no longer share
the same `aggno` and `aggtransno`, which means AggRef would be
calculated repeatedly.
ex.
```
select
sum(a) <- aggref1
from
any_table
having
sum(a) < 100; <- won't use the result of aggref1 to do the
filter.
```
This problem was introduced when cloudberry cherry-picked PG14.
But after cloudberry was open-sourced, the history of the changes
had been merged, and this problem only exists in ORCA.
In PG14, it no longer computes shared AggRef in `ExecInitAgg`,
but moved the logic into the planner. However in ORCA, it only
uses `idx++` to generate aggno and aggtransno. I do think that
is a workaround logic.
The current commit fixes this behavior by adopting the same
logic as the planner. But it's worth noting that, in multi-stage
agg, the final agg often gets an outer reference `Var` in AggRef,
so for the final agg, it still cannot get the same aggno (this
logic is consistent with greenplum; in the TPCDS testing, even i
generating the same aggno for final agg, it didn't bring performance
improvement). But if necessary, we could consider moving the logic
from `dxltoplstmt` to the pre-processor, which could also reduce
some output columns.
---
src/backend/commands/explain.c | 40 +++++
src/backend/gpopt/gpdbwrappers.cpp | 134 +++++++++++++++
.../gpopt/translate/CContextDXLToPlStmt.cpp | 45 ++++-
.../gpopt/translate/CTranslatorDXLToPlStmt.cpp | 189 ++++++++++++++++-----
src/backend/optimizer/prep/prepagg.c | 26 +--
src/backend/utils/misc/guc.c | 10 ++
src/include/gpopt/gpdbwrappers.h | 23 +++
src/include/gpopt/translate/CContextDXLToPlStmt.h | 17 ++
.../gpopt/translate/CTranslatorDXLToPlStmt.h | 3 +
src/include/optimizer/prep.h | 8 +
src/include/utils/guc.h | 1 +
src/include/utils/unsync_guc_name.h | 1 +
src/test/regress/expected/aggregates.out | 121 +++++++++++++
src/test/regress/expected/aggregates_optimizer.out | 121 +++++++++++++
src/test/regress/sql/aggregates.sql | 77 +++++++++
15 files changed, 757 insertions(+), 59 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index c89d088c4a2..e87fb30e7c4 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -134,6 +134,7 @@ static void show_tablesample(TableSampleClause *tsc,
PlanState *planstate,
List *ancestors,
ExplainState *es);
static void show_sort_info(SortState *sortstate, ExplainState *es);
static void show_windowagg_keys(WindowAggState *waggstate, List *ancestors,
ExplainState *es);
+static void show_aggref_info(List *targetList, const char *qlabel,
ExplainState *es);
static void show_incremental_sort_info(IncrementalSortState *incrsortstate,
ExplainState *es);
static void show_hash_info(HashState *hashstate, ExplainState *es);
@@ -2737,6 +2738,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
case T_Agg:
show_agg_keys(castNode(AggState, planstate), ancestors,
es);
show_upper_qual(plan->qual, "Filter", planstate,
ancestors, es);
+ show_aggref_info(plan->targetlist,
"AggRefs(TargetList)", es);
show_hashagg_info((AggState *) planstate, es);
if (plan->qual)
show_instrumentation_count("Rows Removed by
Filter", 1,
@@ -3168,6 +3170,44 @@ show_sort_keys(SortState *sortstate, List *ancestors,
ExplainState *es)
ancestors, es);
}
+static void
+show_aggref_info(List *targetList, const char *qlabel, ExplainState *es) {
+ StringInfoData buf;
+ ListCell *lc;
+ bool already_got_one = false;
+
+ if (!Debug_print_aggref_in_explain) {
+ return;
+ }
+
+ initStringInfo(&buf);
+ appendStringInfoString(&buf, "[");
+
+ foreach (lc, targetList)
+ {
+ if (!IsA(lfirst(lc),TargetEntry)) {
+ continue;
+ }
+
+ TargetEntry *te = (TargetEntry *) lfirst(lc);
+ if (!IsA(te->expr, Aggref))
+ {
+ continue;
+ }
+
+ Aggref *aggref = (Aggref *) te->expr;
+ if (already_got_one) {
+ appendStringInfo(&buf, ", (%d, %d)", aggref->aggno,
aggref->aggtransno);
+ } else {
+ appendStringInfo(&buf, "(%d, %d)", aggref->aggno,
aggref->aggtransno);
+ already_got_one = true;
+ }
+ }
+ appendStringInfoString(&buf, "]");
+
+ ExplainPropertyText(qlabel, buf.data, es);
+}
+
static void
show_windowagg_keys(WindowAggState *waggstate, List *ancestors, ExplainState
*es)
{
diff --git a/src/backend/gpopt/gpdbwrappers.cpp
b/src/backend/gpopt/gpdbwrappers.cpp
index 3eed0ed1e40..bbc686c3e37 100644
--- a/src/backend/gpopt/gpdbwrappers.cpp
+++ b/src/backend/gpopt/gpdbwrappers.cpp
@@ -36,12 +36,14 @@ extern "C" {
#include "access/amapi.h"
#include "access/external.h"
#include "access/genam.h"
+#include "catalog/pg_aggregate.h"
#include "catalog/pg_inherits.h"
#include "foreign/fdwapi.h"
#include "nodes/nodeFuncs.h"
#include "optimizer/clauses.h"
#include "optimizer/optimizer.h"
#include "optimizer/plancat.h"
+#include "optimizer/prep.h"
#include "optimizer/subselect.h"
#include "parser/parse_agg.h"
#include "partitioning/partdesc.h"
@@ -490,6 +492,15 @@ gpdb::TypeCollation(Oid type)
return 0;
}
+void
+gpdb::TypLenByVal(Oid typid, int16 *typlen, bool *typbyval)
+{
+ GP_WRAP_START;
+ {
+ get_typlenbyval(typid, typlen, typbyval);
+ }
+ GP_WRAP_END;
+}
List *
gpdb::ExtractNodesPlan(Plan *pl, int node_tag, bool descend_into_subqueries)
@@ -661,6 +672,129 @@ gpdb::ResolveAggregateTransType(Oid aggfnoid, Oid
aggtranstype, Oid *inputTypes,
return 0;
}
+static Datum
+GetAggInitVal(Datum textInitVal, Oid transtype)
+{
+ Oid typinput,
+ typioparam;
+ char *strInitVal;
+ Datum initVal;
+
+ getTypeInputInfo(transtype, &typinput, &typioparam);
+ strInitVal = TextDatumGetCString(textInitVal);
+ initVal = OidInputFunctionCall(typinput, strInitVal,
+ typioparam,
-1);
+ pfree(strInitVal);
+ return initVal;
+}
+
+void
+gpdb::GetAggregateInfo(Aggref *aggref, Oid *aggtransfn,
+ Oid *aggfinalfn, Oid *aggcombinefn,
+ Oid *aggserialfn, Oid *aggdeserialfn,
+ Oid *aggtranstype, int
*aggtransspace,
+ Datum *initValue, bool
*initValueIsNull,
+ bool *shareable)
+{
+ GP_WRAP_START;
+ {
+ HeapTuple aggTuple;
+ Form_pg_aggregate aggform;
+ Datum textInitVal;
+ Oid inputTypes[FUNC_MAX_ARGS];
+ int numArguments;
+
+ aggTuple = SearchSysCache1(AGGFNOID,
+
ObjectIdGetDatum(aggref->aggfnoid));
+ if (!HeapTupleIsValid(aggTuple))
+ elog(ERROR, "cache lookup failed for aggregate %u",
+ aggref->aggfnoid);
+
+ aggform = (Form_pg_aggregate) GETSTRUCT(aggTuple);
+ *aggtransfn = aggform->aggtransfn;
+ *aggfinalfn = aggform->aggfinalfn;
+ *aggcombinefn = aggform->aggcombinefn;
+ *aggserialfn = aggform->aggserialfn;
+ *aggdeserialfn = aggform->aggdeserialfn;
+ *aggtranstype = aggform->aggtranstype;
+ *aggtransspace = aggform->aggtransspace;
+
+ (aggform->aggfinalmodify != AGGMODIFY_READ_WRITE);
+
+
+ /*
+ * Resolve the possibly-polymorphic aggregate transition type.
+ */
+ /* extract argument types (ignoring any ORDER BY expressions) */
+ numArguments = get_aggregate_argtypes(aggref, inputTypes);
+
+ /* resolve actual type of transition state, if polymorphic */
+ *aggtranstype = resolve_aggregate_transtype(aggref->aggfnoid,
+
*aggtranstype,
+
inputTypes,
+
numArguments);
+
+ /* get initial value */
+ textInitVal = SysCacheGetAttr(AGGFNOID, aggTuple,
+
Anum_pg_aggregate_agginitval,
+
initValueIsNull);
+
+
+ if (*initValueIsNull)
+ *initValue = (Datum) 0;
+ else
+ *initValue = GetAggInitVal(textInitVal, *aggtranstype);
+
+ /*
+ * If finalfn is marked read-write, we can't share transition
states; but
+ * it is okay to share states for AGGMODIFY_SHAREABLE aggs.
+ *
+ * In principle, in a partial aggregate, we could share the
transition
+ * state even if the final function is marked as read-write,
because the
+ * partial aggregate doesn't execute the final function. But
it's too
+ * early to know whether we're going perform a partial
aggregate.
+ */
+ *shareable = (aggform->aggfinalmodify != AGGMODIFY_READ_WRITE);
+
+ ReleaseSysCache(aggTuple);
+
+ }
+ GP_WRAP_END;
+}
+
+
+int
+gpdb::FindCompatibleAgg(List *agginfos, Aggref *newagg,
+ List **same_input_transnos)
+{
+
+ GP_WRAP_START;
+ {
+ return find_compatible_agg(agginfos, newagg,
same_input_transnos);
+ }
+ GP_WRAP_END;
+ return -1;
+}
+
+int
+gpdb::FindCompatibleTrans(List *aggtransinfos, bool shareable,
+ Oid aggtransfn, Oid
aggtranstype,
+ int transtypeLen, bool
transtypeByVal,
+ Oid aggcombinefn, Oid
aggserialfn,
+ Oid aggdeserialfn, Datum
initValue,
+ bool initValueIsNull, List
*transnos)
+{
+ GP_WRAP_START;
+ {
+ return find_compatible_trans(aggtransinfos, shareable,
aggtransfn,
+ aggtranstype, transtypeLen, transtypeByVal,
aggcombinefn, aggserialfn,
+ aggdeserialfn, initValue, initValueIsNull, transnos);
+ }
+ GP_WRAP_END;
+ return -1;
+}
+
+
Query *
gpdb::FlattenJoinAliasVar(Query *query, gpos::ULONG query_level)
{
diff --git a/src/backend/gpopt/translate/CContextDXLToPlStmt.cpp
b/src/backend/gpopt/translate/CContextDXLToPlStmt.cpp
index 376810a27f1..44b84abd29c 100644
--- a/src/backend/gpopt/translate/CContextDXLToPlStmt.cpp
+++ b/src/backend/gpopt/translate/CContextDXLToPlStmt.cpp
@@ -56,7 +56,9 @@ CContextDXLToPlStmt::CContextDXLToPlStmt(
m_slices_list(nullptr),
m_result_relation_index(0),
m_distribution_policy(nullptr),
- m_part_selector_to_param_map(nullptr)
+ m_part_selector_to_param_map(nullptr),
+ m_agg_infos(nullptr),
+ m_agg_trans_infos(nullptr)
{
m_cte_producer_info = GPOS_NEW(m_mp) HMUlCTEProducerInfo(m_mp);
m_part_selector_to_param_map = GPOS_NEW(m_mp) UlongToUlongMap(m_mp);
@@ -159,8 +161,9 @@ CContextDXLToPlStmt::GetParamTypes()
//
//---------------------------------------------------------------------------
void
-CContextDXLToPlStmt::RegisterCTEProducerInfo(ULONG cte_id,
- ULongPtrArray *producer_output_colidx_map, ShareInputScan *siscan)
+CContextDXLToPlStmt::RegisterCTEProducerInfo(
+ ULONG cte_id, ULongPtrArray *producer_output_colidx_map,
+ ShareInputScan *siscan)
{
ULONG *key = GPOS_NEW(m_mp) ULONG(cte_id);
BOOL result GPOS_ASSERTS_ONLY = m_cte_producer_info->Insert(
@@ -575,4 +578,40 @@ CContextDXLToPlStmt::GetRTEIndexByAssignedQueryId(
return gpdb::ListLength(m_rtable_entries_list) + 1;
}
+void
+CContextDXLToPlStmt::AppendAggInfos(AggInfo *agginfo)
+{
+ m_agg_infos = gpdb::LAppend(m_agg_infos, agginfo);
+}
+
+void
+CContextDXLToPlStmt::AppendAggTransInfos(AggTransInfo *transinfo)
+{
+ m_agg_trans_infos = gpdb::LAppend(m_agg_trans_infos, transinfo);
+}
+
+void
+CContextDXLToPlStmt::ResetAggInfosAndTransInfos()
+{
+ ListCell *lc;
+ foreach (lc, m_agg_infos)
+ {
+ AggInfo *agginfo = (AggInfo *) lfirst(lc);
+ gpdb::GPDBFree(agginfo);
+ }
+
+ gpdb::ListFree(m_agg_infos);
+
+ foreach (lc, m_agg_trans_infos)
+ {
+ AggTransInfo *aggtransinfo = (AggTransInfo *) lfirst(lc);
+ gpdb::GPDBFree(aggtransinfo);
+ }
+
+ gpdb::ListFree(m_agg_trans_infos);
+
+ m_agg_infos = nullptr;
+ m_agg_trans_infos = nullptr;
+}
+
// EOF
diff --git a/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp
b/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp
index 3e94e8d77f0..8aa1984badd 100644
--- a/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp
+++ b/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp
@@ -2765,6 +2765,124 @@
CTranslatorDXLToPlStmt::TranslateDXLRedistributeMotionToResultHashFilters(
return (Plan *) result;
}
+
+//---------------------------------------------------------------------------
+// @function:
+// CTranslatorDXLToPlStmt::TranslateAggFillInfo
+//
+// @doc:
+// Fill the aggregate node with aggno and aggtransno
+//
+//---------------------------------------------------------------------------
+void
+CTranslatorDXLToPlStmt::TranslateAggFillInfo(CContextDXLToPlStmt *ctx,
+
Aggref *aggref)
+{
+ Oid aggtransfn;
+ Oid aggfinalfn;
+ Oid aggcombinefn;
+ Oid aggserialfn;
+ Oid aggdeserialfn;
+ Oid aggtranstype;
+ int32 aggtranstypmod;
+ int32 aggtransspace;
+
+ Datum initValue;
+ bool initValueIsNull;
+ List *same_input_transnos;
+
+ bool shareable;
+ int16 resulttypeLen;
+ bool resulttypeByVal;
+ int16 transtypeLen;
+ bool transtypeByVal;
+
+ int aggno, transno;
+
+ gpdb::GetAggregateInfo(aggref, &aggtransfn, &aggfinalfn,
+ &aggcombinefn, &aggserialfn,
&aggdeserialfn,
+ &aggtranstype,
&aggtransspace, &initValue,
+ &initValueIsNull,
&shareable);
+
+ /*
+ * If transition state is of same type as first aggregated input, assume
+ * it's the same typmod (same width) as well. This works for cases like
+ * MAX/MIN and is probably somewhat reasonable otherwise.
+ */
+ aggtranstypmod = -1;
+ if (aggref->args)
+ {
+ TargetEntry *tle = (TargetEntry *) linitial(aggref->args);
+
+ if (aggtranstype == gpdb::ExprType((Node *) tle->expr))
+ aggtranstypmod = gpdb::ExprTypeMod((Node *) tle->expr);
+ }
+
+ gpdb::TypLenByVal(aggref->aggtype, &resulttypeLen, &resulttypeByVal);
+
+ /*
+ * 1. See if this is identical to another aggregate function call that
+ * we've seen already.
+ */
+ aggno = gpdb::FindCompatibleAgg(ctx->GetAggInfos(), aggref,
+
&same_input_transnos);
+ if (aggno != -1)
+ {
+ AggInfo *agginfo = (AggInfo *)
gpdb::ListNth(ctx->GetAggInfos(), aggno);
+
+ transno = agginfo->transno;
+ }
+ else
+ {
+ AggInfo *agginfo = (AggInfo *) gpdb::GPDBAlloc(sizeof(AggInfo));
+
+ agginfo->finalfn_oid = aggfinalfn;
+ agginfo->representative_aggref = aggref;
+ agginfo->shareable = shareable;
+
+ aggno = gpdb::ListLength(ctx->GetAggInfos());
+ ctx->AppendAggInfos(agginfo);
+
+ gpdb::TypLenByVal(aggtranstype, &transtypeLen, &transtypeByVal);
+
+ /*
+ * 2. See if this aggregate can share transition state with
another
+ * aggregate that we've initialized already.
+ */
+ transno = gpdb::FindCompatibleTrans(
+ ctx->GetAggTransInfos(), shareable, aggtransfn,
aggtranstype,
+ transtypeLen, transtypeByVal, aggcombinefn, aggserialfn,
+ aggdeserialfn, initValue, initValueIsNull,
same_input_transnos);
+ if (transno == -1)
+ {
+ AggTransInfo *transinfo =
+ (AggTransInfo *)
gpdb::GPDBAlloc(sizeof(AggTransInfo));
+
+ transinfo->args = aggref->args;
+ transinfo->aggfilter = aggref->aggfilter;
+ transinfo->transfn_oid = aggtransfn;
+ transinfo->combinefn_oid = aggcombinefn;
+ transinfo->serialfn_oid = aggserialfn;
+ transinfo->deserialfn_oid = aggdeserialfn;
+ transinfo->aggtranstype = aggtranstype;
+ transinfo->aggtranstypmod = aggtranstypmod;
+ transinfo->transtypeLen = transtypeLen;
+ transinfo->transtypeByVal = transtypeByVal;
+ transinfo->aggtransspace = aggtransspace;
+ transinfo->initValue = initValue;
+ transinfo->initValueIsNull = initValueIsNull;
+
+ transno = gpdb::ListLength(ctx->GetAggTransInfos());
+ ctx->AppendAggTransInfos(transinfo);
+ }
+ agginfo->transno = transno;
+ }
+
+ // setting the aggno and transno
+ aggref->aggno = aggno;
+ aggref->aggtransno = transno;
+}
+
//---------------------------------------------------------------------------
// @function:
// CTranslatorDXLToPlStmt::TranslateDXLAgg
@@ -2812,44 +2930,6 @@ CTranslatorDXLToPlStmt::TranslateDXLAgg(
child_contexts,
// pdxltrctxRight,
&plan->targetlist,
&plan->qual, output_context);
- /*
- * GPDB_14_MERGE_FIXME: TODO Deduplicate aggregates and transition
functions in orca
- */
- // Set the aggsplit for the agg node
- ListCell *lc;
- INT aggsplit = 0;
- int idx = 0;
- ForEach (lc, plan->targetlist)
- {
- TargetEntry *te = (TargetEntry *) lfirst(lc);
- if (IsA(te->expr, Aggref))
- {
- Aggref *aggref = (Aggref *) te->expr;
-
- if (AGGSPLIT_INTERMEDIATE != aggsplit)
- {
- aggsplit |= aggref->aggsplit;
- }
-
- aggref->aggno = idx;
- aggref->aggtransno = idx;
- idx++;
- }
- }
- agg->aggsplit = (AggSplit) aggsplit;
-
- ForEach (lc, plan->qual)
- {
- Expr *expr = (Expr *) lfirst(lc);
- if (IsA(expr, Aggref))
- {
- Aggref *aggref = (Aggref *) expr;
- aggref->aggno = idx;
- aggref->aggtransno = idx;
- idx++;
- }
- }
-
plan->lefttree = child_plan;
// translate aggregation strategy
@@ -2919,6 +2999,39 @@ CTranslatorDXLToPlStmt::TranslateDXLAgg(
agg->numGroups =
std::max(1L, (long) std::min(agg->plan.plan_rows, (double)
LONG_MAX));
+
+ // Set the aggsplit,aggno,aggtransno for the agg node
+ ListCell *lc;
+ INT aggsplit = 0;
+ ForEach (lc, plan->targetlist)
+ {
+ TargetEntry *te = (TargetEntry *) lfirst(lc);
+ if (IsA(te->expr, Aggref))
+ {
+ Aggref *aggref = (Aggref *) te->expr;
+
+ if (AGGSPLIT_INTERMEDIATE != aggsplit)
+ {
+ aggsplit |= aggref->aggsplit;
+ }
+ TranslateAggFillInfo(m_dxl_to_plstmt_context, aggref);
+ }
+ }
+ agg->aggsplit = (AggSplit) aggsplit;
+
+ ForEach (lc, plan->qual)
+ {
+ Expr *expr = (Expr *) lfirst(lc);
+ if (IsA(expr, Aggref))
+ {
+ Aggref *aggref = (Aggref *) expr;
+ // ORCA won't create the qual but a scalar in AGG
+ TranslateAggFillInfo(m_dxl_to_plstmt_context, aggref);
+ }
+ }
+
+ m_dxl_to_plstmt_context->ResetAggInfosAndTransInfos();
+
SetParamIds(plan);
// cleanup
diff --git a/src/backend/optimizer/prep/prepagg.c
b/src/backend/optimizer/prep/prepagg.c
index 5388b1c4934..b16e4adb220 100644
--- a/src/backend/optimizer/prep/prepagg.c
+++ b/src/backend/optimizer/prep/prepagg.c
@@ -53,16 +53,6 @@
#include "utils/syscache.h"
static bool preprocess_aggrefs_walker(Node *node, PlannerInfo *root);
-static int find_compatible_agg(PlannerInfo *root, Aggref *newagg,
- List
**same_input_transnos);
-static int find_compatible_trans(PlannerInfo *root, Aggref *newagg,
- bool
shareable,
- Oid
aggtransfn, Oid aggtranstype,
- int
transtypeLen, bool transtypeByVal,
- Oid
aggcombinefn,
- Oid
aggserialfn, Oid aggdeserialfn,
- Datum
initValue, bool initValueIsNull,
- List
*transnos);
static Datum GetAggInitVal(Datum textInitVal, Oid transtype);
/* -----------------
@@ -220,7 +210,7 @@ preprocess_aggref(Aggref *aggref, PlannerInfo *root)
* 1. See if this is identical to another aggregate function call that
* we've seen already.
*/
- aggno = find_compatible_agg(root, aggref, &same_input_transnos);
+ aggno = find_compatible_agg(root->agginfos, aggref,
&same_input_transnos);
if (aggno != -1)
{
AggInfo *agginfo = list_nth(root->agginfos, aggno);
@@ -266,7 +256,7 @@ preprocess_aggref(Aggref *aggref, PlannerInfo *root)
* 2. See if this aggregate can share transition state with
another
* aggregate that we've initialized already.
*/
- transno = find_compatible_trans(root, aggref, shareable,
+ transno = find_compatible_trans(root->aggtransinfos, shareable,
aggtransfn, aggtranstype,
transtypeLen, transtypeByVal,
aggcombinefn,
@@ -367,8 +357,8 @@ preprocess_aggrefs_walker(Node *node, PlannerInfo *root)
* passed later to find_compatible_trans, to see if we can at least reuse
* the state value of another aggregate.
*/
-static int
-find_compatible_agg(PlannerInfo *root, Aggref *newagg,
+int
+find_compatible_agg(List *agginfos, Aggref *newagg,
List **same_input_transnos)
{
ListCell *lc;
@@ -390,7 +380,7 @@ find_compatible_agg(PlannerInfo *root, Aggref *newagg,
* same transition function will be checked later.)
*/
aggno = -1;
- foreach(lc, root->agginfos)
+ foreach(lc, agginfos)
{
AggInfo *agginfo = (AggInfo *) lfirst(lc);
Aggref *existingRef;
@@ -445,8 +435,8 @@ find_compatible_agg(PlannerInfo *root, Aggref *newagg,
* transition function and initial condition. (The inputs have already been
* verified to match.)
*/
-static int
-find_compatible_trans(PlannerInfo *root, Aggref *newagg, bool shareable,
+int
+find_compatible_trans(List *aggtransinfos, bool shareable,
Oid aggtransfn, Oid aggtranstype,
int transtypeLen, bool transtypeByVal,
Oid aggcombinefn,
@@ -463,7 +453,7 @@ find_compatible_trans(PlannerInfo *root, Aggref *newagg,
bool shareable,
foreach(lc, transnos)
{
int transno = lfirst_int(lc);
- AggTransInfo *pertrans = (AggTransInfo *)
list_nth(root->aggtransinfos, transno);
+ AggTransInfo *pertrans = (AggTransInfo *)
list_nth(aggtransinfos, transno);
/*
* if the transfns or transition state types are not the same
then the
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 626eeb7aa70..fae7f8a0e5c 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -582,6 +582,7 @@ bool Debug_print_parse = false;
bool Debug_print_rewritten = false;
bool Debug_pretty_print = true;
bool Debug_print_ivm = false;
+bool Debug_print_aggref_in_explain = false;
bool log_parser_stats = false;
bool log_planner_stats = false;
@@ -1545,6 +1546,15 @@ static struct config_bool ConfigureNamesBool[] =
false,
NULL, NULL, NULL
},
+ {
+ {"debug_print_aggref_in_explain", PGC_USERSET, LOGGING_WHAT,
+ gettext_noop("Logs the aggnos/aggtransno in explain."),
+ NULL
+ },
+ &Debug_print_aggref_in_explain,
+ false,
+ NULL, NULL, NULL
+ },
{
{"log_parser_stats", PGC_SUSET, STATS_MONITORING,
gettext_noop("Writes parser performance statistics to
the server log."),
diff --git a/src/include/gpopt/gpdbwrappers.h b/src/include/gpopt/gpdbwrappers.h
index cb75b2d493c..39e294db8fa 100644
--- a/src/include/gpopt/gpdbwrappers.h
+++ b/src/include/gpopt/gpdbwrappers.h
@@ -166,6 +166,9 @@ Oid ExprCollation(Node *expr);
// expression collation - GPDB_91_MERGE_FIXME
Oid TypeCollation(Oid type);
+// Byval type get len
+void TypLenByVal(Oid typid, int16 *typlen, bool *typbyval);
+
// extract nodes with specific tag from a plan tree
List *ExtractNodesPlan(Plan *pl, int node_tag, bool descend_into_subqueries);
@@ -183,6 +186,26 @@ int GetAggregateArgTypes(Aggref *aggref, Oid *inputTypes);
Oid ResolveAggregateTransType(Oid aggfnoid, Oid aggtranstype, Oid *inputTypes,
int numArguments);
+// intermediate result of given aggregate
+void GetAggregateInfo(Aggref *aggref, Oid *aggtransfn,
+ Oid *aggfinalfn, Oid *aggcombinefn,
+ Oid *aggserialfn, Oid *aggdeserialfn,
+ Oid *aggtranstype, int *aggtransspace,
+ Datum *initValue, bool
*initValueIsNull,
+ bool *shareable);
+
+int
+FindCompatibleAgg(List *agginfos, Aggref *newagg,
+ List **same_input_transnos);
+int
+FindCompatibleTrans(List *aggtransinfos, bool shareable,
+ Oid aggtransfn, Oid aggtranstype,
+ int transtypeLen, bool transtypeByVal,
+ Oid aggcombinefn, Oid aggserialfn,
+ Oid aggdeserialfn, Datum initValue,
+ bool initValueIsNull, List *transnos);
+
+
// replace Vars that reference JOIN outputs with references to the original
// relation variables instead
Query *FlattenJoinAliasVar(Query *query, gpos::ULONG query_level);
diff --git a/src/include/gpopt/translate/CContextDXLToPlStmt.h
b/src/include/gpopt/translate/CContextDXLToPlStmt.h
index 29e9eb4401b..3a1be11d196 100644
--- a/src/include/gpopt/translate/CContextDXLToPlStmt.h
+++ b/src/include/gpopt/translate/CContextDXLToPlStmt.h
@@ -127,6 +127,9 @@ private:
// hash map of the queryid (of DML query) and the target relation index
HMUlIndex *m_used_rte_indexes;
+ // the aggno and aggtransno in agg
+ List *m_agg_infos; /* AggInfo structs */
+ List *m_agg_trans_infos; /* AggTransInfo structs */
public:
// ctor/dtor
CContextDXLToPlStmt(CMemoryPool *mp, CIdGenerator *plan_id_counter,
@@ -233,6 +236,20 @@ public:
Index GetRTEIndexByAssignedQueryId(ULONG
assigned_query_id_for_target_rel,
BOOL
*is_rte_exists);
+ // List of AggInfo and AggTransInfo
+ inline List *GetAggInfos() const
+ {
+ return m_agg_infos;
+ }
+
+ inline List *GetAggTransInfos() const
+ {
+ return m_agg_trans_infos;
+ }
+
+ void AppendAggInfos(AggInfo *agginfo);
+ void AppendAggTransInfos(AggTransInfo *transinfo);
+ void ResetAggInfosAndTransInfos();
};
} // namespace gpdxl
diff --git a/src/include/gpopt/translate/CTranslatorDXLToPlStmt.h
b/src/include/gpopt/translate/CTranslatorDXLToPlStmt.h
index 0507dc05105..b2cb6104fb2 100644
--- a/src/include/gpopt/translate/CTranslatorDXLToPlStmt.h
+++ b/src/include/gpopt/translate/CTranslatorDXLToPlStmt.h
@@ -647,6 +647,9 @@ private:
CDXLTranslationContextArray *ctxt_translation_prev_siblings,
CDXLTranslateContext *output_context,
CDXLNode *index_cond_list_dxlnode);
+
+ // fill the aggno and transno for the aggnode
+ static void TranslateAggFillInfo(CContextDXLToPlStmt *context, Aggref
*aggref);
};
} // namespace gpdxl
diff --git a/src/include/optimizer/prep.h b/src/include/optimizer/prep.h
index ca57e87b367..6dcf03f9234 100644
--- a/src/include/optimizer/prep.h
+++ b/src/include/optimizer/prep.h
@@ -54,6 +54,14 @@ extern void compute_agg_clause_costs(PlannerInfo *root,
AggSplit aggsplit,
AggClauseCosts
*agg_costs);
extern void preprocess_aggrefs(PlannerInfo *root, Node *clause);
+extern int find_compatible_agg(List *agginfos, Aggref *newagg, List
**same_input_transnos);
+extern int find_compatible_trans(List *aggtransinfos, bool shareable,
+ Oid
aggtransfn, Oid aggtranstype,
+ int
transtypeLen, bool transtypeByVal,
+ Oid
aggcombinefn,
+ Oid
aggserialfn, Oid aggdeserialfn,
+ Datum
initValue, bool initValueIsNull,
+ List
*transnos);
/*
* prototypes for prepunion.c
*/
diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h
index 30a9b3d3122..7c46011c2f2 100644
--- a/src/include/utils/guc.h
+++ b/src/include/utils/guc.h
@@ -261,6 +261,7 @@ extern bool Debug_print_parse;
extern bool Debug_print_rewritten;
extern bool Debug_pretty_print;
extern bool Debug_print_ivm;
+extern bool Debug_print_aggref_in_explain;
extern bool Debug_print_full_dtm;
extern bool Debug_print_snapshot_dtm;
diff --git a/src/include/utils/unsync_guc_name.h
b/src/include/utils/unsync_guc_name.h
index 959d2a89a73..7fc09fb45f3 100644
--- a/src/include/utils/unsync_guc_name.h
+++ b/src/include/utils/unsync_guc_name.h
@@ -89,6 +89,7 @@
"debug_print_slice_table",
"debug_print_snapshot_dtm",
"debug_print_ivm",
+ "debug_print_aggref_in_explain",
"debug_resource_group",
"debug_walrepl_rcv",
"debug_walrepl_snd",
diff --git a/src/test/regress/expected/aggregates.out
b/src/test/regress/expected/aggregates.out
index fadb82728f1..f81478dabd4 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -3279,3 +3279,124 @@ explain analyze select count(*) from pg_class, (select
count(*) > 0 from (select
Execution Time: 0.302 ms
(10 rows)
+-- test aggno
+create table tgb1(v1 int, v2 int, v3 int);
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'v1'
as the Apache Cloudberry data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make
sure column(s) chosen are the optimal data distribution key to minimize skew.
+create table tgb2(v1 int, v2 int, v3 int);
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'v1'
as the Apache Cloudberry data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make
sure column(s) chosen are the optimal data distribution key to minimize skew.
+insert into tgb1
values(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3);
+insert into tgb2
values(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3);
+set debug_print_aggref_in_explain to on;
+explain select
+ sum(tgb1.v3 * tgb2.v3)
+from
+ tgb1,tgb2
+where
+ tgb1.v1 = tgb2.v1 group by tgb2.v2
+having sum(tgb1.v3 * tgb2.v3) > 100 and
+ sum(tgb1.v3 * tgb2.v3) > 101 and
+ sum(tgb1.v3 * tgb2.v3) < 1020 and
+ sum(tgb1.v3 * tgb2.v3) != 103 and
+ sum(tgb1.v3 * tgb2.v3) >= 104 and
+ avg(tgb1.v3 * tgb2.v3) != 11 and
+ avg(tgb1.v3 * tgb2.v3) != 12 and
+ avg(tgb2.v1 * tgb1.v1) != 13 and
+ sum(tgb1.v3) > 200;
+
QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=145986.91..145999.46 rows=4
width=12)
+ -> Finalize HashAggregate (cost=145986.91..145999.41 rows=1 width=12)
+ Group Key: tgb2.v2
+ Filter: ((sum((tgb1.v3 * tgb2.v3)) > 100) AND (sum((tgb1.v3 *
tgb2.v3)) > 101) AND (sum((tgb1.v3 * tgb2.v3)) < 1020) AND (sum((tgb1.v3 *
tgb2.v3)) <> 103) AND (sum((tgb1.v3 * tgb2.v3)) >= 104) AND (avg((tgb1.v3 *
tgb2.v3)) <> '11'::numeric) AND (avg((tgb1.v3 * tgb2.v3)) <> '12'::numeric) AND
(avg((tgb2.v1 * tgb1.v1)) <> '13'::numeric) AND (sum(tgb1.v3) > 200))
+ AggRefs(TargetList): [(0, 0)]
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=145944.41..145974.41 rows=1000 width=84)
+ Hash Key: tgb2.v2
+ -> Partial HashAggregate (cost=145944.41..145954.41 rows=1000
width=84)
+ Group Key: tgb2.v2
+ AggRefs(TargetList): [(0, 0), (1, 1), (2, 2), (3, 3)]
+ -> Hash Join (cost=618.25..105488.34 rows=2022803
width=20)
+ Hash Cond: (tgb1.v1 = tgb2.v1)
+ -> Seq Scan on tgb1 (cost=0.00..293.67 rows=25967
width=8)
+ -> Hash (cost=293.67..293.67 rows=25967 width=12)
+ -> Seq Scan on tgb2 (cost=0.00..293.67
rows=25967 width=12)
+ Optimizer: Postgres query optimizer
+(16 rows)
+
+select
+ sum(tgb1.v3 * tgb2.v3)
+from
+ tgb1,tgb2
+where
+ tgb1.v1 = tgb2.v1 group by tgb2.v2
+having sum(tgb1.v3 * tgb2.v3) > 100 and
+ sum(tgb1.v3 * tgb2.v3) > 101 and
+ sum(tgb1.v3 * tgb2.v3) < 1020 and
+ sum(tgb1.v3 * tgb2.v3) != 103 and
+ sum(tgb1.v3 * tgb2.v3) >= 104 and
+ avg(tgb1.v3 * tgb2.v3) != 11 and
+ avg(tgb1.v3 * tgb2.v3) != 12 and
+ avg(tgb2.v1 * tgb1.v1) != 13 and
+ sum(tgb1.v3) > 200;
+ sum
+-----
+(0 rows)
+
+set optimizer_force_multistage_agg to on;
+explain select
+ sum(tgb1.v3 * tgb2.v3)
+from
+ tgb1,tgb2
+where
+ tgb1.v1 = tgb2.v1 group by tgb2.v2
+having sum(tgb1.v3 * tgb2.v3) > 100 and
+ sum(tgb1.v3 * tgb2.v3) > 101 and
+ sum(tgb1.v3 * tgb2.v3) < 1020 and
+ sum(tgb1.v3 * tgb2.v3) != 103 and
+ sum(tgb1.v3 * tgb2.v3) >= 104 and
+ avg(tgb1.v3 * tgb2.v3) != 11 and
+ avg(tgb1.v3 * tgb2.v3) != 12 and
+ avg(tgb2.v1 * tgb1.v1) != 13 and
+ sum(tgb1.v3) > 200;
+
QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=145986.91..145999.46 rows=4
width=12)
+ -> Finalize HashAggregate (cost=145986.91..145999.41 rows=1 width=12)
+ Group Key: tgb2.v2
+ Filter: ((sum((tgb1.v3 * tgb2.v3)) > 100) AND (sum((tgb1.v3 *
tgb2.v3)) > 101) AND (sum((tgb1.v3 * tgb2.v3)) < 1020) AND (sum((tgb1.v3 *
tgb2.v3)) <> 103) AND (sum((tgb1.v3 * tgb2.v3)) >= 104) AND (avg((tgb1.v3 *
tgb2.v3)) <> '11'::numeric) AND (avg((tgb1.v3 * tgb2.v3)) <> '12'::numeric) AND
(avg((tgb2.v1 * tgb1.v1)) <> '13'::numeric) AND (sum(tgb1.v3) > 200))
+ AggRefs(TargetList): [(0, 0)]
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=145944.41..145974.41 rows=1000 width=84)
+ Hash Key: tgb2.v2
+ -> Partial HashAggregate (cost=145944.41..145954.41 rows=1000
width=84)
+ Group Key: tgb2.v2
+ AggRefs(TargetList): [(0, 0), (1, 1), (2, 2), (3, 3)]
+ -> Hash Join (cost=618.25..105488.34 rows=2022803
width=20)
+ Hash Cond: (tgb1.v1 = tgb2.v1)
+ -> Seq Scan on tgb1 (cost=0.00..293.67 rows=25967
width=8)
+ -> Hash (cost=293.67..293.67 rows=25967 width=12)
+ -> Seq Scan on tgb2 (cost=0.00..293.67
rows=25967 width=12)
+ Optimizer: Postgres query optimizer
+(16 rows)
+
+select
+ sum(tgb1.v3 * tgb2.v3)
+from
+ tgb1,tgb2
+where
+ tgb1.v1 = tgb2.v1 group by tgb2.v2
+having sum(tgb1.v3 * tgb2.v3) > 100 and
+ sum(tgb1.v3 * tgb2.v3) > 101 and
+ sum(tgb1.v3 * tgb2.v3) < 1020 and
+ sum(tgb1.v3 * tgb2.v3) != 103 and
+ sum(tgb1.v3 * tgb2.v3) >= 104 and
+ avg(tgb1.v3 * tgb2.v3) != 11 and
+ avg(tgb1.v3 * tgb2.v3) != 12 and
+ avg(tgb2.v1 * tgb1.v1) != 13 and
+ sum(tgb1.v3) > 200;
+ sum
+-----
+(0 rows)
+
+reset debug_print_aggref_in_explain;
+reset optimizer_force_multistage_agg;
diff --git a/src/test/regress/expected/aggregates_optimizer.out
b/src/test/regress/expected/aggregates_optimizer.out
index 86dd330de84..a840742448b 100644
--- a/src/test/regress/expected/aggregates_optimizer.out
+++ b/src/test/regress/expected/aggregates_optimizer.out
@@ -3448,3 +3448,124 @@ DETAIL: Falling back to Postgres-based planner because
GPORCA does not support
Execution Time: 0.198 ms
(11 rows)
+-- test aggno
+create table tgb1(v1 int, v2 int, v3 int);
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'v1'
as the Apache Cloudberry data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make
sure column(s) chosen are the optimal data distribution key to minimize skew.
+create table tgb2(v1 int, v2 int, v3 int);
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'v1'
as the Apache Cloudberry data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make
sure column(s) chosen are the optimal data distribution key to minimize skew.
+insert into tgb1
values(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3);
+insert into tgb2
values(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3);
+set debug_print_aggref_in_explain to on;
+explain select
+ sum(tgb1.v3 * tgb2.v3)
+from
+ tgb1,tgb2
+where
+ tgb1.v1 = tgb2.v1 group by tgb2.v2
+having sum(tgb1.v3 * tgb2.v3) > 100 and
+ sum(tgb1.v3 * tgb2.v3) > 101 and
+ sum(tgb1.v3 * tgb2.v3) < 1020 and
+ sum(tgb1.v3 * tgb2.v3) != 103 and
+ sum(tgb1.v3 * tgb2.v3) >= 104 and
+ avg(tgb1.v3 * tgb2.v3) != 11 and
+ avg(tgb1.v3 * tgb2.v3) != 12 and
+ avg(tgb2.v1 * tgb1.v1) != 13 and
+ sum(tgb1.v3) > 200;
+
QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..862.00 rows=1 width=8)
+ -> Result (cost=0.00..862.00 rows=1 width=8)
+ Filter: (((sum((tgb1.v3 * tgb2.v3))) > 100) AND ((sum((tgb1.v3 *
tgb2.v3))) > 101) AND ((sum((tgb1.v3 * tgb2.v3))) < 1020) AND ((sum((tgb1.v3 *
tgb2.v3))) <> 103) AND ((sum((tgb1.v3 * tgb2.v3))) >= 104) AND ((avg((tgb1.v3 *
tgb2.v3))) <> '11'::numeric) AND ((avg((tgb1.v3 * tgb2.v3))) <> '12'::numeric)
AND ((avg((tgb2.v1 * tgb1.v1))) <> '13'::numeric) AND ((sum(tgb1.v3)) > 200))
+ -> HashAggregate (cost=0.00..862.00 rows=1 width=80)
+ Group Key: tgb2.v2
+ AggRefs(TargetList): [(0, 0), (0, 0), (0, 0), (0, 0), (0, 0),
(0, 0), (1, 1), (1, 1), (2, 2), (3, 3)]
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.00..862.00 rows=1 width=20)
+ Hash Key: tgb2.v2
+ -> Hash Join (cost=0.00..862.00 rows=1 width=20)
+ Hash Cond: (tgb2.v1 = tgb1.v1)
+ -> Seq Scan on tgb2 (cost=0.00..431.00 rows=1
width=12)
+ -> Hash (cost=431.00..431.00 rows=1 width=8)
+ -> Seq Scan on tgb1 (cost=0.00..431.00
rows=1 width=8)
+ Optimizer: GPORCA
+(14 rows)
+
+select
+ sum(tgb1.v3 * tgb2.v3)
+from
+ tgb1,tgb2
+where
+ tgb1.v1 = tgb2.v1 group by tgb2.v2
+having sum(tgb1.v3 * tgb2.v3) > 100 and
+ sum(tgb1.v3 * tgb2.v3) > 101 and
+ sum(tgb1.v3 * tgb2.v3) < 1020 and
+ sum(tgb1.v3 * tgb2.v3) != 103 and
+ sum(tgb1.v3 * tgb2.v3) >= 104 and
+ avg(tgb1.v3 * tgb2.v3) != 11 and
+ avg(tgb1.v3 * tgb2.v3) != 12 and
+ avg(tgb2.v1 * tgb1.v1) != 13 and
+ sum(tgb1.v3) > 200;
+ sum
+-----
+(0 rows)
+
+set optimizer_force_multistage_agg to on;
+explain select
+ sum(tgb1.v3 * tgb2.v3)
+from
+ tgb1,tgb2
+where
+ tgb1.v1 = tgb2.v1 group by tgb2.v2
+having sum(tgb1.v3 * tgb2.v3) > 100 and
+ sum(tgb1.v3 * tgb2.v3) > 101 and
+ sum(tgb1.v3 * tgb2.v3) < 1020 and
+ sum(tgb1.v3 * tgb2.v3) != 103 and
+ sum(tgb1.v3 * tgb2.v3) >= 104 and
+ avg(tgb1.v3 * tgb2.v3) != 11 and
+ avg(tgb1.v3 * tgb2.v3) != 12 and
+ avg(tgb2.v1 * tgb1.v1) != 13 and
+ sum(tgb1.v3) > 200;
+
QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..862.00 rows=1 width=8)
+ -> Result (cost=0.00..862.00 rows=1 width=8)
+ Filter: (((sum((tgb1.v3 * tgb2.v3))) > 100) AND ((sum((tgb1.v3 *
tgb2.v3))) > 101) AND ((sum((tgb1.v3 * tgb2.v3))) < 1020) AND ((sum((tgb1.v3 *
tgb2.v3))) <> 103) AND ((sum((tgb1.v3 * tgb2.v3))) >= 104) AND ((avg((tgb1.v3 *
tgb2.v3))) <> '11'::numeric) AND ((avg((tgb1.v3 * tgb2.v3))) <> '12'::numeric)
AND ((avg((tgb2.v1 * tgb1.v1))) <> '13'::numeric) AND ((sum(tgb1.v3)) > 200))
+ -> Finalize HashAggregate (cost=0.00..862.00 rows=1 width=80)
+ Group Key: tgb2.v2
+ AggRefs(TargetList): [(0, 0), (1, 1), (2, 2), (3, 3), (4, 4),
(5, 5), (6, 6), (7, 7), (8, 8), (9, 9)]
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
(cost=0.00..862.00 rows=1 width=84)
+ Hash Key: tgb2.v2
+ -> Streaming Partial HashAggregate (cost=0.00..862.00
rows=1 width=84)
+ Group Key: tgb2.v2
+ AggRefs(TargetList): [(0, 0), (0, 0), (0, 0), (0,
0), (0, 0), (0, 0), (1, 1), (1, 1), (2, 2), (3, 3)]
+ -> Redistribute Motion 3:3 (slice3; segments: 3)
(cost=0.00..862.00 rows=1 width=20)
+ -> Hash Join (cost=0.00..862.00 rows=1
width=20)
+ Hash Cond: (tgb2.v1 = tgb1.v1)
+ -> Seq Scan on tgb2
(cost=0.00..431.00 rows=1 width=12)
+ -> Hash (cost=431.00..431.00 rows=1
width=8)
+ -> Seq Scan on tgb1
(cost=0.00..431.00 rows=1 width=8)
+ Optimizer: GPORCA
+(18 rows)
+
+select
+ sum(tgb1.v3 * tgb2.v3)
+from
+ tgb1,tgb2
+where
+ tgb1.v1 = tgb2.v1 group by tgb2.v2
+having sum(tgb1.v3 * tgb2.v3) > 100 and
+ sum(tgb1.v3 * tgb2.v3) > 101 and
+ sum(tgb1.v3 * tgb2.v3) < 1020 and
+ sum(tgb1.v3 * tgb2.v3) != 103 and
+ sum(tgb1.v3 * tgb2.v3) >= 104 and
+ avg(tgb1.v3 * tgb2.v3) != 11 and
+ avg(tgb1.v3 * tgb2.v3) != 12 and
+ avg(tgb2.v1 * tgb1.v1) != 13 and
+ sum(tgb1.v3) > 200;
+ sum
+-----
+(0 rows)
+
+reset debug_print_aggref_in_explain;
+reset optimizer_force_multistage_agg;
diff --git a/src/test/regress/sql/aggregates.sql
b/src/test/regress/sql/aggregates.sql
index e376c0681d9..7229bbb6128 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -1447,3 +1447,80 @@ drop table agg_hash_4;
-- GitHub issue https://github.com/greenplum-db/gpdb/issues/12061
-- numsegments of the general locus should be -1 on create_minmaxagg_path
explain analyze select count(*) from pg_class, (select count(*) > 0 from
(select count(*) from pg_class where relnatts > 8) x) y;
+
+-- test aggno
+create table tgb1(v1 int, v2 int, v3 int);
+create table tgb2(v1 int, v2 int, v3 int);
+
+insert into tgb1
values(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3);
+insert into tgb2
values(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3),(1,2,3);
+
+set debug_print_aggref_in_explain to on;
+
+explain select
+ sum(tgb1.v3 * tgb2.v3)
+from
+ tgb1,tgb2
+where
+ tgb1.v1 = tgb2.v1 group by tgb2.v2
+having sum(tgb1.v3 * tgb2.v3) > 100 and
+ sum(tgb1.v3 * tgb2.v3) > 101 and
+ sum(tgb1.v3 * tgb2.v3) < 1020 and
+ sum(tgb1.v3 * tgb2.v3) != 103 and
+ sum(tgb1.v3 * tgb2.v3) >= 104 and
+ avg(tgb1.v3 * tgb2.v3) != 11 and
+ avg(tgb1.v3 * tgb2.v3) != 12 and
+ avg(tgb2.v1 * tgb1.v1) != 13 and
+ sum(tgb1.v3) > 200;
+
+select
+ sum(tgb1.v3 * tgb2.v3)
+from
+ tgb1,tgb2
+where
+ tgb1.v1 = tgb2.v1 group by tgb2.v2
+having sum(tgb1.v3 * tgb2.v3) > 100 and
+ sum(tgb1.v3 * tgb2.v3) > 101 and
+ sum(tgb1.v3 * tgb2.v3) < 1020 and
+ sum(tgb1.v3 * tgb2.v3) != 103 and
+ sum(tgb1.v3 * tgb2.v3) >= 104 and
+ avg(tgb1.v3 * tgb2.v3) != 11 and
+ avg(tgb1.v3 * tgb2.v3) != 12 and
+ avg(tgb2.v1 * tgb1.v1) != 13 and
+ sum(tgb1.v3) > 200;
+
+set optimizer_force_multistage_agg to on;
+explain select
+ sum(tgb1.v3 * tgb2.v3)
+from
+ tgb1,tgb2
+where
+ tgb1.v1 = tgb2.v1 group by tgb2.v2
+having sum(tgb1.v3 * tgb2.v3) > 100 and
+ sum(tgb1.v3 * tgb2.v3) > 101 and
+ sum(tgb1.v3 * tgb2.v3) < 1020 and
+ sum(tgb1.v3 * tgb2.v3) != 103 and
+ sum(tgb1.v3 * tgb2.v3) >= 104 and
+ avg(tgb1.v3 * tgb2.v3) != 11 and
+ avg(tgb1.v3 * tgb2.v3) != 12 and
+ avg(tgb2.v1 * tgb1.v1) != 13 and
+ sum(tgb1.v3) > 200;
+
+select
+ sum(tgb1.v3 * tgb2.v3)
+from
+ tgb1,tgb2
+where
+ tgb1.v1 = tgb2.v1 group by tgb2.v2
+having sum(tgb1.v3 * tgb2.v3) > 100 and
+ sum(tgb1.v3 * tgb2.v3) > 101 and
+ sum(tgb1.v3 * tgb2.v3) < 1020 and
+ sum(tgb1.v3 * tgb2.v3) != 103 and
+ sum(tgb1.v3 * tgb2.v3) >= 104 and
+ avg(tgb1.v3 * tgb2.v3) != 11 and
+ avg(tgb1.v3 * tgb2.v3) != 12 and
+ avg(tgb2.v1 * tgb1.v1) != 13 and
+ sum(tgb1.v3) > 200;
+
+reset debug_print_aggref_in_explain;
+reset optimizer_force_multistage_agg;
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
