public inbox for [email protected]
help / color / mirror / Atom feedFrom: [email protected] <[email protected]>
To: Robert Haas <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Alexander Pyhalov <[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: Thu, 22 Feb 2024 07:20:45 +0000
Message-ID: <TYAPR01MB5514F0CBD9CD4F84A261198195562@TYAPR01MB5514.jpnprd01.prod.outlook.com> (raw)
In-Reply-To: <CALDaNm0S5fb_8o+TtR8bXf3zOpqabOBLy0EJ3+oVzHH_dzpXvw@mail.gmail.com>
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>
Hi. Mr.Haas, hackers.
I apologize for the significant delay since my last post.
I have conducted investigations and considerations regarding the remaining tasks as follows.
Would it be possible for you to review them?
In particular, could you please confirm if the approach mentioned in 1. is acceptable?
If there are no issues with the direction outlined in 1., I plan to make a simple prototype based on this approach.
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.
If the version of the local server is higher than or equal to the version of the remote server, the proposed method can be simplified. The export version mentioned later in (1) would not be necessary. Furthermore, if the version of the local server matches the version of the remote server, the proposed method can be further simplified.
I would appreciate your input on reasonable assumptions regarding the differences in versions between the local server and the remote server.
I will explain the specifications of the export function, import function, the new SQL keyword for aggregate expressions, and the behavior of query processing for partial aggregation separately.
(1) Export Function Specification
This function is another final function for partial aggregate.
This function converts the state value that represents the result of partial aggregation into a format that can be read by the local server.
This function is called instead of the existing finalfunc during the final stage of aggregation when performing partial aggregation.
The conversion process described above will be referred to as "export".
The argument of an export function is the version of the server that will receive the return value.
Hereafter, this version will be referred to as the export version.
The concept of an export version is necessary to handle cases where the version of the local server is smaller than the version of the remote server.
The return value of the export function is the transformed state value, and its data type is bytea.
For backward compatibility, the developer of the export function must ensure that the export can be performed for major versions up to five versions prior to the major version of PostgreSQL that the export function is being developed for.
For built-in functions, I believe it is necessary to allow for the possibility of not developing the export functionality for specific versions in the future (due to reasons such as development burden) after the export function is developed for a certain version.
To achieve this, for built-in functions, we will add a column to the pg_aggregate catalog that indicates the presence or absence of export functionality for each major version, including the major version being developed and the previous five major versions. This column will be named safety_export_versions and will have a data type of boolean[6].
For user-defined functions, we will refer to the extensions option and add an external server option called safety_export_extensions, which will maintain a list of extensions that include only the aggregate functions that can be exported to the local server version.
(2) Import Function Specification
The import function is a function that performs validity checks on the exported data and converts it into a state value. The process of this conversion is referred to as importing.
The import function is called from postgres_fdw in the local server.
The arguments of the import function are the exported data and the export version.
The return value of the import function is a state value that can be read on the local server.
The import function will terminate with an error if the validity check determines that the exported result cannot be read on the local server.
For backward compatibility, developers of the import function must ensure that it can be imported if the export version is up to five versions prior to their own version.
(3) The new SQL keyword for aggregate expressions
The local server passes the instructions for partial aggregation and the export version to the remote server using SQL keywords. The syntax specification is as follows:
aggregate_function(PARTIAL_AGGREGATE(export_version) expr)
Here, PARTIAL_AGGREGATE is a keyword that indicates partial aggregation, and export_version is a string constant that indicates the export version.
(4) The behavior of query processing for partial aggregation
I will illustrate the flow of query processing using the example query "select aggfunc(c) from t".
In the following explanation, the major version of the remote server will be referred to as remote_version, and the major version of the local server will be referred to as local_version.
STEP1. Checking the feasibility of partial aggregation pushdown on the local server
(i) Retrieving the remote_version
The postgres_fdw connects to the remote server and retrieves the remote_version.
(ii) Checking the versions
The postgres_fdw determines whether the difference between local_version and remote_version is within 5. If the difference is 6 or more, it is determined that partial aggregation pushdown is not possible.
(iii) Checking the import function
The postgres_fdw checks the pg_aggregate catalog to see if there is an import function for aggfunc. If there is none, it is determined that partial aggregation pushdown is not possible.
(iv) Checking the export function
If aggfunc is a built-in function, the postgres_fdw checks the pg_aggregate catalog. It checks if there is a version number export_version that satisfies the conditions local_version >= export_version >= local_version-5 and if there is an export function available for that version. If the version number export_version does not exist, it is determined that partial aggregation pushdown is not possible. This check is only performed if local_version >= remote_version.
If aggfunc is a user-defined function, the postgres_fdw checks if the extension on which aggfunc depends is included in export_safety_extensions. If it is not included, it is determined that partial aggregation pushdown is not possible.
STEP2. Sending a remote query on the local server
The query containing the keyword indicating partial aggregation is sent to the remote server. The remote query for the sample query would be as follows:
"select aggfunc(PARTIAL_AGGREGATE(export_version) c) from t"
STEP3. Executing the remote query on the remote server
The remote server performs partial aggregation for aggfunc. Instead of calling the final function at the last stage of aggregation, the remote server calls the export function with export_version and generates the return value of the partial aggregation.
STEP4. Receiving the result of the remote query on the local server
The postgres_fdw passes the export_version and the return value of STEP3 to the import function of aggfunc and receives the state value. The postgres_fdw then passes the received state value to the executor of the local server.
2. The approach of adding SQL keywords
> From: Robert Haas <[email protected]>
> Sent: Tuesday, November 21, 2023 5:52 AM
> I do have a concern about this, though. It adds a lot of bloat. It adds a whole lot of additional entries to pg_aggregate, and
> every new aggregate we add in the future will require a bonus entry for this, and it needs a bunch of new pg_proc entries as
> well. One idea that I've had in the past is to instead introduce syntax that just does this, without requiring a separate
> aggregate definition in each case.
> For example, maybe instead of changing string_agg(whatever) to string_agg_p_text_text(whatever), you can say
> PARTIAL_AGGREGATE
> string_agg(whatever) or string_agg(PARTIAL_AGGREGATE whatever) or something. Then all aggregates could be treated
> in a generic way. I'm not completely sure that's better, but I think it's worth considering.
I have prototyped an approach using SQL keywords for the patch that does not include the functionality of Step 1. Please find the prototype attached as a file.
# I apologize for not including sufficient comments, documentation, and tests in the prototype. Please understand.
Mainly, it seems that we can address this by adding handling for the new SQL keywords in the parser and making modifications to the finalize process for aggregation in the executor.
As pointed out by Mr.Haas, it has been realized that the code can be significantly simplified.
The additional lines of code, excluding documentation and tests, are as follows.
Adding new aggregate functions approach(approach #1): 1,069
Adding new SQL keyword approach(approach #2): 318
As mentioned in 1., I plan to modify the patch by adding SQL keywords in the future.
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.
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.patch (143.2K, ../TYAPR01MB5514F0CBD9CD4F84A261198195562@TYAPR01MB5514.jpnprd01.prod.outlook.com/2-0001-Partial-aggregates-push-down-SQL-keyword.patch)
download | inline diff:
From 5fb11f43c2d19981b04548abf3b9f4be2eb749e0 Mon Sep 17 00:00:00 2001
From: Yuki Fujii <[email protected]>
Date: Thu, 22 Feb 2024 09:38:33 -0500
Subject: [PATCH] Partial aggregates push down SQL keyword
---
contrib/postgres_fdw/deparse.c | 84 ++-
.../postgres_fdw/expected/postgres_fdw.out | 642 ++++++++++++++++--
contrib/postgres_fdw/option.c | 4 +-
contrib/postgres_fdw/postgres_fdw.c | 47 +-
contrib/postgres_fdw/postgres_fdw.h | 10 +
contrib/postgres_fdw/sql/postgres_fdw.sql | 155 ++++-
src/backend/executor/nodeAgg.c | 52 +-
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 8fc66fa11c..c8adaea33c 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,34 @@ 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 +3882,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 +3903,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);
@@ -4189,3 +4207,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 c355e8f3f7..61b63663a9 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -10017,36 +10017,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;
@@ -10080,32 +10100,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
@@ -10118,27 +10140,541 @@ 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
+ -> Partial HashAggregate
+ Output: pagg_tab.b, PARTIAL avg(pagg_tab.a), PARTIAL max(pagg_tab.a), PARTIAL count(*), PARTIAL sum(pagg_tab.a)
+ Group Key: pagg_tab.b
+ -> Foreign Scan on public.fpagg_tab_p1 pagg_tab
+ Output: pagg_tab.b, pagg_tab.a
+ Remote SQL: SELECT a, b FROM public.pagg_tab_p1
+ -> Partial HashAggregate
+ 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)
+ Group Key: pagg_tab_1.b
+ -> Foreign Scan on public.fpagg_tab_p2 pagg_tab_1
+ Output: pagg_tab_1.b, pagg_tab_1.a
+ Remote SQL: SELECT a, b FROM public.pagg_tab_p2
+ -> Partial HashAggregate
+ 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)
+ Group Key: pagg_tab_2.b
+ -> Foreign Scan on public.fpagg_tab_p3 pagg_tab_2
+ Output: pagg_tab_2.b, pagg_tab_2.a
+ Remote SQL: SELECT a, b FROM public.pagg_tab_p3
+(26 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-08 | 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-08 | 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 630b304338..cc6d80844b 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 142dcfc995..7278ffcf6f 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,11 +6473,16 @@ 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)
return false;
+ /* It is unsafe to push HAVING statements with partial aggregates */
+ if ((extra->patype == PARTITIONWISE_AGGREGATE_PARTIAL) && extra->havingQual)
+ return false;
+
/* Get the fpinfo of the underlying scan relation. */
ofpinfo = (PgFdwRelationInfo *) fpinfo->outerrel->fdw_private;
@@ -6498,6 +6511,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 +6528,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 +6614,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 +6636,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 +6730,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 +6747,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 +6791,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 +6812,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 37c1575af6..dcf164163e 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 812e7646e1..9be4d51f7f 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3117,16 +3117,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');
@@ -3134,9 +3149,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
@@ -3156,9 +3175,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 dbd5661d17..69dedee4af 100644
--- a/src/backend/executor/nodeAgg.c
+++ b/src/backend/executor/nodeAgg.c
@@ -1079,7 +1079,8 @@ finalize_aggregate(AggState *aggstate,
/*
* Apply the agg's finalfn if one is provided, else return transValue.
*/
- if (OidIsValid(peragg->finalfn_oid))
+ if (OidIsValid(peragg->finalfn_oid) &&
+ (peragg->aggref->agg_partial == false))
{
int numFinalArgs = peragg->numFinalArgs;
@@ -1125,6 +1126,42 @@ finalize_aggregate(AggState *aggstate,
}
aggstate->curperagg = NULL;
}
+ else if (peragg->aggref->agg_partial
+ && (peragg->aggref->aggtranstype == INTERNALOID)
+ && OidIsValid(peragg->serialfn_oid))
+ {
+ /* set up aggstate->curperagg for AggGetAggref() */
+ aggstate->curperagg = peragg;
+
+ InitFunctionCallInfoData(*fcinfo, &peragg->serialfn, 1,
+ InvalidOid, (void *) aggstate, NULL);
+
+ /* Fill in the transition state value */
+ fcinfo->args[0].value =
+ MakeExpandedObjectReadOnly(pergroupstate->transValue,
+ pergroupstate->transValueIsNull,
+ pertrans->transtypeLen);
+ fcinfo->args[0].isnull = pergroupstate->transValueIsNull;
+ anynull |= pergroupstate->transValueIsNull;
+
+ if (fcinfo->flinfo->fn_strict && anynull)
+ {
+ /* don't call a strict function with NULL inputs */
+ *resultVal = (Datum) 0;
+ *resultIsNull = true;
+ }
+ else
+ {
+ Datum result;
+
+ result = FunctionCallInvoke(fcinfo);
+ *resultIsNull = fcinfo->isnull;
+ *resultVal = MakeExpandedObjectReadOnly(result,
+ fcinfo->isnull,
+ peragg->resulttypeLen);
+ }
+ aggstate->curperagg = NULL;
+ }
else
{
*resultVal =
@@ -3663,7 +3700,7 @@ ExecInitAgg(Agg *node, EState *estate, int eflags)
Oid serialfn_oid,
deserialfn_oid;
Oid aggOwner;
- Expr *finalfnexpr;
+ Expr *finalfnexpr, *serialfnexpr;
Oid aggtranstype;
/* Planner should have assigned aggregate to correct level */
@@ -3820,6 +3857,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 a02332a1ec..aac35b9d9a 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -597,6 +597,7 @@ makeFuncCall(List *name, List *args, CoercionForm funcformat, int location)
n->agg_within_group = false;
n->agg_star = false;
n->agg_distinct = false;
+ n->agg_partial = false;
n->func_variadic = false;
n->funcformat = funcformat;
n->location = location;
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index be4e182869..daed187eb6 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -208,7 +208,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);
@@ -5339,6 +5339,99 @@ make_group_input_target(PlannerInfo *root, PathTarget *final_target)
return set_pathtarget_cost_width(root, input_target);
}
+/*
+ * setGroupClausePartial
+ * Generate a groupClause and a pathtarget for partial aggregate
+ * pushdown by FDW and set them to GroupPathExtraData.
+ */
+static void
+setGroupClausePartial(PathTarget *partial_target, List *non_group_exprs,
+ List *groupClause, GroupPathExtraData *extra)
+{
+ int exprno,
+ refno;
+ ListCell *lc;
+ Index maxRef = 0;
+ List *exprs_processed = NIL;
+ int exprs_num = 0;
+
+ foreach(lc, groupClause)
+ {
+ SortGroupClause *sgc = (SortGroupClause *) lfirst(lc);
+
+ if (sgc->tleSortGroupRef > maxRef)
+ maxRef = sgc->tleSortGroupRef;
+ }
+ maxRef++;
+
+ extra->groupClausePartial = list_copy_deep(groupClause);
+ extra->partial_target = copy_pathtarget(partial_target);
+
+ if (partial_target->exprs)
+ exprs_num = partial_target->exprs->length;
+
+ foreach(lc, non_group_exprs)
+ {
+ Expr *expr = (Expr *) lfirst(lc);
+
+ refno = -1;
+ if (list_member(exprs_processed, expr) ||
+ (!IsA(expr, Var) && !IsA(expr, PlaceHolderVar)))
+ continue;
+ exprs_processed = lappend(exprs_processed, expr);
+ for (exprno = 0; exprno < exprs_num; exprno++)
+ {
+ Expr *target_expr = (Expr *) list_nth(partial_target->exprs, exprno);
+
+ if (equal(target_expr, expr))
+ {
+ refno = exprno;
+ break;
+ }
+ }
+ if (refno < 0)
+ {
+ SortGroupClause *grpcl = makeNode(SortGroupClause);
+
+ grpcl->tleSortGroupRef = maxRef++;
+ extra->groupClausePartial = lappend(extra->groupClausePartial, grpcl);
+ add_column_to_pathtarget(extra->partial_target, expr, grpcl->tleSortGroupRef);
+ }
+ }
+}
+
+/*
+ * adjustAggrefForPartial
+ * Adjust Aggrefs to put them in partial mode
+ */
+static void
+adjustAggrefForPartial(List *exprs)
+{
+ ListCell *lc;
+
+ foreach(lc, exprs)
+ {
+ Aggref *aggref = (Aggref *) lfirst(lc);
+
+ if (IsA(aggref, Aggref))
+ {
+ Aggref *newaggref;
+
+ /*
+ * We shouldn't need to copy the substructure of the Aggref node,
+ * but flat-copy the node itself to avoid damaging other trees.
+ */
+ newaggref = makeNode(Aggref);
+ memcpy(newaggref, aggref, sizeof(Aggref));
+
+ /* For now, assume serialization is required */
+ mark_partial_aggref(newaggref, AGGSPLIT_INITIAL_SERIAL);
+
+ lfirst(lc) = newaggref;
+ }
+ }
+}
+
/*
* make_partial_grouping_target
* Generate appropriate PathTarget for output of partial aggregate
@@ -5354,11 +5447,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;
@@ -5400,8 +5497,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
@@ -5414,35 +5511,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);
@@ -7095,7 +7174,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 22a1fa29f3..0bba27d06b 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -2557,6 +2557,7 @@ convert_combining_aggrefs(Node *node, void *context)
parent_agg = copyObject(child_agg);
child_agg->args = orig_agg->args;
child_agg->aggfilter = orig_agg->aggfilter;
+ child_agg->agg_partial = 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 127f25bb2f..6cdd791f4a 100644
--- a/src/backend/optimizer/prep/prepagg.c
+++ b/src/backend/optimizer/prep/prepagg.c
@@ -412,6 +412,7 @@ find_compatible_agg(PlannerInfo *root, Aggref *newagg,
/* all of the following must be the same or it's no match */
if (newagg->inputcollid != existingRef->inputcollid ||
newagg->aggtranstype != existingRef->aggtranstype ||
+ newagg->agg_partial != existingRef->agg_partial ||
newagg->aggstar != existingRef->aggstar ||
newagg->aggvariadic != existingRef->aggvariadic ||
newagg->aggkind != existingRef->aggkind ||
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 130f7fc7c3..fd6e7506c9 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -744,7 +744,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
@@ -15297,6 +15297,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 '(' '*' ')'
{
/*
@@ -15352,6 +15372,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),
@@ -17618,6 +17643,7 @@ reserved_keyword:
| ONLY
| OR
| ORDER
+ | PARTIAL_AGGREGATE
| PLACING
| PRIMARY
| REFERENCES
@@ -17924,6 +17950,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 9d151a880b..432d18bc47 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 4b50278fd0..6b24f047f8 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -543,6 +543,7 @@ transformRangeFunction(ParseState *pstate, RangeFunction *r)
fc->over == NULL &&
!fc->agg_star &&
!fc->agg_distinct &&
+ !fc->agg_partial &&
!fc->func_variadic &&
coldeflist == NIL)
{
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 9300c7b9ab..3c34df48bb 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 fdb3e6df33..0c30afb1c7 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -97,6 +97,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
bool agg_within_group = (fn ? fn->agg_within_group : false);
bool agg_star = (fn ? fn->agg_star : false);
bool agg_distinct = (fn ? fn->agg_distinct : false);
+ bool agg_partial = (fn ? fn->agg_partial : false);
bool func_variadic = (fn ? fn->func_variadic : false);
CoercionForm funcformat = (fn ? fn->funcformat : COERCE_EXPLICIT_CALL);
bool could_be_projection;
@@ -222,7 +223,8 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
*/
could_be_projection = (nargs == 1 && !proc_call &&
agg_order == NIL && agg_filter == NULL &&
- !agg_star && !agg_distinct && over == NULL &&
+ !agg_star && !agg_distinct &&
+ !agg_partial && over == NULL &&
!func_variadic && argnames == NIL &&
list_length(funcname) == 1 &&
(actual_arg_types[0] == RECORDOID ||
@@ -322,6 +324,12 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
errmsg("DISTINCT specified, but %s is not an aggregate function",
NameListToString(funcname)),
parser_errposition(pstate, location)));
+ if (agg_partial)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("PARTIAL_AGGREGATE specified, but %s is not an aggregate function",
+ NameListToString(funcname)),
+ parser_errposition(pstate, location)));
if (agg_within_group)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -392,6 +400,8 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
parser_errposition(pstate, location)));
/* gram.y rejects DISTINCT + WITHIN GROUP */
Assert(!agg_distinct);
+ /* gram.y rejects PARTIAL_AGGREGATE */
+ Assert(!agg_partial);
/* gram.y rejects VARIADIC + WITHIN GROUP */
Assert(!func_variadic);
@@ -814,7 +824,8 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
parser_errposition(pstate, location)));
/* parse_agg.c does additional aggregate-specific processing */
- transformAggregateCall(pstate, aggref, fargs, agg_order, agg_distinct);
+ transformAggregateCall(pstate, aggref, fargs, agg_order, agg_distinct,
+ agg_partial);
retval = (Node *) aggref;
}
@@ -845,6 +856,15 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
errmsg("DISTINCT is not implemented for window functions"),
parser_errposition(pstate, location)));
+ /*
+ * partial aggregates not allowed in windows yet
+ */
+ if (agg_partial)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("PARTIAL_AGGREGATE is not implemented for window functions"),
+ parser_errposition(pstate, location)));
+
/*
* Reject attempt to call a parameterless aggregate without (*)
* syntax. This is mere pedantry but some folks insisted ...
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index a928a8c55d..21265ce4da 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -10123,7 +10123,8 @@ get_agg_expr_helper(Aggref *aggref, deparse_context *context,
/* Print the aggregate name, schema-qualified if needed */
appendStringInfo(buf, "%s(%s", funcname,
- (aggref->aggdistinct != NIL) ? "DISTINCT " : "");
+ (aggref->aggdistinct != NIL) ? "DISTINCT " :
+ aggref->agg_partial ? "PARTIAL_AGGREGATE " : "");
if (AGGKIND_IS_ORDERED_SET(aggref->aggkind))
{
diff --git a/src/include/executor/nodeAgg.h b/src/include/executor/nodeAgg.h
index 684779a6a3..079360c153 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 476d55dd24..cf0d5bc2a8 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -421,6 +421,7 @@ typedef struct FuncCall
bool agg_within_group; /* ORDER BY appeared in WITHIN GROUP */
bool agg_star; /* argument was really '*' */
bool agg_distinct; /* arguments were labeled DISTINCT */
+ bool agg_partial; /* arguments were labeled PARTIAL_AGGREGATE */
bool func_variadic; /* last argument was labeled VARIADIC */
CoercionForm funcformat; /* how to display this node */
int location; /* token location, or -1 if unknown */
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 534692bee1..c29e91f144 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 4a154606d2..7ccbdc44f3 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 2331acac09..942081a5ad 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 4df9b37e6d..58cbad7524 100644
--- a/src/include/parser/parse_agg.h
+++ b/src/include/parser/parse_agg.h
@@ -17,7 +17,7 @@
extern void transformAggregateCall(ParseState *pstate, Aggref *agg,
List *args, List *aggorder,
- bool agg_distinct);
+ bool agg_distinct, bool agg_partial);
extern Node *transformGroupingFunc(ParseState *pstate, GroupingFunc *p);
--
2.39.3
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: <TYAPR01MB5514F0CBD9CD4F84A261198195562@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