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: Finnerty, Jim <[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: Sat, 16 Mar 2024 02:28:50 +0000
Message-ID: <TYAPR01MB55141D18188AC86ADCE35FCB952F2@TYAPR01MB5514.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]>
Hi. Mr.Pyhalov.
> From: Alexander Pyhalov <[email protected]>
> Sent: Wednesday, February 28, 2024 10:43 PM
> > 1. Transmitting state value safely between machines
> >> From: Robert Haas <[email protected]>
> >> Sent: Wednesday, December 6, 2023 10:25 PM the problems around
> >> transmitting serialized bytea blobs between machines that can't be
> >> assumed to fully trust each other will need to be addressed in some
> >> way, which seems like it will require a good deal of design work,
> >> forming some kind of consensus, and then implementation work to
> >> follow.
> > I have considered methods for safely transmitting state values between
> > different machines.
> > I have taken into account the version policy of PostgreSQL (5 years of
> > support) and the major version release cycle over the past 10 years (1
> > year), and as a result, I have made the assumption that transmission
> > is allowed only when the difference between the local version and the
> > remote version is 5 or less.
> > I believe that by adding new components, "export function" and "import
> > function", to the aggregate functions, and further introducing a new
> > SQL keyword to the query syntax of aggregate expressions, we can
> > address this issue.
> >
...
>
> I honestly think that achieving cross-version compatibility in this way puts a significant burden on developers. Can we
> instead always use the more or less universal export and import function to fix possible issues with binary representations
> on different architectures and just refuse to push down partial aggregates on server version mismatch? At least at the first
> step?
Thank you for your comment. I agree with your point that the proposed method would impose a significant burden on developers. In order to ensure cross-version compatibility, it is necessary to impose constraints on the format of the state values exchanged between servers, which would indeed burden developers. As you mentioned, I think that it is realistic to allow partial aggregation pushdown only when coordinating between the same versions in the first step.
> From: Alexander Pyhalov <[email protected]>
> Sent: Wednesday, February 28, 2024 10:43 PM
> > 3. Fixing the behavior when the HAVING clause is present
> >> From: Robert Haas <[email protected]>
> >> Sent: Tuesday, November 28, 2023 4:08 AM
> >>
> >> On Wed, Nov 22, 2023 at 1:32 AM Alexander Pyhalov
> >> <[email protected]> wrote:
> >> > Hi. HAVING is also a problem. Consider the following query
> >> >
> >> > SELECT count(a) FROM t HAVING count(a) > 10 - we can't push it down
> >> > to foreign server as HAVING needs full aggregate result, but
> >> > foreign server don't know it.
> >>
> >> I don't see it that way. What we would push to the foreign server
> >> would be something like SELECT count(a) FROM t. Then, after we get
> >> the results back and combine the various partial counts locally, we
> >> would locally evaluate the HAVING clause afterward. That is, partial
> >> aggregation is a barrier to pushing down HAVING clause itself, but it
> >> doesn't preclude pushing down the aggregation.
> > I have made modifications in the attached patch to ensure that when
> > the HAVING clause is present, the HAVING clause is executed locally
> > while the partial aggregations are pushed down.
> >
> >
>
> Sorry, I don't see how it works. When we have partial aggregates and having clause, foreign_grouping_ok() returns false and
> add_foreign_grouping_paths() adds no paths.
> I'm not saying it's necessary to fix this in the first patch version.
Our sincere apologies. I had attached an older version before this modification.
> From: Alexander Pyhalov <[email protected]>
> Sent: Wednesday, February 28, 2024 10:43 PM
> contrib/postgres_fdw/deparse.c: comment before appendFunctionName() has gone, this seems to be wrong.
Fixed.
> From: Alexander Pyhalov <[email protected]>
> Sent: Wednesday, February 28, 2024 10:43 PM
> In finalize_aggregate()
>
> 1079 /*
> 1080 * Apply the agg's finalfn if one is provided, else return
> transValue.
> 1081 */
>
> Comment should be updated to note behavior for agg_partial aggregates.
Fixed.
> From: Alexander Pyhalov <[email protected]>
> Sent: Wednesday, February 28, 2024 10:43 PM
> In this if branch, should we check just for peragg->aggref->agg_partial and peragg->aggref->aggtranstype ==
> INTERNALOID? It seems that if
> peragg->aggref->aggtranstype == INTERNALOID and there's no
> serialfn_oid, it's likely an error (and one should be generated).
As you pointed out, I have made modifications to the source code so that it terminates with an error if serialfn is invalid.
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-v2.patch (143.3K, ../TYAPR01MB55141D18188AC86ADCE35FCB952F2@TYAPR01MB5514.jpnprd01.prod.outlook.com/2-0001-Partial-aggregates-push-down-SQL-keyword-v2.patch)
download | inline diff:
From 06c28577da902167d5234c4fcac090a6b2ac4cd9 Mon Sep 17 00:00:00 2001
From: "Yuki Fujii [email protected]"
<[email protected]>
Date: Sat, 16 Mar 2024 21:02:57 +0900
Subject: [PATCH] Partial aggregates push down SQL keyword v2
---
contrib/postgres_fdw/deparse.c | 83 +++-
contrib/postgres_fdw/expected/postgres_fdw.out | 639 +++++++++++++++++++++++--
contrib/postgres_fdw/option.c | 4 +-
contrib/postgres_fdw/postgres_fdw.c | 43 +-
contrib/postgres_fdw/postgres_fdw.h | 10 +
contrib/postgres_fdw/sql/postgres_fdw.sql | 155 +++++-
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, 1115 insertions(+), 120 deletions(-)
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index ce765ec..c89161c 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -206,7 +206,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,
@@ -911,8 +911,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. */
@@ -3659,18 +3660,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))
{
@@ -3865,6 +3883,7 @@ 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)
@@ -3885,7 +3904,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);
@@ -4206,3 +4225,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 58a603a..7864159 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -10058,36 +10058,56 @@ 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;
+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;
@@ -10121,32 +10141,34 @@ SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 O
-- 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
@@ -10159,27 +10181,538 @@ 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
------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------
Finalize GroupAggregate
+ Output: pagg_tab.b, avg(pagg_tab.a), max(pagg_tab.a), count(*)
Group Key: pagg_tab.b
Filter: (sum(pagg_tab.a) < 700)
+ -> Sort
+ Output: pagg_tab.b, (PARTIAL avg(pagg_tab.a)), (PARTIAL max(pagg_tab.a)), (PARTIAL count(*)), (PARTIAL sum(pagg_tab.a))
+ Sort Key: pagg_tab.b
+ -> Append
+ -> Foreign Scan
+ Output: pagg_tab.b, (PARTIAL avg(pagg_tab.a)), (PARTIAL max(pagg_tab.a)), (PARTIAL count(*)), (PARTIAL sum(pagg_tab.a))
+ Filter: ((sum(pagg_tab.a)) < 700)
+ Relations: Aggregate on (public.fpagg_tab_p1 pagg_tab)
+ Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), max(a), count(*), sum(a), 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))
+ Filter: ((sum(pagg_tab_1.a)) < 700)
+ Relations: Aggregate on (public.fpagg_tab_p2 pagg_tab_1)
+ Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), max(a), count(*), sum(a), 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))
+ Filter: ((sum(pagg_tab_2.a)) < 700)
+ Relations: Aggregate on (public.fpagg_tab_p3 pagg_tab_2)
+ Remote SQL: SELECT b, avg(PARTIAL_AGGREGATE a), max(a), count(*), sum(a), sum(a) FROM public.pagg_tab_p3 GROUP BY 1
+(23 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 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(PARTIAL_AGGREGATE 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(PARTIAL_AGGREGATE 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(PARTIAL_AGGREGATE 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-07 | Sat Jan 01 00:00:30 2000 | Sat Jan 01 00:00:30 2000 PST | 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-07 | Sat Jan 01 00:00:01 2000 | Sat Jan 01 00:00:01 2000 PST | 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 630b304..cc6d808 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 142dcfc..42eb17a 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;
@@ -6031,9 +6034,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;
@@ -6215,6 +6219,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);
}
@@ -6274,6 +6280,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. */
@@ -6456,7 +6464,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;
@@ -6465,6 +6473,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)
@@ -6498,6 +6507,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);
@@ -6509,7 +6524,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;
@@ -6595,9 +6610,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)
{
- foreach(lc, (List *) havingQual)
+ foreach(lc, (List *) extra->havingQual)
{
Expr *expr = (Expr *) lfirst(lc);
RestrictInfo *rinfo;
@@ -6617,7 +6632,7 @@ foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel,
grouped_rel->relids,
NULL,
NULL);
- if (is_foreign_expr(root, grouped_rel, expr))
+ if (is_foreign_expr(root, grouped_rel, expr) && !partial)
fpinfo->remote_conds = lappend(fpinfo->remote_conds, rinfo);
else
fpinfo->local_conds = lappend(fpinfo->local_conds, rinfo);
@@ -6711,6 +6726,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)
@@ -6727,6 +6743,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;
@@ -6767,7 +6787,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;
@@ -6787,7 +6808,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 37c1575..dcf1641 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 e3d147d..ebe3c14 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3137,16 +3137,31 @@ 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;
+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');
@@ -3154,9 +3169,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
@@ -3176,9 +3195,131 @@ 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 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 53ead77..eb64ed5 100644
--- a/src/backend/executor/nodeAgg.c
+++ b/src/backend/executor/nodeAgg.c
@@ -1076,9 +1076,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, if 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;
@@ -1124,6 +1129,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 note 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 =
@@ -3662,7 +3705,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 */
@@ -3819,6 +3862,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 33d4d23..7b17738 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -596,6 +596,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 5564826..228cb01 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -201,7 +201,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);
@@ -5333,6 +5333,99 @@ make_group_input_target(PlannerInfo *root, PathTarget *final_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
* (or partial grouping, if there are no aggregates) nodes.
@@ -5347,11 +5440,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;
@@ -5393,8 +5490,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
@@ -5407,35 +5504,17 @@ make_partial_grouping_target(PlannerInfo *root,
PVC_INCLUDE_AGGREGATES |
PVC_RECURSE_WINDOWFUNCS |
PVC_INCLUDE_PLACEHOLDERS);
-
+ setGroupClausePartial(partial_target, non_group_exprs, root->parse->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);
@@ -7088,7 +7167,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 42603db..dfe5520 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -2556,6 +2556,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 = false;
/*
* 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 4606df3..aced9f7 100644
--- a/src/backend/optimizer/prep/prepagg.c
+++ b/src/backend/optimizer/prep/prepagg.c
@@ -411,6 +411,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 c6e2f67..7de4342 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -745,7 +745,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
@@ -15303,6 +15303,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 '(' '*' ')'
{
/*
@@ -15358,6 +15378,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),
@@ -17624,6 +17649,7 @@ reserved_keyword:
| ONLY
| OR
| ORDER
+ | PARTIAL_AGGREGATE
| PLACING
| PRIMARY
| REFERENCES
@@ -17930,6 +17956,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 9d151a8..432d18b 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -101,8 +101,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;
@@ -147,8 +147,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
{
@@ -222,6 +222,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 d2ac867..e2a766c 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -541,6 +541,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 9300c7b..3c34df4 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 fdb3e6d..0c30afb 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;
}
@@ -846,6 +857,15 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
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 2a1ee69..cf26f71 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -10122,7 +10122,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 684779a..079360c 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 aadaf67..e150ff3 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -430,6 +430,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 534692b..c29e91f 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -3258,6 +3258,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
{
@@ -3272,6 +3275,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 4a15460..7ccbdc4 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 2331aca..942081a 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 4df9b37..58cbad7 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);
--
1.8.3.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], [email protected]
Subject: RE: Partial aggregates pushdown
In-Reply-To: <TYAPR01MB55141D18188AC86ADCE35FCB952F2@TYAPR01MB5514.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