public inbox for [email protected]
help / color / mirror / Atom feedFrom: [email protected] <[email protected]>
To: Alexander Pyhalov <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Bruce Momjian <[email protected]>
Cc: Stephen Frost <[email protected]>
Cc: Andres Freund <[email protected]>
Cc: Tomas Vondra <[email protected]>
Cc: Julien Rouhaud <[email protected]>
Cc: Daniel Gustafsson <[email protected]>
Cc: vignesh C <[email protected]>
Cc: [email protected] <[email protected]>
Subject: RE: Partial aggregates pushdown
Date: Mon, 27 May 2024 21:30:59 +0000
Message-ID: <TYAPR01MB30884F8E4D94038CD661FFEF95F02@TYAPR01MB3088.jpnprd01.prod.outlook.com> (raw)
In-Reply-To: <[email protected]>
References: <OSZPR01MB94244C52ABBE67C4A033B5E495B3A@OSZPR01MB9424.jpnprd01.prod.outlook.com>
<CA+Tgmobvja+jytj5zcEcYgqzOaeJiqrrJxgqDf1q=3k8FepuWQ@mail.gmail.com>
<[email protected]>
<CA+TgmoaiH_Na3y17PankZH59EgJWbgdPM-szb1eP6DQoOAMgYg@mail.gmail.com>
<[email protected]>
<TYAPR01MB55149B8569F2FDA262654C4F95BAA@TYAPR01MB5514.jpnprd01.prod.outlook.com>
<CA+TgmoYQKF-8vL8sctWuw_dskBiQWcspuNPJuH4ttusuiP529g@mail.gmail.com>
<CA+TgmoZHtdFEOULVXqhWgSg36R1e1ZEdSKJKb+VzTfRU0HaSZQ@mail.gmail.com>
<[email protected]>
<CA+Tgmob7Dn_+UN1qgb_NUEoeg1jdug5mh+qz7q3Ed1NHAL_LNQ@mail.gmail.com>
<[email protected]>
<TYAPR01MB551411A7DDFF0B38C5AAEDFE9584A@TYAPR01MB5514.jpnprd01.prod.outlook.com>
<CA+TgmoYCrtOvk2f32qQKZV=jNL35tandf2A2Dp_2F5ASuiG1BA@mail.gmail.com>
<TYAPR01MB5514C939F39F97BC171F58FD958BA@TYAPR01MB5514.jpnprd01.prod.outlook.com>
<CALDaNm0S5fb_8o+TtR8bXf3zOpqabOBLy0EJ3+oVzHH_dzpXvw@mail.gmail.com>
<TYAPR01MB5514F0CBD9CD4F84A261198195562@TYAPR01MB5514.jpnprd01.prod.outlook.com>
<[email protected]>
<TYAPR01MB55141D18188AC86ADCE35FCB952F2@TYAPR01MB5514.jpnprd01.prod.outlook.com>
<[email protected]>
<[email protected]>
<[email protected]>
Hi Mr. Pyhalov.
Sorry for the late reply.
Thank you for your modification and detailed review.
I attach a fixed patch, have been not yet rebased.
Monday, 25 March 2024 16:01 Alexander Pyhalov <[email protected]>:.
> Comment in nodeAgg.c seems to be strange:
>
> 1079 /*
> 1080 * If the agg's finalfn is provided and PARTIAL_AGGREGATE
> keyword is
> 1081 * not specified, apply the agg's finalfn.
> 1082 * If PARTIAL_AGGREGATE keyword is specified and the
> transValue type
> 1083 * is internal, apply the agg's serialfn. In this case, if
> the agg's
> 1084 * serialfn must not be invalid. Otherwise return
> transValue.
> 1085 */
>
> Likely, you mean:
>
> ... In this case the agg'ss serialfn must not be invalid...
Fixed.
> Lower, in the same file, please, correct error message:
>
> 1136 if(!OidIsValid(peragg->serialfn_oid))
> 1137 elog(ERROR, "serialfunc is note provided
> for partial aggregate");
>
> it should be "serialfunc is not provided for partial aggregate"
Fixed.
> Also something is wrong with the following test :
>
> SELECT /* aggregate <> partial aggregate */
> array_agg(c_int4array), array_agg(b),
> avg(b::int2), avg(b::int4), avg(b::int8), avg(c_interval),
> avg(b::float4), avg(b::float8),
> corr(b::float8, (b * b)::float8),
> covar_pop(b::float8, (b * b)::float8),
> covar_samp(b::float8, (b * b)::float8),
> regr_avgx((2 * b)::float8, b::float8),
> .....
>
> Its results have changed since last patch. Do they depend on daylight
> saving time?
You are right. In my environment, TimeZone is set to 'PST8PDT'
with which timetz values depends on daylight saving time.
Changed TimeZone to 'UTC' in this test.
> You can see that filter is applied before append. The result is correct
> only by chance, as sum in every partition is actually < 700. If you
> lower this bound, let's say, to 200, you'll start getting wrong results
> as data is filtered prior to aggregation.
>
> It seems, however, that in partial case you should just avoid pulling
> conditions from having qual at all, all filters will be applied on upper
> level. Something like
Thank you for your modification.
> Found one more problem. You can fire partial aggregate over partitioned
> table, but convert_combining_aggrefs() will make non-partial copy, which
> leads to
> 'variable not found in subplan target list' error.
Thanks for the correction as well.
As you pointed out,
the original patch certainly had the potential to cause problems.
However, I could not actually reproduce the problem in cases such as the following.
Settings:
t(c1, c2) is a patitioned table whose partition key is c1.
t1, t2 are patitions of t and are partitioned table.
t11, t12: partitions of t1 and foreign table of postgres_fdw.
t21, t22: partitions of t2 and foreign table of postgres_fdw.
Query:
select c2 / 2, sum(c1) from t group by c2 / 2 order by 1
If you have a reproducible example, I would like to add it to
the regression test.
Do you have a reproducible example?
> Also denied partial agregates pushdown on server version mismatch.
> Should check_partial_aggregate_support be 'true' by default?
Could we discuss this point after we determine how to transfer state values?
If we determine this point, we can easly determine whether check_partial_aggregate_support shold be 'true' by default.
> I'm not sure what to do with current grammar - it precludes partial
> distinct aggregates. I understand that it's currently impossible to have
> partial aggregation for distinct agregates -but does it worth to have
> such restriction at grammar level?
If partial aggregation for distinct agregates becomes possible in the future,
I see no problem with the policy of accepting new SQL keywords,
such as "PARTIL_AGGREGATE DISTINCT".
Sincerely yours,
Yuuki Fujii
--
Yuuki Fujii
Information Technology R&D Center Mitsubishi Electric Corporation
Attachments:
[application/octet-stream] 0001-Partial-aggregates-push-down-SQL-keyword-v5.patch (147.9K, ../TYAPR01MB30884F8E4D94038CD661FFEF95F02@TYAPR01MB3088.jpnprd01.prod.outlook.com/2-0001-Partial-aggregates-push-down-SQL-keyword-v5.patch)
download | inline diff:
From 283e2ce12dbaeed44c675f6e776aa77c62062a7b Mon Sep 17 00:00:00 2001
From: Yuki Fujii <[email protected]>
Date: Tue, 28 May 2024 06:27:02 +0900
Subject: [PATCH] Partial aggregates push down SQL keyword v5
---
contrib/postgres_fdw/deparse.c | 85 ++-
.../postgres_fdw/expected/postgres_fdw.out | 714 ++++++++++++++++--
contrib/postgres_fdw/option.c | 4 +-
contrib/postgres_fdw/postgres_fdw.c | 41 +-
contrib/postgres_fdw/postgres_fdw.h | 10 +
contrib/postgres_fdw/sql/postgres_fdw.sql | 167 +++-
src/backend/executor/nodeAgg.c | 60 +-
src/backend/nodes/makefuncs.c | 1 +
src/backend/optimizer/plan/planner.c | 133 +++-
src/backend/optimizer/plan/setrefs.c | 1 +
src/backend/optimizer/prep/prepagg.c | 1 +
src/backend/parser/gram.y | 29 +-
src/backend/parser/parse_agg.c | 24 +-
src/backend/parser/parse_clause.c | 1 +
src/backend/parser/parse_expr.c | 3 +-
src/backend/parser/parse_func.c | 24 +-
src/backend/utils/adt/ruleutils.c | 3 +-
src/include/executor/nodeAgg.h | 9 +
src/include/nodes/parsenodes.h | 1 +
src/include/nodes/pathnodes.h | 5 +
src/include/nodes/primnodes.h | 3 +
src/include/parser/kwlist.h | 1 +
src/include/parser/parse_agg.h | 2 +-
23 files changed, 1199 insertions(+), 123 deletions(-)
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 09fd489a90..ddc92f7825 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -202,7 +202,7 @@ static bool is_subquery_var(Var *node, RelOptInfo *foreignrel,
int *relno, int *colno);
static void get_relation_column_alias_ids(Var *node, RelOptInfo *foreignrel,
int *relno, int *colno);
-
+static bool partial_agg_ok(Aggref *agg, PgFdwRelationInfo *fpinfo);
/*
* Examine each qual clause in input_conds, and classify them into two groups,
@@ -907,8 +907,9 @@ foreign_expr_walker(Node *node,
if (!IS_UPPER_REL(glob_cxt->foreignrel))
return false;
- /* Only non-split aggregates are pushable. */
- if (agg->aggsplit != AGGSPLIT_SIMPLE)
+ if (agg->aggsplit != AGGSPLIT_SIMPLE && agg->aggsplit != AGGSPLIT_INITIAL_SERIAL)
+ return false;
+ if (agg->aggsplit == AGGSPLIT_INITIAL_SERIAL && !partial_agg_ok(agg, fpinfo))
return false;
/* As usual, it must be shippable. */
@@ -3517,18 +3518,35 @@ deparseAggref(Aggref *node, deparse_expr_cxt *context)
StringInfo buf = context->buf;
bool use_variadic;
- /* Only basic, non-split aggregation accepted. */
- Assert(node->aggsplit == AGGSPLIT_SIMPLE);
+
+ Assert((node->aggsplit == AGGSPLIT_SIMPLE) ||
+ (node->aggsplit == AGGSPLIT_INITIAL_SERIAL));
/* Check if need to print VARIADIC (cf. ruleutils.c) */
use_variadic = node->aggvariadic;
/* Find aggregate name from aggfnoid which is a pg_proc entry */
appendFunctionName(node->aggfnoid, context);
+
appendStringInfoChar(buf, '(');
- /* Add DISTINCT */
- appendStringInfoString(buf, (node->aggdistinct != NIL) ? "DISTINCT " : "");
+ /* Add DISTINCT or PARTIAL_AGGREGATE */
+ if (node->aggdistinct != NIL) {
+ appendStringInfoString(buf, "DISTINCT ");
+ } else if (node->aggsplit == AGGSPLIT_INITIAL_SERIAL) {
+ HeapTuple aggtup;
+ Form_pg_aggregate aggform;
+
+ aggtup = SearchSysCache1(AGGFNOID, ObjectIdGetDatum(node->aggfnoid));
+ if (!HeapTupleIsValid(aggtup))
+ elog(ERROR, "cache lookup failed for aggregate %u", node->aggfnoid);
+ aggform = (Form_pg_aggregate) GETSTRUCT(aggtup);
+
+ if ((aggform->aggtranstype == INTERNALOID) || OidIsValid(aggform->aggfinalfn))
+ appendStringInfoString(buf, "PARTIAL_AGGREGATE ");
+
+ ReleaseSysCache(aggtup);
+ }
if (AGGKIND_IS_ORDERED_SET(node->aggkind))
{
@@ -3723,9 +3741,10 @@ appendGroupByClause(List *tlist, deparse_expr_cxt *context)
Query *query = context->root->parse;
ListCell *lc;
bool first = true;
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) context->foreignrel->fdw_private;
/* Nothing to be done, if there's no GROUP BY clause in the query. */
- if (!query->groupClause)
+ if (!fpinfo->group_clause)
return;
appendStringInfoString(buf, " GROUP BY ");
@@ -3743,7 +3762,7 @@ appendGroupByClause(List *tlist, deparse_expr_cxt *context)
* to empty, and in any case the redundancy situation on the remote might
* be different than what we think here.
*/
- foreach(lc, query->groupClause)
+ foreach(lc, fpinfo->group_clause)
{
SortGroupClause *grp = (SortGroupClause *) lfirst(lc);
@@ -4047,3 +4066,51 @@ get_relation_column_alias_ids(Var *node, RelOptInfo *foreignrel,
/* Shouldn't get here */
elog(ERROR, "unexpected expression in subquery output");
}
+
+/*
+ * ----------
+ * Check that partial aggregate "agg" is safe to push down.
+ *
+ * It is pushdown-safe when all of the following conditions are true:
+ *
+ * * agg is an AGGKIND_NORMAL aggregate which contains no DISTINCT or
+ * ORDER BY clauses
+ * * remote server can return partial aggregate results
+ * ----------
+ */
+static bool
+partial_agg_ok(Aggref *agg, PgFdwRelationInfo *fpinfo)
+{
+ HeapTuple aggtup;
+ Form_pg_aggregate aggform;
+ bool partial_agg_ok = true;
+
+ Assert(agg->aggsplit == AGGSPLIT_INITIAL_SERIAL);
+
+ /* We don't support complex partial aggregates */
+ if (agg->aggdistinct || agg->aggkind != AGGKIND_NORMAL || agg->aggorder != NIL)
+ return false;
+
+ aggtup = SearchSysCache1(AGGFNOID, ObjectIdGetDatum(agg->aggfnoid));
+ if (!HeapTupleIsValid(aggtup))
+ elog(ERROR, "cache lookup failed for aggregate %u", agg->aggfnoid);
+ aggform = (Form_pg_aggregate) GETSTRUCT(aggtup);
+
+ if ((OidIsValid(aggform->aggfinalfn) ||
+ (aggform->aggtranstype == INTERNALOID)) &&
+ fpinfo->check_partial_aggregate_support)
+ {
+ if (fpinfo->remoteversion == 0)
+ {
+ PGconn *conn = GetConnection(fpinfo->user, false, NULL);
+
+ fpinfo->remoteversion = PQserverVersion(conn);
+ }
+
+ if (fpinfo->remoteversion != PG_VERSION_NUM)
+ partial_agg_ok = false;
+ }
+
+ ReleaseSysCache(aggtup);
+ return partial_agg_ok;
+}
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 64bcc66b8d..61b6daa452 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -9757,36 +9757,57 @@ RESET enable_partitionwise_join;
-- ===================================================================
-- test partitionwise aggregates
-- ===================================================================
-CREATE TABLE pagg_tab (a int, b int, c text) PARTITION BY RANGE(a);
+ALTER SERVER loopback OPTIONS (ADD fdw_tuple_cost '0.1');
+CREATE TYPE mood AS enum ('sad', 'ok', 'happy');
+ALTER EXTENSION postgres_fdw ADD TYPE mood;
+CREATE TABLE pagg_tab (a int, b int, c text, c_serial int4,
+ c_int4array _int4, c_interval interval,
+ c_money money, c_1c text, c_1b bytea,
+ c_bit bit(2), c_1or3int2 int2,
+ c_1or3int4 int4, c_1or3int8 int8,
+ c_bool bool, c_enum mood, c_pg_lsn pg_lsn,
+ c_tid tid, c_int4range int4range,
+ c_int4multirange int4multirange,
+ c_time time, c_timetz timetz,
+ c_timestamp timestamp, c_timestamptz timestamptz,
+ c_xid8 xid8)
+ PARTITION BY RANGE(a);
CREATE TABLE pagg_tab_p1 (LIKE pagg_tab);
CREATE TABLE pagg_tab_p2 (LIKE pagg_tab);
CREATE TABLE pagg_tab_p3 (LIKE pagg_tab);
-INSERT INTO pagg_tab_p1 SELECT i % 30, i % 50, to_char(i/30, 'FM0000') FROM generate_series(1, 3000) i WHERE (i % 30) < 10;
-INSERT INTO pagg_tab_p2 SELECT i % 30, i % 50, to_char(i/30, 'FM0000') FROM generate_series(1, 3000) i WHERE (i % 30) < 20 and (i % 30) >= 10;
-INSERT INTO pagg_tab_p3 SELECT i % 30, i % 50, to_char(i/30, 'FM0000') FROM generate_series(1, 3000) i WHERE (i % 30) < 30 and (i % 30) >= 20;
+SET TimeZone = 'UTC';
+INSERT INTO pagg_tab_p1 SELECT i % 30, i % 50, to_char(i/30, 'FM0000'), i, array[(i % 2), 0], ((i % 2) || ' seconds')::interval, (i % 2)::money, ((i - 1) % 10)::text, (((i - 1) % 10)::text)::bytea, case when (i % 2) = 0 then B'01' else B'11' end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then true else false end, (case when (i % 2) = 0 then 'sad' else 'happy' end)::mood, ('0/' || i)::pg_lsn, ('(0,' || i || ')')::tid, int4range(0, i), int4multirange(int4range(0, i), int4range(100, 100 + i)), '00:00:00'::time + (i || ' seconds')::interval, '00:00:00'::timetz + (i || ' seconds')::interval, '2000-01-01'::timestamp + (i || ' seconds')::interval, '2000-01-01'::timestamptz + (i || ' seconds')::interval, ((i % 10)::text)::xid8 FROM generate_series(1, 3000) i WHERE (i % 30) < 10;
+INSERT INTO pagg_tab_p2 SELECT i % 30, i % 50, to_char(i/30, 'FM0000'), i, array[(i % 2), 0], ((i % 2) || ' seconds')::interval, (i % 2)::money, ((i - 1) % 10)::text, (((i - 1) % 10)::text)::bytea, case when (i % 2) = 0 then B'01' else B'11' end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then true else false end, (case when (i % 2) = 0 then 'sad' else 'happy' end)::mood, ('0/' || i)::pg_lsn, ('(0,' || i || ')')::tid, int4range(0, i), int4multirange(int4range(0, i), int4range(100, 100 + i)), '00:00:00'::time + (i || ' seconds')::interval, '00:00:00'::timetz + (i || ' seconds')::interval, '2000-01-01'::timestamp + (i || ' seconds')::interval, '2000-01-01'::timestamptz + (i || ' seconds')::interval, ((i % 10)::text)::xid8 FROM generate_series(1, 3000) i WHERE (i % 30) < 20 and (i % 30) >= 10;
+INSERT INTO pagg_tab_p3 SELECT i % 30, i % 50, to_char(i/30, 'FM0000'), i, array[(i % 2), 0], ((i % 2) || ' seconds')::interval, (i % 2)::money, ((i - 1) % 10)::text, (((i - 1) % 10)::text)::bytea, case when (i % 2) = 0 then B'01' else B'11' end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then true else false end, (case when (i % 2) = 0 then 'sad' else 'happy' end)::mood, ('0/' || i)::pg_lsn, ('(0,' || i || ')')::tid, int4range(0, i), int4multirange(int4range(0, i), int4range(100, 100 + i)), '00:00:00'::time + (i || ' seconds')::interval, '00:00:00'::timetz + (i || ' seconds')::interval, '2000-01-01'::timestamp + (i || ' seconds')::interval, '2000-01-01'::timestamptz + (i || ' seconds')::interval, ((i % 10)::text)::xid8 FROM generate_series(1, 3000) i WHERE (i % 30) < 30 and (i % 30) >= 20;
-- Create foreign partitions
CREATE FOREIGN TABLE fpagg_tab_p1 PARTITION OF pagg_tab FOR VALUES FROM (0) TO (10) SERVER loopback OPTIONS (table_name 'pagg_tab_p1');
CREATE FOREIGN TABLE fpagg_tab_p2 PARTITION OF pagg_tab FOR VALUES FROM (10) TO (20) SERVER loopback OPTIONS (table_name 'pagg_tab_p2');
CREATE FOREIGN TABLE fpagg_tab_p3 PARTITION OF pagg_tab FOR VALUES FROM (20) TO (30) SERVER loopback OPTIONS (table_name 'pagg_tab_p3');
ANALYZE pagg_tab;
+ANALYZE pagg_tab_p1;
+ANALYZE pagg_tab_p2;
+ANALYZE pagg_tab_p3;
ANALYZE fpagg_tab_p1;
ANALYZE fpagg_tab_p2;
ANALYZE fpagg_tab_p3;
+SET extra_float_digits = 0;
-- When GROUP BY clause matches with PARTITION KEY.
-- Plan with partitionwise aggregates is disabled
SET enable_partitionwise_aggregate TO false;
EXPLAIN (COSTS OFF)
SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
- QUERY PLAN
------------------------------------------------------
- GroupAggregate
- Group Key: pagg_tab.a
- Filter: (avg(pagg_tab.b) < '22'::numeric)
- -> Append
- -> Foreign Scan on fpagg_tab_p1 pagg_tab_1
- -> Foreign Scan on fpagg_tab_p2 pagg_tab_2
- -> Foreign Scan on fpagg_tab_p3 pagg_tab_3
-(7 rows)
+ QUERY PLAN
+-----------------------------------------------------------
+ Sort
+ Sort Key: pagg_tab.a
+ -> HashAggregate
+ Group Key: pagg_tab.a
+ Filter: (avg(pagg_tab.b) < '22'::numeric)
+ -> Append
+ -> Foreign Scan on fpagg_tab_p1 pagg_tab_1
+ -> Foreign Scan on fpagg_tab_p2 pagg_tab_2
+ -> Foreign Scan on fpagg_tab_p3 pagg_tab_3
+(9 rows)
-- Plan with partitionwise aggregates is enabled
SET enable_partitionwise_aggregate TO true;
@@ -9816,36 +9837,66 @@ SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 O
21 | 2100 | 1 | 100
(6 rows)
+-- Check partial aggregate over partitioned table
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT avg(PARTIAL_AGGREGATE a), avg(a) FROM pagg_tab;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------
+ Finalize Aggregate
+ Output: avg(PARTIAL_AGGREGATE pagg_tab.a), avg(pagg_tab.a)
+ -> Append
+ -> Foreign Scan
+ Output: (PARTIAL avg(PARTIAL_AGGREGATE pagg_tab.a)), (PARTIAL avg(pagg_tab.a))
+ Relations: Aggregate on (public.fpagg_tab_p1 pagg_tab)
+ Remote SQL: SELECT avg(PARTIAL_AGGREGATE a), avg(PARTIAL_AGGREGATE a) FROM public.pagg_tab_p1
+ -> Foreign Scan
+ Output: (PARTIAL avg(PARTIAL_AGGREGATE pagg_tab_1.a)), (PARTIAL avg(pagg_tab_1.a))
+ Relations: Aggregate on (public.fpagg_tab_p2 pagg_tab_1)
+ Remote SQL: SELECT avg(PARTIAL_AGGREGATE a), avg(PARTIAL_AGGREGATE a) FROM public.pagg_tab_p2
+ -> Foreign Scan
+ Output: (PARTIAL avg(PARTIAL_AGGREGATE pagg_tab_2.a)), (PARTIAL avg(pagg_tab_2.a))
+ Relations: Aggregate on (public.fpagg_tab_p3 pagg_tab_2)
+ Remote SQL: SELECT avg(PARTIAL_AGGREGATE a), avg(PARTIAL_AGGREGATE a) FROM public.pagg_tab_p3
+(15 rows)
+
+SELECT avg(PARTIAL_AGGREGATE a), avg(a) FROM pagg_tab;
+ avg | avg
+--------------+---------------------
+ {3000,43500} | 14.5000000000000000
+(1 row)
+
-- Check with whole-row reference
-- Should have all the columns in the target list for the given relation
EXPLAIN (VERBOSE, COSTS OFF)
SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
- QUERY PLAN
---------------------------------------------------------------------------------------------
- Merge Append
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Sort
+ Output: t1.a, (count(((t1.*)::pagg_tab)))
Sort Key: t1.a
- -> GroupAggregate
- Output: t1.a, count(((t1.*)::pagg_tab))
- Group Key: t1.a
- Filter: (avg(t1.b) < '22'::numeric)
- -> Foreign Scan on public.fpagg_tab_p1 t1
- Output: t1.a, t1.*, t1.b
- Remote SQL: SELECT a, b, c FROM public.pagg_tab_p1 ORDER BY a ASC NULLS LAST
- -> GroupAggregate
- Output: t1_1.a, count(((t1_1.*)::pagg_tab))
- Group Key: t1_1.a
- Filter: (avg(t1_1.b) < '22'::numeric)
- -> Foreign Scan on public.fpagg_tab_p2 t1_1
- Output: t1_1.a, t1_1.*, t1_1.b
- Remote SQL: SELECT a, b, c FROM public.pagg_tab_p2 ORDER BY a ASC NULLS LAST
- -> GroupAggregate
- Output: t1_2.a, count(((t1_2.*)::pagg_tab))
- Group Key: t1_2.a
- Filter: (avg(t1_2.b) < '22'::numeric)
- -> Foreign Scan on public.fpagg_tab_p3 t1_2
- Output: t1_2.a, t1_2.*, t1_2.b
- Remote SQL: SELECT a, b, c FROM public.pagg_tab_p3 ORDER BY a ASC NULLS LAST
-(23 rows)
+ -> Append
+ -> HashAggregate
+ Output: t1.a, count(((t1.*)::pagg_tab))
+ Group Key: t1.a
+ Filter: (avg(t1.b) < '22'::numeric)
+ -> Foreign Scan on public.fpagg_tab_p1 t1
+ Output: t1.a, t1.*, t1.b
+ Remote SQL: SELECT a, b, c, c_serial, c_int4array, c_interval, c_money, c_1c, c_1b, c_bit, c_1or3int2, c_1or3int4, c_1or3int8, c_bool, c_enum, c_pg_lsn, c_tid, c_int4range, c_int4multirange, c_time, c_timetz, c_timestamp, c_timestamptz, c_xid8 FROM public.pagg_tab_p1
+ -> HashAggregate
+ Output: t1_1.a, count(((t1_1.*)::pagg_tab))
+ Group Key: t1_1.a
+ Filter: (avg(t1_1.b) < '22'::numeric)
+ -> Foreign Scan on public.fpagg_tab_p2 t1_1
+ Output: t1_1.a, t1_1.*, t1_1.b
+ Remote SQL: SELECT a, b, c, c_serial, c_int4array, c_interval, c_money, c_1c, c_1b, c_bit, c_1or3int2, c_1or3int4, c_1or3int8, c_bool, c_enum, c_pg_lsn, c_tid, c_int4range, c_int4multirange, c_time, c_timetz, c_timestamp, c_timestamptz, c_xid8 FROM public.pagg_tab_p2
+ -> HashAggregate
+ Output: t1_2.a, count(((t1_2.*)::pagg_tab))
+ Group Key: t1_2.a
+ Filter: (avg(t1_2.b) < '22'::numeric)
+ -> Foreign Scan on public.fpagg_tab_p3 t1_2
+ Output: t1_2.a, t1_2.*, t1_2.b
+ Remote SQL: SELECT a, b, c, c_serial, c_int4array, c_interval, c_money, c_1c, c_1b, c_bit, c_1or3int2, c_1or3int4, c_1or3int8, c_bool, c_enum, c_pg_lsn, c_tid, c_int4range, c_int4multirange, c_time, c_timetz, c_timestamp, c_timestamptz, c_xid8 FROM public.pagg_tab_p3
+(25 rows)
SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
a | count
@@ -9858,27 +9909,578 @@ SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
21 | 100
(6 rows)
--- When GROUP BY clause does not match with PARTITION KEY.
-EXPLAIN (COSTS OFF)
+-- Partial aggregates are safe to push down when there is a HAVING clause
+EXPLAIN (VERBOSE, COSTS OFF)
SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700 ORDER BY 1;
- QUERY PLAN
------------------------------------------------------------
- Finalize GroupAggregate
- Group Key: pagg_tab.b
- Filter: (sum(pagg_tab.a) < 700)
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------
+ Sort
+ Output: pagg_tab.b, (avg(pagg_tab.a)), (max(pagg_tab.a)), (count(*))
+ Sort Key: pagg_tab.b
+ -> Finalize HashAggregate
+ Output: pagg_tab.b, avg(pagg_tab.a), max(pagg_tab.a), count(*)
+ Group Key: pagg_tab.b
+ Filter: (sum(pagg_tab.a) < 700)
+ -> Append
+ -> Foreign Scan
+ Output: pagg_tab.b, (PARTIAL avg(pagg_tab.a)), (PARTIAL max(pagg_tab.a)), (PARTIAL count(*)), (PARTIAL sum(pagg_tab.a))
+ Relations: Aggregate on (public.fpagg_tab_p1 pagg_tab)
+ Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), max(a), count(*), sum(a) FROM public.pagg_tab_p1 GROUP BY 1
+ -> Foreign Scan
+ Output: pagg_tab_1.b, (PARTIAL avg(pagg_tab_1.a)), (PARTIAL max(pagg_tab_1.a)), (PARTIAL count(*)), (PARTIAL sum(pagg_tab_1.a))
+ Relations: Aggregate on (public.fpagg_tab_p2 pagg_tab_1)
+ Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), max(a), count(*), sum(a) FROM public.pagg_tab_p2 GROUP BY 1
+ -> Foreign Scan
+ Output: pagg_tab_2.b, (PARTIAL avg(pagg_tab_2.a)), (PARTIAL max(pagg_tab_2.a)), (PARTIAL count(*)), (PARTIAL sum(pagg_tab_2.a))
+ Relations: Aggregate on (public.fpagg_tab_p3 pagg_tab_2)
+ Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), max(a), count(*), sum(a) FROM public.pagg_tab_p3 GROUP BY 1
+(20 rows)
+
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700 ORDER BY 1;
+ b | avg | max | count
+----+---------------------+-----+-------
+ 0 | 10.0000000000000000 | 20 | 60
+ 1 | 11.0000000000000000 | 21 | 60
+ 10 | 10.0000000000000000 | 20 | 60
+ 11 | 11.0000000000000000 | 21 | 60
+ 20 | 10.0000000000000000 | 20 | 60
+ 21 | 11.0000000000000000 | 21 | 60
+ 30 | 10.0000000000000000 | 20 | 60
+ 31 | 11.0000000000000000 | 21 | 60
+ 40 | 10.0000000000000000 | 20 | 60
+ 41 | 11.0000000000000000 | 21 | 60
+(10 rows)
+
+-- Partial aggregates are safe to push down without having clause
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b ORDER BY 1;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------
+ Sort
+ Output: pagg_tab.b, (avg(pagg_tab.a)), (max(pagg_tab.a)), (count(*))
+ Sort Key: pagg_tab.b
+ -> Finalize HashAggregate
+ Output: pagg_tab.b, avg(pagg_tab.a), max(pagg_tab.a), count(*)
+ Group Key: pagg_tab.b
+ -> Append
+ -> Foreign Scan
+ Output: pagg_tab.b, (PARTIAL avg(pagg_tab.a)), (PARTIAL max(pagg_tab.a)), (PARTIAL count(*))
+ Relations: Aggregate on (public.fpagg_tab_p1 pagg_tab)
+ Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), max(a), count(*) FROM public.pagg_tab_p1 GROUP BY 1
+ -> Foreign Scan
+ Output: pagg_tab_1.b, (PARTIAL avg(pagg_tab_1.a)), (PARTIAL max(pagg_tab_1.a)), (PARTIAL count(*))
+ Relations: Aggregate on (public.fpagg_tab_p2 pagg_tab_1)
+ Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), max(a), count(*) FROM public.pagg_tab_p2 GROUP BY 1
+ -> Foreign Scan
+ Output: pagg_tab_2.b, (PARTIAL avg(pagg_tab_2.a)), (PARTIAL max(pagg_tab_2.a)), (PARTIAL count(*))
+ Relations: Aggregate on (public.fpagg_tab_p3 pagg_tab_2)
+ Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), max(a), count(*) FROM public.pagg_tab_p3 GROUP BY 1
+(19 rows)
+
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b ORDER BY 1;
+ b | avg | max | count
+----+---------------------+-----+-------
+ 0 | 10.0000000000000000 | 20 | 60
+ 1 | 11.0000000000000000 | 21 | 60
+ 2 | 12.0000000000000000 | 22 | 60
+ 3 | 13.0000000000000000 | 23 | 60
+ 4 | 14.0000000000000000 | 24 | 60
+ 5 | 15.0000000000000000 | 25 | 60
+ 6 | 16.0000000000000000 | 26 | 60
+ 7 | 17.0000000000000000 | 27 | 60
+ 8 | 18.0000000000000000 | 28 | 60
+ 9 | 19.0000000000000000 | 29 | 60
+ 10 | 10.0000000000000000 | 20 | 60
+ 11 | 11.0000000000000000 | 21 | 60
+ 12 | 12.0000000000000000 | 22 | 60
+ 13 | 13.0000000000000000 | 23 | 60
+ 14 | 14.0000000000000000 | 24 | 60
+ 15 | 15.0000000000000000 | 25 | 60
+ 16 | 16.0000000000000000 | 26 | 60
+ 17 | 17.0000000000000000 | 27 | 60
+ 18 | 18.0000000000000000 | 28 | 60
+ 19 | 19.0000000000000000 | 29 | 60
+ 20 | 10.0000000000000000 | 20 | 60
+ 21 | 11.0000000000000000 | 21 | 60
+ 22 | 12.0000000000000000 | 22 | 60
+ 23 | 13.0000000000000000 | 23 | 60
+ 24 | 14.0000000000000000 | 24 | 60
+ 25 | 15.0000000000000000 | 25 | 60
+ 26 | 16.0000000000000000 | 26 | 60
+ 27 | 17.0000000000000000 | 27 | 60
+ 28 | 18.0000000000000000 | 28 | 60
+ 29 | 19.0000000000000000 | 29 | 60
+ 30 | 10.0000000000000000 | 20 | 60
+ 31 | 11.0000000000000000 | 21 | 60
+ 32 | 12.0000000000000000 | 22 | 60
+ 33 | 13.0000000000000000 | 23 | 60
+ 34 | 14.0000000000000000 | 24 | 60
+ 35 | 15.0000000000000000 | 25 | 60
+ 36 | 16.0000000000000000 | 26 | 60
+ 37 | 17.0000000000000000 | 27 | 60
+ 38 | 18.0000000000000000 | 28 | 60
+ 39 | 19.0000000000000000 | 29 | 60
+ 40 | 10.0000000000000000 | 20 | 60
+ 41 | 11.0000000000000000 | 21 | 60
+ 42 | 12.0000000000000000 | 22 | 60
+ 43 | 13.0000000000000000 | 23 | 60
+ 44 | 14.0000000000000000 | 24 | 60
+ 45 | 15.0000000000000000 | 25 | 60
+ 46 | 16.0000000000000000 | 26 | 60
+ 47 | 17.0000000000000000 | 27 | 60
+ 48 | 18.0000000000000000 | 28 | 60
+ 49 | 19.0000000000000000 | 29 | 60
+(50 rows)
+
+-- Partial aggregates are safe to push down even if we need both variable and variable-based expression
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT avg(a), max(a), count(*), (b/2)::numeric FROM pagg_tab GROUP BY b/2 ORDER BY 4;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------
+ Sort
+ Output: (avg(pagg_tab.a)), (max(pagg_tab.a)), (count(*)), ((((pagg_tab.b / 2)))::numeric), ((pagg_tab.b / 2))
+ Sort Key: ((((pagg_tab.b / 2)))::numeric)
+ -> Finalize HashAggregate
+ Output: avg(pagg_tab.a), max(pagg_tab.a), count(*), (((pagg_tab.b / 2)))::numeric, ((pagg_tab.b / 2))
+ Group Key: ((pagg_tab.b / 2))
+ -> Append
+ -> Foreign Scan
+ Output: ((pagg_tab.b / 2)), (PARTIAL avg(pagg_tab.a)), (PARTIAL max(pagg_tab.a)), (PARTIAL count(*)), pagg_tab.b
+ Relations: Aggregate on (public.fpagg_tab_p1 pagg_tab)
+ Remote SQL: SELECT (b / 2), b, avg(PARTIAL_AGGREGATE a), max(a), count(*) FROM public.pagg_tab_p1 GROUP BY 1, 2
+ -> Foreign Scan
+ Output: ((pagg_tab_1.b / 2)), (PARTIAL avg(pagg_tab_1.a)), (PARTIAL max(pagg_tab_1.a)), (PARTIAL count(*)), pagg_tab_1.b
+ Relations: Aggregate on (public.fpagg_tab_p2 pagg_tab_1)
+ Remote SQL: SELECT (b / 2), b, avg(PARTIAL_AGGREGATE a), max(a), count(*) FROM public.pagg_tab_p2 GROUP BY 1, 2
+ -> Foreign Scan
+ Output: ((pagg_tab_2.b / 2)), (PARTIAL avg(pagg_tab_2.a)), (PARTIAL max(pagg_tab_2.a)), (PARTIAL count(*)), pagg_tab_2.b
+ Relations: Aggregate on (public.fpagg_tab_p3 pagg_tab_2)
+ Remote SQL: SELECT (b / 2), b, avg(PARTIAL_AGGREGATE a), max(a), count(*) FROM public.pagg_tab_p3 GROUP BY 1, 2
+(19 rows)
+
+SELECT avg(a), max(a), count(*), (b/2)::numeric FROM pagg_tab GROUP BY b/2 ORDER BY 4;
+ avg | max | count | numeric
+---------------------+-----+-------+---------
+ 10.5000000000000000 | 21 | 120 | 0
+ 12.5000000000000000 | 23 | 120 | 1
+ 14.5000000000000000 | 25 | 120 | 2
+ 16.5000000000000000 | 27 | 120 | 3
+ 18.5000000000000000 | 29 | 120 | 4
+ 10.5000000000000000 | 21 | 120 | 5
+ 12.5000000000000000 | 23 | 120 | 6
+ 14.5000000000000000 | 25 | 120 | 7
+ 16.5000000000000000 | 27 | 120 | 8
+ 18.5000000000000000 | 29 | 120 | 9
+ 10.5000000000000000 | 21 | 120 | 10
+ 12.5000000000000000 | 23 | 120 | 11
+ 14.5000000000000000 | 25 | 120 | 12
+ 16.5000000000000000 | 27 | 120 | 13
+ 18.5000000000000000 | 29 | 120 | 14
+ 10.5000000000000000 | 21 | 120 | 15
+ 12.5000000000000000 | 23 | 120 | 16
+ 14.5000000000000000 | 25 | 120 | 17
+ 16.5000000000000000 | 27 | 120 | 18
+ 18.5000000000000000 | 29 | 120 | 19
+ 10.5000000000000000 | 21 | 120 | 20
+ 12.5000000000000000 | 23 | 120 | 21
+ 14.5000000000000000 | 25 | 120 | 22
+ 16.5000000000000000 | 27 | 120 | 23
+ 18.5000000000000000 | 29 | 120 | 24
+(25 rows)
+
+-- Partial aggregates pushdown behaves sane with non-shipped grouping elements
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT avg(a), (b/2)::numeric, 'test' test FROM pagg_tab GROUP BY 3, b/2 ORDER BY 1, 2 LIMIT 10;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: (avg(pagg_tab.a)), ((((pagg_tab.b / 2)))::numeric), 'test'::text, ((pagg_tab.b / 2))
+ -> Sort
+ Output: (avg(pagg_tab.a)), ((((pagg_tab.b / 2)))::numeric), 'test'::text, ((pagg_tab.b / 2))
+ Sort Key: (avg(pagg_tab.a)), ((((pagg_tab.b / 2)))::numeric)
+ -> Finalize HashAggregate
+ Output: avg(pagg_tab.a), (((pagg_tab.b / 2)))::numeric, 'test'::text, ((pagg_tab.b / 2))
+ Group Key: ((pagg_tab.b / 2))
+ -> Append
+ -> Foreign Scan
+ Output: ((pagg_tab.b / 2)), (PARTIAL avg(pagg_tab.a)), pagg_tab.b
+ Relations: Aggregate on (public.fpagg_tab_p1 pagg_tab)
+ Remote SQL: SELECT (b / 2), b, avg(PARTIAL_AGGREGATE a) FROM public.pagg_tab_p1 GROUP BY 1, 2
+ -> Foreign Scan
+ Output: ((pagg_tab_1.b / 2)), (PARTIAL avg(pagg_tab_1.a)), pagg_tab_1.b
+ Relations: Aggregate on (public.fpagg_tab_p2 pagg_tab_1)
+ Remote SQL: SELECT (b / 2), b, avg(PARTIAL_AGGREGATE a) FROM public.pagg_tab_p2 GROUP BY 1, 2
+ -> Foreign Scan
+ Output: ((pagg_tab_2.b / 2)), (PARTIAL avg(pagg_tab_2.a)), pagg_tab_2.b
+ Relations: Aggregate on (public.fpagg_tab_p3 pagg_tab_2)
+ Remote SQL: SELECT (b / 2), b, avg(PARTIAL_AGGREGATE a) FROM public.pagg_tab_p3 GROUP BY 1, 2
+(21 rows)
+
+SELECT avg(a), (b/2)::numeric, 'test' test FROM pagg_tab GROUP BY 3, b/2 ORDER BY 1, 2 LIMIT 10;
+ avg | numeric | test
+---------------------+---------+------
+ 10.5000000000000000 | 0 | test
+ 10.5000000000000000 | 5 | test
+ 10.5000000000000000 | 10 | test
+ 10.5000000000000000 | 15 | test
+ 10.5000000000000000 | 20 | test
+ 12.5000000000000000 | 1 | test
+ 12.5000000000000000 | 6 | test
+ 12.5000000000000000 | 11 | test
+ 12.5000000000000000 | 16 | test
+ 12.5000000000000000 | 21 | test
+(10 rows)
+
+-- Partial aggregates are safe to push down for all built-in aggregates
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT /* aggregate <> partial aggregate */
+ array_agg(c_int4array), array_agg(b),
+ avg(b::int2), avg(b::int4), avg(b::int8), avg(c_interval), avg(b::float4), avg(b::float8), avg(b::numeric),
+ corr(b::float8, (b * b)::float8),
+ covar_pop(b::float8, (b * b)::float8),
+ covar_samp(b::float8, (b * b)::float8),
+ regr_avgx((2 * b)::float8, b::float8),
+ regr_avgy((2 * b)::float8, b::float8),
+ regr_intercept((2 * b + 3)::float8, b::float8),
+ regr_r2((b * b)::float8, b::float8),
+ regr_slope((2 * b + 3)::float8, b::float8),
+ regr_sxx((2 * b + 3)::float8, b::float8),
+ regr_sxy((b * b)::float8, b::float8),
+ regr_syy((2 * b + 3)::float8, b::float8),
+ stddev(b::float4), stddev(b::float8), stddev(b::int2), stddev(b::int4), stddev(b::int8), stddev(b::numeric),
+ stddev_pop(b::float4), stddev_pop(b::float8), stddev_pop(b::int2), stddev_pop(b::int4), stddev_pop(b::int8), stddev_pop(b::numeric),
+ stddev_samp(b::float4), stddev_samp(b::float8), stddev_samp(b::int2), stddev_samp(b::int4), stddev_samp(b::int8), stddev_samp(b::numeric),
+ string_agg(c_1c, ','), string_agg(c_1b, ','),
+ sum(b::int8), sum(b::numeric),
+ variance(b::float4), variance(b::float8), variance(b::int2), variance(b::int4), variance(b::int8), variance(b::numeric),
+ var_pop(b::float4), var_pop(b::float8), var_pop(b::int2), var_pop(b::int4), var_pop(b::int8), var_pop(b::numeric),
+ var_samp(b::float4), var_samp(b::float8), var_samp(b::int2), var_samp(b::int4), var_samp(b::int8), var_samp(b::numeric),
+ /* aggregate = partial aggregate */
+ any_value(b * 0),
+ bit_and(c_bit), bit_and(c_1or3int2), bit_and(c_1or3int4), bit_and(c_1or3int8),
+ bit_or(c_bit), bit_or(c_1or3int2), bit_or(c_1or3int4), bit_or(c_1or3int8),
+ bit_xor(c_bit), bit_xor(c_1or3int2), bit_xor(c_1or3int4), bit_xor(c_1or3int8),
+ bool_and(c_bool),
+ bool_or(c_bool),
+ count(b), count(*),
+ every(c_bool),
+ max(c_int4array), max(c_enum), max(c_1c::char(1)), max('2000-01-01'::date + b), max('0.0.0.0'::inet + b), max(b::float4), max(b::float8), max(b::int2), max(b::int4), max(b::int8), max(c_interval), max(c_money), max(b::numeric), max(b::oid), max(c_pg_lsn), max(c_tid), max(c_1c), max(c_time), max(c_timetz), max(c_timestamp), max(c_timestamptz), max(c_xid8),
+ min(c_int4array), min(c_enum), min(c_1c::char(1)), min('2000-01-01'::date + b), min('0.0.0.0'::inet + b), min(b::float4), min(b::float8), min(b::int2), min(b::int4), min(b::int8), min(c_interval), min(c_money), min(b::numeric), min(b::oid), min(c_pg_lsn), min(c_tid), min(c_1c), min(c_time), min(c_timetz), min(c_timestamp), min(c_timestamptz), min(c_xid8),
+ range_intersect_agg(c_int4range), range_intersect_agg(c_int4multirange),
+ regr_count((2 * b + 3)::float8, b::float8),
+ sum(b::float4), sum(b::float8), sum(b::int2), sum(b::int4), sum(c_interval), sum(c_money)
+ FROM pagg_tab WHERE c_serial between 1 and 30;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Finalize Aggregate
+ Output: array_agg(pagg_tab.c_int4array), array_agg(pagg_tab.b), avg((pagg_tab.b)::smallint), avg(pagg_tab.b), avg((pagg_tab.b)::bigint), avg(pagg_tab.c_interval), avg((pagg_tab.b)::real), avg((pagg_tab.b)::double precision), avg((pagg_tab.b)::numeric), corr((pagg_tab.b)::double precision, ((pagg_tab.b * pagg_tab.b))::double precision), covar_pop((pagg_tab.b)::double precision, ((pagg_tab.b * pagg_tab.b))::double precision), covar_samp((pagg_tab.b)::double precision, ((pagg_tab.b * pagg_tab.b))::double precision), regr_avgx(((2 * pagg_tab.b))::double precision, (pagg_tab.b)::double precision), regr_avgy(((2 * pagg_tab.b))::double precision, (pagg_tab.b)::double precision), regr_intercept((((2 * pagg_tab.b) + 3))::double precision, (pagg_tab.b)::double precision), regr_r2(((pagg_tab.b * pagg_tab.b))::double precision, (pagg_tab.b)::double precision), regr_slope((((2 * pagg_tab.b) + 3))::double precision, (pagg_tab.b)::double precision), regr_sxx((((2 * pagg_tab.b) + 3))::double precision, (pagg_tab.b)::double precision), regr_sxy(((pagg_tab.b * pagg_tab.b))::double precision, (pagg_tab.b)::double precision), regr_syy((((2 * pagg_tab.b) + 3))::double precision, (pagg_tab.b)::double precision), stddev((pagg_tab.b)::real), stddev((pagg_tab.b)::double precision), stddev((pagg_tab.b)::smallint), stddev(pagg_tab.b), stddev((pagg_tab.b)::bigint), stddev((pagg_tab.b)::numeric), stddev_pop((pagg_tab.b)::real), stddev_pop((pagg_tab.b)::double precision), stddev_pop((pagg_tab.b)::smallint), stddev_pop(pagg_tab.b), stddev_pop((pagg_tab.b)::bigint), stddev_pop((pagg_tab.b)::numeric), stddev_samp((pagg_tab.b)::real), stddev_samp((pagg_tab.b)::double precision), stddev_samp((pagg_tab.b)::smallint), stddev_samp(pagg_tab.b), stddev_samp((pagg_tab.b)::bigint), stddev_samp((pagg_tab.b)::numeric), string_agg(pagg_tab.c_1c, ','::text), string_agg(pagg_tab.c_1b, '\x2c'::bytea), sum((pagg_tab.b)::bigint), sum((pagg_tab.b)::numeric), variance((pagg_tab.b)::real), variance((pagg_tab.b)::double precision), variance((pagg_tab.b)::smallint), variance(pagg_tab.b), variance((pagg_tab.b)::bigint), variance((pagg_tab.b)::numeric), var_pop((pagg_tab.b)::real), var_pop((pagg_tab.b)::double precision), var_pop((pagg_tab.b)::smallint), var_pop(pagg_tab.b), var_pop((pagg_tab.b)::bigint), var_pop((pagg_tab.b)::numeric), var_samp((pagg_tab.b)::real), var_samp((pagg_tab.b)::double precision), var_samp((pagg_tab.b)::smallint), var_samp(pagg_tab.b), var_samp((pagg_tab.b)::bigint), var_samp((pagg_tab.b)::numeric), any_value((pagg_tab.b * 0)), bit_and(pagg_tab.c_bit), bit_and(pagg_tab.c_1or3int2), bit_and(pagg_tab.c_1or3int4), bit_and(pagg_tab.c_1or3int8), bit_or(pagg_tab.c_bit), bit_or(pagg_tab.c_1or3int2), bit_or(pagg_tab.c_1or3int4), bit_or(pagg_tab.c_1or3int8), bit_xor(pagg_tab.c_bit), bit_xor(pagg_tab.c_1or3int2), bit_xor(pagg_tab.c_1or3int4), bit_xor(pagg_tab.c_1or3int8), bool_and(pagg_tab.c_bool), bool_or(pagg_tab.c_bool), count(pagg_tab.b), count(*), every(pagg_tab.c_bool), max(pagg_tab.c_int4array), max(pagg_tab.c_enum), max((pagg_tab.c_1c)::character(1)), max(('01-01-2000'::date + pagg_tab.b)), max(('0.0.0.0'::inet + (pagg_tab.b)::bigint)), max((pagg_tab.b)::real), max((pagg_tab.b)::double precision), max((pagg_tab.b)::smallint), max(pagg_tab.b), max((pagg_tab.b)::bigint), max(pagg_tab.c_interval), max(pagg_tab.c_money), max((pagg_tab.b)::numeric), max((pagg_tab.b)::oid), max(pagg_tab.c_pg_lsn), max(pagg_tab.c_tid), max(pagg_tab.c_1c), max(pagg_tab.c_time), max(pagg_tab.c_timetz), max(pagg_tab.c_timestamp), max(pagg_tab.c_timestamptz), max(pagg_tab.c_xid8), min(pagg_tab.c_int4array), min(pagg_tab.c_enum), min((pagg_tab.c_1c)::character(1)), min(('01-01-2000'::date + pagg_tab.b)), min(('0.0.0.0'::inet + (pagg_tab.b)::bigint)), min((pagg_tab.b)::real), min((pagg_tab.b)::double precision), min((pagg_tab.b)::smallint), min(pagg_tab.b), min((pagg_tab.b)::bigint), min(pagg_tab.c_interval), min(pagg_tab.c_money), min((pagg_tab.b)::numeric), min((pagg_tab.b)::oid), min(pagg_tab.c_pg_lsn), min(pagg_tab.c_tid), min(pagg_tab.c_1c), min(pagg_tab.c_time), min(pagg_tab.c_timetz), min(pagg_tab.c_timestamp), min(pagg_tab.c_timestamptz), min(pagg_tab.c_xid8), range_intersect_agg(pagg_tab.c_int4range), range_intersect_agg(pagg_tab.c_int4multirange), regr_count((((2 * pagg_tab.b) + 3))::double precision, (pagg_tab.b)::double precision), sum((pagg_tab.b)::real), sum((pagg_tab.b)::double precision), sum((pagg_tab.b)::smallint), sum(pagg_tab.b), sum(pagg_tab.c_interval), sum(pagg_tab.c_money)
+ -> Append
+ -> Foreign Scan
+ Output: (PARTIAL array_agg(pagg_tab.c_int4array)), (PARTIAL array_agg(pagg_tab.b)), (PARTIAL avg((pagg_tab.b)::smallint)), (PARTIAL avg(pagg_tab.b)), (PARTIAL avg((pagg_tab.b)::bigint)), (PARTIAL avg(pagg_tab.c_interval)), (PARTIAL avg((pagg_tab.b)::real)), (PARTIAL avg((pagg_tab.b)::double precision)), (PARTIAL avg((pagg_tab.b)::numeric)), (PARTIAL corr((pagg_tab.b)::double precision, ((pagg_tab.b * pagg_tab.b))::double precision)), (PARTIAL covar_pop((pagg_tab.b)::double precision, ((pagg_tab.b * pagg_tab.b))::double precision)), (PARTIAL covar_samp((pagg_tab.b)::double precision, ((pagg_tab.b * pagg_tab.b))::double precision)), (PARTIAL regr_avgx(((2 * pagg_tab.b))::double precision, (pagg_tab.b)::double precision)), (PARTIAL regr_avgy(((2 * pagg_tab.b))::double precision, (pagg_tab.b)::double precision)), (PARTIAL regr_intercept((((2 * pagg_tab.b) + 3))::double precision, (pagg_tab.b)::double precision)), (PARTIAL regr_r2(((pagg_tab.b * pagg_tab.b))::double precision, (pagg_tab.b)::double precision)), (PARTIAL regr_slope((((2 * pagg_tab.b) + 3))::double precision, (pagg_tab.b)::double precision)), (PARTIAL regr_sxx((((2 * pagg_tab.b) + 3))::double precision, (pagg_tab.b)::double precision)), (PARTIAL regr_sxy(((pagg_tab.b * pagg_tab.b))::double precision, (pagg_tab.b)::double precision)), (PARTIAL regr_syy((((2 * pagg_tab.b) + 3))::double precision, (pagg_tab.b)::double precision)), (PARTIAL stddev((pagg_tab.b)::real)), (PARTIAL stddev((pagg_tab.b)::double precision)), (PARTIAL stddev((pagg_tab.b)::smallint)), (PARTIAL stddev(pagg_tab.b)), (PARTIAL stddev((pagg_tab.b)::bigint)), (PARTIAL stddev((pagg_tab.b)::numeric)), (PARTIAL stddev_pop((pagg_tab.b)::real)), (PARTIAL stddev_pop((pagg_tab.b)::double precision)), (PARTIAL stddev_pop((pagg_tab.b)::smallint)), (PARTIAL stddev_pop(pagg_tab.b)), (PARTIAL stddev_pop((pagg_tab.b)::bigint)), (PARTIAL stddev_pop((pagg_tab.b)::numeric)), (PARTIAL stddev_samp((pagg_tab.b)::real)), (PARTIAL stddev_samp((pagg_tab.b)::double precision)), (PARTIAL stddev_samp((pagg_tab.b)::smallint)), (PARTIAL stddev_samp(pagg_tab.b)), (PARTIAL stddev_samp((pagg_tab.b)::bigint)), (PARTIAL stddev_samp((pagg_tab.b)::numeric)), (PARTIAL string_agg(pagg_tab.c_1c, ','::text)), (PARTIAL string_agg(pagg_tab.c_1b, '\x2c'::bytea)), (PARTIAL sum((pagg_tab.b)::bigint)), (PARTIAL sum((pagg_tab.b)::numeric)), (PARTIAL variance((pagg_tab.b)::real)), (PARTIAL variance((pagg_tab.b)::double precision)), (PARTIAL variance((pagg_tab.b)::smallint)), (PARTIAL variance(pagg_tab.b)), (PARTIAL variance((pagg_tab.b)::bigint)), (PARTIAL variance((pagg_tab.b)::numeric)), (PARTIAL var_pop((pagg_tab.b)::real)), (PARTIAL var_pop((pagg_tab.b)::double precision)), (PARTIAL var_pop((pagg_tab.b)::smallint)), (PARTIAL var_pop(pagg_tab.b)), (PARTIAL var_pop((pagg_tab.b)::bigint)), (PARTIAL var_pop((pagg_tab.b)::numeric)), (PARTIAL var_samp((pagg_tab.b)::real)), (PARTIAL var_samp((pagg_tab.b)::double precision)), (PARTIAL var_samp((pagg_tab.b)::smallint)), (PARTIAL var_samp(pagg_tab.b)), (PARTIAL var_samp((pagg_tab.b)::bigint)), (PARTIAL var_samp((pagg_tab.b)::numeric)), (PARTIAL any_value((pagg_tab.b * 0))), (PARTIAL bit_and(pagg_tab.c_bit)), (PARTIAL bit_and(pagg_tab.c_1or3int2)), (PARTIAL bit_and(pagg_tab.c_1or3int4)), (PARTIAL bit_and(pagg_tab.c_1or3int8)), (PARTIAL bit_or(pagg_tab.c_bit)), (PARTIAL bit_or(pagg_tab.c_1or3int2)), (PARTIAL bit_or(pagg_tab.c_1or3int4)), (PARTIAL bit_or(pagg_tab.c_1or3int8)), (PARTIAL bit_xor(pagg_tab.c_bit)), (PARTIAL bit_xor(pagg_tab.c_1or3int2)), (PARTIAL bit_xor(pagg_tab.c_1or3int4)), (PARTIAL bit_xor(pagg_tab.c_1or3int8)), (PARTIAL bool_and(pagg_tab.c_bool)), (PARTIAL bool_or(pagg_tab.c_bool)), (PARTIAL count(pagg_tab.b)), (PARTIAL count(*)), (PARTIAL every(pagg_tab.c_bool)), (PARTIAL max(pagg_tab.c_int4array)), (PARTIAL max(pagg_tab.c_enum)), (PARTIAL max((pagg_tab.c_1c)::character(1))), (PARTIAL max(('01-01-2000'::date + pagg_tab.b))), (PARTIAL max(('0.0.0.0'::inet + (pagg_tab.b)::bigint))), (PARTIAL max((pagg_tab.b)::real)), (PARTIAL max((pagg_tab.b)::double precision)), (PARTIAL max((pagg_tab.b)::smallint)), (PARTIAL max(pagg_tab.b)), (PARTIAL max((pagg_tab.b)::bigint)), (PARTIAL max(pagg_tab.c_interval)), (PARTIAL max(pagg_tab.c_money)), (PARTIAL max((pagg_tab.b)::numeric)), (PARTIAL max((pagg_tab.b)::oid)), (PARTIAL max(pagg_tab.c_pg_lsn)), (PARTIAL max(pagg_tab.c_tid)), (PARTIAL max(pagg_tab.c_1c)), (PARTIAL max(pagg_tab.c_time)), (PARTIAL max(pagg_tab.c_timetz)), (PARTIAL max(pagg_tab.c_timestamp)), (PARTIAL max(pagg_tab.c_timestamptz)), (PARTIAL max(pagg_tab.c_xid8)), (PARTIAL min(pagg_tab.c_int4array)), (PARTIAL min(pagg_tab.c_enum)), (PARTIAL min((pagg_tab.c_1c)::character(1))), (PARTIAL min(('01-01-2000'::date + pagg_tab.b))), (PARTIAL min(('0.0.0.0'::inet + (pagg_tab.b)::bigint))), (PARTIAL min((pagg_tab.b)::real)), (PARTIAL min((pagg_tab.b)::double precision)), (PARTIAL min((pagg_tab.b)::smallint)), (PARTIAL min(pagg_tab.b)), (PARTIAL min((pagg_tab.b)::bigint)), (PARTIAL min(pagg_tab.c_interval)), (PARTIAL min(pagg_tab.c_money)), (PARTIAL min((pagg_tab.b)::numeric)), (PARTIAL min((pagg_tab.b)::oid)), (PARTIAL min(pagg_tab.c_pg_lsn)), (PARTIAL min(pagg_tab.c_tid)), (PARTIAL min(pagg_tab.c_1c)), (PARTIAL min(pagg_tab.c_time)), (PARTIAL min(pagg_tab.c_timetz)), (PARTIAL min(pagg_tab.c_timestamp)), (PARTIAL min(pagg_tab.c_timestamptz)), (PARTIAL min(pagg_tab.c_xid8)), (PARTIAL range_intersect_agg(pagg_tab.c_int4range)), (PARTIAL range_intersect_agg(pagg_tab.c_int4multirange)), (PARTIAL regr_count((((2 * pagg_tab.b) + 3))::double precision, (pagg_tab.b)::double precision)), (PARTIAL sum((pagg_tab.b)::real)), (PARTIAL sum((pagg_tab.b)::double precision)), (PARTIAL sum((pagg_tab.b)::smallint)), (PARTIAL sum(pagg_tab.b)), (PARTIAL sum(pagg_tab.c_interval)), (PARTIAL sum(pagg_tab.c_money))
+ Relations: Aggregate on (public.fpagg_tab_p1 pagg_tab)
+ Remote SQL: SELECT array_agg(PARTIAL_AGGREGATE c_int4array), array_agg(PARTIAL_AGGREGATE b), avg(PARTIAL_AGGREGATE b::smallint), avg(PARTIAL_AGGREGATE b), avg(PARTIAL_AGGREGATE b::bigint), avg(PARTIAL_AGGREGATE c_interval), avg(PARTIAL_AGGREGATE b::real), avg(PARTIAL_AGGREGATE b::double precision), avg(PARTIAL_AGGREGATE b::numeric), corr(PARTIAL_AGGREGATE b::double precision, (b * b)::double precision), covar_pop(PARTIAL_AGGREGATE b::double precision, (b * b)::double precision), covar_samp(PARTIAL_AGGREGATE b::double precision, (b * b)::double precision), regr_avgx(PARTIAL_AGGREGATE (2 * b)::double precision, b::double precision), regr_avgy(PARTIAL_AGGREGATE (2 * b)::double precision, b::double precision), regr_intercept(PARTIAL_AGGREGATE ((2 * b) + 3)::double precision, b::double precision), regr_r2(PARTIAL_AGGREGATE (b * b)::double precision, b::double precision), regr_slope(PARTIAL_AGGREGATE ((2 * b) + 3)::double precision, b::double precision), regr_sxx(PARTIAL_AGGREGATE ((2 * b) + 3)::double precision, b::double precision), regr_sxy(PARTIAL_AGGREGATE (b * b)::double precision, b::double precision), regr_syy(PARTIAL_AGGREGATE ((2 * b) + 3)::double precision, b::double precision), stddev(PARTIAL_AGGREGATE b::real), stddev(PARTIAL_AGGREGATE b::double precision), stddev(PARTIAL_AGGREGATE b::smallint), stddev(PARTIAL_AGGREGATE b), stddev(PARTIAL_AGGREGATE b::bigint), stddev(PARTIAL_AGGREGATE b::numeric), stddev_pop(PARTIAL_AGGREGATE b::real), stddev_pop(PARTIAL_AGGREGATE b::double precision), stddev_pop(PARTIAL_AGGREGATE b::smallint), stddev_pop(PARTIAL_AGGREGATE b), stddev_pop(PARTIAL_AGGREGATE b::bigint), stddev_pop(PARTIAL_AGGREGATE b::numeric), stddev_samp(PARTIAL_AGGREGATE b::real), stddev_samp(PARTIAL_AGGREGATE b::double precision), stddev_samp(PARTIAL_AGGREGATE b::smallint), stddev_samp(PARTIAL_AGGREGATE b), stddev_samp(PARTIAL_AGGREGATE b::bigint), stddev_samp(PARTIAL_AGGREGATE b::numeric), string_agg(PARTIAL_AGGREGATE c_1c, ','::text), string_agg(PARTIAL_AGGREGATE c_1b, E'\\x2c'::bytea), sum(PARTIAL_AGGREGATE b::bigint), sum(PARTIAL_AGGREGATE b::numeric), variance(PARTIAL_AGGREGATE b::real), variance(PARTIAL_AGGREGATE b::double precision), variance(PARTIAL_AGGREGATE b::smallint), variance(PARTIAL_AGGREGATE b), variance(PARTIAL_AGGREGATE b::bigint), variance(PARTIAL_AGGREGATE b::numeric), var_pop(PARTIAL_AGGREGATE b::real), var_pop(PARTIAL_AGGREGATE b::double precision), var_pop(PARTIAL_AGGREGATE b::smallint), var_pop(PARTIAL_AGGREGATE b), var_pop(PARTIAL_AGGREGATE b::bigint), var_pop(PARTIAL_AGGREGATE b::numeric), var_samp(PARTIAL_AGGREGATE b::real), var_samp(PARTIAL_AGGREGATE b::double precision), var_samp(PARTIAL_AGGREGATE b::smallint), var_samp(PARTIAL_AGGREGATE b), var_samp(PARTIAL_AGGREGATE b::bigint), var_samp(PARTIAL_AGGREGATE b::numeric), any_value((b * 0)), bit_and(c_bit), bit_and(c_1or3int2), bit_and(c_1or3int4), bit_and(c_1or3int8), bit_or(c_bit), bit_or(c_1or3int2), bit_or(c_1or3int4), bit_or(c_1or3int8), bit_xor(c_bit), bit_xor(c_1or3int2), bit_xor(c_1or3int4), bit_xor(c_1or3int8), bool_and(c_bool), bool_or(c_bool), count(b), count(*), every(c_bool), max(c_int4array), max(c_enum), max(c_1c::character(1)), max(('01-01-2000'::date + b)), max(('0.0.0.0'::inet + b)), max(b::real), max(b::double precision), max(b::smallint), max(b), max(b::bigint), max(c_interval), max(c_money), max(b::numeric), max(b::oid), max(c_pg_lsn), max(c_tid), max(c_1c), max(c_time), max(c_timetz), max(c_timestamp), max(c_timestamptz), max(c_xid8), min(c_int4array), min(c_enum), min(c_1c::character(1)), min(('01-01-2000'::date + b)), min(('0.0.0.0'::inet + b)), min(b::real), min(b::double precision), min(b::smallint), min(b), min(b::bigint), min(c_interval), min(c_money), min(b::numeric), min(b::oid), min(c_pg_lsn), min(c_tid), min(c_1c), min(c_time), min(c_timetz), min(c_timestamp), min(c_timestamptz), min(c_xid8), range_intersect_agg(c_int4range), range_intersect_agg(c_int4multirange), regr_count(((2 * b) + 3)::double precision, b::double precision), sum(b::real), sum(b::double precision), sum(b::smallint), sum(b), sum(c_interval), sum(c_money) FROM public.pagg_tab_p1 WHERE ((c_serial >= 1)) AND ((c_serial <= 30))
+ -> Foreign Scan
+ Output: (PARTIAL array_agg(pagg_tab_1.c_int4array)), (PARTIAL array_agg(pagg_tab_1.b)), (PARTIAL avg((pagg_tab_1.b)::smallint)), (PARTIAL avg(pagg_tab_1.b)), (PARTIAL avg((pagg_tab_1.b)::bigint)), (PARTIAL avg(pagg_tab_1.c_interval)), (PARTIAL avg((pagg_tab_1.b)::real)), (PARTIAL avg((pagg_tab_1.b)::double precision)), (PARTIAL avg((pagg_tab_1.b)::numeric)), (PARTIAL corr((pagg_tab_1.b)::double precision, ((pagg_tab_1.b * pagg_tab_1.b))::double precision)), (PARTIAL covar_pop((pagg_tab_1.b)::double precision, ((pagg_tab_1.b * pagg_tab_1.b))::double precision)), (PARTIAL covar_samp((pagg_tab_1.b)::double precision, ((pagg_tab_1.b * pagg_tab_1.b))::double precision)), (PARTIAL regr_avgx(((2 * pagg_tab_1.b))::double precision, (pagg_tab_1.b)::double precision)), (PARTIAL regr_avgy(((2 * pagg_tab_1.b))::double precision, (pagg_tab_1.b)::double precision)), (PARTIAL regr_intercept((((2 * pagg_tab_1.b) + 3))::double precision, (pagg_tab_1.b)::double precision)), (PARTIAL regr_r2(((pagg_tab_1.b * pagg_tab_1.b))::double precision, (pagg_tab_1.b)::double precision)), (PARTIAL regr_slope((((2 * pagg_tab_1.b) + 3))::double precision, (pagg_tab_1.b)::double precision)), (PARTIAL regr_sxx((((2 * pagg_tab_1.b) + 3))::double precision, (pagg_tab_1.b)::double precision)), (PARTIAL regr_sxy(((pagg_tab_1.b * pagg_tab_1.b))::double precision, (pagg_tab_1.b)::double precision)), (PARTIAL regr_syy((((2 * pagg_tab_1.b) + 3))::double precision, (pagg_tab_1.b)::double precision)), (PARTIAL stddev((pagg_tab_1.b)::real)), (PARTIAL stddev((pagg_tab_1.b)::double precision)), (PARTIAL stddev((pagg_tab_1.b)::smallint)), (PARTIAL stddev(pagg_tab_1.b)), (PARTIAL stddev((pagg_tab_1.b)::bigint)), (PARTIAL stddev((pagg_tab_1.b)::numeric)), (PARTIAL stddev_pop((pagg_tab_1.b)::real)), (PARTIAL stddev_pop((pagg_tab_1.b)::double precision)), (PARTIAL stddev_pop((pagg_tab_1.b)::smallint)), (PARTIAL stddev_pop(pagg_tab_1.b)), (PARTIAL stddev_pop((pagg_tab_1.b)::bigint)), (PARTIAL stddev_pop((pagg_tab_1.b)::numeric)), (PARTIAL stddev_samp((pagg_tab_1.b)::real)), (PARTIAL stddev_samp((pagg_tab_1.b)::double precision)), (PARTIAL stddev_samp((pagg_tab_1.b)::smallint)), (PARTIAL stddev_samp(pagg_tab_1.b)), (PARTIAL stddev_samp((pagg_tab_1.b)::bigint)), (PARTIAL stddev_samp((pagg_tab_1.b)::numeric)), (PARTIAL string_agg(pagg_tab_1.c_1c, ','::text)), (PARTIAL string_agg(pagg_tab_1.c_1b, '\x2c'::bytea)), (PARTIAL sum((pagg_tab_1.b)::bigint)), (PARTIAL sum((pagg_tab_1.b)::numeric)), (PARTIAL variance((pagg_tab_1.b)::real)), (PARTIAL variance((pagg_tab_1.b)::double precision)), (PARTIAL variance((pagg_tab_1.b)::smallint)), (PARTIAL variance(pagg_tab_1.b)), (PARTIAL variance((pagg_tab_1.b)::bigint)), (PARTIAL variance((pagg_tab_1.b)::numeric)), (PARTIAL var_pop((pagg_tab_1.b)::real)), (PARTIAL var_pop((pagg_tab_1.b)::double precision)), (PARTIAL var_pop((pagg_tab_1.b)::smallint)), (PARTIAL var_pop(pagg_tab_1.b)), (PARTIAL var_pop((pagg_tab_1.b)::bigint)), (PARTIAL var_pop((pagg_tab_1.b)::numeric)), (PARTIAL var_samp((pagg_tab_1.b)::real)), (PARTIAL var_samp((pagg_tab_1.b)::double precision)), (PARTIAL var_samp((pagg_tab_1.b)::smallint)), (PARTIAL var_samp(pagg_tab_1.b)), (PARTIAL var_samp((pagg_tab_1.b)::bigint)), (PARTIAL var_samp((pagg_tab_1.b)::numeric)), (PARTIAL any_value((pagg_tab_1.b * 0))), (PARTIAL bit_and(pagg_tab_1.c_bit)), (PARTIAL bit_and(pagg_tab_1.c_1or3int2)), (PARTIAL bit_and(pagg_tab_1.c_1or3int4)), (PARTIAL bit_and(pagg_tab_1.c_1or3int8)), (PARTIAL bit_or(pagg_tab_1.c_bit)), (PARTIAL bit_or(pagg_tab_1.c_1or3int2)), (PARTIAL bit_or(pagg_tab_1.c_1or3int4)), (PARTIAL bit_or(pagg_tab_1.c_1or3int8)), (PARTIAL bit_xor(pagg_tab_1.c_bit)), (PARTIAL bit_xor(pagg_tab_1.c_1or3int2)), (PARTIAL bit_xor(pagg_tab_1.c_1or3int4)), (PARTIAL bit_xor(pagg_tab_1.c_1or3int8)), (PARTIAL bool_and(pagg_tab_1.c_bool)), (PARTIAL bool_or(pagg_tab_1.c_bool)), (PARTIAL count(pagg_tab_1.b)), (PARTIAL count(*)), (PARTIAL every(pagg_tab_1.c_bool)), (PARTIAL max(pagg_tab_1.c_int4array)), (PARTIAL max(pagg_tab_1.c_enum)), (PARTIAL max((pagg_tab_1.c_1c)::character(1))), (PARTIAL max(('01-01-2000'::date + pagg_tab_1.b))), (PARTIAL max(('0.0.0.0'::inet + (pagg_tab_1.b)::bigint))), (PARTIAL max((pagg_tab_1.b)::real)), (PARTIAL max((pagg_tab_1.b)::double precision)), (PARTIAL max((pagg_tab_1.b)::smallint)), (PARTIAL max(pagg_tab_1.b)), (PARTIAL max((pagg_tab_1.b)::bigint)), (PARTIAL max(pagg_tab_1.c_interval)), (PARTIAL max(pagg_tab_1.c_money)), (PARTIAL max((pagg_tab_1.b)::numeric)), (PARTIAL max((pagg_tab_1.b)::oid)), (PARTIAL max(pagg_tab_1.c_pg_lsn)), (PARTIAL max(pagg_tab_1.c_tid)), (PARTIAL max(pagg_tab_1.c_1c)), (PARTIAL max(pagg_tab_1.c_time)), (PARTIAL max(pagg_tab_1.c_timetz)), (PARTIAL max(pagg_tab_1.c_timestamp)), (PARTIAL max(pagg_tab_1.c_timestamptz)), (PARTIAL max(pagg_tab_1.c_xid8)), (PARTIAL min(pagg_tab_1.c_int4array)), (PARTIAL min(pagg_tab_1.c_enum)), (PARTIAL min((pagg_tab_1.c_1c)::character(1))), (PARTIAL min(('01-01-2000'::date + pagg_tab_1.b))), (PARTIAL min(('0.0.0.0'::inet + (pagg_tab_1.b)::bigint))), (PARTIAL min((pagg_tab_1.b)::real)), (PARTIAL min((pagg_tab_1.b)::double precision)), (PARTIAL min((pagg_tab_1.b)::smallint)), (PARTIAL min(pagg_tab_1.b)), (PARTIAL min((pagg_tab_1.b)::bigint)), (PARTIAL min(pagg_tab_1.c_interval)), (PARTIAL min(pagg_tab_1.c_money)), (PARTIAL min((pagg_tab_1.b)::numeric)), (PARTIAL min((pagg_tab_1.b)::oid)), (PARTIAL min(pagg_tab_1.c_pg_lsn)), (PARTIAL min(pagg_tab_1.c_tid)), (PARTIAL min(pagg_tab_1.c_1c)), (PARTIAL min(pagg_tab_1.c_time)), (PARTIAL min(pagg_tab_1.c_timetz)), (PARTIAL min(pagg_tab_1.c_timestamp)), (PARTIAL min(pagg_tab_1.c_timestamptz)), (PARTIAL min(pagg_tab_1.c_xid8)), (PARTIAL range_intersect_agg(pagg_tab_1.c_int4range)), (PARTIAL range_intersect_agg(pagg_tab_1.c_int4multirange)), (PARTIAL regr_count((((2 * pagg_tab_1.b) + 3))::double precision, (pagg_tab_1.b)::double precision)), (PARTIAL sum((pagg_tab_1.b)::real)), (PARTIAL sum((pagg_tab_1.b)::double precision)), (PARTIAL sum((pagg_tab_1.b)::smallint)), (PARTIAL sum(pagg_tab_1.b)), (PARTIAL sum(pagg_tab_1.c_interval)), (PARTIAL sum(pagg_tab_1.c_money))
+ Relations: Aggregate on (public.fpagg_tab_p2 pagg_tab_1)
+ Remote SQL: SELECT array_agg(PARTIAL_AGGREGATE c_int4array), array_agg(PARTIAL_AGGREGATE b), avg(PARTIAL_AGGREGATE b::smallint), avg(PARTIAL_AGGREGATE b), avg(PARTIAL_AGGREGATE b::bigint), avg(PARTIAL_AGGREGATE c_interval), avg(PARTIAL_AGGREGATE b::real), avg(PARTIAL_AGGREGATE b::double precision), avg(PARTIAL_AGGREGATE b::numeric), corr(PARTIAL_AGGREGATE b::double precision, (b * b)::double precision), covar_pop(PARTIAL_AGGREGATE b::double precision, (b * b)::double precision), covar_samp(PARTIAL_AGGREGATE b::double precision, (b * b)::double precision), regr_avgx(PARTIAL_AGGREGATE (2 * b)::double precision, b::double precision), regr_avgy(PARTIAL_AGGREGATE (2 * b)::double precision, b::double precision), regr_intercept(PARTIAL_AGGREGATE ((2 * b) + 3)::double precision, b::double precision), regr_r2(PARTIAL_AGGREGATE (b * b)::double precision, b::double precision), regr_slope(PARTIAL_AGGREGATE ((2 * b) + 3)::double precision, b::double precision), regr_sxx(PARTIAL_AGGREGATE ((2 * b) + 3)::double precision, b::double precision), regr_sxy(PARTIAL_AGGREGATE (b * b)::double precision, b::double precision), regr_syy(PARTIAL_AGGREGATE ((2 * b) + 3)::double precision, b::double precision), stddev(PARTIAL_AGGREGATE b::real), stddev(PARTIAL_AGGREGATE b::double precision), stddev(PARTIAL_AGGREGATE b::smallint), stddev(PARTIAL_AGGREGATE b), stddev(PARTIAL_AGGREGATE b::bigint), stddev(PARTIAL_AGGREGATE b::numeric), stddev_pop(PARTIAL_AGGREGATE b::real), stddev_pop(PARTIAL_AGGREGATE b::double precision), stddev_pop(PARTIAL_AGGREGATE b::smallint), stddev_pop(PARTIAL_AGGREGATE b), stddev_pop(PARTIAL_AGGREGATE b::bigint), stddev_pop(PARTIAL_AGGREGATE b::numeric), stddev_samp(PARTIAL_AGGREGATE b::real), stddev_samp(PARTIAL_AGGREGATE b::double precision), stddev_samp(PARTIAL_AGGREGATE b::smallint), stddev_samp(PARTIAL_AGGREGATE b), stddev_samp(PARTIAL_AGGREGATE b::bigint), stddev_samp(PARTIAL_AGGREGATE b::numeric), string_agg(PARTIAL_AGGREGATE c_1c, ','::text), string_agg(PARTIAL_AGGREGATE c_1b, E'\\x2c'::bytea), sum(PARTIAL_AGGREGATE b::bigint), sum(PARTIAL_AGGREGATE b::numeric), variance(PARTIAL_AGGREGATE b::real), variance(PARTIAL_AGGREGATE b::double precision), variance(PARTIAL_AGGREGATE b::smallint), variance(PARTIAL_AGGREGATE b), variance(PARTIAL_AGGREGATE b::bigint), variance(PARTIAL_AGGREGATE b::numeric), var_pop(PARTIAL_AGGREGATE b::real), var_pop(PARTIAL_AGGREGATE b::double precision), var_pop(PARTIAL_AGGREGATE b::smallint), var_pop(PARTIAL_AGGREGATE b), var_pop(PARTIAL_AGGREGATE b::bigint), var_pop(PARTIAL_AGGREGATE b::numeric), var_samp(PARTIAL_AGGREGATE b::real), var_samp(PARTIAL_AGGREGATE b::double precision), var_samp(PARTIAL_AGGREGATE b::smallint), var_samp(PARTIAL_AGGREGATE b), var_samp(PARTIAL_AGGREGATE b::bigint), var_samp(PARTIAL_AGGREGATE b::numeric), any_value((b * 0)), bit_and(c_bit), bit_and(c_1or3int2), bit_and(c_1or3int4), bit_and(c_1or3int8), bit_or(c_bit), bit_or(c_1or3int2), bit_or(c_1or3int4), bit_or(c_1or3int8), bit_xor(c_bit), bit_xor(c_1or3int2), bit_xor(c_1or3int4), bit_xor(c_1or3int8), bool_and(c_bool), bool_or(c_bool), count(b), count(*), every(c_bool), max(c_int4array), max(c_enum), max(c_1c::character(1)), max(('01-01-2000'::date + b)), max(('0.0.0.0'::inet + b)), max(b::real), max(b::double precision), max(b::smallint), max(b), max(b::bigint), max(c_interval), max(c_money), max(b::numeric), max(b::oid), max(c_pg_lsn), max(c_tid), max(c_1c), max(c_time), max(c_timetz), max(c_timestamp), max(c_timestamptz), max(c_xid8), min(c_int4array), min(c_enum), min(c_1c::character(1)), min(('01-01-2000'::date + b)), min(('0.0.0.0'::inet + b)), min(b::real), min(b::double precision), min(b::smallint), min(b), min(b::bigint), min(c_interval), min(c_money), min(b::numeric), min(b::oid), min(c_pg_lsn), min(c_tid), min(c_1c), min(c_time), min(c_timetz), min(c_timestamp), min(c_timestamptz), min(c_xid8), range_intersect_agg(c_int4range), range_intersect_agg(c_int4multirange), regr_count(((2 * b) + 3)::double precision, b::double precision), sum(b::real), sum(b::double precision), sum(b::smallint), sum(b), sum(c_interval), sum(c_money) FROM public.pagg_tab_p2 WHERE ((c_serial >= 1)) AND ((c_serial <= 30))
+ -> Foreign Scan
+ Output: (PARTIAL array_agg(pagg_tab_2.c_int4array)), (PARTIAL array_agg(pagg_tab_2.b)), (PARTIAL avg((pagg_tab_2.b)::smallint)), (PARTIAL avg(pagg_tab_2.b)), (PARTIAL avg((pagg_tab_2.b)::bigint)), (PARTIAL avg(pagg_tab_2.c_interval)), (PARTIAL avg((pagg_tab_2.b)::real)), (PARTIAL avg((pagg_tab_2.b)::double precision)), (PARTIAL avg((pagg_tab_2.b)::numeric)), (PARTIAL corr((pagg_tab_2.b)::double precision, ((pagg_tab_2.b * pagg_tab_2.b))::double precision)), (PARTIAL covar_pop((pagg_tab_2.b)::double precision, ((pagg_tab_2.b * pagg_tab_2.b))::double precision)), (PARTIAL covar_samp((pagg_tab_2.b)::double precision, ((pagg_tab_2.b * pagg_tab_2.b))::double precision)), (PARTIAL regr_avgx(((2 * pagg_tab_2.b))::double precision, (pagg_tab_2.b)::double precision)), (PARTIAL regr_avgy(((2 * pagg_tab_2.b))::double precision, (pagg_tab_2.b)::double precision)), (PARTIAL regr_intercept((((2 * pagg_tab_2.b) + 3))::double precision, (pagg_tab_2.b)::double precision)), (PARTIAL regr_r2(((pagg_tab_2.b * pagg_tab_2.b))::double precision, (pagg_tab_2.b)::double precision)), (PARTIAL regr_slope((((2 * pagg_tab_2.b) + 3))::double precision, (pagg_tab_2.b)::double precision)), (PARTIAL regr_sxx((((2 * pagg_tab_2.b) + 3))::double precision, (pagg_tab_2.b)::double precision)), (PARTIAL regr_sxy(((pagg_tab_2.b * pagg_tab_2.b))::double precision, (pagg_tab_2.b)::double precision)), (PARTIAL regr_syy((((2 * pagg_tab_2.b) + 3))::double precision, (pagg_tab_2.b)::double precision)), (PARTIAL stddev((pagg_tab_2.b)::real)), (PARTIAL stddev((pagg_tab_2.b)::double precision)), (PARTIAL stddev((pagg_tab_2.b)::smallint)), (PARTIAL stddev(pagg_tab_2.b)), (PARTIAL stddev((pagg_tab_2.b)::bigint)), (PARTIAL stddev((pagg_tab_2.b)::numeric)), (PARTIAL stddev_pop((pagg_tab_2.b)::real)), (PARTIAL stddev_pop((pagg_tab_2.b)::double precision)), (PARTIAL stddev_pop((pagg_tab_2.b)::smallint)), (PARTIAL stddev_pop(pagg_tab_2.b)), (PARTIAL stddev_pop((pagg_tab_2.b)::bigint)), (PARTIAL stddev_pop((pagg_tab_2.b)::numeric)), (PARTIAL stddev_samp((pagg_tab_2.b)::real)), (PARTIAL stddev_samp((pagg_tab_2.b)::double precision)), (PARTIAL stddev_samp((pagg_tab_2.b)::smallint)), (PARTIAL stddev_samp(pagg_tab_2.b)), (PARTIAL stddev_samp((pagg_tab_2.b)::bigint)), (PARTIAL stddev_samp((pagg_tab_2.b)::numeric)), (PARTIAL string_agg(pagg_tab_2.c_1c, ','::text)), (PARTIAL string_agg(pagg_tab_2.c_1b, '\x2c'::bytea)), (PARTIAL sum((pagg_tab_2.b)::bigint)), (PARTIAL sum((pagg_tab_2.b)::numeric)), (PARTIAL variance((pagg_tab_2.b)::real)), (PARTIAL variance((pagg_tab_2.b)::double precision)), (PARTIAL variance((pagg_tab_2.b)::smallint)), (PARTIAL variance(pagg_tab_2.b)), (PARTIAL variance((pagg_tab_2.b)::bigint)), (PARTIAL variance((pagg_tab_2.b)::numeric)), (PARTIAL var_pop((pagg_tab_2.b)::real)), (PARTIAL var_pop((pagg_tab_2.b)::double precision)), (PARTIAL var_pop((pagg_tab_2.b)::smallint)), (PARTIAL var_pop(pagg_tab_2.b)), (PARTIAL var_pop((pagg_tab_2.b)::bigint)), (PARTIAL var_pop((pagg_tab_2.b)::numeric)), (PARTIAL var_samp((pagg_tab_2.b)::real)), (PARTIAL var_samp((pagg_tab_2.b)::double precision)), (PARTIAL var_samp((pagg_tab_2.b)::smallint)), (PARTIAL var_samp(pagg_tab_2.b)), (PARTIAL var_samp((pagg_tab_2.b)::bigint)), (PARTIAL var_samp((pagg_tab_2.b)::numeric)), (PARTIAL any_value((pagg_tab_2.b * 0))), (PARTIAL bit_and(pagg_tab_2.c_bit)), (PARTIAL bit_and(pagg_tab_2.c_1or3int2)), (PARTIAL bit_and(pagg_tab_2.c_1or3int4)), (PARTIAL bit_and(pagg_tab_2.c_1or3int8)), (PARTIAL bit_or(pagg_tab_2.c_bit)), (PARTIAL bit_or(pagg_tab_2.c_1or3int2)), (PARTIAL bit_or(pagg_tab_2.c_1or3int4)), (PARTIAL bit_or(pagg_tab_2.c_1or3int8)), (PARTIAL bit_xor(pagg_tab_2.c_bit)), (PARTIAL bit_xor(pagg_tab_2.c_1or3int2)), (PARTIAL bit_xor(pagg_tab_2.c_1or3int4)), (PARTIAL bit_xor(pagg_tab_2.c_1or3int8)), (PARTIAL bool_and(pagg_tab_2.c_bool)), (PARTIAL bool_or(pagg_tab_2.c_bool)), (PARTIAL count(pagg_tab_2.b)), (PARTIAL count(*)), (PARTIAL every(pagg_tab_2.c_bool)), (PARTIAL max(pagg_tab_2.c_int4array)), (PARTIAL max(pagg_tab_2.c_enum)), (PARTIAL max((pagg_tab_2.c_1c)::character(1))), (PARTIAL max(('01-01-2000'::date + pagg_tab_2.b))), (PARTIAL max(('0.0.0.0'::inet + (pagg_tab_2.b)::bigint))), (PARTIAL max((pagg_tab_2.b)::real)), (PARTIAL max((pagg_tab_2.b)::double precision)), (PARTIAL max((pagg_tab_2.b)::smallint)), (PARTIAL max(pagg_tab_2.b)), (PARTIAL max((pagg_tab_2.b)::bigint)), (PARTIAL max(pagg_tab_2.c_interval)), (PARTIAL max(pagg_tab_2.c_money)), (PARTIAL max((pagg_tab_2.b)::numeric)), (PARTIAL max((pagg_tab_2.b)::oid)), (PARTIAL max(pagg_tab_2.c_pg_lsn)), (PARTIAL max(pagg_tab_2.c_tid)), (PARTIAL max(pagg_tab_2.c_1c)), (PARTIAL max(pagg_tab_2.c_time)), (PARTIAL max(pagg_tab_2.c_timetz)), (PARTIAL max(pagg_tab_2.c_timestamp)), (PARTIAL max(pagg_tab_2.c_timestamptz)), (PARTIAL max(pagg_tab_2.c_xid8)), (PARTIAL min(pagg_tab_2.c_int4array)), (PARTIAL min(pagg_tab_2.c_enum)), (PARTIAL min((pagg_tab_2.c_1c)::character(1))), (PARTIAL min(('01-01-2000'::date + pagg_tab_2.b))), (PARTIAL min(('0.0.0.0'::inet + (pagg_tab_2.b)::bigint))), (PARTIAL min((pagg_tab_2.b)::real)), (PARTIAL min((pagg_tab_2.b)::double precision)), (PARTIAL min((pagg_tab_2.b)::smallint)), (PARTIAL min(pagg_tab_2.b)), (PARTIAL min((pagg_tab_2.b)::bigint)), (PARTIAL min(pagg_tab_2.c_interval)), (PARTIAL min(pagg_tab_2.c_money)), (PARTIAL min((pagg_tab_2.b)::numeric)), (PARTIAL min((pagg_tab_2.b)::oid)), (PARTIAL min(pagg_tab_2.c_pg_lsn)), (PARTIAL min(pagg_tab_2.c_tid)), (PARTIAL min(pagg_tab_2.c_1c)), (PARTIAL min(pagg_tab_2.c_time)), (PARTIAL min(pagg_tab_2.c_timetz)), (PARTIAL min(pagg_tab_2.c_timestamp)), (PARTIAL min(pagg_tab_2.c_timestamptz)), (PARTIAL min(pagg_tab_2.c_xid8)), (PARTIAL range_intersect_agg(pagg_tab_2.c_int4range)), (PARTIAL range_intersect_agg(pagg_tab_2.c_int4multirange)), (PARTIAL regr_count((((2 * pagg_tab_2.b) + 3))::double precision, (pagg_tab_2.b)::double precision)), (PARTIAL sum((pagg_tab_2.b)::real)), (PARTIAL sum((pagg_tab_2.b)::double precision)), (PARTIAL sum((pagg_tab_2.b)::smallint)), (PARTIAL sum(pagg_tab_2.b)), (PARTIAL sum(pagg_tab_2.c_interval)), (PARTIAL sum(pagg_tab_2.c_money))
+ Relations: Aggregate on (public.fpagg_tab_p3 pagg_tab_2)
+ Remote SQL: SELECT array_agg(PARTIAL_AGGREGATE c_int4array), array_agg(PARTIAL_AGGREGATE b), avg(PARTIAL_AGGREGATE b::smallint), avg(PARTIAL_AGGREGATE b), avg(PARTIAL_AGGREGATE b::bigint), avg(PARTIAL_AGGREGATE c_interval), avg(PARTIAL_AGGREGATE b::real), avg(PARTIAL_AGGREGATE b::double precision), avg(PARTIAL_AGGREGATE b::numeric), corr(PARTIAL_AGGREGATE b::double precision, (b * b)::double precision), covar_pop(PARTIAL_AGGREGATE b::double precision, (b * b)::double precision), covar_samp(PARTIAL_AGGREGATE b::double precision, (b * b)::double precision), regr_avgx(PARTIAL_AGGREGATE (2 * b)::double precision, b::double precision), regr_avgy(PARTIAL_AGGREGATE (2 * b)::double precision, b::double precision), regr_intercept(PARTIAL_AGGREGATE ((2 * b) + 3)::double precision, b::double precision), regr_r2(PARTIAL_AGGREGATE (b * b)::double precision, b::double precision), regr_slope(PARTIAL_AGGREGATE ((2 * b) + 3)::double precision, b::double precision), regr_sxx(PARTIAL_AGGREGATE ((2 * b) + 3)::double precision, b::double precision), regr_sxy(PARTIAL_AGGREGATE (b * b)::double precision, b::double precision), regr_syy(PARTIAL_AGGREGATE ((2 * b) + 3)::double precision, b::double precision), stddev(PARTIAL_AGGREGATE b::real), stddev(PARTIAL_AGGREGATE b::double precision), stddev(PARTIAL_AGGREGATE b::smallint), stddev(PARTIAL_AGGREGATE b), stddev(PARTIAL_AGGREGATE b::bigint), stddev(PARTIAL_AGGREGATE b::numeric), stddev_pop(PARTIAL_AGGREGATE b::real), stddev_pop(PARTIAL_AGGREGATE b::double precision), stddev_pop(PARTIAL_AGGREGATE b::smallint), stddev_pop(PARTIAL_AGGREGATE b), stddev_pop(PARTIAL_AGGREGATE b::bigint), stddev_pop(PARTIAL_AGGREGATE b::numeric), stddev_samp(PARTIAL_AGGREGATE b::real), stddev_samp(PARTIAL_AGGREGATE b::double precision), stddev_samp(PARTIAL_AGGREGATE b::smallint), stddev_samp(PARTIAL_AGGREGATE b), stddev_samp(PARTIAL_AGGREGATE b::bigint), stddev_samp(PARTIAL_AGGREGATE b::numeric), string_agg(PARTIAL_AGGREGATE c_1c, ','::text), string_agg(PARTIAL_AGGREGATE c_1b, E'\\x2c'::bytea), sum(PARTIAL_AGGREGATE b::bigint), sum(PARTIAL_AGGREGATE b::numeric), variance(PARTIAL_AGGREGATE b::real), variance(PARTIAL_AGGREGATE b::double precision), variance(PARTIAL_AGGREGATE b::smallint), variance(PARTIAL_AGGREGATE b), variance(PARTIAL_AGGREGATE b::bigint), variance(PARTIAL_AGGREGATE b::numeric), var_pop(PARTIAL_AGGREGATE b::real), var_pop(PARTIAL_AGGREGATE b::double precision), var_pop(PARTIAL_AGGREGATE b::smallint), var_pop(PARTIAL_AGGREGATE b), var_pop(PARTIAL_AGGREGATE b::bigint), var_pop(PARTIAL_AGGREGATE b::numeric), var_samp(PARTIAL_AGGREGATE b::real), var_samp(PARTIAL_AGGREGATE b::double precision), var_samp(PARTIAL_AGGREGATE b::smallint), var_samp(PARTIAL_AGGREGATE b), var_samp(PARTIAL_AGGREGATE b::bigint), var_samp(PARTIAL_AGGREGATE b::numeric), any_value((b * 0)), bit_and(c_bit), bit_and(c_1or3int2), bit_and(c_1or3int4), bit_and(c_1or3int8), bit_or(c_bit), bit_or(c_1or3int2), bit_or(c_1or3int4), bit_or(c_1or3int8), bit_xor(c_bit), bit_xor(c_1or3int2), bit_xor(c_1or3int4), bit_xor(c_1or3int8), bool_and(c_bool), bool_or(c_bool), count(b), count(*), every(c_bool), max(c_int4array), max(c_enum), max(c_1c::character(1)), max(('01-01-2000'::date + b)), max(('0.0.0.0'::inet + b)), max(b::real), max(b::double precision), max(b::smallint), max(b), max(b::bigint), max(c_interval), max(c_money), max(b::numeric), max(b::oid), max(c_pg_lsn), max(c_tid), max(c_1c), max(c_time), max(c_timetz), max(c_timestamp), max(c_timestamptz), max(c_xid8), min(c_int4array), min(c_enum), min(c_1c::character(1)), min(('01-01-2000'::date + b)), min(('0.0.0.0'::inet + b)), min(b::real), min(b::double precision), min(b::smallint), min(b), min(b::bigint), min(c_interval), min(c_money), min(b::numeric), min(b::oid), min(c_pg_lsn), min(c_tid), min(c_1c), min(c_time), min(c_timetz), min(c_timestamp), min(c_timestamptz), min(c_xid8), range_intersect_agg(c_int4range), range_intersect_agg(c_int4multirange), regr_count(((2 * b) + 3)::double precision, b::double precision), sum(b::real), sum(b::double precision), sum(b::smallint), sum(b), sum(c_interval), sum(c_money) FROM public.pagg_tab_p3 WHERE ((c_serial >= 1)) AND ((c_serial <= 30))
+(15 rows)
+
+SELECT /* aggregate <> partial aggregate */
+ array_agg(c_int4array), array_agg(b),
+ avg(b::int2), avg(b::int4), avg(b::int8), avg(c_interval),
+ avg(b::float4), avg(b::float8),
+ corr(b::float8, (b * b)::float8),
+ covar_pop(b::float8, (b * b)::float8),
+ covar_samp(b::float8, (b * b)::float8),
+ regr_avgx((2 * b)::float8, b::float8),
+ regr_avgy((2 * b)::float8, b::float8),
+ regr_intercept((2 * b + 3)::float8, b::float8),
+ regr_r2((b * b)::float8, b::float8),
+ regr_slope((2 * b + 3)::float8, b::float8),
+ regr_sxx((2 * b + 3)::float8, b::float8),
+ regr_sxy((b * b)::float8, b::float8),
+ regr_syy((2 * b + 3)::float8, b::float8),
+ stddev(b::float4), stddev(b::float8), stddev(b::int2), stddev(b::int4), stddev(b::int8), stddev(b::numeric),
+ stddev_pop(b::float4), stddev_pop(b::float8), stddev_pop(b::int2), stddev_pop(b::int4), stddev_pop(b::int8), stddev_pop(b::numeric),
+ stddev_samp(b::float4), stddev_samp(b::float8), stddev_samp(b::int2), stddev_samp(b::int4), stddev_samp(b::int8), stddev_samp(b::numeric),
+ string_agg(c_1c, ','), string_agg(c_1b, ','),
+ sum(b::int8), sum(b::numeric),
+ variance(b::float4), variance(b::float8), variance(b::int2), variance(b::int4), variance(b::int8), variance(b::numeric),
+ var_pop(b::float4), var_pop(b::float8), var_pop(b::int2), var_pop(b::int4), var_pop(b::int8), var_pop(b::numeric),
+ var_samp(b::float4), var_samp(b::float8), var_samp(b::int2), var_samp(b::int4), var_samp(b::int8), var_samp(b::numeric),
+ /* aggregate = partial aggregate */
+ any_value(b * 0),
+ bit_and(c_bit), bit_and(c_1or3int2), bit_and(c_1or3int4), bit_and(c_1or3int8),
+ bit_or(c_bit), bit_or(c_1or3int2), bit_or(c_1or3int4), bit_or(c_1or3int8),
+ bit_xor(c_bit), bit_xor(c_1or3int2), bit_xor(c_1or3int4), bit_xor(c_1or3int8),
+ bool_and(c_bool),
+ bool_or(c_bool),
+ count(b), count(*),
+ every(c_bool),
+ max(c_int4array), max(c_enum), max(c_1c::char(1)), max('2000-01-01'::date + b), max('0.0.0.0'::inet + b), max(b::float4), max(b::float8), max(b::int2), max(b::int4), max(b::int8), max(c_interval), max(c_money), max(b::numeric), max(b::oid), max(c_pg_lsn), max(c_tid), max(c_1c), max(c_time), max(c_timetz), max(c_timestamp), max(c_timestamptz), max(c_xid8),
+ min(c_int4array), min(c_enum), min(c_1c::char(1)), min('2000-01-01'::date + b), min('0.0.0.0'::inet + b), min(b::float4), min(b::float8), min(b::int2), min(b::int4), min(b::int8), min(c_interval), min(c_money), min(b::numeric), min(b::oid), min(c_pg_lsn), min(c_tid), min(c_1c), min(c_time), min(c_timetz), min(c_timestamp), min(c_timestamptz), min(c_xid8),
+ range_intersect_agg(c_int4range), range_intersect_agg(c_int4multirange),
+ regr_count((2 * b + 3)::float8, b::float8),
+ sum(b::float4), sum(b::float8), sum(b::int2), sum(b::int4), sum(c_interval), sum(c_money)
+ FROM pagg_tab WHERE c_serial between 1 and 30;
+ array_agg | array_agg | avg | avg | avg | avg | avg | avg | corr | covar_pop | covar_samp | regr_avgx | regr_avgy | regr_intercept | regr_r2 | regr_slope | regr_sxx | regr_sxy | regr_syy | stddev | stddev | stddev | stddev | stddev | stddev | stddev_pop | stddev_pop | stddev_pop | stddev_pop | stddev_pop | stddev_pop | stddev_samp | stddev_samp | stddev_samp | stddev_samp | stddev_samp | stddev_samp | string_agg | string_agg | sum | sum | variance | variance | variance | variance | variance | variance | var_pop | var_pop | var_pop | var_pop | var_pop | var_pop | var_samp | var_samp | var_samp | var_samp | var_samp | var_samp | any_value | bit_and | bit_and | bit_and | bit_and | bit_or | bit_or | bit_or | bit_or | bit_xor | bit_xor | bit_xor | bit_xor | bool_and | bool_or | count | count | every | max | max | max | max | max | max | max | max | max | max | max | max | max | max | max | max | max | max | max | max | max | max | min | min | min | min | min | min | min | min | min | min | min | min | min | min | min | min | min | min | min | min | min | min | range_intersect_agg | range_intersect_agg | regr_count | sum | sum | sum | sum | sum | sum
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------+---------------------+---------------------+---------------------+------------+------+------+-------------------+------------------+------------+-----------+-----------+----------------+-------------------+------------+----------+----------+----------+-----------------+-----------------+--------------------+--------------------+--------------------+--------------------+------------------+------------------+--------------------+--------------------+--------------------+--------------------+-----------------+-----------------+--------------------+--------------------+--------------------+--------------------+-------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------+-----+-----+----------+----------+---------------------+---------------------+---------------------+---------------------+------------------+------------------+---------------------+---------------------+---------------------+---------------------+----------+----------+---------------------+---------------------+---------------------+---------------------+-----------+---------+---------+---------+---------+--------+--------+--------+--------+---------+---------+---------+---------+----------+---------+-------+-------+-------+-------+-------+-----+------------+----------+-----+-----+-----+-----+-----+---------+-------+-----+-----+------+--------+-----+----------+-------------+--------------------------+------------------------------+-----+-------+-----+-----+------------+---------+-----+-----+-----+-----+-----+-----+-------+-----+-----+-----+-------+-----+----------+-------------+--------------------------+------------------------------+-----+---------------------+---------------------+------------+-----+-----+-----+-----+-----------+--------
+ {{1,0},{0,0},{1,0},{0,0},{1,0},{0,0},{1,0},{0,0},{1,0},{0,0},{0,0},{1,0},{0,0},{1,0},{0,0},{1,0},{0,0},{1,0},{0,0},{1,0},{0,0},{1,0},{0,0},{1,0},{0,0},{1,0},{0,0},{1,0},{0,0},{1,0}} | {1,2,3,4,5,6,7,8,9,30,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29} | 15.5000000000000000 | 15.5000000000000000 | 15.5000000000000000 | @ 0.5 secs | 15.5 | 15.5 | 0.970298913589258 | 2322.41666666667 | 2402.5 | 15.5 | 31 | 3 | 0.941479981712494 | 2 | 2247.5 | 69672.5 | 8990 | 8.8034084308295 | 8.8034084308295 | 8.8034084308295046 | 8.8034084308295046 | 8.8034084308295046 | 8.8034084308295046 | 8.65544144839919 | 8.65544144839919 | 8.6554414483991899 | 8.6554414483991899 | 8.6554414483991899 | 8.6554414483991899 | 8.8034084308295 | 8.8034084308295 | 8.8034084308295046 | 8.8034084308295046 | 8.8034084308295046 | 8.8034084308295046 | 0,1,2,3,4,5,6,7,8,9,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8 | \x302c312c322c332c342c352c362c372c382c392c392c302c312c322c332c342c352c362c372c382c392c302c312c322c332c342c352c362c372c38 | 465 | 465 | 77.5 | 77.5 | 77.5000000000000000 | 77.5000000000000000 | 77.5000000000000000 | 77.5000000000000000 | 74.9166666666667 | 74.9166666666667 | 74.9166666666666667 | 74.9166666666666667 | 74.9166666666666667 | 74.9166666666666667 | 77.5 | 77.5 | 77.5000000000000000 | 77.5000000000000000 | 77.5000000000000000 | 77.5000000000000000 | 0 | 01 | 1 | 1 | 1 | 11 | 3 | 3 | 3 | 10 | 2 | 2 | 2 | f | t | 30 | 30 | f | {1,0} | happy | 9 | 01-31-2000 | 0.0.0.30 | 30 | 30 | 30 | 30 | 30 | @ 1 sec | $1.00 | 30 | 30 | 0/30 | (0,30) | 9 | 00:00:30 | 00:00:30+00 | Sat Jan 01 00:00:30 2000 | Sat Jan 01 00:00:30 2000 UTC | 9 | {0,0} | sad | 0 | 01-02-2000 | 0.0.0.1 | 1 | 1 | 1 | 1 | 1 | @ 0 | $0.00 | 1 | 1 | 0/1 | (0,1) | 0 | 00:00:01 | 00:00:01+00 | Sat Jan 01 00:00:01 2000 | Sat Jan 01 00:00:01 2000 UTC | 0 | [0,1) | {[0,1),[100,101)} | 30 | 465 | 465 | 465 | 465 | @ 15 secs | $15.00
+(1 row)
+
+-- Tests for backward compatibility
+ALTER SERVER loopback OPTIONS (ADD check_partial_aggregate_support 'false');
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b ORDER BY 1;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------
+ Sort
+ Output: pagg_tab.b, (avg(pagg_tab.a)), (max(pagg_tab.a)), (count(*))
+ Sort Key: pagg_tab.b
+ -> Finalize HashAggregate
+ Output: pagg_tab.b, avg(pagg_tab.a), max(pagg_tab.a), count(*)
+ Group Key: pagg_tab.b
+ -> Append
+ -> Foreign Scan
+ Output: pagg_tab.b, (PARTIAL avg(pagg_tab.a)), (PARTIAL max(pagg_tab.a)), (PARTIAL count(*))
+ Relations: Aggregate on (public.fpagg_tab_p1 pagg_tab)
+ Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), max(a), count(*) FROM public.pagg_tab_p1 GROUP BY 1
+ -> Foreign Scan
+ Output: pagg_tab_1.b, (PARTIAL avg(pagg_tab_1.a)), (PARTIAL max(pagg_tab_1.a)), (PARTIAL count(*))
+ Relations: Aggregate on (public.fpagg_tab_p2 pagg_tab_1)
+ Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), max(a), count(*) FROM public.pagg_tab_p2 GROUP BY 1
+ -> Foreign Scan
+ Output: pagg_tab_2.b, (PARTIAL avg(pagg_tab_2.a)), (PARTIAL max(pagg_tab_2.a)), (PARTIAL count(*))
+ Relations: Aggregate on (public.fpagg_tab_p3 pagg_tab_2)
+ Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), max(a), count(*) FROM public.pagg_tab_p3 GROUP BY 1
+(19 rows)
+
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b ORDER BY 1;
+ b | avg | max | count
+----+---------------------+-----+-------
+ 0 | 10.0000000000000000 | 20 | 60
+ 1 | 11.0000000000000000 | 21 | 60
+ 2 | 12.0000000000000000 | 22 | 60
+ 3 | 13.0000000000000000 | 23 | 60
+ 4 | 14.0000000000000000 | 24 | 60
+ 5 | 15.0000000000000000 | 25 | 60
+ 6 | 16.0000000000000000 | 26 | 60
+ 7 | 17.0000000000000000 | 27 | 60
+ 8 | 18.0000000000000000 | 28 | 60
+ 9 | 19.0000000000000000 | 29 | 60
+ 10 | 10.0000000000000000 | 20 | 60
+ 11 | 11.0000000000000000 | 21 | 60
+ 12 | 12.0000000000000000 | 22 | 60
+ 13 | 13.0000000000000000 | 23 | 60
+ 14 | 14.0000000000000000 | 24 | 60
+ 15 | 15.0000000000000000 | 25 | 60
+ 16 | 16.0000000000000000 | 26 | 60
+ 17 | 17.0000000000000000 | 27 | 60
+ 18 | 18.0000000000000000 | 28 | 60
+ 19 | 19.0000000000000000 | 29 | 60
+ 20 | 10.0000000000000000 | 20 | 60
+ 21 | 11.0000000000000000 | 21 | 60
+ 22 | 12.0000000000000000 | 22 | 60
+ 23 | 13.0000000000000000 | 23 | 60
+ 24 | 14.0000000000000000 | 24 | 60
+ 25 | 15.0000000000000000 | 25 | 60
+ 26 | 16.0000000000000000 | 26 | 60
+ 27 | 17.0000000000000000 | 27 | 60
+ 28 | 18.0000000000000000 | 28 | 60
+ 29 | 19.0000000000000000 | 29 | 60
+ 30 | 10.0000000000000000 | 20 | 60
+ 31 | 11.0000000000000000 | 21 | 60
+ 32 | 12.0000000000000000 | 22 | 60
+ 33 | 13.0000000000000000 | 23 | 60
+ 34 | 14.0000000000000000 | 24 | 60
+ 35 | 15.0000000000000000 | 25 | 60
+ 36 | 16.0000000000000000 | 26 | 60
+ 37 | 17.0000000000000000 | 27 | 60
+ 38 | 18.0000000000000000 | 28 | 60
+ 39 | 19.0000000000000000 | 29 | 60
+ 40 | 10.0000000000000000 | 20 | 60
+ 41 | 11.0000000000000000 | 21 | 60
+ 42 | 12.0000000000000000 | 22 | 60
+ 43 | 13.0000000000000000 | 23 | 60
+ 44 | 14.0000000000000000 | 24 | 60
+ 45 | 15.0000000000000000 | 25 | 60
+ 46 | 16.0000000000000000 | 26 | 60
+ 47 | 17.0000000000000000 | 27 | 60
+ 48 | 18.0000000000000000 | 28 | 60
+ 49 | 19.0000000000000000 | 29 | 60
+(50 rows)
+
+ALTER SERVER loopback OPTIONS (SET check_partial_aggregate_support 'true');
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b ORDER BY 1;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------
+ Sort
+ Output: pagg_tab.b, (avg(pagg_tab.a)), (max(pagg_tab.a)), (count(*))
+ Sort Key: pagg_tab.b
+ -> Finalize HashAggregate
+ Output: pagg_tab.b, avg(pagg_tab.a), max(pagg_tab.a), count(*)
+ Group Key: pagg_tab.b
+ -> Append
+ -> Foreign Scan
+ Output: pagg_tab.b, (PARTIAL avg(pagg_tab.a)), (PARTIAL max(pagg_tab.a)), (PARTIAL count(*))
+ Relations: Aggregate on (public.fpagg_tab_p1 pagg_tab)
+ Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), max(a), count(*) FROM public.pagg_tab_p1 GROUP BY 1
+ -> Foreign Scan
+ Output: pagg_tab_1.b, (PARTIAL avg(pagg_tab_1.a)), (PARTIAL max(pagg_tab_1.a)), (PARTIAL count(*))
+ Relations: Aggregate on (public.fpagg_tab_p2 pagg_tab_1)
+ Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), max(a), count(*) FROM public.pagg_tab_p2 GROUP BY 1
+ -> Foreign Scan
+ Output: pagg_tab_2.b, (PARTIAL avg(pagg_tab_2.a)), (PARTIAL max(pagg_tab_2.a)), (PARTIAL count(*))
+ Relations: Aggregate on (public.fpagg_tab_p3 pagg_tab_2)
+ Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), max(a), count(*) FROM public.pagg_tab_p3 GROUP BY 1
+(19 rows)
+
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b ORDER BY 1;
+ b | avg | max | count
+----+---------------------+-----+-------
+ 0 | 10.0000000000000000 | 20 | 60
+ 1 | 11.0000000000000000 | 21 | 60
+ 2 | 12.0000000000000000 | 22 | 60
+ 3 | 13.0000000000000000 | 23 | 60
+ 4 | 14.0000000000000000 | 24 | 60
+ 5 | 15.0000000000000000 | 25 | 60
+ 6 | 16.0000000000000000 | 26 | 60
+ 7 | 17.0000000000000000 | 27 | 60
+ 8 | 18.0000000000000000 | 28 | 60
+ 9 | 19.0000000000000000 | 29 | 60
+ 10 | 10.0000000000000000 | 20 | 60
+ 11 | 11.0000000000000000 | 21 | 60
+ 12 | 12.0000000000000000 | 22 | 60
+ 13 | 13.0000000000000000 | 23 | 60
+ 14 | 14.0000000000000000 | 24 | 60
+ 15 | 15.0000000000000000 | 25 | 60
+ 16 | 16.0000000000000000 | 26 | 60
+ 17 | 17.0000000000000000 | 27 | 60
+ 18 | 18.0000000000000000 | 28 | 60
+ 19 | 19.0000000000000000 | 29 | 60
+ 20 | 10.0000000000000000 | 20 | 60
+ 21 | 11.0000000000000000 | 21 | 60
+ 22 | 12.0000000000000000 | 22 | 60
+ 23 | 13.0000000000000000 | 23 | 60
+ 24 | 14.0000000000000000 | 24 | 60
+ 25 | 15.0000000000000000 | 25 | 60
+ 26 | 16.0000000000000000 | 26 | 60
+ 27 | 17.0000000000000000 | 27 | 60
+ 28 | 18.0000000000000000 | 28 | 60
+ 29 | 19.0000000000000000 | 29 | 60
+ 30 | 10.0000000000000000 | 20 | 60
+ 31 | 11.0000000000000000 | 21 | 60
+ 32 | 12.0000000000000000 | 22 | 60
+ 33 | 13.0000000000000000 | 23 | 60
+ 34 | 14.0000000000000000 | 24 | 60
+ 35 | 15.0000000000000000 | 25 | 60
+ 36 | 16.0000000000000000 | 26 | 60
+ 37 | 17.0000000000000000 | 27 | 60
+ 38 | 18.0000000000000000 | 28 | 60
+ 39 | 19.0000000000000000 | 29 | 60
+ 40 | 10.0000000000000000 | 20 | 60
+ 41 | 11.0000000000000000 | 21 | 60
+ 42 | 12.0000000000000000 | 22 | 60
+ 43 | 13.0000000000000000 | 23 | 60
+ 44 | 14.0000000000000000 | 24 | 60
+ 45 | 15.0000000000000000 | 25 | 60
+ 46 | 16.0000000000000000 | 26 | 60
+ 47 | 17.0000000000000000 | 27 | 60
+ 48 | 18.0000000000000000 | 28 | 60
+ 49 | 19.0000000000000000 | 29 | 60
+(50 rows)
+
+ALTER SERVER loopback OPTIONS (SET fdw_tuple_cost '1.0');
+SET enable_partitionwise_join=on;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT avg(t1.b), avg(t1.b::int8) FROM pagg_tab t1 JOIN pagg_tab t2 USING(a);
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Finalize Aggregate
+ Output: avg(t1.b), avg((t1.b)::bigint)
+ -> Append
+ -> Foreign Scan
+ Output: (PARTIAL avg(t1.b)), (PARTIAL avg((t1.b)::bigint))
+ Relations: Aggregate on ((public.fpagg_tab_p1 t1) INNER JOIN (public.fpagg_tab_p1 t2))
+ Remote SQL: SELECT avg(PARTIAL_AGGREGATE r4.b), avg(PARTIAL_AGGREGATE r4.b::bigint) FROM (public.pagg_tab_p1 r4 INNER JOIN public.pagg_tab_p1 r7 ON (((r4.a = r7.a))))
+ -> Foreign Scan
+ Output: (PARTIAL avg(t1_1.b)), (PARTIAL avg((t1_1.b)::bigint))
+ Relations: Aggregate on ((public.fpagg_tab_p2 t1_1) INNER JOIN (public.fpagg_tab_p2 t2_1))
+ Remote SQL: SELECT avg(PARTIAL_AGGREGATE r5.b), avg(PARTIAL_AGGREGATE r5.b::bigint) FROM (public.pagg_tab_p2 r5 INNER JOIN public.pagg_tab_p2 r8 ON (((r5.a = r8.a))))
+ -> Foreign Scan
+ Output: (PARTIAL avg(t1_2.b)), (PARTIAL avg((t1_2.b)::bigint))
+ Relations: Aggregate on ((public.fpagg_tab_p3 t1_2) INNER JOIN (public.fpagg_tab_p3 t2_2))
+ Remote SQL: SELECT avg(PARTIAL_AGGREGATE r6.b), avg(PARTIAL_AGGREGATE r6.b::bigint) FROM (public.pagg_tab_p3 r6 INNER JOIN public.pagg_tab_p3 r9 ON (((r6.a = r9.a))))
+(15 rows)
+
+SELECT avg(t1.b), avg(t1.b::int8) FROM pagg_tab t1 JOIN pagg_tab t2 USING(a);
+ avg | avg
+---------------------+---------------------
+ 24.5000000000000000 | 24.5000000000000000
+(1 row)
+
+RESET enable_partitionwise_join;
+ALTER SERVER loopback OPTIONS (SET fdw_tuple_cost '0.1');
+ALTER SERVER loopback OPTIONS (DROP check_partial_aggregate_support);
+-- It is unsafe to push down partial aggregates which contain DISTINCT clauses
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT max(a), count(distinct b) FROM pagg_tab;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Aggregate
+ Output: max(pagg_tab.a), count(DISTINCT pagg_tab.b)
-> Merge Append
Sort Key: pagg_tab.b
- -> Partial GroupAggregate
- Group Key: pagg_tab.b
- -> Foreign Scan on fpagg_tab_p1 pagg_tab
- -> Partial GroupAggregate
- Group Key: pagg_tab_1.b
- -> Foreign Scan on fpagg_tab_p2 pagg_tab_1
- -> Partial GroupAggregate
- Group Key: pagg_tab_2.b
- -> Foreign Scan on fpagg_tab_p3 pagg_tab_2
-(14 rows)
+ -> Foreign Scan on public.fpagg_tab_p1 pagg_tab_1
+ Output: pagg_tab_1.a, pagg_tab_1.b
+ Remote SQL: SELECT a, b FROM public.pagg_tab_p1 ORDER BY b ASC NULLS LAST
+ -> Foreign Scan on public.fpagg_tab_p2 pagg_tab_2
+ Output: pagg_tab_2.a, pagg_tab_2.b
+ Remote SQL: SELECT a, b FROM public.pagg_tab_p2 ORDER BY b ASC NULLS LAST
+ -> Foreign Scan on public.fpagg_tab_p3 pagg_tab_3
+ Output: pagg_tab_3.a, pagg_tab_3.b
+ Remote SQL: SELECT a, b FROM public.pagg_tab_p3 ORDER BY b ASC NULLS LAST
+(13 rows)
+
+SELECT max(a), count(distinct b) FROM pagg_tab;
+ max | count
+-----+-------
+ 29 | 50
+(1 row)
+
+-- It is unsafe to push down partial aggregates which contain ORDER BY clauses
+EXPLAIN (VERBOSE, COSTS OFF) SELECT array_agg(b order by b) FROM pagg_tab WHERE c_serial between 1 and 30;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------
+ Aggregate
+ Output: array_agg(pagg_tab.b ORDER BY pagg_tab.b)
+ -> Sort
+ Output: pagg_tab.b
+ Sort Key: pagg_tab.b
+ -> Append
+ -> Foreign Scan on public.fpagg_tab_p1 pagg_tab_1
+ Output: pagg_tab_1.b
+ Remote SQL: SELECT b FROM public.pagg_tab_p1 WHERE ((c_serial >= 1)) AND ((c_serial <= 30))
+ -> Foreign Scan on public.fpagg_tab_p2 pagg_tab_2
+ Output: pagg_tab_2.b
+ Remote SQL: SELECT b FROM public.pagg_tab_p2 WHERE ((c_serial >= 1)) AND ((c_serial <= 30))
+ -> Foreign Scan on public.fpagg_tab_p3 pagg_tab_3
+ Output: pagg_tab_3.b
+ Remote SQL: SELECT b FROM public.pagg_tab_p3 WHERE ((c_serial >= 1)) AND ((c_serial <= 30))
+(15 rows)
+
+SELECT array_agg(b order by b) FROM pagg_tab WHERE c_serial between 1 and 30;
+ array_agg
+------------------------------------------------------------------------------------
+ {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30}
+(1 row)
+ALTER SERVER loopback OPTIONS (DROP fdw_tuple_cost);
-- ===================================================================
-- access rights and superuser
-- ===================================================================
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
index 8c822f4ef9..67e15c8bb9 100644
--- a/contrib/postgres_fdw/option.c
+++ b/contrib/postgres_fdw/option.c
@@ -126,7 +126,8 @@ postgres_fdw_validator(PG_FUNCTION_ARGS)
strcmp(def->defname, "async_capable") == 0 ||
strcmp(def->defname, "parallel_commit") == 0 ||
strcmp(def->defname, "parallel_abort") == 0 ||
- strcmp(def->defname, "keep_connections") == 0)
+ strcmp(def->defname, "keep_connections") == 0 ||
+ strcmp(def->defname, "check_partial_aggregate_support") == 0)
{
/* these accept only boolean values */
(void) defGetBoolean(def);
@@ -268,6 +269,7 @@ InitPgFdwOptions(void)
/* batch_size is available on both server and table */
{"batch_size", ForeignServerRelationId, false},
{"batch_size", ForeignTableRelationId, false},
+ {"check_partial_aggregate_support", ForeignServerRelationId, false},
/* async_capable is available on both server and table */
{"async_capable", ForeignServerRelationId, false},
{"async_capable", ForeignTableRelationId, false},
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 6de2bec3b7..1350ed3d28 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -36,6 +36,7 @@
#include "optimizer/pathnode.h"
#include "optimizer/paths.h"
#include "optimizer/planmain.h"
+#include "optimizer/planner.h"
#include "optimizer/prep.h"
#include "optimizer/restrictinfo.h"
#include "optimizer/tlist.h"
@@ -519,7 +520,7 @@ static bool foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel,
JoinType jointype, RelOptInfo *outerrel, RelOptInfo *innerrel,
JoinPathExtraData *extra);
static bool foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel,
- Node *havingQual);
+ GroupPathExtraData *extra);
static List *get_useful_pathkeys_for_relation(PlannerInfo *root,
RelOptInfo *rel);
static List *get_useful_ecs_for_relation(PlannerInfo *root, RelOptInfo *rel);
@@ -650,6 +651,8 @@ postgresGetForeignRelSize(PlannerInfo *root,
fpinfo->fdw_tuple_cost = DEFAULT_FDW_TUPLE_COST;
fpinfo->shippable_extensions = NIL;
fpinfo->fetch_size = 100;
+ fpinfo->check_partial_aggregate_support = false;
+ fpinfo->remoteversion = 0;
fpinfo->async_capable = false;
apply_server_options(fpinfo);
@@ -661,7 +664,7 @@ postgresGetForeignRelSize(PlannerInfo *root,
* should match what ExecCheckPermissions() does. If we fail due to lack
* of permissions, the query would have failed at runtime anyway.
*/
- if (fpinfo->use_remote_estimate)
+ if (fpinfo->use_remote_estimate || fpinfo->check_partial_aggregate_support)
{
Oid userid;
@@ -5948,9 +5951,10 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
fpinfo->pushdown_safe = true;
/* Get user mapping */
- if (fpinfo->use_remote_estimate)
+ if (fpinfo->use_remote_estimate || fpinfo->check_partial_aggregate_support)
{
- if (fpinfo_o->use_remote_estimate)
+ if (fpinfo_o->use_remote_estimate ||
+ fpinfo_o->check_partial_aggregate_support)
fpinfo->user = fpinfo_o->user;
else
fpinfo->user = fpinfo_i->user;
@@ -6132,6 +6136,8 @@ apply_server_options(PgFdwRelationInfo *fpinfo)
ExtractExtensionList(defGetString(def), false);
else if (strcmp(def->defname, "fetch_size") == 0)
(void) parse_int(defGetString(def), &fpinfo->fetch_size, 0, NULL);
+ else if (strcmp(def->defname, "check_partial_aggregate_support") == 0)
+ fpinfo->check_partial_aggregate_support = defGetBoolean(def);
else if (strcmp(def->defname, "async_capable") == 0)
fpinfo->async_capable = defGetBoolean(def);
}
@@ -6191,6 +6197,8 @@ merge_fdw_options(PgFdwRelationInfo *fpinfo,
fpinfo->shippable_extensions = fpinfo_o->shippable_extensions;
fpinfo->use_remote_estimate = fpinfo_o->use_remote_estimate;
fpinfo->fetch_size = fpinfo_o->fetch_size;
+ fpinfo->check_partial_aggregate_support = fpinfo_o->check_partial_aggregate_support;
+ fpinfo->remoteversion = fpinfo_o->remoteversion;
fpinfo->async_capable = fpinfo_o->async_capable;
/* Merge the table level options from either side of the join. */
@@ -6373,7 +6381,7 @@ postgresGetForeignJoinPaths(PlannerInfo *root,
*/
static bool
foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel,
- Node *havingQual)
+ GroupPathExtraData *extra)
{
Query *query = root->parse;
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) grouped_rel->fdw_private;
@@ -6382,6 +6390,7 @@ foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel,
ListCell *lc;
int i;
List *tlist = NIL;
+ bool partial = extra->patype == PARTITIONWISE_AGGREGATE_PARTIAL;
/* We currently don't support pushing Grouping Sets. */
if (query->groupingSets)
@@ -6415,6 +6424,12 @@ foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel,
* a node, as long as it's not at top level; then no match is possible.
*/
i = 0;
+ fpinfo->group_clause = query->groupClause;
+ if (partial)
+ {
+ fpinfo->group_clause = extra->groupClausePartial;
+ grouping_target = extra->partial_target;
+ }
foreach(lc, grouping_target->exprs)
{
Expr *expr = (Expr *) lfirst(lc);
@@ -6426,7 +6441,7 @@ foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel,
* check the whole GROUP BY clause not just processed_groupClause,
* because we will ship all of it, cf. appendGroupByClause.
*/
- if (sgref && get_sortgroupref_clause_noerr(sgref, query->groupClause))
+ if (sgref && get_sortgroupref_clause_noerr(sgref, fpinfo->group_clause))
{
TargetEntry *tle;
@@ -6512,9 +6527,9 @@ foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel,
* Classify the pushable and non-pushable HAVING clauses and save them in
* remote_conds and local_conds of the grouped rel's fpinfo.
*/
- if (havingQual)
+ if (extra->havingQual && !partial)
{
- foreach(lc, (List *) havingQual)
+ foreach(lc, (List *) extra->havingQual)
{
Expr *expr = (Expr *) lfirst(lc);
RestrictInfo *rinfo;
@@ -6628,6 +6643,7 @@ postgresGetForeignUpperPaths(PlannerInfo *root, UpperRelationKind stage,
/* Ignore stages we don't support; and skip any duplicate calls. */
if ((stage != UPPERREL_GROUP_AGG &&
+ stage != UPPERREL_PARTIAL_GROUP_AGG &&
stage != UPPERREL_ORDERED &&
stage != UPPERREL_FINAL) ||
output_rel->fdw_private)
@@ -6644,6 +6660,10 @@ postgresGetForeignUpperPaths(PlannerInfo *root, UpperRelationKind stage,
add_foreign_grouping_paths(root, input_rel, output_rel,
(GroupPathExtraData *) extra);
break;
+ case UPPERREL_PARTIAL_GROUP_AGG:
+ add_foreign_grouping_paths(root, input_rel, output_rel,
+ (GroupPathExtraData *) extra);
+ break;
case UPPERREL_ORDERED:
add_foreign_ordered_paths(root, input_rel, output_rel);
break;
@@ -6684,7 +6704,8 @@ add_foreign_grouping_paths(PlannerInfo *root, RelOptInfo *input_rel,
return;
Assert(extra->patype == PARTITIONWISE_AGGREGATE_NONE ||
- extra->patype == PARTITIONWISE_AGGREGATE_FULL);
+ extra->patype == PARTITIONWISE_AGGREGATE_FULL ||
+ extra->patype == PARTITIONWISE_AGGREGATE_PARTIAL);
/* save the input_rel as outerrel in fpinfo */
fpinfo->outerrel = input_rel;
@@ -6704,7 +6725,7 @@ add_foreign_grouping_paths(PlannerInfo *root, RelOptInfo *input_rel,
* Use HAVING qual from extra. In case of child partition, it will have
* translated Vars.
*/
- if (!foreign_grouping_ok(root, grouped_rel, extra->havingQual))
+ if (!foreign_grouping_ok(root, grouped_rel, extra))
return;
/*
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index 47157ac887..a1a9688c9f 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -86,6 +86,15 @@ typedef struct PgFdwRelationInfo
ForeignServer *server;
UserMapping *user; /* only set in use_remote_estimate mode */
+ /* for partial aggregate pushdown */
+ bool check_partial_aggregate_support;
+
+ /*
+ * If remoteversion is zero, it means the remote server version has not
+ * been acquired.
+ */
+ int remoteversion;
+
int fetch_size; /* fetch size for this remote table */
/*
@@ -110,6 +119,7 @@ typedef struct PgFdwRelationInfo
/* Grouping information */
List *grouped_tlist;
+ List *group_clause;
/* Subquery information */
bool make_outerrel_subquery; /* do we deparse outerrel as a
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 2d14eeadb5..49558d7a0f 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -2993,16 +2993,33 @@ RESET enable_partitionwise_join;
-- ===================================================================
-- test partitionwise aggregates
-- ===================================================================
-
-CREATE TABLE pagg_tab (a int, b int, c text) PARTITION BY RANGE(a);
+ALTER SERVER loopback OPTIONS (ADD fdw_tuple_cost '0.1');
+
+CREATE TYPE mood AS enum ('sad', 'ok', 'happy');
+ALTER EXTENSION postgres_fdw ADD TYPE mood;
+
+CREATE TABLE pagg_tab (a int, b int, c text, c_serial int4,
+ c_int4array _int4, c_interval interval,
+ c_money money, c_1c text, c_1b bytea,
+ c_bit bit(2), c_1or3int2 int2,
+ c_1or3int4 int4, c_1or3int8 int8,
+ c_bool bool, c_enum mood, c_pg_lsn pg_lsn,
+ c_tid tid, c_int4range int4range,
+ c_int4multirange int4multirange,
+ c_time time, c_timetz timetz,
+ c_timestamp timestamp, c_timestamptz timestamptz,
+ c_xid8 xid8)
+ PARTITION BY RANGE(a);
CREATE TABLE pagg_tab_p1 (LIKE pagg_tab);
CREATE TABLE pagg_tab_p2 (LIKE pagg_tab);
CREATE TABLE pagg_tab_p3 (LIKE pagg_tab);
-INSERT INTO pagg_tab_p1 SELECT i % 30, i % 50, to_char(i/30, 'FM0000') FROM generate_series(1, 3000) i WHERE (i % 30) < 10;
-INSERT INTO pagg_tab_p2 SELECT i % 30, i % 50, to_char(i/30, 'FM0000') FROM generate_series(1, 3000) i WHERE (i % 30) < 20 and (i % 30) >= 10;
-INSERT INTO pagg_tab_p3 SELECT i % 30, i % 50, to_char(i/30, 'FM0000') FROM generate_series(1, 3000) i WHERE (i % 30) < 30 and (i % 30) >= 20;
+SET TimeZone = 'UTC';
+
+INSERT INTO pagg_tab_p1 SELECT i % 30, i % 50, to_char(i/30, 'FM0000'), i, array[(i % 2), 0], ((i % 2) || ' seconds')::interval, (i % 2)::money, ((i - 1) % 10)::text, (((i - 1) % 10)::text)::bytea, case when (i % 2) = 0 then B'01' else B'11' end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then true else false end, (case when (i % 2) = 0 then 'sad' else 'happy' end)::mood, ('0/' || i)::pg_lsn, ('(0,' || i || ')')::tid, int4range(0, i), int4multirange(int4range(0, i), int4range(100, 100 + i)), '00:00:00'::time + (i || ' seconds')::interval, '00:00:00'::timetz + (i || ' seconds')::interval, '2000-01-01'::timestamp + (i || ' seconds')::interval, '2000-01-01'::timestamptz + (i || ' seconds')::interval, ((i % 10)::text)::xid8 FROM generate_series(1, 3000) i WHERE (i % 30) < 10;
+INSERT INTO pagg_tab_p2 SELECT i % 30, i % 50, to_char(i/30, 'FM0000'), i, array[(i % 2), 0], ((i % 2) || ' seconds')::interval, (i % 2)::money, ((i - 1) % 10)::text, (((i - 1) % 10)::text)::bytea, case when (i % 2) = 0 then B'01' else B'11' end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then true else false end, (case when (i % 2) = 0 then 'sad' else 'happy' end)::mood, ('0/' || i)::pg_lsn, ('(0,' || i || ')')::tid, int4range(0, i), int4multirange(int4range(0, i), int4range(100, 100 + i)), '00:00:00'::time + (i || ' seconds')::interval, '00:00:00'::timetz + (i || ' seconds')::interval, '2000-01-01'::timestamp + (i || ' seconds')::interval, '2000-01-01'::timestamptz + (i || ' seconds')::interval, ((i % 10)::text)::xid8 FROM generate_series(1, 3000) i WHERE (i % 30) < 20 and (i % 30) >= 10;
+INSERT INTO pagg_tab_p3 SELECT i % 30, i % 50, to_char(i/30, 'FM0000'), i, array[(i % 2), 0], ((i % 2) || ' seconds')::interval, (i % 2)::money, ((i - 1) % 10)::text, (((i - 1) % 10)::text)::bytea, case when (i % 2) = 0 then B'01' else B'11' end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then true else false end, (case when (i % 2) = 0 then 'sad' else 'happy' end)::mood, ('0/' || i)::pg_lsn, ('(0,' || i || ')')::tid, int4range(0, i), int4multirange(int4range(0, i), int4range(100, 100 + i)), '00:00:00'::time + (i || ' seconds')::interval, '00:00:00'::timetz + (i || ' seconds')::interval, '2000-01-01'::timestamp + (i || ' seconds')::interval, '2000-01-01'::timestamptz + (i || ' seconds')::interval, ((i % 10)::text)::xid8 FROM generate_series(1, 3000) i WHERE (i % 30) < 30 and (i % 30) >= 20;
-- Create foreign partitions
CREATE FOREIGN TABLE fpagg_tab_p1 PARTITION OF pagg_tab FOR VALUES FROM (0) TO (10) SERVER loopback OPTIONS (table_name 'pagg_tab_p1');
@@ -3010,9 +3027,13 @@ CREATE FOREIGN TABLE fpagg_tab_p2 PARTITION OF pagg_tab FOR VALUES FROM (10) TO
CREATE FOREIGN TABLE fpagg_tab_p3 PARTITION OF pagg_tab FOR VALUES FROM (20) TO (30) SERVER loopback OPTIONS (table_name 'pagg_tab_p3');
ANALYZE pagg_tab;
+ANALYZE pagg_tab_p1;
+ANALYZE pagg_tab_p2;
+ANALYZE pagg_tab_p3;
ANALYZE fpagg_tab_p1;
ANALYZE fpagg_tab_p2;
ANALYZE fpagg_tab_p3;
+SET extra_float_digits = 0;
-- When GROUP BY clause matches with PARTITION KEY.
-- Plan with partitionwise aggregates is disabled
@@ -3026,16 +3047,148 @@ EXPLAIN (COSTS OFF)
SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
+-- Check partial aggregate over partitioned table
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT avg(PARTIAL_AGGREGATE a), avg(a) FROM pagg_tab;
+SELECT avg(PARTIAL_AGGREGATE a), avg(a) FROM pagg_tab;
+
-- Check with whole-row reference
-- Should have all the columns in the target list for the given relation
EXPLAIN (VERBOSE, COSTS OFF)
SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
--- When GROUP BY clause does not match with PARTITION KEY.
-EXPLAIN (COSTS OFF)
+-- Partial aggregates are safe to push down when there is a HAVING clause
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700 ORDER BY 1;
SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700 ORDER BY 1;
+-- Partial aggregates are safe to push down without having clause
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b ORDER BY 1;
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b ORDER BY 1;
+
+-- Partial aggregates are safe to push down even if we need both variable and variable-based expression
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT avg(a), max(a), count(*), (b/2)::numeric FROM pagg_tab GROUP BY b/2 ORDER BY 4;
+SELECT avg(a), max(a), count(*), (b/2)::numeric FROM pagg_tab GROUP BY b/2 ORDER BY 4;
+
+-- Partial aggregates pushdown behaves sane with non-shipped grouping elements
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT avg(a), (b/2)::numeric, 'test' test FROM pagg_tab GROUP BY 3, b/2 ORDER BY 1, 2 LIMIT 10;
+SELECT avg(a), (b/2)::numeric, 'test' test FROM pagg_tab GROUP BY 3, b/2 ORDER BY 1, 2 LIMIT 10;
+
+-- Partial aggregates are safe to push down for all built-in aggregates
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT /* aggregate <> partial aggregate */
+ array_agg(c_int4array), array_agg(b),
+ avg(b::int2), avg(b::int4), avg(b::int8), avg(c_interval), avg(b::float4), avg(b::float8), avg(b::numeric),
+ corr(b::float8, (b * b)::float8),
+ covar_pop(b::float8, (b * b)::float8),
+ covar_samp(b::float8, (b * b)::float8),
+ regr_avgx((2 * b)::float8, b::float8),
+ regr_avgy((2 * b)::float8, b::float8),
+ regr_intercept((2 * b + 3)::float8, b::float8),
+ regr_r2((b * b)::float8, b::float8),
+ regr_slope((2 * b + 3)::float8, b::float8),
+ regr_sxx((2 * b + 3)::float8, b::float8),
+ regr_sxy((b * b)::float8, b::float8),
+ regr_syy((2 * b + 3)::float8, b::float8),
+ stddev(b::float4), stddev(b::float8), stddev(b::int2), stddev(b::int4), stddev(b::int8), stddev(b::numeric),
+ stddev_pop(b::float4), stddev_pop(b::float8), stddev_pop(b::int2), stddev_pop(b::int4), stddev_pop(b::int8), stddev_pop(b::numeric),
+ stddev_samp(b::float4), stddev_samp(b::float8), stddev_samp(b::int2), stddev_samp(b::int4), stddev_samp(b::int8), stddev_samp(b::numeric),
+ string_agg(c_1c, ','), string_agg(c_1b, ','),
+ sum(b::int8), sum(b::numeric),
+ variance(b::float4), variance(b::float8), variance(b::int2), variance(b::int4), variance(b::int8), variance(b::numeric),
+ var_pop(b::float4), var_pop(b::float8), var_pop(b::int2), var_pop(b::int4), var_pop(b::int8), var_pop(b::numeric),
+ var_samp(b::float4), var_samp(b::float8), var_samp(b::int2), var_samp(b::int4), var_samp(b::int8), var_samp(b::numeric),
+ /* aggregate = partial aggregate */
+ any_value(b * 0),
+ bit_and(c_bit), bit_and(c_1or3int2), bit_and(c_1or3int4), bit_and(c_1or3int8),
+ bit_or(c_bit), bit_or(c_1or3int2), bit_or(c_1or3int4), bit_or(c_1or3int8),
+ bit_xor(c_bit), bit_xor(c_1or3int2), bit_xor(c_1or3int4), bit_xor(c_1or3int8),
+ bool_and(c_bool),
+ bool_or(c_bool),
+ count(b), count(*),
+ every(c_bool),
+ max(c_int4array), max(c_enum), max(c_1c::char(1)), max('2000-01-01'::date + b), max('0.0.0.0'::inet + b), max(b::float4), max(b::float8), max(b::int2), max(b::int4), max(b::int8), max(c_interval), max(c_money), max(b::numeric), max(b::oid), max(c_pg_lsn), max(c_tid), max(c_1c), max(c_time), max(c_timetz), max(c_timestamp), max(c_timestamptz), max(c_xid8),
+ min(c_int4array), min(c_enum), min(c_1c::char(1)), min('2000-01-01'::date + b), min('0.0.0.0'::inet + b), min(b::float4), min(b::float8), min(b::int2), min(b::int4), min(b::int8), min(c_interval), min(c_money), min(b::numeric), min(b::oid), min(c_pg_lsn), min(c_tid), min(c_1c), min(c_time), min(c_timetz), min(c_timestamp), min(c_timestamptz), min(c_xid8),
+ range_intersect_agg(c_int4range), range_intersect_agg(c_int4multirange),
+ regr_count((2 * b + 3)::float8, b::float8),
+ sum(b::float4), sum(b::float8), sum(b::int2), sum(b::int4), sum(c_interval), sum(c_money)
+ FROM pagg_tab WHERE c_serial between 1 and 30;
+
+SELECT /* aggregate <> partial aggregate */
+ array_agg(c_int4array), array_agg(b),
+ avg(b::int2), avg(b::int4), avg(b::int8), avg(c_interval),
+ avg(b::float4), avg(b::float8),
+ corr(b::float8, (b * b)::float8),
+ covar_pop(b::float8, (b * b)::float8),
+ covar_samp(b::float8, (b * b)::float8),
+ regr_avgx((2 * b)::float8, b::float8),
+ regr_avgy((2 * b)::float8, b::float8),
+ regr_intercept((2 * b + 3)::float8, b::float8),
+ regr_r2((b * b)::float8, b::float8),
+ regr_slope((2 * b + 3)::float8, b::float8),
+ regr_sxx((2 * b + 3)::float8, b::float8),
+ regr_sxy((b * b)::float8, b::float8),
+ regr_syy((2 * b + 3)::float8, b::float8),
+ stddev(b::float4), stddev(b::float8), stddev(b::int2), stddev(b::int4), stddev(b::int8), stddev(b::numeric),
+ stddev_pop(b::float4), stddev_pop(b::float8), stddev_pop(b::int2), stddev_pop(b::int4), stddev_pop(b::int8), stddev_pop(b::numeric),
+ stddev_samp(b::float4), stddev_samp(b::float8), stddev_samp(b::int2), stddev_samp(b::int4), stddev_samp(b::int8), stddev_samp(b::numeric),
+ string_agg(c_1c, ','), string_agg(c_1b, ','),
+ sum(b::int8), sum(b::numeric),
+ variance(b::float4), variance(b::float8), variance(b::int2), variance(b::int4), variance(b::int8), variance(b::numeric),
+ var_pop(b::float4), var_pop(b::float8), var_pop(b::int2), var_pop(b::int4), var_pop(b::int8), var_pop(b::numeric),
+ var_samp(b::float4), var_samp(b::float8), var_samp(b::int2), var_samp(b::int4), var_samp(b::int8), var_samp(b::numeric),
+ /* aggregate = partial aggregate */
+ any_value(b * 0),
+ bit_and(c_bit), bit_and(c_1or3int2), bit_and(c_1or3int4), bit_and(c_1or3int8),
+ bit_or(c_bit), bit_or(c_1or3int2), bit_or(c_1or3int4), bit_or(c_1or3int8),
+ bit_xor(c_bit), bit_xor(c_1or3int2), bit_xor(c_1or3int4), bit_xor(c_1or3int8),
+ bool_and(c_bool),
+ bool_or(c_bool),
+ count(b), count(*),
+ every(c_bool),
+ max(c_int4array), max(c_enum), max(c_1c::char(1)), max('2000-01-01'::date + b), max('0.0.0.0'::inet + b), max(b::float4), max(b::float8), max(b::int2), max(b::int4), max(b::int8), max(c_interval), max(c_money), max(b::numeric), max(b::oid), max(c_pg_lsn), max(c_tid), max(c_1c), max(c_time), max(c_timetz), max(c_timestamp), max(c_timestamptz), max(c_xid8),
+ min(c_int4array), min(c_enum), min(c_1c::char(1)), min('2000-01-01'::date + b), min('0.0.0.0'::inet + b), min(b::float4), min(b::float8), min(b::int2), min(b::int4), min(b::int8), min(c_interval), min(c_money), min(b::numeric), min(b::oid), min(c_pg_lsn), min(c_tid), min(c_1c), min(c_time), min(c_timetz), min(c_timestamp), min(c_timestamptz), min(c_xid8),
+ range_intersect_agg(c_int4range), range_intersect_agg(c_int4multirange),
+ regr_count((2 * b + 3)::float8, b::float8),
+ sum(b::float4), sum(b::float8), sum(b::int2), sum(b::int4), sum(c_interval), sum(c_money)
+ FROM pagg_tab WHERE c_serial between 1 and 30;
+
+-- Tests for backward compatibility
+ALTER SERVER loopback OPTIONS (ADD check_partial_aggregate_support 'false');
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b ORDER BY 1;
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b ORDER BY 1;
+
+ALTER SERVER loopback OPTIONS (SET check_partial_aggregate_support 'true');
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b ORDER BY 1;
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b ORDER BY 1;
+
+ALTER SERVER loopback OPTIONS (SET fdw_tuple_cost '1.0');
+SET enable_partitionwise_join=on;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT avg(t1.b), avg(t1.b::int8) FROM pagg_tab t1 JOIN pagg_tab t2 USING(a);
+SELECT avg(t1.b), avg(t1.b::int8) FROM pagg_tab t1 JOIN pagg_tab t2 USING(a);
+RESET enable_partitionwise_join;
+ALTER SERVER loopback OPTIONS (SET fdw_tuple_cost '0.1');
+
+ALTER SERVER loopback OPTIONS (DROP check_partial_aggregate_support);
+
+-- It is unsafe to push down partial aggregates which contain DISTINCT clauses
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT max(a), count(distinct b) FROM pagg_tab;
+SELECT max(a), count(distinct b) FROM pagg_tab;
+
+-- It is unsafe to push down partial aggregates which contain ORDER BY clauses
+EXPLAIN (VERBOSE, COSTS OFF) SELECT array_agg(b order by b) FROM pagg_tab WHERE c_serial between 1 and 30;
+SELECT array_agg(b order by b) FROM pagg_tab WHERE c_serial between 1 and 30;
+
+ALTER SERVER loopback OPTIONS (DROP fdw_tuple_cost);
+
-- ===================================================================
-- access rights and superuser
-- ===================================================================
diff --git a/src/backend/executor/nodeAgg.c b/src/backend/executor/nodeAgg.c
index f154f28902..67e0ffa666 100644
--- a/src/backend/executor/nodeAgg.c
+++ b/src/backend/executor/nodeAgg.c
@@ -1077,9 +1077,14 @@ finalize_aggregate(AggState *aggstate,
}
/*
- * Apply the agg's finalfn if one is provided, else return transValue.
+ * If the agg's finalfn is provided and PARTIAL_AGGREGATE keyword is
+ * not specified, apply the agg's finalfn.
+ * If PARTIAL_AGGREGATE keyword is specified and the transValue type
+ * is internal, apply the agg's serialfn. In this case the agg's
+ * serialfn must not be invalid. Otherwise return transValue.
*/
- if (OidIsValid(peragg->finalfn_oid))
+ if (OidIsValid(peragg->finalfn_oid) &&
+ (peragg->aggref->agg_partial == false))
{
int numFinalArgs = peragg->numFinalArgs;
@@ -1125,6 +1130,44 @@ finalize_aggregate(AggState *aggstate,
}
aggstate->curperagg = NULL;
}
+ else if (peragg->aggref->agg_partial
+ && (peragg->aggref->aggtranstype == INTERNALOID))
+ {
+ if(!OidIsValid(peragg->serialfn_oid))
+ elog(ERROR, "serialfunc is not provided for partial aggregate");
+
+ /* set up aggstate->curperagg for AggGetAggref() */
+ aggstate->curperagg = peragg;
+
+ InitFunctionCallInfoData(*fcinfo, &peragg->serialfn, 1,
+ InvalidOid, (void *) aggstate, NULL);
+
+ /* Fill in the transition state value */
+ fcinfo->args[0].value =
+ MakeExpandedObjectReadOnly(pergroupstate->transValue,
+ pergroupstate->transValueIsNull,
+ pertrans->transtypeLen);
+ fcinfo->args[0].isnull = pergroupstate->transValueIsNull;
+ anynull |= pergroupstate->transValueIsNull;
+
+ if (fcinfo->flinfo->fn_strict && anynull)
+ {
+ /* don't call a strict function with NULL inputs */
+ *resultVal = (Datum) 0;
+ *resultIsNull = true;
+ }
+ else
+ {
+ Datum result;
+
+ result = FunctionCallInvoke(fcinfo);
+ *resultIsNull = fcinfo->isnull;
+ *resultVal = MakeExpandedObjectReadOnly(result,
+ fcinfo->isnull,
+ peragg->resulttypeLen);
+ }
+ aggstate->curperagg = NULL;
+ }
else
{
*resultVal =
@@ -3663,7 +3706,7 @@ ExecInitAgg(Agg *node, EState *estate, int eflags)
Oid serialfn_oid,
deserialfn_oid;
Oid aggOwner;
- Expr *finalfnexpr;
+ Expr *finalfnexpr, *serialfnexpr;
Oid aggtranstype;
/* Planner should have assigned aggregate to correct level */
@@ -3820,6 +3863,17 @@ ExecInitAgg(Agg *node, EState *estate, int eflags)
fmgr_info(finalfn_oid, &peragg->finalfn);
fmgr_info_set_expr((Node *) finalfnexpr, &peragg->finalfn);
}
+ /*
+ * build expression trees for the serialfn, if it exists and is required.
+ */
+ if (OidIsValid(aggform->aggserialfn) && peragg->aggref->agg_partial)
+ {
+ build_aggregate_serialfn_expr(aggform->aggserialfn,
+ &serialfnexpr);
+ peragg->serialfn_oid = aggform->aggserialfn;
+ fmgr_info(aggform->aggserialfn, &peragg->serialfn);
+ fmgr_info_set_expr((Node *) serialfnexpr, &peragg->serialfn);
+ }
/* get info about the output value's datatype */
get_typlenbyval(aggref->aggtype,
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index c6fb571982..73d059f597 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -597,6 +597,7 @@ makeFuncCall(List *name, List *args, CoercionForm funcformat, int location)
n->agg_within_group = false;
n->agg_star = false;
n->agg_distinct = false;
+ n->agg_partial = false;
n->func_variadic = false;
n->funcformat = funcformat;
n->location = location;
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index a8cea5efe1..682983a153 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -206,7 +206,7 @@ static PathTarget *make_group_input_target(PlannerInfo *root,
PathTarget *final_target);
static PathTarget *make_partial_grouping_target(PlannerInfo *root,
PathTarget *grouping_target,
- Node *havingQual);
+ GroupPathExtraData *extra);
static List *postprocess_setop_tlist(List *new_tlist, List *orig_tlist);
static void optimize_window_clauses(PlannerInfo *root,
WindowFuncLists *wflists);
@@ -5415,6 +5415,99 @@ make_group_input_target(PlannerInfo *root, PathTarget *final_target)
return set_pathtarget_cost_width(root, input_target);
}
+/*
+ * setGroupClausePartial
+ * Generate a groupClause and a pathtarget for partial aggregate
+ * pushdown by FDW and set them to GroupPathExtraData.
+ */
+static void
+setGroupClausePartial(PathTarget *partial_target, List *non_group_exprs,
+ List *groupClause, GroupPathExtraData *extra)
+{
+ int exprno,
+ refno;
+ ListCell *lc;
+ Index maxRef = 0;
+ List *exprs_processed = NIL;
+ int exprs_num = 0;
+
+ foreach(lc, groupClause)
+ {
+ SortGroupClause *sgc = (SortGroupClause *) lfirst(lc);
+
+ if (sgc->tleSortGroupRef > maxRef)
+ maxRef = sgc->tleSortGroupRef;
+ }
+ maxRef++;
+
+ extra->groupClausePartial = list_copy_deep(groupClause);
+ extra->partial_target = copy_pathtarget(partial_target);
+
+ if (partial_target->exprs)
+ exprs_num = partial_target->exprs->length;
+
+ foreach(lc, non_group_exprs)
+ {
+ Expr *expr = (Expr *) lfirst(lc);
+
+ refno = -1;
+ if (list_member(exprs_processed, expr) ||
+ (!IsA(expr, Var) && !IsA(expr, PlaceHolderVar)))
+ continue;
+ exprs_processed = lappend(exprs_processed, expr);
+ for (exprno = 0; exprno < exprs_num; exprno++)
+ {
+ Expr *target_expr = (Expr *) list_nth(partial_target->exprs, exprno);
+
+ if (equal(target_expr, expr))
+ {
+ refno = exprno;
+ break;
+ }
+ }
+ if (refno < 0)
+ {
+ SortGroupClause *grpcl = makeNode(SortGroupClause);
+
+ grpcl->tleSortGroupRef = maxRef++;
+ extra->groupClausePartial = lappend(extra->groupClausePartial, grpcl);
+ add_column_to_pathtarget(extra->partial_target, expr, grpcl->tleSortGroupRef);
+ }
+ }
+}
+
+/*
+ * adjustAggrefForPartial
+ * Adjust Aggrefs to put them in partial mode
+ */
+static void
+adjustAggrefForPartial(List *exprs)
+{
+ ListCell *lc;
+
+ foreach(lc, exprs)
+ {
+ Aggref *aggref = (Aggref *) lfirst(lc);
+
+ if (IsA(aggref, Aggref))
+ {
+ Aggref *newaggref;
+
+ /*
+ * We shouldn't need to copy the substructure of the Aggref node,
+ * but flat-copy the node itself to avoid damaging other trees.
+ */
+ newaggref = makeNode(Aggref);
+ memcpy(newaggref, aggref, sizeof(Aggref));
+
+ /* For now, assume serialization is required */
+ mark_partial_aggref(newaggref, AGGSPLIT_INITIAL_SERIAL);
+
+ lfirst(lc) = newaggref;
+ }
+ }
+}
+
/*
* make_partial_grouping_target
* Generate appropriate PathTarget for output of partial aggregate
@@ -5430,11 +5523,15 @@ make_group_input_target(PlannerInfo *root, PathTarget *final_target)
*
* grouping_target is the tlist to be emitted by the topmost aggregation step.
* havingQual represents the HAVING clause.
+ *
+ * Modified PathTarget cannot be used by FDW as-is to deparse this statement.
+ * So, before modifying PathTarget, setGroupClausePartial generates
+ * another Pathtarget and another list of SortGroupClauses.
*/
static PathTarget *
make_partial_grouping_target(PlannerInfo *root,
PathTarget *grouping_target,
- Node *havingQual)
+ GroupPathExtraData *extra)
{
PathTarget *partial_target;
List *non_group_cols;
@@ -5476,8 +5573,8 @@ make_partial_grouping_target(PlannerInfo *root,
/*
* If there's a HAVING clause, we'll need the Vars/Aggrefs it uses, too.
*/
- if (havingQual)
- non_group_cols = lappend(non_group_cols, havingQual);
+ if (extra->havingQual)
+ non_group_cols = lappend(non_group_cols, extra->havingQual);
/*
* Pull out all the Vars, PlaceHolderVars, and Aggrefs mentioned in
@@ -5490,35 +5587,17 @@ make_partial_grouping_target(PlannerInfo *root,
PVC_INCLUDE_AGGREGATES |
PVC_RECURSE_WINDOWFUNCS |
PVC_INCLUDE_PLACEHOLDERS);
-
+ setGroupClausePartial(partial_target, non_group_exprs, root->processed_groupClause, extra);
add_new_columns_to_pathtarget(partial_target, non_group_exprs);
+ add_new_columns_to_pathtarget(extra->partial_target, non_group_exprs);
/*
* Adjust Aggrefs to put them in partial mode. At this point all Aggrefs
* are at the top level of the target list, so we can just scan the list
* rather than recursing through the expression trees.
*/
- foreach(lc, partial_target->exprs)
- {
- Aggref *aggref = (Aggref *) lfirst(lc);
-
- if (IsA(aggref, Aggref))
- {
- Aggref *newaggref;
-
- /*
- * We shouldn't need to copy the substructure of the Aggref node,
- * but flat-copy the node itself to avoid damaging other trees.
- */
- newaggref = makeNode(Aggref);
- memcpy(newaggref, aggref, sizeof(Aggref));
-
- /* For now, assume serialization is required */
- mark_partial_aggref(newaggref, AGGSPLIT_INITIAL_SERIAL);
-
- lfirst(lc) = newaggref;
- }
- }
+ adjustAggrefForPartial(partial_target->exprs);
+ adjustAggrefForPartial(extra->partial_target->exprs);
/* clean up cruft */
list_free(non_group_exprs);
@@ -7148,7 +7227,7 @@ create_partial_grouping_paths(PlannerInfo *root,
*/
partially_grouped_rel->reltarget =
make_partial_grouping_target(root, grouped_rel->reltarget,
- extra->havingQual);
+ extra);
if (!extra->partial_costs_set)
{
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index fc3709510d..95b3bf2776 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -2557,6 +2557,7 @@ convert_combining_aggrefs(Node *node, void *context)
parent_agg = copyObject(child_agg);
child_agg->args = orig_agg->args;
child_agg->aggfilter = orig_agg->aggfilter;
+ child_agg->agg_partial = orig_agg->agg_partial;
/*
* Now, set up child_agg to represent the first phase of partial
diff --git a/src/backend/optimizer/prep/prepagg.c b/src/backend/optimizer/prep/prepagg.c
index 806078311c..8e3d33c374 100644
--- a/src/backend/optimizer/prep/prepagg.c
+++ b/src/backend/optimizer/prep/prepagg.c
@@ -412,6 +412,7 @@ find_compatible_agg(PlannerInfo *root, Aggref *newagg,
/* all of the following must be the same or it's no match */
if (newagg->inputcollid != existingRef->inputcollid ||
newagg->aggtranstype != existingRef->aggtranstype ||
+ newagg->agg_partial != existingRef->agg_partial ||
newagg->aggstar != existingRef->aggstar ||
newagg->aggvariadic != existingRef->aggvariadic ||
newagg->aggkind != existingRef->aggkind ||
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c224df4ecc..d6ad629700 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -743,7 +743,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
ORDER ORDINALITY OTHERS OUT_P OUTER_P
OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER
- PARALLEL PARAMETER PARSER PARTIAL PARTITION PASSING PASSWORD
+ PARALLEL PARAMETER PARSER PARTIAL PARTIAL_AGGREGATE PARTITION PASSING PASSWORD
PLACING PLANS POLICY
POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION
@@ -15263,6 +15263,26 @@ func_application: func_name '(' ')'
n->agg_distinct = true;
$$ = (Node *) n;
}
+ | func_name '(' PARTIAL_AGGREGATE func_arg_list opt_sort_clause ')'
+ {
+ FuncCall *n = makeFuncCall($1, $4,
+ COERCE_EXPLICIT_CALL,
+ @1);
+
+ n->agg_order = $5;
+ n->agg_partial = true;
+ $$ = (Node *) n;
+ }
+ | func_name '(' PARTIAL_AGGREGATE '*' ')'
+ {
+ FuncCall *n = makeFuncCall($1, NIL,
+ COERCE_EXPLICIT_CALL,
+ @1);
+
+ n->agg_star = true;
+ n->agg_partial = true;
+ $$ = (Node *) n;
+ }
| func_name '(' '*' ')'
{
/*
@@ -15318,6 +15338,11 @@ func_expr: func_application within_group_clause filter_clause over_clause
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("cannot use DISTINCT with WITHIN GROUP"),
parser_errposition(@2)));
+ if (n->agg_partial)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("cannot use PARTIAL_AGGREGATE with WITHIN GROUP"),
+ parser_errposition(@2)));
if (n->func_variadic)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -17551,6 +17576,7 @@ reserved_keyword:
| ONLY
| OR
| ORDER
+ | PARTIAL_AGGREGATE
| PLACING
| PRIMARY
| REFERENCES
@@ -17857,6 +17883,7 @@ bare_label_keyword:
| PARAMETER
| PARSER
| PARTIAL
+ | PARTIAL_AGGREGATE
| PARTITION
| PASSING
| PASSWORD
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 9bbad33fbd..05d8b1ec83 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -100,8 +100,8 @@ static Node *make_agg_arg(Oid argtype, Oid argcollation);
* pstate level.
*/
void
-transformAggregateCall(ParseState *pstate, Aggref *agg,
- List *args, List *aggorder, bool agg_distinct)
+transformAggregateCall(ParseState *pstate, Aggref *agg, List *args,
+ List *aggorder, bool agg_distinct, bool agg_partial)
{
List *argtypes = NIL;
List *tlist = NIL;
@@ -146,8 +146,8 @@ transformAggregateCall(ParseState *pstate, Aggref *agg,
torder, tlist, sortby);
}
- /* Never any DISTINCT in an ordered-set agg */
- Assert(!agg_distinct);
+ /* Never any DISTINCT and PARTIAL_AGG in an ordered-set agg */
+ Assert(!agg_distinct || !agg_partial);
}
else
{
@@ -221,6 +221,22 @@ transformAggregateCall(ParseState *pstate, Aggref *agg,
agg->args = tlist;
agg->aggorder = torder;
agg->aggdistinct = tdistinct;
+ agg->agg_partial = agg_partial;
+ if(agg->agg_partial){
+ HeapTuple aggtup;
+ Form_pg_aggregate aggform;
+
+ aggtup = SearchSysCache1(AGGFNOID, ObjectIdGetDatum(agg->aggfnoid));
+ if (!HeapTupleIsValid(aggtup))
+ elog(ERROR, "cache lookup failed for aggregate %u", agg->aggfnoid);
+ aggform = (Form_pg_aggregate) GETSTRUCT(aggtup);
+ ReleaseSysCache(aggtup);
+ if(aggform->aggtranstype == INTERNALOID){
+ agg->aggtype = BYTEAOID;
+ } else {
+ agg->aggtype = aggform->aggtranstype;
+ }
+ }
/*
* Now build the aggargtypes list with the type OIDs of the direct and
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 334b9b42bd..1503bdef96 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -543,6 +543,7 @@ transformRangeFunction(ParseState *pstate, RangeFunction *r)
fc->over == NULL &&
!fc->agg_star &&
!fc->agg_distinct &&
+ !fc->agg_partial &&
!fc->func_variadic &&
coldeflist == NIL)
{
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 64c582c344..6f2f2aebc7 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -3776,7 +3776,8 @@ transformJsonAggConstructor(ParseState *pstate, JsonAggConstructor *agg_ctor,
aggref->aggtransno = -1;
aggref->location = agg_ctor->location;
- transformAggregateCall(pstate, aggref, args, agg_ctor->agg_order, false);
+ transformAggregateCall(pstate, aggref, args, agg_ctor->agg_order, false,
+ false);
node = (Node *) aggref;
}
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index 6c29471bb3..065c874a4e 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -97,6 +97,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
bool agg_within_group = (fn ? fn->agg_within_group : false);
bool agg_star = (fn ? fn->agg_star : false);
bool agg_distinct = (fn ? fn->agg_distinct : false);
+ bool agg_partial = (fn ? fn->agg_partial : false);
bool func_variadic = (fn ? fn->func_variadic : false);
CoercionForm funcformat = (fn ? fn->funcformat : COERCE_EXPLICIT_CALL);
bool could_be_projection;
@@ -222,7 +223,8 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
*/
could_be_projection = (nargs == 1 && !proc_call &&
agg_order == NIL && agg_filter == NULL &&
- !agg_star && !agg_distinct && over == NULL &&
+ !agg_star && !agg_distinct &&
+ !agg_partial && over == NULL &&
!func_variadic && argnames == NIL &&
list_length(funcname) == 1 &&
(actual_arg_types[0] == RECORDOID ||
@@ -322,6 +324,12 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
errmsg("DISTINCT specified, but %s is not an aggregate function",
NameListToString(funcname)),
parser_errposition(pstate, location)));
+ if (agg_partial)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("PARTIAL_AGGREGATE specified, but %s is not an aggregate function",
+ NameListToString(funcname)),
+ parser_errposition(pstate, location)));
if (agg_within_group)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -392,6 +400,8 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
parser_errposition(pstate, location)));
/* gram.y rejects DISTINCT + WITHIN GROUP */
Assert(!agg_distinct);
+ /* gram.y rejects PARTIAL_AGGREGATE */
+ Assert(!agg_partial);
/* gram.y rejects VARIADIC + WITHIN GROUP */
Assert(!func_variadic);
@@ -814,7 +824,8 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
parser_errposition(pstate, location)));
/* parse_agg.c does additional aggregate-specific processing */
- transformAggregateCall(pstate, aggref, fargs, agg_order, agg_distinct);
+ transformAggregateCall(pstate, aggref, fargs, agg_order, agg_distinct,
+ agg_partial);
retval = (Node *) aggref;
}
@@ -845,6 +856,15 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
errmsg("DISTINCT is not implemented for window functions"),
parser_errposition(pstate, location)));
+ /*
+ * partial aggregates not allowed in windows yet
+ */
+ if (agg_partial)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("PARTIAL_AGGREGATE is not implemented for window functions"),
+ parser_errposition(pstate, location)));
+
/*
* Reject attempt to call a parameterless aggregate without (*)
* syntax. This is mere pedantry but some folks insisted ...
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index ed7f40f053..defad453c7 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -10123,7 +10123,8 @@ get_agg_expr_helper(Aggref *aggref, deparse_context *context,
/* Print the aggregate name, schema-qualified if needed */
appendStringInfo(buf, "%s(%s", funcname,
- (aggref->aggdistinct != NIL) ? "DISTINCT " : "");
+ (aggref->aggdistinct != NIL) ? "DISTINCT " :
+ aggref->agg_partial ? "PARTIAL_AGGREGATE " : "");
if (AGGKIND_IS_ORDERED_SET(aggref->aggkind))
{
diff --git a/src/include/executor/nodeAgg.h b/src/include/executor/nodeAgg.h
index 4e43b86d31..b1c8945a40 100644
--- a/src/include/executor/nodeAgg.h
+++ b/src/include/executor/nodeAgg.h
@@ -206,6 +206,15 @@ typedef struct AggStatePerAggData
*/
FmgrInfo finalfn;
+ /* Optional Oid of serial function (may be InvalidOid) */
+ Oid serialfn_oid;
+
+ /*
+ * fmgr lookup data for serial function --- only valid when serialfn_oid is
+ * not InvalidOid.
+ */
+ FmgrInfo serialfn;
+
/*
* Number of arguments to pass to the finalfn. This is always at least 1
* (the transition state value) plus any ordered-set direct args. If the
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index e494309da8..4c6f051da5 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -421,6 +421,7 @@ typedef struct FuncCall
bool agg_within_group; /* ORDER BY appeared in WITHIN GROUP */
bool agg_star; /* argument was really '*' */
bool agg_distinct; /* arguments were labeled DISTINCT */
+ bool agg_partial; /* arguments were labeled PARTIAL_AGGREGATE */
bool func_variadic; /* last argument was labeled VARIADIC */
CoercionForm funcformat; /* how to display this node */
int location; /* token location, or -1 if unknown */
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index ed85dc7414..411a249482 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -3243,6 +3243,9 @@ typedef enum
* havingQual gives list of quals to be applied after aggregation.
* targetList gives list of columns to be projected.
* patype is the type of partitionwise aggregation that is being performed.
+ * groupClausePartial is List of SortGroupClauses for partial aggregate
+ * pushdown by FDW
+ * partial_target is PathTarget for partial aggregate pushdown by FDW
*/
typedef struct
{
@@ -3257,6 +3260,8 @@ typedef struct
Node *havingQual;
List *targetList;
PartitionwiseAggregateType patype;
+ List *groupClausePartial;
+ PathTarget *partial_target;
} GroupPathExtraData;
/*
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index bb930afb52..1287eff29e 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -459,6 +459,9 @@ typedef struct Aggref
/* DISTINCT (list of SortGroupClause) */
List *aggdistinct;
+ /* true if there is PARTIAL_AGGREGATE keyword */
+ bool agg_partial;
+
/* FILTER expression, if any */
Expr *aggfilter;
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 5984dcfa4b..15dbe413d9 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -323,6 +323,7 @@ PG_KEYWORD("parallel", PARALLEL, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("parameter", PARAMETER, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("parser", PARSER, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("partial", PARTIAL, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("partial_aggregate", PARTIAL_AGGREGATE, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("partition", PARTITION, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("passing", PASSING, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("password", PASSWORD, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/include/parser/parse_agg.h b/src/include/parser/parse_agg.h
index e9afd99d11..288f8a77d3 100644
--- a/src/include/parser/parse_agg.h
+++ b/src/include/parser/parse_agg.h
@@ -17,7 +17,7 @@
extern void transformAggregateCall(ParseState *pstate, Aggref *agg,
List *args, List *aggorder,
- bool agg_distinct);
+ bool agg_distinct, bool agg_partial);
extern Node *transformGroupingFunc(ParseState *pstate, GroupingFunc *p);
--
2.31.1
view thread (45+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: RE: Partial aggregates pushdown
In-Reply-To: <TYAPR01MB30884F8E4D94038CD661FFEF95F02@TYAPR01MB3088.jpnprd01.prod.outlook.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox