public inbox for [email protected]
help / color / mirror / Atom feedFrom: [email protected] <[email protected]>
To: Jelte Fennema-Nio <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Cc: Bruce Momjian <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Stephen Frost <[email protected]>
Cc: Andres Freund <[email protected]>
Cc: Tomas Vondra <[email protected]>
Cc: Julien Rouhaud <[email protected]>
Cc: Daniel Gustafsson <[email protected]>
Cc: vignesh C <[email protected]>
Cc: Alexander Pyhalov <[email protected]>
Cc: [email protected] <[email protected]>
Subject: RE: Partial aggregates pushdown
Date: Sun, 30 Jun 2024 21:42:19 +0000
Message-ID: <TY2PR01MB3835CB1D110362A3213E5F6795D22@TY2PR01MB3835.jpnprd01.prod.outlook.com> (raw)
In-Reply-To: <CAGECzQT=CjkORFaZuBn4Db9CRt9C_EpB2isadmx_iNS3ZzQMwg@mail.gmail.com>
References: <CALDaNm0S5fb_8o+TtR8bXf3zOpqabOBLy0EJ3+oVzHH_dzpXvw@mail.gmail.com>
<TYAPR01MB5514F0CBD9CD4F84A261198195562@TYAPR01MB5514.jpnprd01.prod.outlook.com>
<[email protected]>
<TYAPR01MB55141D18188AC86ADCE35FCB952F2@TYAPR01MB5514.jpnprd01.prod.outlook.com>
<[email protected]>
<[email protected]>
<[email protected]>
<TYAPR01MB30884F8E4D94038CD661FFEF95F02@TYAPR01MB3088.jpnprd01.prod.outlook.com>
<[email protected]>
<[email protected]>
<[email protected]>
<TYAPR01MB3088EEECFEB79434E0F4339695C72@TYAPR01MB3088.jpnprd01.prod.outlook.com>
<CAGECzQRVuUZEhPL+rzY0_0UKEaZ-sFFkLJ6GN+bad+im2r9=uQ@mail.gmail.com>
<TYAPR01MB308841016A3F1AE8247BAA6495C02@TYAPR01MB3088.jpnprd01.prod.outlook.com>
<CAGECzQSXXKVCQ13jHXtmk=PMsJHhFGb78PoT3JiT4eAcoLXxoA@mail.gmail.com>
<TY2PR01MB383585CACD74F2106A0563C195CB2@TY2PR01MB3835.jpnprd01.prod.outlook.com>
<CAGECzQSKMO9C8-9Eo-feE3BJTF68EWKHXY4-xf_5BMKm8yAHPw@mail.gmail.com>
<TY2PR01MB38353E34056D0F91736F358395D42@TY2PR01MB3835.jpnprd01.prod.outlook.com>
<CAGECzQTnfUdRO21CkwZeMowFZC3zQ9+dhNp3EB-G52=BXJvyPg@mail.gmail.com>
<TY2PR01MB3835DE59D4654A499A5A699795D52@TY2PR01MB3835.jpnprd01.prod.outlook.com>
<CAGECzQT=CjkORFaZuBn4Db9CRt9C_EpB2isadmx_iNS3ZzQMwg@mail.gmail.com>
Hi hackers.
On Wed, Jun 5, 2024 at 9:15?AM [email protected] <[email protected]> wrote:
> Requirement2. Consider appropriate position of the new keyword "PARTIAL_AGGREGATE". (with Robert)
> Existing patch: Before the target expression. Ex. avg(PARTIAL_AGGREGATE c1)
> Ideal: Before the aggregate function. Ex. PARTIAL_AGGREGATE avg(c1)
> Requirement3. Consider to avoid to make the new keyword "PARTIAL_AGGREGATE" become a reserved word. (with Robert)
> In the existing patch, "PARTIAL_AGGREGATE" is a reserved word.
I considered the above two requirement.
Based on my research, there is no way to use PARTIAL_AGGREGATE in front of a function name without making it a reserved word.
Instead, I can make PARTIAL_AGGREGATE an unreserved word by placing it after the FILTER clause, like avg(c1) FILTER (WHERE c2 > 0) PARTIAL_AGGREGATE, and by marking it as an ASLABEL word like FILTER.
I attached the patch of the method.
If there are no objections, I would like to proceed with the method described above.
I'd appreciate it if anyone comment the method.
I have addressed several comments, though not all of them.
On Mon, Jun 24, 2024 at 6:09?PM Jelte Fennema-Nio <[email protected]> wrote:
> 4. Related to 3, I think it would be good to have some tests of
> PARTIAL_AGGREGATE that don't involve postgres_fdw at all. I also
> spotted some comments too that mention FDW, even though they apply to
> the "pure" PARTIAL_AGGREGATE code.
> 5. This comment now seems incorrect:
> - * Apply the agg's finalfn if one is provided, else return transValue.
> + * If the agg's finalfn is provided and PARTIAL_AGGREGATE keyword is
> + * not specified, apply the agg's finalfn.
> + * If PARTIAL_AGGREGATE keyword is specified and the transValue type
> + * is internal, apply the agg's serialfn. In this case the agg's
> + * serialfn must not be invalid. Otherwise return transValue.
>
> 6. These errors are not on purpose afaict (if they are a comment in
> the test would be good to explain why)
>
> +SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b ORDER BY 1;
> +ERROR: could not connect to server "loopback"
> +DETAIL: invalid connection option "partial_aggregate_support"
Fixed.
Best regards, Yuki Fujii
--
Yuki Fujii
Information Technology R&D Center, Mitsubishi Electric Corporation
Attachments:
[application/octet-stream] 0002-v2-POC-Partial-aggregate-pushdown-internal-transtype.patch (63.7K, ../TY2PR01MB3835CB1D110362A3213E5F6795D22@TY2PR01MB3835.jpnprd01.prod.outlook.com/2-0002-v2-POC-Partial-aggregate-pushdown-internal-transtype.patch)
download | inline diff:
From 9e7019adbb268e54405b1031cdd6420293d3d39f Mon Sep 17 00:00:00 2001
From: Yuki Fujii <[email protected]>
Date: Fri, 21 Jun 2024 09:00:16 +0900
Subject: [PATCH 2/2] v2 POC Partial aggregate pushdown; internal transtype
This is a POC patch.
Add the cases in that the transtype is internal and not pseudo type.
So far, only support avg(int8).
There are the following tasks remaining.
1. Add sufficient documents and comments and tests.
2. Support other built-in aggregate functions like sum, avg, count.
3. Support user-defined aggregate functions.
---
contrib/postgres_fdw/deparse.c | 2 +-
.../postgres_fdw/expected/postgres_fdw.out | 32 +++--
contrib/postgres_fdw/sql/postgres_fdw.sql | 8 +-
src/backend/executor/nodeAgg.c | 81 ++++++++++-
src/backend/parser/parse_agg.c | 43 +++++-
src/backend/utils/adt/arrayfuncs.c | 6 +
src/backend/utils/adt/numeric.c | 126 ++++++++++++++++--
src/include/catalog/pg_aggregate.dat | 2 +
src/include/catalog/pg_aggregate.h | 4 +
src/include/catalog/pg_proc.dat | 6 +
src/include/executor/nodeAgg.h | 9 ++
src/include/parser/parse_agg.h | 6 +
src/test/regress/expected/oidjoins.out | 1 +
src/test/regress/expected/opr_sanity.out | 7 +-
14 files changed, 297 insertions(+), 36 deletions(-)
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 4b6bc6e9fd..28de143a16 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -4245,7 +4245,7 @@ partial_agg_ok(Aggref *agg, PgFdwRelationInfo *fpinfo)
aggform = (Form_pg_aggregate) GETSTRUCT(aggtup);
partial_agg_ok = aggform->aggpartialpushdownsafe && fpinfo->partial_aggregate_support;
- if(aggform->aggtranstype == INTERNALOID ||
+ if(aggform->aggtranstype != INTERNALOID &&
get_typtype(aggform->aggtranstype) == TYPTYPE_PSEUDO)
partial_agg_ok = false;
if(partial_agg_ok){
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 48ac44df07..c6e87609c6 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -10475,25 +10475,27 @@ SELECT
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_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),
/* The cases that require only import functions */
- avg(b::int4)
+ avg(b::int4),
+ /* The cases that don't require import or export functions */
+ avg(b::int8)
FROM pagg_tab WHERE c_serial between 1 and 30;
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate
- Output: 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), every(pagg_tab.c_bool), 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_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), avg(pagg_tab.b)
+ Output: 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), every(pagg_tab.c_bool), 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_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), avg(pagg_tab.b), avg((pagg_tab.b)::bigint)
-> Append
-> Foreign Scan
- Output: (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 every(pagg_tab.c_bool)), (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_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 avg(pagg_tab.b))
+ Output: (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 every(pagg_tab.c_bool)), (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_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 avg(pagg_tab.b)), (PARTIAL avg((pagg_tab.b)::bigint))
Relations: Aggregate on (public.fpagg_tab_p1 pagg_tab)
- Remote SQL: SELECT bit_and(c_bit) PARTIAL_AGGREGATE , bit_and(c_1or3int2) PARTIAL_AGGREGATE , bit_and(c_1or3int4) PARTIAL_AGGREGATE , bit_and(c_1or3int8) PARTIAL_AGGREGATE , bit_or(c_bit) PARTIAL_AGGREGATE , bit_or(c_1or3int2) PARTIAL_AGGREGATE , bit_or(c_1or3int4) PARTIAL_AGGREGATE , bit_or(c_1or3int8) PARTIAL_AGGREGATE , bit_xor(c_bit) PARTIAL_AGGREGATE , bit_xor(c_1or3int2) PARTIAL_AGGREGATE , bit_xor(c_1or3int4) PARTIAL_AGGREGATE , bit_xor(c_1or3int8) PARTIAL_AGGREGATE , bool_and(c_bool) PARTIAL_AGGREGATE , bool_or(c_bool) PARTIAL_AGGREGATE , every(c_bool) PARTIAL_AGGREGATE , max(c_1c::character(1)) PARTIAL_AGGREGATE , max(('01-01-2000'::date + b)) PARTIAL_AGGREGATE , max(('0.0.0.0'::inet + b)) PARTIAL_AGGREGATE , max(b::real) PARTIAL_AGGREGATE , max(b::double precision) PARTIAL_AGGREGATE , max(b::smallint) PARTIAL_AGGREGATE , max(b) PARTIAL_AGGREGATE , max(b::bigint) PARTIAL_AGGREGATE , max(c_interval) PARTIAL_AGGREGATE , max(c_money) PARTIAL_AGGREGATE , max(b::numeric) PARTIAL_AGGREGATE , max(b::oid) PARTIAL_AGGREGATE , max(c_pg_lsn) PARTIAL_AGGREGATE , max(c_tid) PARTIAL_AGGREGATE , max(c_1c) PARTIAL_AGGREGATE , max(c_time) PARTIAL_AGGREGATE , max(c_timetz) PARTIAL_AGGREGATE , max(c_timestamp) PARTIAL_AGGREGATE , max(c_timestamptz) PARTIAL_AGGREGATE , max(c_xid8) PARTIAL_AGGREGATE , min(c_1c::character(1)) PARTIAL_AGGREGATE , min(('01-01-2000'::date + b)) PARTIAL_AGGREGATE , min(('0.0.0.0'::inet + b)) PARTIAL_AGGREGATE , min(b::real) PARTIAL_AGGREGATE , min(b::double precision) PARTIAL_AGGREGATE , min(b::smallint) PARTIAL_AGGREGATE , min(b) PARTIAL_AGGREGATE , min(b::bigint) PARTIAL_AGGREGATE , min(c_interval) PARTIAL_AGGREGATE , min(c_money) PARTIAL_AGGREGATE , min(b::numeric) PARTIAL_AGGREGATE , min(b::oid) PARTIAL_AGGREGATE , min(c_pg_lsn) PARTIAL_AGGREGATE , min(c_tid) PARTIAL_AGGREGATE , min(c_1c) PARTIAL_AGGREGATE , min(c_time) PARTIAL_AGGREGATE , min(c_timetz) PARTIAL_AGGREGATE , min(c_timestamp) PARTIAL_AGGREGATE , min(c_timestamptz) PARTIAL_AGGREGATE , min(c_xid8) PARTIAL_AGGREGATE , avg(b) PARTIAL_AGGREGATE FROM public.pagg_tab_p1 WHERE ((c_serial >= 1)) AND ((c_serial <= 30))
+ Remote SQL: SELECT bit_and(c_bit) PARTIAL_AGGREGATE , bit_and(c_1or3int2) PARTIAL_AGGREGATE , bit_and(c_1or3int4) PARTIAL_AGGREGATE , bit_and(c_1or3int8) PARTIAL_AGGREGATE , bit_or(c_bit) PARTIAL_AGGREGATE , bit_or(c_1or3int2) PARTIAL_AGGREGATE , bit_or(c_1or3int4) PARTIAL_AGGREGATE , bit_or(c_1or3int8) PARTIAL_AGGREGATE , bit_xor(c_bit) PARTIAL_AGGREGATE , bit_xor(c_1or3int2) PARTIAL_AGGREGATE , bit_xor(c_1or3int4) PARTIAL_AGGREGATE , bit_xor(c_1or3int8) PARTIAL_AGGREGATE , bool_and(c_bool) PARTIAL_AGGREGATE , bool_or(c_bool) PARTIAL_AGGREGATE , every(c_bool) PARTIAL_AGGREGATE , max(c_1c::character(1)) PARTIAL_AGGREGATE , max(('01-01-2000'::date + b)) PARTIAL_AGGREGATE , max(('0.0.0.0'::inet + b)) PARTIAL_AGGREGATE , max(b::real) PARTIAL_AGGREGATE , max(b::double precision) PARTIAL_AGGREGATE , max(b::smallint) PARTIAL_AGGREGATE , max(b) PARTIAL_AGGREGATE , max(b::bigint) PARTIAL_AGGREGATE , max(c_interval) PARTIAL_AGGREGATE , max(c_money) PARTIAL_AGGREGATE , max(b::numeric) PARTIAL_AGGREGATE , max(b::oid) PARTIAL_AGGREGATE , max(c_pg_lsn) PARTIAL_AGGREGATE , max(c_tid) PARTIAL_AGGREGATE , max(c_1c) PARTIAL_AGGREGATE , max(c_time) PARTIAL_AGGREGATE , max(c_timetz) PARTIAL_AGGREGATE , max(c_timestamp) PARTIAL_AGGREGATE , max(c_timestamptz) PARTIAL_AGGREGATE , max(c_xid8) PARTIAL_AGGREGATE , min(c_1c::character(1)) PARTIAL_AGGREGATE , min(('01-01-2000'::date + b)) PARTIAL_AGGREGATE , min(('0.0.0.0'::inet + b)) PARTIAL_AGGREGATE , min(b::real) PARTIAL_AGGREGATE , min(b::double precision) PARTIAL_AGGREGATE , min(b::smallint) PARTIAL_AGGREGATE , min(b) PARTIAL_AGGREGATE , min(b::bigint) PARTIAL_AGGREGATE , min(c_interval) PARTIAL_AGGREGATE , min(c_money) PARTIAL_AGGREGATE , min(b::numeric) PARTIAL_AGGREGATE , min(b::oid) PARTIAL_AGGREGATE , min(c_pg_lsn) PARTIAL_AGGREGATE , min(c_tid) PARTIAL_AGGREGATE , min(c_1c) PARTIAL_AGGREGATE , min(c_time) PARTIAL_AGGREGATE , min(c_timetz) PARTIAL_AGGREGATE , min(c_timestamp) PARTIAL_AGGREGATE , min(c_timestamptz) PARTIAL_AGGREGATE , min(c_xid8) PARTIAL_AGGREGATE , avg(b) PARTIAL_AGGREGATE , avg(b::bigint) PARTIAL_AGGREGATE FROM public.pagg_tab_p1 WHERE ((c_serial >= 1)) AND ((c_serial <= 30))
-> Foreign Scan
- Output: (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 every(pagg_tab_1.c_bool)), (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_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 avg(pagg_tab_1.b))
+ Output: (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 every(pagg_tab_1.c_bool)), (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_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 avg(pagg_tab_1.b)), (PARTIAL avg((pagg_tab_1.b)::bigint))
Relations: Aggregate on (public.fpagg_tab_p2 pagg_tab_1)
- Remote SQL: SELECT bit_and(c_bit) PARTIAL_AGGREGATE , bit_and(c_1or3int2) PARTIAL_AGGREGATE , bit_and(c_1or3int4) PARTIAL_AGGREGATE , bit_and(c_1or3int8) PARTIAL_AGGREGATE , bit_or(c_bit) PARTIAL_AGGREGATE , bit_or(c_1or3int2) PARTIAL_AGGREGATE , bit_or(c_1or3int4) PARTIAL_AGGREGATE , bit_or(c_1or3int8) PARTIAL_AGGREGATE , bit_xor(c_bit) PARTIAL_AGGREGATE , bit_xor(c_1or3int2) PARTIAL_AGGREGATE , bit_xor(c_1or3int4) PARTIAL_AGGREGATE , bit_xor(c_1or3int8) PARTIAL_AGGREGATE , bool_and(c_bool) PARTIAL_AGGREGATE , bool_or(c_bool) PARTIAL_AGGREGATE , every(c_bool) PARTIAL_AGGREGATE , max(c_1c::character(1)) PARTIAL_AGGREGATE , max(('01-01-2000'::date + b)) PARTIAL_AGGREGATE , max(('0.0.0.0'::inet + b)) PARTIAL_AGGREGATE , max(b::real) PARTIAL_AGGREGATE , max(b::double precision) PARTIAL_AGGREGATE , max(b::smallint) PARTIAL_AGGREGATE , max(b) PARTIAL_AGGREGATE , max(b::bigint) PARTIAL_AGGREGATE , max(c_interval) PARTIAL_AGGREGATE , max(c_money) PARTIAL_AGGREGATE , max(b::numeric) PARTIAL_AGGREGATE , max(b::oid) PARTIAL_AGGREGATE , max(c_pg_lsn) PARTIAL_AGGREGATE , max(c_tid) PARTIAL_AGGREGATE , max(c_1c) PARTIAL_AGGREGATE , max(c_time) PARTIAL_AGGREGATE , max(c_timetz) PARTIAL_AGGREGATE , max(c_timestamp) PARTIAL_AGGREGATE , max(c_timestamptz) PARTIAL_AGGREGATE , max(c_xid8) PARTIAL_AGGREGATE , min(c_1c::character(1)) PARTIAL_AGGREGATE , min(('01-01-2000'::date + b)) PARTIAL_AGGREGATE , min(('0.0.0.0'::inet + b)) PARTIAL_AGGREGATE , min(b::real) PARTIAL_AGGREGATE , min(b::double precision) PARTIAL_AGGREGATE , min(b::smallint) PARTIAL_AGGREGATE , min(b) PARTIAL_AGGREGATE , min(b::bigint) PARTIAL_AGGREGATE , min(c_interval) PARTIAL_AGGREGATE , min(c_money) PARTIAL_AGGREGATE , min(b::numeric) PARTIAL_AGGREGATE , min(b::oid) PARTIAL_AGGREGATE , min(c_pg_lsn) PARTIAL_AGGREGATE , min(c_tid) PARTIAL_AGGREGATE , min(c_1c) PARTIAL_AGGREGATE , min(c_time) PARTIAL_AGGREGATE , min(c_timetz) PARTIAL_AGGREGATE , min(c_timestamp) PARTIAL_AGGREGATE , min(c_timestamptz) PARTIAL_AGGREGATE , min(c_xid8) PARTIAL_AGGREGATE , avg(b) PARTIAL_AGGREGATE FROM public.pagg_tab_p2 WHERE ((c_serial >= 1)) AND ((c_serial <= 30))
+ Remote SQL: SELECT bit_and(c_bit) PARTIAL_AGGREGATE , bit_and(c_1or3int2) PARTIAL_AGGREGATE , bit_and(c_1or3int4) PARTIAL_AGGREGATE , bit_and(c_1or3int8) PARTIAL_AGGREGATE , bit_or(c_bit) PARTIAL_AGGREGATE , bit_or(c_1or3int2) PARTIAL_AGGREGATE , bit_or(c_1or3int4) PARTIAL_AGGREGATE , bit_or(c_1or3int8) PARTIAL_AGGREGATE , bit_xor(c_bit) PARTIAL_AGGREGATE , bit_xor(c_1or3int2) PARTIAL_AGGREGATE , bit_xor(c_1or3int4) PARTIAL_AGGREGATE , bit_xor(c_1or3int8) PARTIAL_AGGREGATE , bool_and(c_bool) PARTIAL_AGGREGATE , bool_or(c_bool) PARTIAL_AGGREGATE , every(c_bool) PARTIAL_AGGREGATE , max(c_1c::character(1)) PARTIAL_AGGREGATE , max(('01-01-2000'::date + b)) PARTIAL_AGGREGATE , max(('0.0.0.0'::inet + b)) PARTIAL_AGGREGATE , max(b::real) PARTIAL_AGGREGATE , max(b::double precision) PARTIAL_AGGREGATE , max(b::smallint) PARTIAL_AGGREGATE , max(b) PARTIAL_AGGREGATE , max(b::bigint) PARTIAL_AGGREGATE , max(c_interval) PARTIAL_AGGREGATE , max(c_money) PARTIAL_AGGREGATE , max(b::numeric) PARTIAL_AGGREGATE , max(b::oid) PARTIAL_AGGREGATE , max(c_pg_lsn) PARTIAL_AGGREGATE , max(c_tid) PARTIAL_AGGREGATE , max(c_1c) PARTIAL_AGGREGATE , max(c_time) PARTIAL_AGGREGATE , max(c_timetz) PARTIAL_AGGREGATE , max(c_timestamp) PARTIAL_AGGREGATE , max(c_timestamptz) PARTIAL_AGGREGATE , max(c_xid8) PARTIAL_AGGREGATE , min(c_1c::character(1)) PARTIAL_AGGREGATE , min(('01-01-2000'::date + b)) PARTIAL_AGGREGATE , min(('0.0.0.0'::inet + b)) PARTIAL_AGGREGATE , min(b::real) PARTIAL_AGGREGATE , min(b::double precision) PARTIAL_AGGREGATE , min(b::smallint) PARTIAL_AGGREGATE , min(b) PARTIAL_AGGREGATE , min(b::bigint) PARTIAL_AGGREGATE , min(c_interval) PARTIAL_AGGREGATE , min(c_money) PARTIAL_AGGREGATE , min(b::numeric) PARTIAL_AGGREGATE , min(b::oid) PARTIAL_AGGREGATE , min(c_pg_lsn) PARTIAL_AGGREGATE , min(c_tid) PARTIAL_AGGREGATE , min(c_1c) PARTIAL_AGGREGATE , min(c_time) PARTIAL_AGGREGATE , min(c_timetz) PARTIAL_AGGREGATE , min(c_timestamp) PARTIAL_AGGREGATE , min(c_timestamptz) PARTIAL_AGGREGATE , min(c_xid8) PARTIAL_AGGREGATE , avg(b) PARTIAL_AGGREGATE , avg(b::bigint) PARTIAL_AGGREGATE FROM public.pagg_tab_p2 WHERE ((c_serial >= 1)) AND ((c_serial <= 30))
-> Foreign Scan
- Output: (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 every(pagg_tab_2.c_bool)), (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_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 avg(pagg_tab_2.b))
+ Output: (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 every(pagg_tab_2.c_bool)), (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_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 avg(pagg_tab_2.b)), (PARTIAL avg((pagg_tab_2.b)::bigint))
Relations: Aggregate on (public.fpagg_tab_p3 pagg_tab_2)
- Remote SQL: SELECT bit_and(c_bit) PARTIAL_AGGREGATE , bit_and(c_1or3int2) PARTIAL_AGGREGATE , bit_and(c_1or3int4) PARTIAL_AGGREGATE , bit_and(c_1or3int8) PARTIAL_AGGREGATE , bit_or(c_bit) PARTIAL_AGGREGATE , bit_or(c_1or3int2) PARTIAL_AGGREGATE , bit_or(c_1or3int4) PARTIAL_AGGREGATE , bit_or(c_1or3int8) PARTIAL_AGGREGATE , bit_xor(c_bit) PARTIAL_AGGREGATE , bit_xor(c_1or3int2) PARTIAL_AGGREGATE , bit_xor(c_1or3int4) PARTIAL_AGGREGATE , bit_xor(c_1or3int8) PARTIAL_AGGREGATE , bool_and(c_bool) PARTIAL_AGGREGATE , bool_or(c_bool) PARTIAL_AGGREGATE , every(c_bool) PARTIAL_AGGREGATE , max(c_1c::character(1)) PARTIAL_AGGREGATE , max(('01-01-2000'::date + b)) PARTIAL_AGGREGATE , max(('0.0.0.0'::inet + b)) PARTIAL_AGGREGATE , max(b::real) PARTIAL_AGGREGATE , max(b::double precision) PARTIAL_AGGREGATE , max(b::smallint) PARTIAL_AGGREGATE , max(b) PARTIAL_AGGREGATE , max(b::bigint) PARTIAL_AGGREGATE , max(c_interval) PARTIAL_AGGREGATE , max(c_money) PARTIAL_AGGREGATE , max(b::numeric) PARTIAL_AGGREGATE , max(b::oid) PARTIAL_AGGREGATE , max(c_pg_lsn) PARTIAL_AGGREGATE , max(c_tid) PARTIAL_AGGREGATE , max(c_1c) PARTIAL_AGGREGATE , max(c_time) PARTIAL_AGGREGATE , max(c_timetz) PARTIAL_AGGREGATE , max(c_timestamp) PARTIAL_AGGREGATE , max(c_timestamptz) PARTIAL_AGGREGATE , max(c_xid8) PARTIAL_AGGREGATE , min(c_1c::character(1)) PARTIAL_AGGREGATE , min(('01-01-2000'::date + b)) PARTIAL_AGGREGATE , min(('0.0.0.0'::inet + b)) PARTIAL_AGGREGATE , min(b::real) PARTIAL_AGGREGATE , min(b::double precision) PARTIAL_AGGREGATE , min(b::smallint) PARTIAL_AGGREGATE , min(b) PARTIAL_AGGREGATE , min(b::bigint) PARTIAL_AGGREGATE , min(c_interval) PARTIAL_AGGREGATE , min(c_money) PARTIAL_AGGREGATE , min(b::numeric) PARTIAL_AGGREGATE , min(b::oid) PARTIAL_AGGREGATE , min(c_pg_lsn) PARTIAL_AGGREGATE , min(c_tid) PARTIAL_AGGREGATE , min(c_1c) PARTIAL_AGGREGATE , min(c_time) PARTIAL_AGGREGATE , min(c_timetz) PARTIAL_AGGREGATE , min(c_timestamp) PARTIAL_AGGREGATE , min(c_timestamptz) PARTIAL_AGGREGATE , min(c_xid8) PARTIAL_AGGREGATE , avg(b) PARTIAL_AGGREGATE FROM public.pagg_tab_p3 WHERE ((c_serial >= 1)) AND ((c_serial <= 30))
+ Remote SQL: SELECT bit_and(c_bit) PARTIAL_AGGREGATE , bit_and(c_1or3int2) PARTIAL_AGGREGATE , bit_and(c_1or3int4) PARTIAL_AGGREGATE , bit_and(c_1or3int8) PARTIAL_AGGREGATE , bit_or(c_bit) PARTIAL_AGGREGATE , bit_or(c_1or3int2) PARTIAL_AGGREGATE , bit_or(c_1or3int4) PARTIAL_AGGREGATE , bit_or(c_1or3int8) PARTIAL_AGGREGATE , bit_xor(c_bit) PARTIAL_AGGREGATE , bit_xor(c_1or3int2) PARTIAL_AGGREGATE , bit_xor(c_1or3int4) PARTIAL_AGGREGATE , bit_xor(c_1or3int8) PARTIAL_AGGREGATE , bool_and(c_bool) PARTIAL_AGGREGATE , bool_or(c_bool) PARTIAL_AGGREGATE , every(c_bool) PARTIAL_AGGREGATE , max(c_1c::character(1)) PARTIAL_AGGREGATE , max(('01-01-2000'::date + b)) PARTIAL_AGGREGATE , max(('0.0.0.0'::inet + b)) PARTIAL_AGGREGATE , max(b::real) PARTIAL_AGGREGATE , max(b::double precision) PARTIAL_AGGREGATE , max(b::smallint) PARTIAL_AGGREGATE , max(b) PARTIAL_AGGREGATE , max(b::bigint) PARTIAL_AGGREGATE , max(c_interval) PARTIAL_AGGREGATE , max(c_money) PARTIAL_AGGREGATE , max(b::numeric) PARTIAL_AGGREGATE , max(b::oid) PARTIAL_AGGREGATE , max(c_pg_lsn) PARTIAL_AGGREGATE , max(c_tid) PARTIAL_AGGREGATE , max(c_1c) PARTIAL_AGGREGATE , max(c_time) PARTIAL_AGGREGATE , max(c_timetz) PARTIAL_AGGREGATE , max(c_timestamp) PARTIAL_AGGREGATE , max(c_timestamptz) PARTIAL_AGGREGATE , max(c_xid8) PARTIAL_AGGREGATE , min(c_1c::character(1)) PARTIAL_AGGREGATE , min(('01-01-2000'::date + b)) PARTIAL_AGGREGATE , min(('0.0.0.0'::inet + b)) PARTIAL_AGGREGATE , min(b::real) PARTIAL_AGGREGATE , min(b::double precision) PARTIAL_AGGREGATE , min(b::smallint) PARTIAL_AGGREGATE , min(b) PARTIAL_AGGREGATE , min(b::bigint) PARTIAL_AGGREGATE , min(c_interval) PARTIAL_AGGREGATE , min(c_money) PARTIAL_AGGREGATE , min(b::numeric) PARTIAL_AGGREGATE , min(b::oid) PARTIAL_AGGREGATE , min(c_pg_lsn) PARTIAL_AGGREGATE , min(c_tid) PARTIAL_AGGREGATE , min(c_1c) PARTIAL_AGGREGATE , min(c_time) PARTIAL_AGGREGATE , min(c_timetz) PARTIAL_AGGREGATE , min(c_timestamp) PARTIAL_AGGREGATE , min(c_timestamptz) PARTIAL_AGGREGATE , min(c_xid8) PARTIAL_AGGREGATE , avg(b) PARTIAL_AGGREGATE , avg(b::bigint) PARTIAL_AGGREGATE FROM public.pagg_tab_p3 WHERE ((c_serial >= 1)) AND ((c_serial <= 30))
(15 rows)
SELECT
@@ -10507,11 +10509,13 @@ SELECT
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_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),
/* The cases that require only import functions */
- avg(b::int4)
+ avg(b::int4),
+ /* The cases that don't require import or export functions */
+ avg(b::int8)
FROM pagg_tab WHERE c_serial between 1 and 30;
- 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 | every | 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 | avg
----------+---------+---------+---------+--------+--------+--------+--------+---------+---------+---------+---------+----------+---------+-------+-----+------------+----------+-----+-----+-----+-----+-----+---------+-------+-----+-----+------+--------+-----+----------+-------------+--------------------------+------------------------------+-----+-----+------------+---------+-----+-----+-----+-----+-----+-----+-------+-----+-----+-----+-------+-----+----------+-------------+--------------------------+------------------------------+-----+---------------------
- 01 | 1 | 1 | 1 | 11 | 3 | 3 | 3 | 10 | 2 | 2 | 2 | f | t | f | 9 | 01-31-2000 | 0.0.0.30 | 30 | 30 | 30 | 30 | 30 | @ 1 sec | $1.00 | 30 | 30 | 0/30 | (0,30) | 9 | 00:00:30 | 00:00:30+00 | Sat Jan 01 00:00:30 2000 | Sat Jan 01 00:00:30 2000 UTC | 9 | 0 | 01-02-2000 | 0.0.0.1 | 1 | 1 | 1 | 1 | 1 | @ 0 | $0.00 | 1 | 1 | 0/1 | (0,1) | 0 | 00:00:01 | 00:00:01+00 | Sat Jan 01 00:00:01 2000 | Sat Jan 01 00:00:01 2000 UTC | 0 | 15.5000000000000000
+ 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 | every | 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 | avg | avg
+---------+---------+---------+---------+--------+--------+--------+--------+---------+---------+---------+---------+----------+---------+-------+-----+------------+----------+-----+-----+-----+-----+-----+---------+-------+-----+-----+------+--------+-----+----------+-------------+--------------------------+------------------------------+-----+-----+------------+---------+-----+-----+-----+-----+-----+-----+-------+-----+-----+-----+-------+-----+----------+-------------+--------------------------+------------------------------+-----+---------------------+---------------------
+ 01 | 1 | 1 | 1 | 11 | 3 | 3 | 3 | 10 | 2 | 2 | 2 | f | t | f | 9 | 01-31-2000 | 0.0.0.30 | 30 | 30 | 30 | 30 | 30 | @ 1 sec | $1.00 | 30 | 30 | 0/30 | (0,30) | 9 | 00:00:30 | 00:00:30+00 | Sat Jan 01 00:00:30 2000 | Sat Jan 01 00:00:30 2000 UTC | 9 | 0 | 01-02-2000 | 0.0.0.1 | 1 | 1 | 1 | 1 | 1 | @ 0 | $0.00 | 1 | 1 | 0/1 | (0,1) | 0 | 00:00:01 | 00:00:01+00 | Sat Jan 01 00:00:01 2000 | Sat Jan 01 00:00:01 2000 UTC | 0 | 15.5000000000000000 | 15.5000000000000000
(1 row)
-- Tests for partial_aggregate_support
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 78fa8b7b57..83336bb7e8 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3243,7 +3243,9 @@ SELECT
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_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),
/* The cases that require only import functions */
- avg(b::int4)
+ avg(b::int4),
+ /* The cases that don't require import or export functions */
+ avg(b::int8)
FROM pagg_tab WHERE c_serial between 1 and 30;
SELECT
@@ -3257,7 +3259,9 @@ SELECT
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_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),
/* The cases that require only import functions */
- avg(b::int4)
+ avg(b::int4),
+ /* The cases that don't require import or export functions */
+ avg(b::int8)
FROM pagg_tab WHERE c_serial between 1 and 30;
-- Tests for partial_aggregate_support
diff --git a/src/backend/executor/nodeAgg.c b/src/backend/executor/nodeAgg.c
index 6023169ef4..6ce08905e5 100644
--- a/src/backend/executor/nodeAgg.c
+++ b/src/backend/executor/nodeAgg.c
@@ -1077,7 +1077,10 @@ finalize_aggregate(AggState *aggstate,
/*
* Apply the agg's finalfn if one is provided and PARTIAL_AGGREGATE
- * keyword is not specified, else return transValue.
+ * keyword is not specified.
+ * If exportfn is provided and PARTIAL_AGGREGATE keyword is
+ * specified, apply the agg's exportfn.
+ * Otherwise return transValue.
*/
if (OidIsValid(peragg->finalfn_oid) &&
(peragg->aggref->agg_partial == false))
@@ -1126,6 +1129,40 @@ finalize_aggregate(AggState *aggstate,
}
aggstate->curperagg = NULL;
}
+ else if (peragg->aggref->agg_partial && OidIsValid(peragg->exportfn_oid))
+ {
+ /* set up aggstate->curperagg for AggGetAggref() */
+ aggstate->curperagg = peragg;
+
+ InitFunctionCallInfoData(*fcinfo, &peragg->exportfn, 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 =
@@ -3664,7 +3701,7 @@ ExecInitAgg(Agg *node, EState *estate, int eflags)
Oid serialfn_oid,
deserialfn_oid;
Oid aggOwner;
- Expr *finalfnexpr;
+ Expr *finalfnexpr, *serialfnexpr, *exportfnexpr;
Oid aggtranstype;
/* Planner should have assigned aggregate to correct level */
@@ -3702,8 +3739,10 @@ ExecInitAgg(Agg *node, EState *estate, int eflags)
Assert(OidIsValid(aggtranstype));
/* Final function only required if we're finalizing the aggregates */
- if (DO_AGGSPLIT_SKIPFINAL(aggstate->aggsplit))
+ if (DO_AGGSPLIT_SKIPFINAL(aggstate->aggsplit)){
peragg->finalfn_oid = finalfn_oid = InvalidOid;
+ peragg->exportfn_oid = aggform->aggpartialexportfn;
+ }
else
peragg->finalfn_oid = finalfn_oid = aggform->aggfinalfn;
@@ -3821,6 +3860,42 @@ 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))
+ {
+ 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);
+ }
+ /*
+ * build expression trees for the exportfn, if it exists and is required.
+ */
+ if (OidIsValid(aggform->aggpartialexportfn) && peragg->aggref->agg_partial)
+ {
+ HeapTuple procTuple;
+ Form_pg_proc procform;
+
+ procTuple = SearchSysCache1(PROCOID,
+ ObjectIdGetDatum(aggform->aggpartialexportfn));
+ if (!HeapTupleIsValid(procTuple))
+ elog(ERROR, "cache lookup failed for function %u",
+ aggform->aggpartialexportfn);
+ procform = (Form_pg_proc) GETSTRUCT(procTuple);
+ ReleaseSysCache(procTuple);
+
+ build_aggregate_exportfn_expr(aggform->aggpartialexportfn,
+ aggtranstype,
+ procform->prorettype,
+ aggref->inputcollid,
+ &exportfnexpr);
+ peragg->exportfn_oid = aggform->aggpartialexportfn;
+ fmgr_info(aggform->aggpartialexportfn, &peragg->exportfn);
+ fmgr_info_set_expr((Node *) exportfnexpr, &peragg->exportfn);
+ }
/* get info about the output value's datatype */
get_typlenbyval(aggref->aggtype,
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 8439d0bce6..e52ecfd120 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -227,6 +227,8 @@ transformAggregateCall(ParseState *pstate, Aggref *agg, List *args,
if(agg->agg_partial){
HeapTuple aggtup;
Form_pg_aggregate aggform;
+ HeapTuple proctup;
+ Form_pg_proc procform;
aggtup = SearchSysCache1(AGGFNOID, ObjectIdGetDatum(agg->aggfnoid));
if (!HeapTupleIsValid(aggtup))
@@ -235,8 +237,21 @@ transformAggregateCall(ParseState *pstate, Aggref *agg, List *args,
if (!aggform->aggpartialpushdownsafe)
elog(ERROR, "partial aggregate is unsafe for aggregate %u",
agg->aggfnoid);
+ if (aggform->aggtranstype == INTERNALOID &&
+ aggform->aggpartialexportfn == InvalidOid)
+ elog(ERROR, "definition of aggregate %u is invalid",
+ agg->aggfnoid);
ReleaseSysCache(aggtup);
- agg->aggtype = aggform->aggtranstype;
+ if(aggform->aggpartialexportfn != InvalidOid){
+ proctup = SearchSysCache1(PROCOID,
+ ObjectIdGetDatum(aggform->aggpartialexportfn));
+ if (!HeapTupleIsValid(proctup))
+ elog(ERROR, "cache lookup failed for export function %u",
+ aggform->aggpartialexportfn);
+ procform = (Form_pg_proc) GETSTRUCT(proctup);
+ agg->aggtype = procform->prorettype;
+ } else
+ agg->aggtype = aggform->aggtranstype;
}
/*
@@ -2181,6 +2196,32 @@ build_aggregate_finalfn_expr(Oid *agg_input_types,
/* finalfn is currently never treated as variadic */
}
+/*
+ * Like build_aggregate_transfn_expr, but creates an expression tree for the
+ * export function of an aggregate.
+ */
+void
+build_aggregate_exportfn_expr(Oid exportfn_oid,
+ Oid agg_state_type,
+ Oid agg_result_type,
+ Oid agg_input_collation,
+ Expr **exportfnexpr)
+{
+ List *args;
+ FuncExpr *fexpr;
+
+ /* Build expr tree for export function */
+ args = list_make1(make_agg_arg(agg_state_type, agg_input_collation));
+
+ fexpr = makeFuncExpr(exportfn_oid,
+ agg_result_type,
+ args,
+ InvalidOid,
+ agg_input_collation,
+ COERCE_EXPLICIT_CALL);
+ *exportfnexpr = (Expr *) fexpr;
+}
+
/*
* Convenience function to build dummy argument expressions for aggregates.
*
diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c
index d6641b570d..83c9d53b52 100644
--- a/src/backend/utils/adt/arrayfuncs.c
+++ b/src/backend/utils/adt/arrayfuncs.c
@@ -3727,6 +3727,12 @@ deconstruct_array_builtin(ArrayType *array,
elmalign = TYPALIGN_SHORT;
break;
+ case NUMERICOID:
+ elmlen = -1;
+ elmbyval = false;
+ elmalign = TYPALIGN_INT;
+ break;
+
default:
elog(ERROR, "type %u not supported by deconstruct_array_builtin()", elmtype);
/* keep compiler quiet */
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index d09e3042ca..1046c35487 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -5544,6 +5544,7 @@ typedef NumericAggState PolyNumAggState;
#define makePolyNumAggState makeNumericAggState
#define makePolyNumAggStateCurrentContext makeNumericAggStateCurrentContext
#endif
+static bytea* int8_avg_serialize_internal(PolyNumAggState *state);
Datum
int2_accum(PG_FUNCTION_ARGS)
@@ -5870,23 +5871,15 @@ int8_avg_combine(PG_FUNCTION_ARGS)
}
/*
- * int8_avg_serialize
- * Serialize PolyNumAggState into bytea using the standard
- * recv-function infrastructure.
+ * core of int8_avg_serialize
*/
-Datum
-int8_avg_serialize(PG_FUNCTION_ARGS)
+bytea*
+int8_avg_serialize_internal(PolyNumAggState *state)
{
- PolyNumAggState *state;
StringInfoData buf;
bytea *result;
NumericVar tmp_var;
- /* Ensure we disallow calling when not in aggregate context */
- if (!AggCheckCallContext(fcinfo, NULL))
- elog(ERROR, "aggregate function called in non-aggregate context");
-
- state = (PolyNumAggState *) PG_GETARG_POINTER(0);
/*
* If the platform supports int128 then sumX will be a 128 integer type.
* Here we'll convert that into a numeric type so that the combine state
@@ -5915,7 +5908,25 @@ int8_avg_serialize(PG_FUNCTION_ARGS)
free_var(&tmp_var);
- PG_RETURN_BYTEA_P(result);
+ return result;
+}
+
+/*
+ * int8_avg_serialize
+ * Serialize PolyNumAggState into bytea using the standard
+ * recv-function infrastructure.
+ */
+Datum
+int8_avg_serialize(PG_FUNCTION_ARGS)
+{
+ PolyNumAggState *state;
+
+ /* Ensure we disallow calling when not in aggregate context */
+ if (!AggCheckCallContext(fcinfo, NULL))
+ elog(ERROR, "aggregate function called in non-aggregate context");
+
+ state = (PolyNumAggState *) PG_GETARG_POINTER(0);
+ PG_RETURN_BYTEA_P(int8_avg_serialize_internal(state));
}
/*
@@ -5964,6 +5975,97 @@ int8_avg_deserialize(PG_FUNCTION_ARGS)
PG_RETURN_POINTER(result);
}
+/*
+ * int8_avg_export
+ * Convert PolyNumAggState into _numeric of count and sum.
+ */
+Datum
+int8_avg_export(PG_FUNCTION_ARGS)
+{
+ PolyNumAggState *state;
+ NumericVar N_var, sumX_var;
+ ArrayBuildState *astate;
+
+ /* Ensure we disallow calling when not in aggregate context */
+ if (!AggCheckCallContext(fcinfo, NULL))
+ elog(ERROR, "aggregate function called in non-aggregate context");
+
+ state = (PolyNumAggState *) PG_GETARG_POINTER(0);
+
+ init_var(&N_var);
+ init_var(&sumX_var);
+
+ astate = initArrayResult(NUMERICOID, CurrentMemoryContext, false);
+
+ /* N */
+ int64_to_numericvar(state->N, &N_var);
+ accumArrayResult(astate, NumericGetDatum(make_result(&N_var)), false,
+ NUMERICOID, CurrentMemoryContext);
+
+ /* sumX */
+#ifdef HAVE_INT128
+ int128_to_numericvar(state->sumX, &sumX_var);
+#else
+ accum_sum_final(&state->sumX, &sumX_var);
+#endif
+ accumArrayResult(astate, NumericGetDatum(make_result(&sumX_var)), false,
+ NUMERICOID, CurrentMemoryContext);
+
+ PG_RETURN_DATUM(makeArrayResult(astate, CurrentMemoryContext));
+}
+
+/*
+ * int8_avg_import
+ * Convert _numeric of count and sum into PolyNumAggState.
+ */
+Datum
+int8_avg_import(PG_FUNCTION_ARGS)
+{
+ PolyNumAggState *state;
+ Numeric N_numeric, sumX_numeric;
+ NumericVar N_var, sumX_var;
+ ArrayType *transarray;
+
+ Datum *datums;
+ bool *nulls;
+ int ndatum;
+
+ transarray = PG_GETARG_ARRAYTYPE_P(0);
+
+ if (ARR_HASNULL(transarray)
+ || ArrayGetNItems(ARR_NDIM(transarray), ARR_DIMS(transarray)) != 2
+ || ARR_NDIM(transarray) != 1
+ || ARR_ELEMTYPE(transarray) != NUMERICOID)
+ elog(ERROR, "expected 2-element numeric array");
+
+ deconstruct_array_builtin(transarray, NUMERICOID, &datums, &nulls, &ndatum);
+ N_numeric = DatumGetNumeric(datums[0]);
+ sumX_numeric = DatumGetNumeric(datums[1]);
+
+ if(NUMERIC_IS_SPECIAL(N_numeric) || NUMERIC_IS_SPECIAL(sumX_numeric))
+ elog(ERROR, "both of count and sum must be not special value of numeric.");
+
+ if(cmp_numerics(N_numeric, int64_to_numeric(0)) <= 0)
+ elog(ERROR, "count must be positive");
+
+ state = makePolyNumAggStateCurrentContext(false);
+
+ /* N */
+ init_var_from_num(N_numeric, &N_var);
+ if (!numericvar_to_int64(&N_var, &state->N))
+ elog(ERROR, "count must be in range in bigint");
+
+ /* sumX */
+ init_var_from_num(sumX_numeric, &sumX_var);
+ #ifdef HAVE_INT128
+ numericvar_to_int128(&sumX_var, &state->sumX);
+ #else
+ accum_sum_add(&state->sumX, &sumX_var);
+ #endif
+ PG_RETURN_BYTEA_P(int8_avg_serialize_internal(state));
+}
+
+
/*
* Inverse transition functions to go with the above.
*/
diff --git a/src/include/catalog/pg_aggregate.dat b/src/include/catalog/pg_aggregate.dat
index da1b1f8fab..db752b6786 100644
--- a/src/include/catalog/pg_aggregate.dat
+++ b/src/include/catalog/pg_aggregate.dat
@@ -16,6 +16,8 @@
{ aggfnoid => 'avg(int8)', aggtransfn => 'int8_avg_accum',
aggfinalfn => 'numeric_poly_avg', aggcombinefn => 'int8_avg_combine',
aggserialfn => 'int8_avg_serialize', aggdeserialfn => 'int8_avg_deserialize',
+ aggpartialpushdownsafe => 't', aggpartialexportfn => 'int8_avg_export',
+ aggpartialimportfn => 'int8_avg_import',
aggmtransfn => 'int8_avg_accum', aggminvtransfn => 'int8_avg_accum_inv',
aggmfinalfn => 'numeric_poly_avg', aggtranstype => 'internal',
aggtransspace => '48', aggmtranstype => 'internal', aggmtransspace => '48' },
diff --git a/src/include/catalog/pg_aggregate.h b/src/include/catalog/pg_aggregate.h
index 54481b0ac2..3e56af78cf 100644
--- a/src/include/catalog/pg_aggregate.h
+++ b/src/include/catalog/pg_aggregate.h
@@ -58,6 +58,10 @@ CATALOG(pg_aggregate,2600,AggregateRelationId)
/* true if partial aggregate is fine to push down */
bool aggpartialpushdownsafe BKI_DEFAULT(f);
+ /* function to convert aggregate's transition (state) data from local
+ format to standard format (0 if none). */
+ regproc aggpartialexportfn BKI_DEFAULT(-) BKI_LOOKUP_OPT(pg_proc);
+
/* function to check validity of aggregate's transition (state)
data and to convert it from standard format to local format (0 if none) */
regproc aggpartialimportfn BKI_DEFAULT(-) BKI_LOOKUP_OPT(pg_proc);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index db8c293b7c..deadcdb6d8 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4878,6 +4878,12 @@
{ oid => '2787', descr => 'aggregate deserial function',
proname => 'int8_avg_deserialize', prorettype => 'internal',
proargtypes => 'bytea internal', prosrc => 'int8_avg_deserialize' },
+{ oid => '6401', descr => 'aggregate export function',
+ proname => 'int8_avg_export', prorettype => '_numeric',
+ proargtypes => 'internal', prosrc => 'int8_avg_export' },
+{ oid => '6402', descr => 'aggregate import function',
+ proname => 'int8_avg_import', prorettype => 'internal',
+ proargtypes => '_numeric', prosrc => 'int8_avg_import' },
{ oid => '3324', descr => 'aggregate combine function',
proname => 'int4_avg_combine', prorettype => '_int8',
proargtypes => '_int8 _int8', prosrc => 'int4_avg_combine' },
diff --git a/src/include/executor/nodeAgg.h b/src/include/executor/nodeAgg.h
index 079360c153..bac795b847 100644
--- a/src/include/executor/nodeAgg.h
+++ b/src/include/executor/nodeAgg.h
@@ -215,6 +215,15 @@ typedef struct AggStatePerAggData
*/
FmgrInfo serialfn;
+ /* Optional Oid of export function (may be InvalidOid) */
+ Oid exportfn_oid;
+
+ /*
+ * fmgr lookup data for export function --- only valid when exportfn_oid is
+ * not InvalidOid.
+ */
+ FmgrInfo exportfn;
+
/*
* 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/parser/parse_agg.h b/src/include/parser/parse_agg.h
index 58cbad7524..ee74d25b43 100644
--- a/src/include/parser/parse_agg.h
+++ b/src/include/parser/parse_agg.h
@@ -62,4 +62,10 @@ extern void build_aggregate_finalfn_expr(Oid *agg_input_types,
Oid finalfn_oid,
Expr **finalfnexpr);
+extern void build_aggregate_exportfn_expr(Oid exportfn_oid,
+ Oid agg_state_type,
+ Oid agg_result_type,
+ Oid agg_input_collation,
+ Expr **exportfnexpr);
+
#endif /* PARSE_AGG_H */
diff --git a/src/test/regress/expected/oidjoins.out b/src/test/regress/expected/oidjoins.out
index da0cd81c08..ebfe066fe6 100644
--- a/src/test/regress/expected/oidjoins.out
+++ b/src/test/regress/expected/oidjoins.out
@@ -145,6 +145,7 @@ NOTICE: checking pg_aggregate {aggfinalfn} => pg_proc {oid}
NOTICE: checking pg_aggregate {aggcombinefn} => pg_proc {oid}
NOTICE: checking pg_aggregate {aggserialfn} => pg_proc {oid}
NOTICE: checking pg_aggregate {aggdeserialfn} => pg_proc {oid}
+NOTICE: checking pg_aggregate {aggpartialexportfn} => pg_proc {oid}
NOTICE: checking pg_aggregate {aggpartialimportfn} => pg_proc {oid}
NOTICE: checking pg_aggregate {aggmtransfn} => pg_proc {oid}
NOTICE: checking pg_aggregate {aggminvtransfn} => pg_proc {oid}
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index 9d047b21b8..c82a5562fb 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -287,10 +287,11 @@ SELECT p1.oid, p1.proname
FROM pg_proc as p1
WHERE p1.prorettype = 'internal'::regtype AND NOT
'internal'::regtype = ANY (p1.proargtypes);
- oid | proname
-------+-------------
+ oid | proname
+------+-----------------
+ 6402 | int8_avg_import
2304 | internal_in
-(1 row)
+(2 rows)
-- Look for functions that return a polymorphic type and do not have any
-- polymorphic argument. Calls of such functions would be unresolvable
--
2.31.1
[application/octet-stream] 0001-v2-POC-Partial-aggregate-pushdown-notinternal-transtype.patch (143.4K, ../TY2PR01MB3835CB1D110362A3213E5F6795D22@TY2PR01MB3835.jpnprd01.prod.outlook.com/3-0001-v2-POC-Partial-aggregate-pushdown-notinternal-transtype.patch)
download | inline diff:
From 08bbe07cfeea04da8f9ac07d0e16bf6f11096c02 Mon Sep 17 00:00:00 2001
From: Yuki Fujii <[email protected]>
Date: Thu, 20 Jun 2024 11:35:23 +0900
Subject: [PATCH 1/2] v2 POC Partial aggregate pushdown; notinternal transtype
This is a POC patch.
Add the cases in that the transtype is not internal and not pseudo type.
So far, only avg(int4) and other aggregate functions which do not need import or export functions like min, max, etc.
There are the following tasks remaining.
1. Add sufficient documents and comments and tests.
2. Consider appropriate position of the new keyword "PARTIAL_AGGREGATE".
3. Consider to avoid to make the new keyword "PARTIAL_AGGREGATE" become a reserved word.
4. Support other built-in aggregate functions like sum, avg, count.
5. Support user-defined aggregate functions.
---
contrib/postgres_fdw/deparse.c | 75 +-
.../postgres_fdw/expected/postgres_fdw.out | 659 ++++++++++++++++--
contrib/postgres_fdw/option.c | 4 +-
contrib/postgres_fdw/postgres_fdw.c | 202 +++++-
contrib/postgres_fdw/postgres_fdw.h | 12 +-
contrib/postgres_fdw/sql/postgres_fdw.sql | 114 ++-
src/backend/catalog/pg_aggregate.c | 34 +
src/backend/commands/aggregatecmds.c | 8 +
src/backend/executor/nodeAgg.c | 6 +-
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 | 18 +-
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/numeric.c | 21 +-
src/backend/utils/adt/ruleutils.c | 3 +-
src/bin/pg_dump/pg_dump.c | 9 +
src/include/catalog/pg_aggregate.dat | 222 +++---
src/include/catalog/pg_aggregate.h | 9 +
src/include/catalog/pg_proc.dat | 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 +-
src/test/regress/expected/aggregates.out | 8 +
.../regress/expected/create_aggregate.out | 10 +
src/test/regress/expected/oidjoins.out | 1 +
.../regress/expected/partition_aggregate.out | 29 +
src/test/regress/sql/aggregates.sql | 4 +
src/test/regress/sql/create_aggregate.sql | 11 +
src/test/regress/sql/partition_aggregate.sql | 5 +
37 files changed, 1445 insertions(+), 231 deletions(-)
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index fb590c87e6..4b6bc6e9fd 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,21 @@ 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 ");
if (AGGKIND_IS_ORDERED_SET(node->aggkind))
{
@@ -3740,6 +3744,9 @@ deparseAggref(Aggref *node, deparse_expr_cxt *context)
}
appendStringInfoChar(buf, ')');
+
+ if (node->aggsplit == AGGSPLIT_INITIAL_SERIAL)
+ appendStringInfoString(buf, " PARTIAL_AGGREGATE ");
}
/*
@@ -3865,9 +3872,10 @@ appendGroupByClause(List *tlist, deparse_expr_cxt *context)
Query *query = context->root->parse;
ListCell *lc;
bool first = true;
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) context->foreignrel->fdw_private;
/* Nothing to be done, if there's no GROUP BY clause in the query. */
- if (!query->groupClause)
+ if (!fpinfo->group_clause)
return;
appendStringInfoString(buf, " GROUP BY ");
@@ -3885,7 +3893,7 @@ appendGroupByClause(List *tlist, deparse_expr_cxt *context)
* to empty, and in any case the redundancy situation on the remote might
* be different than what we think here.
*/
- foreach(lc, query->groupClause)
+ foreach(lc, fpinfo->group_clause)
{
SortGroupClause *grp = (SortGroupClause *) lfirst(lc);
@@ -4206,3 +4214,52 @@ 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);
+
+ partial_agg_ok = aggform->aggpartialpushdownsafe && fpinfo->partial_aggregate_support;
+ if(aggform->aggtranstype == INTERNALOID ||
+ get_typtype(aggform->aggtranstype) == TYPTYPE_PSEUDO)
+ partial_agg_ok = false;
+ if(partial_agg_ok){
+ 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 ea566d5034..48ac44df07 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -10095,36 +10095,57 @@ RESET enable_partitionwise_join;
-- ===================================================================
-- test partitionwise aggregates
-- ===================================================================
-CREATE TABLE pagg_tab (a int, b int, c text) PARTITION BY RANGE(a);
+ALTER SERVER loopback OPTIONS (ADD fdw_tuple_cost '0.1');
+CREATE TYPE mood AS enum ('sad', 'ok', 'happy');
+ALTER EXTENSION postgres_fdw ADD TYPE mood;
+CREATE TABLE pagg_tab (a int, b int, c text, c_serial int4,
+ c_int4array _int4, c_interval interval,
+ c_money money, c_1c text, c_1b bytea,
+ c_bit bit(2), c_1or3int2 int2,
+ c_1or3int4 int4, c_1or3int8 int8,
+ c_bool bool, c_enum mood, c_pg_lsn pg_lsn,
+ c_tid tid, c_int4range int4range,
+ c_int4multirange int4multirange,
+ c_time time, c_timetz timetz,
+ c_timestamp timestamp, c_timestamptz timestamptz,
+ c_xid8 xid8)
+ PARTITION BY RANGE(a);
CREATE TABLE pagg_tab_p1 (LIKE pagg_tab);
CREATE TABLE pagg_tab_p2 (LIKE pagg_tab);
CREATE TABLE pagg_tab_p3 (LIKE pagg_tab);
-INSERT INTO pagg_tab_p1 SELECT i % 30, i % 50, to_char(i/30, 'FM0000') FROM generate_series(1, 3000) i WHERE (i % 30) < 10;
-INSERT INTO pagg_tab_p2 SELECT i % 30, i % 50, to_char(i/30, 'FM0000') FROM generate_series(1, 3000) i WHERE (i % 30) < 20 and (i % 30) >= 10;
-INSERT INTO pagg_tab_p3 SELECT i % 30, i % 50, to_char(i/30, 'FM0000') FROM generate_series(1, 3000) i WHERE (i % 30) < 30 and (i % 30) >= 20;
+SET TimeZone = 'UTC';
+INSERT INTO pagg_tab_p1 SELECT i % 30, i % 50, to_char(i/30, 'FM0000'), i, array[(i % 2), 0], ((i % 2) || ' seconds')::interval, (i % 2)::money, ((i - 1) % 10)::text, (((i - 1) % 10)::text)::bytea, case when (i % 2) = 0 then B'01' else B'11' end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then true else false end, (case when (i % 2) = 0 then 'sad' else 'happy' end)::mood, ('0/' || i)::pg_lsn, ('(0,' || i || ')')::tid, int4range(0, i), int4multirange(int4range(0, i), int4range(100, 100 + i)), '00:00:00'::time + (i || ' seconds')::interval, '00:00:00'::timetz + (i || ' seconds')::interval, '2000-01-01'::timestamp + (i || ' seconds')::interval, '2000-01-01'::timestamptz + (i || ' seconds')::interval, ((i % 10)::text)::xid8 FROM generate_series(1, 3000) i WHERE (i % 30) < 10;
+INSERT INTO pagg_tab_p2 SELECT i % 30, i % 50, to_char(i/30, 'FM0000'), i, array[(i % 2), 0], ((i % 2) || ' seconds')::interval, (i % 2)::money, ((i - 1) % 10)::text, (((i - 1) % 10)::text)::bytea, case when (i % 2) = 0 then B'01' else B'11' end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then true else false end, (case when (i % 2) = 0 then 'sad' else 'happy' end)::mood, ('0/' || i)::pg_lsn, ('(0,' || i || ')')::tid, int4range(0, i), int4multirange(int4range(0, i), int4range(100, 100 + i)), '00:00:00'::time + (i || ' seconds')::interval, '00:00:00'::timetz + (i || ' seconds')::interval, '2000-01-01'::timestamp + (i || ' seconds')::interval, '2000-01-01'::timestamptz + (i || ' seconds')::interval, ((i % 10)::text)::xid8 FROM generate_series(1, 3000) i WHERE (i % 30) < 20 and (i % 30) >= 10;
+INSERT INTO pagg_tab_p3 SELECT i % 30, i % 50, to_char(i/30, 'FM0000'), i, array[(i % 2), 0], ((i % 2) || ' seconds')::interval, (i % 2)::money, ((i - 1) % 10)::text, (((i - 1) % 10)::text)::bytea, case when (i % 2) = 0 then B'01' else B'11' end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then true else false end, (case when (i % 2) = 0 then 'sad' else 'happy' end)::mood, ('0/' || i)::pg_lsn, ('(0,' || i || ')')::tid, int4range(0, i), int4multirange(int4range(0, i), int4range(100, 100 + i)), '00:00:00'::time + (i || ' seconds')::interval, '00:00:00'::timetz + (i || ' seconds')::interval, '2000-01-01'::timestamp + (i || ' seconds')::interval, '2000-01-01'::timestamptz + (i || ' seconds')::interval, ((i % 10)::text)::xid8 FROM generate_series(1, 3000) i WHERE (i % 30) < 30 and (i % 30) >= 20;
-- Create foreign partitions
CREATE FOREIGN TABLE fpagg_tab_p1 PARTITION OF pagg_tab FOR VALUES FROM (0) TO (10) SERVER loopback OPTIONS (table_name 'pagg_tab_p1');
CREATE FOREIGN TABLE fpagg_tab_p2 PARTITION OF pagg_tab FOR VALUES FROM (10) TO (20) SERVER loopback OPTIONS (table_name 'pagg_tab_p2');
CREATE FOREIGN TABLE fpagg_tab_p3 PARTITION OF pagg_tab FOR VALUES FROM (20) TO (30) SERVER loopback OPTIONS (table_name 'pagg_tab_p3');
ANALYZE pagg_tab;
+ANALYZE pagg_tab_p1;
+ANALYZE pagg_tab_p2;
+ANALYZE pagg_tab_p3;
ANALYZE fpagg_tab_p1;
ANALYZE fpagg_tab_p2;
ANALYZE fpagg_tab_p3;
+SET extra_float_digits = 0;
-- When GROUP BY clause matches with PARTITION KEY.
-- Plan with partitionwise aggregates is disabled
SET enable_partitionwise_aggregate TO false;
EXPLAIN (COSTS OFF)
SELECT a, sum(b), min(b), count(*) FROM pagg_tab GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
- QUERY PLAN
------------------------------------------------------
- GroupAggregate
- Group Key: pagg_tab.a
- Filter: (avg(pagg_tab.b) < '22'::numeric)
- -> Append
- -> Foreign Scan on fpagg_tab_p1 pagg_tab_1
- -> Foreign Scan on fpagg_tab_p2 pagg_tab_2
- -> Foreign Scan on fpagg_tab_p3 pagg_tab_3
-(7 rows)
+ QUERY PLAN
+-----------------------------------------------------------
+ Sort
+ Sort Key: pagg_tab.a
+ -> HashAggregate
+ Group Key: pagg_tab.a
+ Filter: (avg(pagg_tab.b) < '22'::numeric)
+ -> Append
+ -> Foreign Scan on fpagg_tab_p1 pagg_tab_1
+ -> Foreign Scan on fpagg_tab_p2 pagg_tab_2
+ -> Foreign Scan on fpagg_tab_p3 pagg_tab_3
+(9 rows)
-- Plan with partitionwise aggregates is enabled
SET enable_partitionwise_aggregate TO true;
@@ -10158,32 +10179,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
@@ -10196,27 +10219,557 @@ 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
+------------------------------------------------------------------------------------------------------------------
+ Finalize GroupAggregate
+ Output: pagg_tab.b, avg(pagg_tab.a), max(pagg_tab.a), count(*)
+ Group Key: pagg_tab.b
+ -> Sort
+ Output: pagg_tab.b, (PARTIAL avg(pagg_tab.a)), (PARTIAL max(pagg_tab.a)), (PARTIAL count(*))
+ Sort Key: pagg_tab.b
+ -> Append
+ -> Partial HashAggregate
+ Output: pagg_tab.b, PARTIAL avg(pagg_tab.a), PARTIAL max(pagg_tab.a), PARTIAL count(*)
+ 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(*)
+ 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(*)
+ 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
+(25 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 GroupAggregate
+ 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))
+ -> Sort
+ Output: ((pagg_tab.b / 2)), (PARTIAL avg(pagg_tab.a)), (PARTIAL max(pagg_tab.a)), (PARTIAL count(*)), pagg_tab.b
+ Sort Key: ((pagg_tab.b / 2))
+ -> Append
+ -> Partial HashAggregate
+ Output: ((pagg_tab.b / 2)), PARTIAL avg(pagg_tab.a), PARTIAL max(pagg_tab.a), PARTIAL count(*), pagg_tab.b
+ Group Key: (pagg_tab.b / 2)
+ -> Foreign Scan on public.fpagg_tab_p1 pagg_tab
+ Output: (pagg_tab.b / 2), pagg_tab.a, pagg_tab.b
+ Remote SQL: SELECT a, b FROM public.pagg_tab_p1
+ -> Partial HashAggregate
+ Output: ((pagg_tab_1.b / 2)), PARTIAL avg(pagg_tab_1.a), PARTIAL max(pagg_tab_1.a), PARTIAL count(*), pagg_tab_1.b
+ Group Key: (pagg_tab_1.b / 2)
+ -> Foreign Scan on public.fpagg_tab_p2 pagg_tab_1
+ Output: (pagg_tab_1.b / 2), pagg_tab_1.a, pagg_tab_1.b
+ Remote SQL: SELECT a, b FROM public.pagg_tab_p2
+ -> Partial HashAggregate
+ Output: ((pagg_tab_2.b / 2)), PARTIAL avg(pagg_tab_2.a), PARTIAL max(pagg_tab_2.a), PARTIAL count(*), pagg_tab_2.b
+ Group Key: (pagg_tab_2.b / 2)
+ -> Foreign Scan on public.fpagg_tab_p3 pagg_tab_2
+ Output: (pagg_tab_2.b / 2), pagg_tab_2.a, pagg_tab_2.b
+ Remote SQL: SELECT a, b FROM public.pagg_tab_p3
+(28 rows)
+
+SELECT avg(a), max(a), count(*), (b/2)::numeric FROM pagg_tab GROUP BY b/2 ORDER BY 4;
+ avg | max | count | numeric
+---------------------+-----+-------+---------
+ 10.5000000000000000 | 21 | 120 | 0
+ 12.5000000000000000 | 23 | 120 | 1
+ 14.5000000000000000 | 25 | 120 | 2
+ 16.5000000000000000 | 27 | 120 | 3
+ 18.5000000000000000 | 29 | 120 | 4
+ 10.5000000000000000 | 21 | 120 | 5
+ 12.5000000000000000 | 23 | 120 | 6
+ 14.5000000000000000 | 25 | 120 | 7
+ 16.5000000000000000 | 27 | 120 | 8
+ 18.5000000000000000 | 29 | 120 | 9
+ 10.5000000000000000 | 21 | 120 | 10
+ 12.5000000000000000 | 23 | 120 | 11
+ 14.5000000000000000 | 25 | 120 | 12
+ 16.5000000000000000 | 27 | 120 | 13
+ 18.5000000000000000 | 29 | 120 | 14
+ 10.5000000000000000 | 21 | 120 | 15
+ 12.5000000000000000 | 23 | 120 | 16
+ 14.5000000000000000 | 25 | 120 | 17
+ 16.5000000000000000 | 27 | 120 | 18
+ 18.5000000000000000 | 29 | 120 | 19
+ 10.5000000000000000 | 21 | 120 | 20
+ 12.5000000000000000 | 23 | 120 | 21
+ 14.5000000000000000 | 25 | 120 | 22
+ 16.5000000000000000 | 27 | 120 | 23
+ 18.5000000000000000 | 29 | 120 | 24
+(25 rows)
+
+-- Partial aggregates pushdown behaves sane with non-shipped grouping elements
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT avg(a), (b/2)::numeric, 'test' test FROM pagg_tab GROUP BY 3, b/2 ORDER BY 1, 2 LIMIT 10;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: (avg(pagg_tab.a)), ((((pagg_tab.b / 2)))::numeric), 'test'::text, ((pagg_tab.b / 2))
+ -> Sort
+ Output: (avg(pagg_tab.a)), ((((pagg_tab.b / 2)))::numeric), 'test'::text, ((pagg_tab.b / 2))
+ Sort Key: (avg(pagg_tab.a)), ((((pagg_tab.b / 2)))::numeric)
+ -> Finalize HashAggregate
+ Output: avg(pagg_tab.a), (((pagg_tab.b / 2)))::numeric, 'test'::text, ((pagg_tab.b / 2))
+ Group Key: ((pagg_tab.b / 2))
+ -> Append
+ -> Foreign Scan
+ Output: ((pagg_tab.b / 2)), (PARTIAL avg(pagg_tab.a)), pagg_tab.b
+ Relations: Aggregate on (public.fpagg_tab_p1 pagg_tab)
+ Remote SQL: SELECT (b / 2), b, avg(a) PARTIAL_AGGREGATE FROM public.pagg_tab_p1 GROUP BY 1, 2
+ -> Foreign Scan
+ Output: ((pagg_tab_1.b / 2)), (PARTIAL avg(pagg_tab_1.a)), pagg_tab_1.b
+ Relations: Aggregate on (public.fpagg_tab_p2 pagg_tab_1)
+ Remote SQL: SELECT (b / 2), b, avg(a) PARTIAL_AGGREGATE FROM public.pagg_tab_p2 GROUP BY 1, 2
+ -> Foreign Scan
+ Output: ((pagg_tab_2.b / 2)), (PARTIAL avg(pagg_tab_2.a)), pagg_tab_2.b
+ Relations: Aggregate on (public.fpagg_tab_p3 pagg_tab_2)
+ Remote SQL: SELECT (b / 2), b, avg(a) PARTIAL_AGGREGATE FROM public.pagg_tab_p3 GROUP BY 1, 2
+(21 rows)
+
+SELECT avg(a), (b/2)::numeric, 'test' test FROM pagg_tab GROUP BY 3, b/2 ORDER BY 1, 2 LIMIT 10;
+ avg | numeric | test
+---------------------+---------+------
+ 10.5000000000000000 | 0 | test
+ 10.5000000000000000 | 5 | test
+ 10.5000000000000000 | 10 | test
+ 10.5000000000000000 | 15 | test
+ 10.5000000000000000 | 20 | test
+ 12.5000000000000000 | 1 | test
+ 12.5000000000000000 | 6 | test
+ 12.5000000000000000 | 11 | test
+ 12.5000000000000000 | 16 | test
+ 12.5000000000000000 | 21 | test
+(10 rows)
+
+-- Partial aggregates are safe to push down for all built-in aggregates
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT
+ /* The cases that don't require import or export functions */
+ 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),
+ every(c_bool),
+ 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_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),
+ /* The cases that require only import functions */
+ avg(b::int4)
+ FROM pagg_tab WHERE c_serial between 1 and 30;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Finalize Aggregate
+ Output: 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), every(pagg_tab.c_bool), 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_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), avg(pagg_tab.b)
+ -> Append
+ -> Foreign Scan
+ Output: (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 every(pagg_tab.c_bool)), (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_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 avg(pagg_tab.b))
+ Relations: Aggregate on (public.fpagg_tab_p1 pagg_tab)
+ Remote SQL: SELECT bit_and(c_bit) PARTIAL_AGGREGATE , bit_and(c_1or3int2) PARTIAL_AGGREGATE , bit_and(c_1or3int4) PARTIAL_AGGREGATE , bit_and(c_1or3int8) PARTIAL_AGGREGATE , bit_or(c_bit) PARTIAL_AGGREGATE , bit_or(c_1or3int2) PARTIAL_AGGREGATE , bit_or(c_1or3int4) PARTIAL_AGGREGATE , bit_or(c_1or3int8) PARTIAL_AGGREGATE , bit_xor(c_bit) PARTIAL_AGGREGATE , bit_xor(c_1or3int2) PARTIAL_AGGREGATE , bit_xor(c_1or3int4) PARTIAL_AGGREGATE , bit_xor(c_1or3int8) PARTIAL_AGGREGATE , bool_and(c_bool) PARTIAL_AGGREGATE , bool_or(c_bool) PARTIAL_AGGREGATE , every(c_bool) PARTIAL_AGGREGATE , max(c_1c::character(1)) PARTIAL_AGGREGATE , max(('01-01-2000'::date + b)) PARTIAL_AGGREGATE , max(('0.0.0.0'::inet + b)) PARTIAL_AGGREGATE , max(b::real) PARTIAL_AGGREGATE , max(b::double precision) PARTIAL_AGGREGATE , max(b::smallint) PARTIAL_AGGREGATE , max(b) PARTIAL_AGGREGATE , max(b::bigint) PARTIAL_AGGREGATE , max(c_interval) PARTIAL_AGGREGATE , max(c_money) PARTIAL_AGGREGATE , max(b::numeric) PARTIAL_AGGREGATE , max(b::oid) PARTIAL_AGGREGATE , max(c_pg_lsn) PARTIAL_AGGREGATE , max(c_tid) PARTIAL_AGGREGATE , max(c_1c) PARTIAL_AGGREGATE , max(c_time) PARTIAL_AGGREGATE , max(c_timetz) PARTIAL_AGGREGATE , max(c_timestamp) PARTIAL_AGGREGATE , max(c_timestamptz) PARTIAL_AGGREGATE , max(c_xid8) PARTIAL_AGGREGATE , min(c_1c::character(1)) PARTIAL_AGGREGATE , min(('01-01-2000'::date + b)) PARTIAL_AGGREGATE , min(('0.0.0.0'::inet + b)) PARTIAL_AGGREGATE , min(b::real) PARTIAL_AGGREGATE , min(b::double precision) PARTIAL_AGGREGATE , min(b::smallint) PARTIAL_AGGREGATE , min(b) PARTIAL_AGGREGATE , min(b::bigint) PARTIAL_AGGREGATE , min(c_interval) PARTIAL_AGGREGATE , min(c_money) PARTIAL_AGGREGATE , min(b::numeric) PARTIAL_AGGREGATE , min(b::oid) PARTIAL_AGGREGATE , min(c_pg_lsn) PARTIAL_AGGREGATE , min(c_tid) PARTIAL_AGGREGATE , min(c_1c) PARTIAL_AGGREGATE , min(c_time) PARTIAL_AGGREGATE , min(c_timetz) PARTIAL_AGGREGATE , min(c_timestamp) PARTIAL_AGGREGATE , min(c_timestamptz) PARTIAL_AGGREGATE , min(c_xid8) PARTIAL_AGGREGATE , avg(b) PARTIAL_AGGREGATE FROM public.pagg_tab_p1 WHERE ((c_serial >= 1)) AND ((c_serial <= 30))
+ -> Foreign Scan
+ Output: (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 every(pagg_tab_1.c_bool)), (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_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 avg(pagg_tab_1.b))
+ Relations: Aggregate on (public.fpagg_tab_p2 pagg_tab_1)
+ Remote SQL: SELECT bit_and(c_bit) PARTIAL_AGGREGATE , bit_and(c_1or3int2) PARTIAL_AGGREGATE , bit_and(c_1or3int4) PARTIAL_AGGREGATE , bit_and(c_1or3int8) PARTIAL_AGGREGATE , bit_or(c_bit) PARTIAL_AGGREGATE , bit_or(c_1or3int2) PARTIAL_AGGREGATE , bit_or(c_1or3int4) PARTIAL_AGGREGATE , bit_or(c_1or3int8) PARTIAL_AGGREGATE , bit_xor(c_bit) PARTIAL_AGGREGATE , bit_xor(c_1or3int2) PARTIAL_AGGREGATE , bit_xor(c_1or3int4) PARTIAL_AGGREGATE , bit_xor(c_1or3int8) PARTIAL_AGGREGATE , bool_and(c_bool) PARTIAL_AGGREGATE , bool_or(c_bool) PARTIAL_AGGREGATE , every(c_bool) PARTIAL_AGGREGATE , max(c_1c::character(1)) PARTIAL_AGGREGATE , max(('01-01-2000'::date + b)) PARTIAL_AGGREGATE , max(('0.0.0.0'::inet + b)) PARTIAL_AGGREGATE , max(b::real) PARTIAL_AGGREGATE , max(b::double precision) PARTIAL_AGGREGATE , max(b::smallint) PARTIAL_AGGREGATE , max(b) PARTIAL_AGGREGATE , max(b::bigint) PARTIAL_AGGREGATE , max(c_interval) PARTIAL_AGGREGATE , max(c_money) PARTIAL_AGGREGATE , max(b::numeric) PARTIAL_AGGREGATE , max(b::oid) PARTIAL_AGGREGATE , max(c_pg_lsn) PARTIAL_AGGREGATE , max(c_tid) PARTIAL_AGGREGATE , max(c_1c) PARTIAL_AGGREGATE , max(c_time) PARTIAL_AGGREGATE , max(c_timetz) PARTIAL_AGGREGATE , max(c_timestamp) PARTIAL_AGGREGATE , max(c_timestamptz) PARTIAL_AGGREGATE , max(c_xid8) PARTIAL_AGGREGATE , min(c_1c::character(1)) PARTIAL_AGGREGATE , min(('01-01-2000'::date + b)) PARTIAL_AGGREGATE , min(('0.0.0.0'::inet + b)) PARTIAL_AGGREGATE , min(b::real) PARTIAL_AGGREGATE , min(b::double precision) PARTIAL_AGGREGATE , min(b::smallint) PARTIAL_AGGREGATE , min(b) PARTIAL_AGGREGATE , min(b::bigint) PARTIAL_AGGREGATE , min(c_interval) PARTIAL_AGGREGATE , min(c_money) PARTIAL_AGGREGATE , min(b::numeric) PARTIAL_AGGREGATE , min(b::oid) PARTIAL_AGGREGATE , min(c_pg_lsn) PARTIAL_AGGREGATE , min(c_tid) PARTIAL_AGGREGATE , min(c_1c) PARTIAL_AGGREGATE , min(c_time) PARTIAL_AGGREGATE , min(c_timetz) PARTIAL_AGGREGATE , min(c_timestamp) PARTIAL_AGGREGATE , min(c_timestamptz) PARTIAL_AGGREGATE , min(c_xid8) PARTIAL_AGGREGATE , avg(b) PARTIAL_AGGREGATE FROM public.pagg_tab_p2 WHERE ((c_serial >= 1)) AND ((c_serial <= 30))
+ -> Foreign Scan
+ Output: (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 every(pagg_tab_2.c_bool)), (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_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 avg(pagg_tab_2.b))
+ Relations: Aggregate on (public.fpagg_tab_p3 pagg_tab_2)
+ Remote SQL: SELECT bit_and(c_bit) PARTIAL_AGGREGATE , bit_and(c_1or3int2) PARTIAL_AGGREGATE , bit_and(c_1or3int4) PARTIAL_AGGREGATE , bit_and(c_1or3int8) PARTIAL_AGGREGATE , bit_or(c_bit) PARTIAL_AGGREGATE , bit_or(c_1or3int2) PARTIAL_AGGREGATE , bit_or(c_1or3int4) PARTIAL_AGGREGATE , bit_or(c_1or3int8) PARTIAL_AGGREGATE , bit_xor(c_bit) PARTIAL_AGGREGATE , bit_xor(c_1or3int2) PARTIAL_AGGREGATE , bit_xor(c_1or3int4) PARTIAL_AGGREGATE , bit_xor(c_1or3int8) PARTIAL_AGGREGATE , bool_and(c_bool) PARTIAL_AGGREGATE , bool_or(c_bool) PARTIAL_AGGREGATE , every(c_bool) PARTIAL_AGGREGATE , max(c_1c::character(1)) PARTIAL_AGGREGATE , max(('01-01-2000'::date + b)) PARTIAL_AGGREGATE , max(('0.0.0.0'::inet + b)) PARTIAL_AGGREGATE , max(b::real) PARTIAL_AGGREGATE , max(b::double precision) PARTIAL_AGGREGATE , max(b::smallint) PARTIAL_AGGREGATE , max(b) PARTIAL_AGGREGATE , max(b::bigint) PARTIAL_AGGREGATE , max(c_interval) PARTIAL_AGGREGATE , max(c_money) PARTIAL_AGGREGATE , max(b::numeric) PARTIAL_AGGREGATE , max(b::oid) PARTIAL_AGGREGATE , max(c_pg_lsn) PARTIAL_AGGREGATE , max(c_tid) PARTIAL_AGGREGATE , max(c_1c) PARTIAL_AGGREGATE , max(c_time) PARTIAL_AGGREGATE , max(c_timetz) PARTIAL_AGGREGATE , max(c_timestamp) PARTIAL_AGGREGATE , max(c_timestamptz) PARTIAL_AGGREGATE , max(c_xid8) PARTIAL_AGGREGATE , min(c_1c::character(1)) PARTIAL_AGGREGATE , min(('01-01-2000'::date + b)) PARTIAL_AGGREGATE , min(('0.0.0.0'::inet + b)) PARTIAL_AGGREGATE , min(b::real) PARTIAL_AGGREGATE , min(b::double precision) PARTIAL_AGGREGATE , min(b::smallint) PARTIAL_AGGREGATE , min(b) PARTIAL_AGGREGATE , min(b::bigint) PARTIAL_AGGREGATE , min(c_interval) PARTIAL_AGGREGATE , min(c_money) PARTIAL_AGGREGATE , min(b::numeric) PARTIAL_AGGREGATE , min(b::oid) PARTIAL_AGGREGATE , min(c_pg_lsn) PARTIAL_AGGREGATE , min(c_tid) PARTIAL_AGGREGATE , min(c_1c) PARTIAL_AGGREGATE , min(c_time) PARTIAL_AGGREGATE , min(c_timetz) PARTIAL_AGGREGATE , min(c_timestamp) PARTIAL_AGGREGATE , min(c_timestamptz) PARTIAL_AGGREGATE , min(c_xid8) PARTIAL_AGGREGATE , avg(b) PARTIAL_AGGREGATE FROM public.pagg_tab_p3 WHERE ((c_serial >= 1)) AND ((c_serial <= 30))
+(15 rows)
+
+SELECT
+ /* The cases that don't require import or export functions */
+ 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),
+ every(c_bool),
+ 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_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),
+ /* The cases that require only import functions */
+ avg(b::int4)
+ FROM pagg_tab WHERE c_serial between 1 and 30;
+ 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 | every | 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 | avg
+---------+---------+---------+---------+--------+--------+--------+--------+---------+---------+---------+---------+----------+---------+-------+-----+------------+----------+-----+-----+-----+-----+-----+---------+-------+-----+-----+------+--------+-----+----------+-------------+--------------------------+------------------------------+-----+-----+------------+---------+-----+-----+-----+-----+-----+-----+-------+-----+-----+-----+-------+-----+----------+-------------+--------------------------+------------------------------+-----+---------------------
+ 01 | 1 | 1 | 1 | 11 | 3 | 3 | 3 | 10 | 2 | 2 | 2 | f | t | f | 9 | 01-31-2000 | 0.0.0.30 | 30 | 30 | 30 | 30 | 30 | @ 1 sec | $1.00 | 30 | 30 | 0/30 | (0,30) | 9 | 00:00:30 | 00:00:30+00 | Sat Jan 01 00:00:30 2000 | Sat Jan 01 00:00:30 2000 UTC | 9 | 0 | 01-02-2000 | 0.0.0.1 | 1 | 1 | 1 | 1 | 1 | @ 0 | $0.00 | 1 | 1 | 0/1 | (0,1) | 0 | 00:00:01 | 00:00:01+00 | Sat Jan 01 00:00:01 2000 | Sat Jan 01 00:00:01 2000 UTC | 0 | 15.5000000000000000
+(1 row)
+
+-- Tests for partial_aggregate_support
+ALTER SERVER loopback OPTIONS (ADD partial_aggregate_support 'false');
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT b, max(a) FROM pagg_tab GROUP BY b ORDER BY 1;
+ QUERY PLAN
+---------------------------------------------------------------------------
+ Finalize GroupAggregate
+ Output: pagg_tab.b, max(pagg_tab.a)
+ Group Key: pagg_tab.b
+ -> Sort
+ Output: pagg_tab.b, (PARTIAL max(pagg_tab.a))
+ Sort Key: pagg_tab.b
+ -> Append
+ -> Partial HashAggregate
+ Output: pagg_tab.b, PARTIAL max(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 max(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 max(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
+(25 rows)
+
+SELECT b, max(a) FROM pagg_tab GROUP BY b ORDER BY 1;
+ b | max
+----+-----
+ 0 | 20
+ 1 | 21
+ 2 | 22
+ 3 | 23
+ 4 | 24
+ 5 | 25
+ 6 | 26
+ 7 | 27
+ 8 | 28
+ 9 | 29
+ 10 | 20
+ 11 | 21
+ 12 | 22
+ 13 | 23
+ 14 | 24
+ 15 | 25
+ 16 | 26
+ 17 | 27
+ 18 | 28
+ 19 | 29
+ 20 | 20
+ 21 | 21
+ 22 | 22
+ 23 | 23
+ 24 | 24
+ 25 | 25
+ 26 | 26
+ 27 | 27
+ 28 | 28
+ 29 | 29
+ 30 | 20
+ 31 | 21
+ 32 | 22
+ 33 | 23
+ 34 | 24
+ 35 | 25
+ 36 | 26
+ 37 | 27
+ 38 | 28
+ 39 | 29
+ 40 | 20
+ 41 | 21
+ 42 | 22
+ 43 | 23
+ 44 | 24
+ 45 | 25
+ 46 | 26
+ 47 | 27
+ 48 | 28
+ 49 | 29
+(50 rows)
+
+ALTER SERVER loopback OPTIONS (SET partial_aggregate_support 'true');
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT b, max(a) FROM pagg_tab GROUP BY b ORDER BY 1;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------
+ Sort
+ Output: pagg_tab.b, (max(pagg_tab.a))
+ Sort Key: pagg_tab.b
+ -> Finalize HashAggregate
+ Output: pagg_tab.b, max(pagg_tab.a)
+ Group Key: pagg_tab.b
+ -> Append
+ -> Foreign Scan
+ Output: pagg_tab.b, (PARTIAL max(pagg_tab.a))
+ Relations: Aggregate on (public.fpagg_tab_p1 pagg_tab)
+ Remote SQL: SELECT b, max(a) PARTIAL_AGGREGATE FROM public.pagg_tab_p1 GROUP BY 1
+ -> Foreign Scan
+ Output: pagg_tab_1.b, (PARTIAL max(pagg_tab_1.a))
+ Relations: Aggregate on (public.fpagg_tab_p2 pagg_tab_1)
+ Remote SQL: SELECT b, max(a) PARTIAL_AGGREGATE FROM public.pagg_tab_p2 GROUP BY 1
+ -> Foreign Scan
+ Output: pagg_tab_2.b, (PARTIAL max(pagg_tab_2.a))
+ Relations: Aggregate on (public.fpagg_tab_p3 pagg_tab_2)
+ Remote SQL: SELECT b, max(a) PARTIAL_AGGREGATE FROM public.pagg_tab_p3 GROUP BY 1
+(19 rows)
+
+SELECT b, max(a) FROM pagg_tab GROUP BY b ORDER BY 1;
+ b | max
+----+-----
+ 0 | 20
+ 1 | 21
+ 2 | 22
+ 3 | 23
+ 4 | 24
+ 5 | 25
+ 6 | 26
+ 7 | 27
+ 8 | 28
+ 9 | 29
+ 10 | 20
+ 11 | 21
+ 12 | 22
+ 13 | 23
+ 14 | 24
+ 15 | 25
+ 16 | 26
+ 17 | 27
+ 18 | 28
+ 19 | 29
+ 20 | 20
+ 21 | 21
+ 22 | 22
+ 23 | 23
+ 24 | 24
+ 25 | 25
+ 26 | 26
+ 27 | 27
+ 28 | 28
+ 29 | 29
+ 30 | 20
+ 31 | 21
+ 32 | 22
+ 33 | 23
+ 34 | 24
+ 35 | 25
+ 36 | 26
+ 37 | 27
+ 38 | 28
+ 39 | 29
+ 40 | 20
+ 41 | 21
+ 42 | 22
+ 43 | 23
+ 44 | 24
+ 45 | 25
+ 46 | 26
+ 47 | 27
+ 48 | 28
+ 49 | 29
+(50 rows)
+
+ALTER SERVER loopback OPTIONS (SET fdw_tuple_cost '1.0');
+SET enable_partitionwise_join=on;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT max(t1.b) FROM pagg_tab t1 JOIN pagg_tab t2 USING(a);
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------
+ Finalize Aggregate
+ Output: max(t1.b)
+ -> Append
+ -> Foreign Scan
+ Output: (PARTIAL max(t1.b))
+ Relations: Aggregate on ((public.fpagg_tab_p1 t1) INNER JOIN (public.fpagg_tab_p1 t2))
+ Remote SQL: SELECT max(r4.b) PARTIAL_AGGREGATE FROM (public.pagg_tab_p1 r4 INNER JOIN public.pagg_tab_p1 r7 ON (((r4.a = r7.a))))
+ -> Foreign Scan
+ Output: (PARTIAL max(t1_1.b))
+ Relations: Aggregate on ((public.fpagg_tab_p2 t1_1) INNER JOIN (public.fpagg_tab_p2 t2_1))
+ Remote SQL: SELECT max(r5.b) PARTIAL_AGGREGATE FROM (public.pagg_tab_p2 r5 INNER JOIN public.pagg_tab_p2 r8 ON (((r5.a = r8.a))))
+ -> Foreign Scan
+ Output: (PARTIAL max(t1_2.b))
+ Relations: Aggregate on ((public.fpagg_tab_p3 t1_2) INNER JOIN (public.fpagg_tab_p3 t2_2))
+ Remote SQL: SELECT max(r6.b) PARTIAL_AGGREGATE FROM (public.pagg_tab_p3 r6 INNER JOIN public.pagg_tab_p3 r9 ON (((r6.a = r9.a))))
+(15 rows)
+
+SELECT max(t1.b) FROM pagg_tab t1 JOIN pagg_tab t2 USING(a);
+ max
+-----
+ 49
+(1 row)
+
+RESET enable_partitionwise_join;
+ALTER SERVER loopback OPTIONS (SET fdw_tuple_cost '0.1');
+ALTER SERVER loopback OPTIONS (DROP 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);
+RESET TimeZone;
-- ===================================================================
-- access rights and superuser
-- ===================================================================
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
index 630b304338..8a6a404d2f 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, "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},
+ {"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 0bb9a5ae8f..80d34a4950 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -18,6 +18,8 @@
#include "access/sysattr.h"
#include "access/table.h"
#include "catalog/pg_class.h"
+#include "catalog/pg_aggregate.h"
+#include "catalog/pg_proc.h"
#include "catalog/pg_opfamily.h"
#include "commands/defrem.h"
#include "commands/explain.h"
@@ -36,6 +38,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"
@@ -50,6 +53,7 @@
#include "utils/rel.h"
#include "utils/sampling.h"
#include "utils/selfuncs.h"
+#include "utils/syscache.h"
PG_MODULE_MAGIC;
@@ -83,6 +87,11 @@ enum FdwScanPrivateIndex
* of join, added when the scan is join
*/
FdwScanPrivateRelations,
+
+ /*
+ * List of functions to import partial aggregate result
+ */
+ FdwScanPrivateImportfns
};
/*
@@ -145,6 +154,7 @@ typedef struct PgFdwScanState
/* extracted fdw_private data */
char *query; /* text of SELECT command */
List *retrieved_attrs; /* list of retrieved attribute numbers */
+ List *importfn_list; /* list of converters */
/* for remote query execution */
PGconn *conn; /* connection for the scan */
@@ -476,6 +486,7 @@ static void store_returning_result(PgFdwModifyState *fmstate,
TupleTableSlot *slot, PGresult *res);
static void finish_foreign_modify(PgFdwModifyState *fmstate);
static void deallocate_query(PgFdwModifyState *fmstate);
+static List *build_importfn_list(RelOptInfo *foreignrel);
static List *build_remote_returning(Index rtindex, Relation rel,
List *returningList);
static void rebuild_fdw_scan_tlist(ForeignScan *fscan, List *tlist);
@@ -512,6 +523,7 @@ static HeapTuple make_tuple_from_result_row(PGresult *res,
Relation rel,
AttInMetadata *attinmeta,
List *retrieved_attrs,
+ List *importfn_list,
ForeignScanState *fsstate,
MemoryContext temp_context);
static void conversion_error_callback(void *arg);
@@ -519,7 +531,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 +662,8 @@ postgresGetForeignRelSize(PlannerInfo *root,
fpinfo->fdw_tuple_cost = DEFAULT_FDW_TUPLE_COST;
fpinfo->shippable_extensions = NIL;
fpinfo->fetch_size = 100;
+ fpinfo->remoteversion = 0;
+ fpinfo->partial_aggregate_support = true;
fpinfo->async_capable = false;
apply_server_options(fpinfo);
@@ -661,7 +675,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->partial_aggregate_support)
{
Oid userid;
@@ -1234,6 +1248,7 @@ postgresGetForeignPlan(PlannerInfo *root,
List *local_exprs = NIL;
List *params_list = NIL;
List *fdw_scan_tlist = NIL;
+ List *fdw_importfn_list = NIL;
List *fdw_recheck_quals = NIL;
List *retrieved_attrs;
StringInfoData sql;
@@ -1337,6 +1352,9 @@ postgresGetForeignPlan(PlannerInfo *root,
/* Build the list of columns to be fetched from the foreign server. */
fdw_scan_tlist = build_tlist_to_deparse(foreignrel);
+ /* Build the list of importfns for partial aggregates. */
+ fdw_importfn_list = build_importfn_list(foreignrel);
+
/*
* Ensure that the outer plan produces a tuple whose descriptor
* matches our scan tuple slot. Also, remove the local conditions
@@ -1414,6 +1432,8 @@ postgresGetForeignPlan(PlannerInfo *root,
if (IS_JOIN_REL(foreignrel) || IS_UPPER_REL(foreignrel))
fdw_private = lappend(fdw_private,
makeString(fpinfo->relation_name));
+ if (IS_UPPER_REL(foreignrel))
+ fdw_private = lappend(fdw_private, fdw_importfn_list);
/*
* Create the ForeignScan node for the given relation.
@@ -1432,6 +1452,49 @@ postgresGetForeignPlan(PlannerInfo *root,
outer_plan);
}
+/*
+ * Generate attinmeta if there are some converters:
+ * corresponding attribute type should be converter
+ * input type, not expected result type (BYTEA).
+ */
+static AttInMetadata *
+get_rcvd_attinmeta(TupleDesc tupdesc, List *importfn_list)
+{
+ TupleDesc rcvd_tupdesc;
+
+ Assert(importfn_list != NIL);
+
+ rcvd_tupdesc = CreateTupleDescCopy(tupdesc);
+ for (int i = 0; i < rcvd_tupdesc->natts; i++)
+ {
+ Oid importfn = InvalidOid;
+ Form_pg_attribute att = TupleDescAttr(rcvd_tupdesc, i);
+
+ importfn = list_nth_oid(importfn_list, i);
+ if (importfn != InvalidOid)
+ {
+ HeapTuple proctup;
+ Form_pg_proc procform;
+
+ proctup = SearchSysCache1(PROCOID, ObjectIdGetDatum(importfn));
+
+ if (!HeapTupleIsValid(proctup))
+ elog(ERROR, "cache lookup failed for function %u", importfn);
+
+ procform = (Form_pg_proc) GETSTRUCT(proctup);
+
+ if (procform->pronargs != 1)
+ elog(ERROR, "converter %s is expected to have one argument", NameStr(procform->proname));
+
+ att->atttypid = procform->proargtypes.values[0];
+
+ ReleaseSysCache(proctup);
+ }
+ }
+
+ return TupleDescGetAttInMetadata(rcvd_tupdesc);
+}
+
/*
* Construct a tuple descriptor for the scan tuples handled by a foreign join.
*/
@@ -1542,6 +1605,9 @@ postgresBeginForeignScan(ForeignScanState *node, int eflags)
FdwScanPrivateSelectSql));
fsstate->retrieved_attrs = (List *) list_nth(fsplan->fdw_private,
FdwScanPrivateRetrievedAttrs);
+ if (list_length(fsplan->fdw_private) > FdwScanPrivateImportfns)
+ fsstate->importfn_list = (List *) list_nth(fsplan->fdw_private,
+ FdwScanPrivateImportfns);
fsstate->fetch_size = intVal(list_nth(fsplan->fdw_private,
FdwScanPrivateFetchSize));
@@ -1568,7 +1634,10 @@ postgresBeginForeignScan(ForeignScanState *node, int eflags)
fsstate->tupdesc = get_tupdesc_for_join_scan_tuples(node);
}
- fsstate->attinmeta = TupleDescGetAttInMetadata(fsstate->tupdesc);
+ if (fsstate->importfn_list != NIL)
+ fsstate->attinmeta = get_rcvd_attinmeta(fsstate->tupdesc, fsstate->importfn_list);
+ else
+ fsstate->attinmeta = TupleDescGetAttInMetadata(fsstate->tupdesc);
/*
* Prepare for processing of parameters used in remote query, if any.
@@ -3847,6 +3916,7 @@ fetch_more_data(ForeignScanState *node)
fsstate->rel,
fsstate->attinmeta,
fsstate->retrieved_attrs,
+ fsstate->importfn_list,
node,
fsstate->temp_cxt);
}
@@ -4338,6 +4408,7 @@ store_returning_result(PgFdwModifyState *fmstate,
fmstate->rel,
fmstate->attinmeta,
fmstate->retrieved_attrs,
+ NIL,
NULL,
fmstate->temp_cxt);
@@ -4631,6 +4702,7 @@ get_returning_data(ForeignScanState *node)
dmstate->rel,
dmstate->attinmeta,
dmstate->retrieved_attrs,
+ NIL,
node,
dmstate->temp_cxt);
ExecStoreHeapTuple(newtup, slot, false);
@@ -5421,6 +5493,7 @@ analyze_row_processor(PGresult *res, int row, PgFdwAnalyzeState *astate)
astate->rel,
astate->attinmeta,
astate->retrieved_attrs,
+ NIL,
NULL,
astate->temp_cxt);
@@ -6031,9 +6104,9 @@ 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->partial_aggregate_support)
{
- if (fpinfo_o->use_remote_estimate)
+ if (fpinfo_o->use_remote_estimate || fpinfo_o->partial_aggregate_support)
fpinfo->user = fpinfo_o->user;
else
fpinfo->user = fpinfo_i->user;
@@ -6204,6 +6277,8 @@ apply_server_options(PgFdwRelationInfo *fpinfo)
if (strcmp(def->defname, "use_remote_estimate") == 0)
fpinfo->use_remote_estimate = defGetBoolean(def);
+ else if (strcmp(def->defname, "partial_aggregate_support") == 0)
+ fpinfo->partial_aggregate_support = defGetBoolean(def);
else if (strcmp(def->defname, "fdw_startup_cost") == 0)
(void) parse_real(defGetString(def), &fpinfo->fdw_startup_cost, 0,
NULL);
@@ -6274,6 +6349,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->remoteversion = fpinfo_o->remoteversion;
+ fpinfo->partial_aggregate_support = fpinfo_o->partial_aggregate_support;
fpinfo->async_capable = fpinfo_o->async_capable;
/* Merge the table level options from either side of the join. */
@@ -6456,7 +6533,7 @@ postgresGetForeignJoinPaths(PlannerInfo *root,
*/
static bool
foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel,
- Node *havingQual)
+ GroupPathExtraData *extra)
{
Query *query = root->parse;
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) grouped_rel->fdw_private;
@@ -6465,6 +6542,7 @@ foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel,
ListCell *lc;
int i;
List *tlist = NIL;
+ bool partial = extra->patype == PARTITIONWISE_AGGREGATE_PARTIAL;
/* We currently don't support pushing Grouping Sets. */
if (query->groupingSets)
@@ -6498,6 +6576,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 +6593,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 +6679,9 @@ foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel,
* Classify the pushable and non-pushable HAVING clauses and save them in
* remote_conds and local_conds of the grouped rel's fpinfo.
*/
- if (havingQual)
+ if (extra->havingQual && !partial)
{
- foreach(lc, (List *) havingQual)
+ foreach(lc, (List *) extra->havingQual)
{
Expr *expr = (Expr *) lfirst(lc);
RestrictInfo *rinfo;
@@ -6711,6 +6795,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 +6812,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 +6856,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 +6877,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;
/*
@@ -7318,6 +7408,30 @@ postgresForeignAsyncConfigureWait(AsyncRequest *areq)
NULL, areq);
}
+/*
+ * Interface to fmgr to call importfn
+ */
+static Datum
+import_statevalue(Oid importfn, Oid collation, Datum value, bool isnull, bool *res_isnull)
+{
+ LOCAL_FCINFO(fcinfo, 1);
+ FmgrInfo flinfo;
+ Datum result;
+
+ fmgr_info(importfn, &flinfo);
+
+ InitFunctionCallInfoData(*fcinfo, &flinfo, 1, collation, NULL, NULL);
+
+ fcinfo->args[0].value = value;
+ fcinfo->args[0].isnull = isnull;
+
+ result = FunctionCallInvoke(fcinfo);
+
+ if (res_isnull)
+ *res_isnull = fcinfo->isnull;
+ return result;
+}
+
/*
* postgresForeignAsyncNotify
* Fetch some more tuples from a file descriptor that becomes ready,
@@ -7532,6 +7646,7 @@ make_tuple_from_result_row(PGresult *res,
Relation rel,
AttInMetadata *attinmeta,
List *retrieved_attrs,
+ List *importfn_list,
ForeignScanState *fsstate,
MemoryContext temp_context)
{
@@ -7614,6 +7729,20 @@ make_tuple_from_result_row(PGresult *res,
valstr,
attinmeta->attioparams[i - 1],
attinmeta->atttypmods[i - 1]);
+
+ if (importfn_list != NIL)
+ {
+ Oid importfn = list_nth_oid(importfn_list, i - 1);
+
+ if (importfn != InvalidOid)
+ {
+ Form_pg_attribute att = TupleDescAttr(tupdesc, i);
+ bool res_isnull;
+
+ values[i - 1] = import_statevalue(importfn, att->attcollation, values[i - 1], nulls[i - 1], &res_isnull);
+ nulls[i - 1] = res_isnull;
+ }
+ }
}
else if (i == SelfItemPointerAttributeNumber)
{
@@ -7924,3 +8053,54 @@ get_batch_size_option(Relation rel)
return batch_size;
}
+
+/*
+ * For UPPER_REL build a list of importfns, corresponding to tlist entries.
+ */
+static List *
+build_importfn_list(RelOptInfo *foreignrel)
+{
+ List *importfn_list = NIL;
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
+ ListCell *lc;
+
+ if (!IS_UPPER_REL(foreignrel))
+ return NIL;
+
+ /* For UPPER_REL tlist matches grouped_tlist */
+ foreach(lc, fpinfo->grouped_tlist)
+ {
+ TargetEntry *tlentry = (TargetEntry *) lfirst(lc);
+ Oid importfn_oid = InvalidOid;
+
+ if (IsA(tlentry->expr, Aggref))
+ {
+ Aggref *agg = (Aggref *) tlentry->expr;
+
+ if (agg->aggsplit == AGGSPLIT_INITIAL_SERIAL && agg->aggtranstype == INTERNALOID)
+ {
+ HeapTuple aggtup;
+ Form_pg_aggregate aggform;
+
+ aggtup = SearchSysCache1(AGGFNOID, ObjectIdGetDatum(agg->aggfnoid));
+ if (!HeapTupleIsValid(aggtup))
+ elog(ERROR, "cache lookup failed for function %u", agg->aggfnoid);
+
+ aggform = (Form_pg_aggregate) GETSTRUCT(aggtup);
+
+ importfn_oid = aggform->aggpartialimportfn;
+ Assert(importfn_oid != InvalidOid);
+
+ ReleaseSysCache(aggtup);
+ }
+ }
+
+ /*
+ * We append InvalidOid to importfn_list to preserve one-to-one mapping
+ * between tlist and importfn_list members.
+ */
+ importfn_list = lappend_oid(importfn_list, importfn_oid);
+ }
+
+ return importfn_list;
+}
\ No newline at end of file
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index 37c1575af6..defea714f9 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -84,7 +84,16 @@ typedef struct PgFdwRelationInfo
/* Cached catalog information. */
ForeignTable *table;
ForeignServer *server;
- UserMapping *user; /* only set in use_remote_estimate mode */
+ UserMapping *user; /* only set in use_remote_estimate/partial_aggregate_support mode */
+
+ /* for partial aggregate pushdown */
+ bool 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 b57f8cfda6..78fa8b7b57 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3150,16 +3150,33 @@ RESET enable_partitionwise_join;
-- ===================================================================
-- test partitionwise aggregates
-- ===================================================================
-
-CREATE TABLE pagg_tab (a int, b int, c text) PARTITION BY RANGE(a);
+ALTER SERVER loopback OPTIONS (ADD fdw_tuple_cost '0.1');
+
+CREATE TYPE mood AS enum ('sad', 'ok', 'happy');
+ALTER EXTENSION postgres_fdw ADD TYPE mood;
+
+CREATE TABLE pagg_tab (a int, b int, c text, c_serial int4,
+ c_int4array _int4, c_interval interval,
+ c_money money, c_1c text, c_1b bytea,
+ c_bit bit(2), c_1or3int2 int2,
+ c_1or3int4 int4, c_1or3int8 int8,
+ c_bool bool, c_enum mood, c_pg_lsn pg_lsn,
+ c_tid tid, c_int4range int4range,
+ c_int4multirange int4multirange,
+ c_time time, c_timetz timetz,
+ c_timestamp timestamp, c_timestamptz timestamptz,
+ c_xid8 xid8)
+ PARTITION BY RANGE(a);
CREATE TABLE pagg_tab_p1 (LIKE pagg_tab);
CREATE TABLE pagg_tab_p2 (LIKE pagg_tab);
CREATE TABLE pagg_tab_p3 (LIKE pagg_tab);
-INSERT INTO pagg_tab_p1 SELECT i % 30, i % 50, to_char(i/30, 'FM0000') FROM generate_series(1, 3000) i WHERE (i % 30) < 10;
-INSERT INTO pagg_tab_p2 SELECT i % 30, i % 50, to_char(i/30, 'FM0000') FROM generate_series(1, 3000) i WHERE (i % 30) < 20 and (i % 30) >= 10;
-INSERT INTO pagg_tab_p3 SELECT i % 30, i % 50, to_char(i/30, 'FM0000') FROM generate_series(1, 3000) i WHERE (i % 30) < 30 and (i % 30) >= 20;
+SET TimeZone = 'UTC';
+
+INSERT INTO pagg_tab_p1 SELECT i % 30, i % 50, to_char(i/30, 'FM0000'), i, array[(i % 2), 0], ((i % 2) || ' seconds')::interval, (i % 2)::money, ((i - 1) % 10)::text, (((i - 1) % 10)::text)::bytea, case when (i % 2) = 0 then B'01' else B'11' end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then true else false end, (case when (i % 2) = 0 then 'sad' else 'happy' end)::mood, ('0/' || i)::pg_lsn, ('(0,' || i || ')')::tid, int4range(0, i), int4multirange(int4range(0, i), int4range(100, 100 + i)), '00:00:00'::time + (i || ' seconds')::interval, '00:00:00'::timetz + (i || ' seconds')::interval, '2000-01-01'::timestamp + (i || ' seconds')::interval, '2000-01-01'::timestamptz + (i || ' seconds')::interval, ((i % 10)::text)::xid8 FROM generate_series(1, 3000) i WHERE (i % 30) < 10;
+INSERT INTO pagg_tab_p2 SELECT i % 30, i % 50, to_char(i/30, 'FM0000'), i, array[(i % 2), 0], ((i % 2) || ' seconds')::interval, (i % 2)::money, ((i - 1) % 10)::text, (((i - 1) % 10)::text)::bytea, case when (i % 2) = 0 then B'01' else B'11' end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then true else false end, (case when (i % 2) = 0 then 'sad' else 'happy' end)::mood, ('0/' || i)::pg_lsn, ('(0,' || i || ')')::tid, int4range(0, i), int4multirange(int4range(0, i), int4range(100, 100 + i)), '00:00:00'::time + (i || ' seconds')::interval, '00:00:00'::timetz + (i || ' seconds')::interval, '2000-01-01'::timestamp + (i || ' seconds')::interval, '2000-01-01'::timestamptz + (i || ' seconds')::interval, ((i % 10)::text)::xid8 FROM generate_series(1, 3000) i WHERE (i % 30) < 20 and (i % 30) >= 10;
+INSERT INTO pagg_tab_p3 SELECT i % 30, i % 50, to_char(i/30, 'FM0000'), i, array[(i % 2), 0], ((i % 2) || ' seconds')::interval, (i % 2)::money, ((i - 1) % 10)::text, (((i - 1) % 10)::text)::bytea, case when (i % 2) = 0 then B'01' else B'11' end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then 1 else 3 end, case when (i % 2) = 0 then true else false end, (case when (i % 2) = 0 then 'sad' else 'happy' end)::mood, ('0/' || i)::pg_lsn, ('(0,' || i || ')')::tid, int4range(0, i), int4multirange(int4range(0, i), int4range(100, 100 + i)), '00:00:00'::time + (i || ' seconds')::interval, '00:00:00'::timetz + (i || ' seconds')::interval, '2000-01-01'::timestamp + (i || ' seconds')::interval, '2000-01-01'::timestamptz + (i || ' seconds')::interval, ((i % 10)::text)::xid8 FROM generate_series(1, 3000) i WHERE (i % 30) < 30 and (i % 30) >= 20;
-- Create foreign partitions
CREATE FOREIGN TABLE fpagg_tab_p1 PARTITION OF pagg_tab FOR VALUES FROM (0) TO (10) SERVER loopback OPTIONS (table_name 'pagg_tab_p1');
@@ -3167,9 +3184,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
@@ -3189,9 +3210,88 @@ EXPLAIN (VERBOSE, COSTS OFF)
SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
SELECT a, count(t1) FROM pagg_tab t1 GROUP BY a HAVING avg(b) < 22 ORDER BY 1;
--- When GROUP BY clause does not match with PARTITION KEY.
-EXPLAIN (COSTS OFF)
+-- Partial aggregates are safe to push down when there is a HAVING clause
+EXPLAIN (VERBOSE, COSTS OFF)
SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700 ORDER BY 1;
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700 ORDER BY 1;
+
+-- Partial aggregates are safe to push down without having clause
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b ORDER BY 1;
+SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b ORDER BY 1;
+
+-- Partial aggregates are safe to push down even if we need both variable and variable-based expression
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT avg(a), max(a), count(*), (b/2)::numeric FROM pagg_tab GROUP BY b/2 ORDER BY 4;
+SELECT avg(a), max(a), count(*), (b/2)::numeric FROM pagg_tab GROUP BY b/2 ORDER BY 4;
+
+-- Partial aggregates pushdown behaves sane with non-shipped grouping elements
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT avg(a), (b/2)::numeric, 'test' test FROM pagg_tab GROUP BY 3, b/2 ORDER BY 1, 2 LIMIT 10;
+SELECT avg(a), (b/2)::numeric, 'test' test FROM pagg_tab GROUP BY 3, b/2 ORDER BY 1, 2 LIMIT 10;
+
+-- Partial aggregates are safe to push down for all built-in aggregates
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT
+ /* The cases that don't require import or export functions */
+ 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),
+ every(c_bool),
+ 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_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),
+ /* The cases that require only import functions */
+ avg(b::int4)
+ FROM pagg_tab WHERE c_serial between 1 and 30;
+
+SELECT
+ /* The cases that don't require import or export functions */
+ 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),
+ every(c_bool),
+ 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_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),
+ /* The cases that require only import functions */
+ avg(b::int4)
+ FROM pagg_tab WHERE c_serial between 1 and 30;
+
+-- Tests for partial_aggregate_support
+ALTER SERVER loopback OPTIONS (ADD partial_aggregate_support 'false');
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT b, max(a) FROM pagg_tab GROUP BY b ORDER BY 1;
+SELECT b, max(a) FROM pagg_tab GROUP BY b ORDER BY 1;
+
+ALTER SERVER loopback OPTIONS (SET partial_aggregate_support 'true');
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT b, max(a) FROM pagg_tab GROUP BY b ORDER BY 1;
+SELECT b, max(a) 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 max(t1.b) FROM pagg_tab t1 JOIN pagg_tab t2 USING(a);
+SELECT max(t1.b) 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 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);
+RESET TimeZone;
-- ===================================================================
-- access rights and superuser
diff --git a/src/backend/catalog/pg_aggregate.c b/src/backend/catalog/pg_aggregate.c
index 90fc7db949..9040e400d7 100644
--- a/src/backend/catalog/pg_aggregate.c
+++ b/src/backend/catalog/pg_aggregate.c
@@ -60,6 +60,8 @@ AggregateCreate(const char *aggName,
List *aggcombinefnName,
List *aggserialfnName,
List *aggdeserialfnName,
+ bool aggpartialpushdownsafe,
+ List *aggpartialimportfnName,
List *aggmtransfnName,
List *aggminvtransfnName,
List *aggmfinalfnName,
@@ -88,6 +90,7 @@ AggregateCreate(const char *aggName,
Oid combinefn = InvalidOid; /* can be omitted */
Oid serialfn = InvalidOid; /* can be omitted */
Oid deserialfn = InvalidOid; /* can be omitted */
+ Oid importfn = InvalidOid; /* can be omitted */
Oid mtransfn = InvalidOid; /* can be omitted */
Oid minvtransfn = InvalidOid; /* can be omitted */
Oid mfinalfn = InvalidOid; /* can be omitted */
@@ -481,6 +484,28 @@ AggregateCreate(const char *aggName,
format_type_be(INTERNALOID))));
}
+ /*
+ * Validate the import function, if present.
+ */
+ if (aggpartialimportfnName)
+ {
+ if (!aggpartialpushdownsafe)
+ elog(ERROR, "aggpartialpushdownsafe must be true when aggpartialimportfunc is specified");
+
+ /* signature is always deserialize(bytea, internal) returns internal */
+ fnArgs[0] = aggTransType;
+
+ importfn = lookup_agg_function(aggpartialimportfnName, 1,
+ fnArgs, InvalidOid,
+ &rettype);
+
+ if (rettype != aggTransType)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("return type of import function %s is not stype",
+ NameListToString(aggpartialimportfnName))));
+ }
+
/*
* If finaltype (i.e. aggregate return type) is polymorphic, inputs must
* be polymorphic also, else parser will fail to deduce result type.
@@ -664,6 +689,8 @@ AggregateCreate(const char *aggName,
values[Anum_pg_aggregate_aggcombinefn - 1] = ObjectIdGetDatum(combinefn);
values[Anum_pg_aggregate_aggserialfn - 1] = ObjectIdGetDatum(serialfn);
values[Anum_pg_aggregate_aggdeserialfn - 1] = ObjectIdGetDatum(deserialfn);
+ values[Anum_pg_aggregate_aggpartialpushdownsafe - 1] = BoolGetDatum(aggpartialpushdownsafe);
+ values[Anum_pg_aggregate_aggpartialimportfn - 1] = ObjectIdGetDatum(importfn);
values[Anum_pg_aggregate_aggmtransfn - 1] = ObjectIdGetDatum(mtransfn);
values[Anum_pg_aggregate_aggminvtransfn - 1] = ObjectIdGetDatum(minvtransfn);
values[Anum_pg_aggregate_aggmfinalfn - 1] = ObjectIdGetDatum(mfinalfn);
@@ -777,6 +804,13 @@ AggregateCreate(const char *aggName,
add_exact_object_address(&referenced, addrs);
}
+ /* Depends on import function, if any */
+ if (OidIsValid(importfn))
+ {
+ ObjectAddressSet(referenced, ProcedureRelationId, importfn);
+ add_exact_object_address(&referenced, addrs);
+ }
+
/* Depends on forward transition function, if any */
if (OidIsValid(mtransfn))
{
diff --git a/src/backend/commands/aggregatecmds.c b/src/backend/commands/aggregatecmds.c
index fde5a2a0e0..4e8144a05a 100644
--- a/src/backend/commands/aggregatecmds.c
+++ b/src/backend/commands/aggregatecmds.c
@@ -66,6 +66,8 @@ DefineAggregate(ParseState *pstate,
List *combinefuncName = NIL;
List *serialfuncName = NIL;
List *deserialfuncName = NIL;
+ bool aggpartialpushdownsafe = false;
+ List *importfuncName = NIL;
List *mtransfuncName = NIL;
List *minvtransfuncName = NIL;
List *mfinalfuncName = NIL;
@@ -139,6 +141,10 @@ DefineAggregate(ParseState *pstate,
serialfuncName = defGetQualifiedName(defel);
else if (strcmp(defel->defname, "deserialfunc") == 0)
deserialfuncName = defGetQualifiedName(defel);
+ else if (strcmp(defel->defname, "aggpartialpushdownsafe") == 0)
+ aggpartialpushdownsafe = defGetBoolean(defel);
+ else if (strcmp(defel->defname, "importfunc") == 0)
+ importfuncName = defGetQualifiedName(defel);
else if (strcmp(defel->defname, "msfunc") == 0)
mtransfuncName = defGetQualifiedName(defel);
else if (strcmp(defel->defname, "minvfunc") == 0)
@@ -454,6 +460,8 @@ DefineAggregate(ParseState *pstate,
combinefuncName, /* combine function name */
serialfuncName, /* serial function name */
deserialfuncName, /* deserial function name */
+ aggpartialpushdownsafe, /* partiall aggregate pushdown safe? */
+ importfuncName, /* import function name */
mtransfuncName, /* fwd trans function name */
minvtransfuncName, /* inv trans function name */
mfinalfuncName, /* final function name */
diff --git a/src/backend/executor/nodeAgg.c b/src/backend/executor/nodeAgg.c
index 53ead77ece..6023169ef4 100644
--- a/src/backend/executor/nodeAgg.c
+++ b/src/backend/executor/nodeAgg.c
@@ -1076,9 +1076,11 @@ finalize_aggregate(AggState *aggstate,
}
/*
- * Apply the agg's finalfn if one is provided, else return transValue.
+ * Apply the agg's finalfn if one is provided and PARTIAL_AGGREGATE
+ * keyword is not specified, else return transValue.
*/
- if (OidIsValid(peragg->finalfn_oid))
+ if (OidIsValid(peragg->finalfn_oid) &&
+ (peragg->aggref->agg_partial == false))
{
int numFinalArgs = peragg->numFinalArgs;
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index 61ac172a85..67fbbe291f 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -612,6 +612,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 4711f91239..a89cd6a902 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -205,7 +205,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);
@@ -5512,6 +5512,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
@@ -5527,11 +5620,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;
@@ -5573,8 +5670,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
@@ -5587,35 +5684,17 @@ make_partial_grouping_target(PlannerInfo *root,
PVC_INCLUDE_AGGREGATES |
PVC_RECURSE_WINDOWFUNCS |
PVC_INCLUDE_PLACEHOLDERS);
-
+ setGroupClausePartial(partial_target, non_group_exprs, root->processed_groupClause, extra);
add_new_columns_to_pathtarget(partial_target, non_group_exprs);
+ add_new_columns_to_pathtarget(extra->partial_target, non_group_exprs);
/*
* Adjust Aggrefs to put them in partial mode. At this point all Aggrefs
* are at the top level of the target list, so we can just scan the list
* rather than recursing through the expression trees.
*/
- foreach(lc, partial_target->exprs)
- {
- Aggref *aggref = (Aggref *) lfirst(lc);
-
- if (IsA(aggref, Aggref))
- {
- Aggref *newaggref;
-
- /*
- * We shouldn't need to copy the substructure of the Aggref node,
- * but flat-copy the node itself to avoid damaging other trees.
- */
- newaggref = makeNode(Aggref);
- memcpy(newaggref, aggref, sizeof(Aggref));
-
- /* For now, assume serialization is required */
- mark_partial_aggref(newaggref, AGGSPLIT_INITIAL_SERIAL);
-
- lfirst(lc) = newaggref;
- }
- }
+ adjustAggrefForPartial(partial_target->exprs);
+ adjustAggrefForPartial(extra->partial_target->exprs);
/* clean up cruft */
list_free(non_group_exprs);
@@ -7269,7 +7348,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 7aed84584c..1f0a7b03c9 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -2572,6 +2572,7 @@ convert_combining_aggrefs(Node *node, void *context)
parent_agg = copyObject(child_agg);
child_agg->args = orig_agg->args;
child_agg->aggfilter = orig_agg->aggfilter;
+ child_agg->agg_partial = orig_agg->agg_partial;
/*
* Now, set up child_agg to represent the first phase of partial
diff --git a/src/backend/optimizer/prep/prepagg.c b/src/backend/optimizer/prep/prepagg.c
index 4606df379a..aced9f7b5d 100644
--- a/src/backend/optimizer/prep/prepagg.c
+++ b/src/backend/optimizer/prep/prepagg.c
@@ -411,6 +411,7 @@ find_compatible_agg(PlannerInfo *root, Aggref *newagg,
/* all of the following must be the same or it's no match */
if (newagg->inputcollid != existingRef->inputcollid ||
newagg->aggtranstype != existingRef->aggtranstype ||
+ newagg->agg_partial != existingRef->agg_partial ||
newagg->aggstar != existingRef->aggstar ||
newagg->aggvariadic != existingRef->aggvariadic ||
newagg->aggkind != existingRef->aggkind ||
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 4d582950b7..d6de5854c5 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -634,6 +634,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <list> within_group_clause
%type <node> filter_clause
+%type <boolean> partial_aggregate_clause
%type <list> window_clause window_definition_list opt_partition_clause
%type <windef> window_definition over_clause window_specification
opt_frame_clause frame_extent frame_bound
@@ -763,7 +764,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 PARTITIONS PASSING PASSWORD PATH
+ PARALLEL PARAMETER PARSER PARTIAL PARTIAL_AGGREGATE PARTITION PARTITIONS PASSING PASSWORD PATH
PLACING PLAN PLANS POLICY
POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION
@@ -15616,10 +15617,11 @@ func_application: func_name '(' ')'
* (Note that many of the special SQL functions wouldn't actually make any
* sense as functional index entries, but we ignore that consideration here.)
*/
-func_expr: func_application within_group_clause filter_clause over_clause
+func_expr: func_application within_group_clause filter_clause partial_aggregate_clause over_clause
{
FuncCall *n = (FuncCall *) $1;
+ n->agg_partial = $4;
/*
* The order clause for WITHIN GROUP and the one for
* plain-aggregate ORDER BY share a field, so we have to
@@ -15640,6 +15642,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),
@@ -15649,7 +15656,7 @@ func_expr: func_application within_group_clause filter_clause over_clause
n->agg_within_group = true;
}
n->agg_filter = $3;
- n->over = $4;
+ n->over = $5;
$$ = (Node *) n;
}
| json_aggregate_func filter_clause over_clause
@@ -16241,6 +16248,10 @@ filter_clause:
| /*EMPTY*/ { $$ = NULL; }
;
+partial_aggregate_clause:
+ PARTIAL_AGGREGATE { $$ = true; }
+ | /*EMPTY*/ { $$ = false; }
+ ;
/*
* Window Definitions
@@ -17760,6 +17771,7 @@ unreserved_keyword:
| PARAMETER
| PARSER
| PARTIAL
+ | PARTIAL_AGGREGATE
| PARTITION
| PARTITIONS
| PASSING
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index bee7d8346a..8439d0bce6 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -16,6 +16,7 @@
#include "access/htup_details.h"
#include "catalog/pg_aggregate.h"
+#include "catalog/pg_proc.h"
#include "catalog/pg_constraint.h"
#include "catalog/pg_type.h"
#include "common/int.h"
@@ -101,8 +102,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 +148,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 +223,21 @@ 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);
+ if (!aggform->aggpartialpushdownsafe)
+ elog(ERROR, "partial aggregate is unsafe for aggregate %u",
+ agg->aggfnoid);
+ ReleaseSysCache(aggtup);
+ 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 8118036495..27dab3ba42 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -541,6 +541,7 @@ transformRangeFunction(ParseState *pstate, RangeFunction *r)
fc->over == NULL &&
!fc->agg_star &&
!fc->agg_distinct &&
+ !fc->agg_partial &&
!fc->func_variadic &&
coldeflist == NIL)
{
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 00cd7358eb..b4ec2210c0 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -3869,7 +3869,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 9b23344a3b..65cc7f95e9 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -97,6 +97,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
bool agg_within_group = (fn ? fn->agg_within_group : false);
bool agg_star = (fn ? fn->agg_star : false);
bool agg_distinct = (fn ? fn->agg_distinct : false);
+ bool agg_partial = (fn ? fn->agg_partial : false);
bool func_variadic = (fn ? fn->func_variadic : false);
CoercionForm funcformat = (fn ? fn->funcformat : COERCE_EXPLICIT_CALL);
bool could_be_projection;
@@ -222,7 +223,8 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
*/
could_be_projection = (nargs == 1 && !proc_call &&
agg_order == NIL && agg_filter == NULL &&
- !agg_star && !agg_distinct && over == NULL &&
+ !agg_star && !agg_distinct &&
+ !agg_partial && over == NULL &&
!func_variadic && argnames == NIL &&
list_length(funcname) == 1 &&
(actual_arg_types[0] == RECORDOID ||
@@ -322,6 +324,12 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
errmsg("DISTINCT specified, but %s is not an aggregate function",
NameListToString(funcname)),
parser_errposition(pstate, location)));
+ if (agg_partial)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("PARTIAL_AGGREGATE specified, but %s is not an aggregate function",
+ NameListToString(funcname)),
+ parser_errposition(pstate, location)));
if (agg_within_group)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -392,6 +400,8 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
parser_errposition(pstate, location)));
/* gram.y rejects DISTINCT + WITHIN GROUP */
Assert(!agg_distinct);
+ /* gram.y rejects PARTIAL_AGGREGATE */
+ Assert(!agg_partial);
/* gram.y rejects VARIADIC + WITHIN GROUP */
Assert(!func_variadic);
@@ -814,7 +824,8 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
parser_errposition(pstate, location)));
/* parse_agg.c does additional aggregate-specific processing */
- transformAggregateCall(pstate, aggref, fargs, agg_order, agg_distinct);
+ transformAggregateCall(pstate, aggref, fargs, agg_order, agg_distinct,
+ agg_partial);
retval = (Node *) aggref;
}
@@ -846,6 +857,15 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
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/numeric.c b/src/backend/utils/adt/numeric.c
index 5510a203b0..d09e3042ca 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -610,7 +610,6 @@ static void accum_sum_final(NumericSumAccum *accum, NumericVar *result);
static void accum_sum_copy(NumericSumAccum *dst, NumericSumAccum *src);
static void accum_sum_combine(NumericSumAccum *accum, NumericSumAccum *accum2);
-
/* ----------------------------------------------------------------------
*
* Input-, output- and rounding-functions
@@ -5888,7 +5887,6 @@ int8_avg_serialize(PG_FUNCTION_ARGS)
elog(ERROR, "aggregate function called in non-aggregate context");
state = (PolyNumAggState *) PG_GETARG_POINTER(0);
-
/*
* If the platform supports int128 then sumX will be a 128 integer type.
* Here we'll convert that into a numeric type so that the combine state
@@ -6740,6 +6738,25 @@ int4_avg_combine(PG_FUNCTION_ARGS)
PG_RETURN_ARRAYTYPE_P(transarray1);
}
+Datum
+int4_avg_import(PG_FUNCTION_ARGS)
+{
+ ArrayType *transarray;
+ Int8TransTypeData *transdata;
+
+ transarray = PG_GETARG_ARRAYTYPE_P(0);
+
+ if (ARR_HASNULL(transarray) ||
+ ARR_SIZE(transarray) != ARR_OVERHEAD_NONULLS(1) + sizeof(Int8TransTypeData))
+ elog(ERROR, "expected 2-element int8 array");
+
+ transdata = (Int8TransTypeData *) ARR_DATA_PTR(transarray);
+ if (transdata->count <= 0)
+ elog(ERROR, "count must be positive");
+
+ PG_RETURN_ARRAYTYPE_P(transarray);
+}
+
Datum
int2_avg_accum_inv(PG_FUNCTION_ARGS)
{
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 653685bffc..7aaf8f4082 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -10513,7 +10513,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/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index e324070828..1e347ac965 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -14140,6 +14140,15 @@ dumpAgg(Archive *fout, const AggInfo *agginfo)
"'-' AS aggdeserialfn,\n"
"'u' AS proparallel,\n");
+ if (fout->remoteVersion >= 180000)
+ appendPQExpBufferStr(query,
+ "aggpartialpushdownsafe,\n"
+ "aggpartialimportfn,\n");
+ else
+ appendPQExpBufferStr(query,
+ "false AS aggpartialpushdownsafe,\n"
+ "'-' AS aggpartialimportfn,\n");
+
if (fout->remoteVersion >= 110000)
appendPQExpBufferStr(query,
"aggfinalmodify,\n"
diff --git a/src/include/catalog/pg_aggregate.dat b/src/include/catalog/pg_aggregate.dat
index 5f13532abc..da1b1f8fab 100644
--- a/src/include/catalog/pg_aggregate.dat
+++ b/src/include/catalog/pg_aggregate.dat
@@ -21,6 +21,7 @@
aggtransspace => '48', aggmtranstype => 'internal', aggmtransspace => '48' },
{ aggfnoid => 'avg(int4)', aggtransfn => 'int4_avg_accum',
aggfinalfn => 'int8_avg', aggcombinefn => 'int4_avg_combine',
+ aggpartialpushdownsafe => 't', aggpartialimportfn => 'int4_avg_import',
aggmtransfn => 'int4_avg_accum', aggminvtransfn => 'int4_avg_accum_inv',
aggmfinalfn => 'int8_avg', aggtranstype => '_int8', aggmtranstype => '_int8',
agginitval => '{0,0}', aggminitval => '{0,0}' },
@@ -93,139 +94,139 @@
# max
{ aggfnoid => 'max(int8)', aggtransfn => 'int8larger',
- aggcombinefn => 'int8larger', aggsortop => '>(int8,int8)',
- aggtranstype => 'int8' },
+ aggcombinefn => 'int8larger', aggpartialpushdownsafe => 't',
+ aggsortop => '>(int8,int8)', aggtranstype => 'int8' },
{ aggfnoid => 'max(int4)', aggtransfn => 'int4larger',
- aggcombinefn => 'int4larger', aggsortop => '>(int4,int4)',
- aggtranstype => 'int4' },
+ aggcombinefn => 'int4larger', aggpartialpushdownsafe => 't',
+ aggsortop => '>(int4,int4)', aggtranstype => 'int4' },
{ aggfnoid => 'max(int2)', aggtransfn => 'int2larger',
- aggcombinefn => 'int2larger', aggsortop => '>(int2,int2)',
- aggtranstype => 'int2' },
+ aggcombinefn => 'int2larger', aggpartialpushdownsafe => 't',
+ aggsortop => '>(int2,int2)', aggtranstype => 'int2' },
{ aggfnoid => 'max(oid)', aggtransfn => 'oidlarger',
- aggcombinefn => 'oidlarger', aggsortop => '>(oid,oid)',
- aggtranstype => 'oid' },
+ aggcombinefn => 'oidlarger', aggpartialpushdownsafe => 't',
+ aggsortop => '>(oid,oid)', aggtranstype => 'oid' },
{ aggfnoid => 'max(float4)', aggtransfn => 'float4larger',
- aggcombinefn => 'float4larger', aggsortop => '>(float4,float4)',
- aggtranstype => 'float4' },
+ aggcombinefn => 'float4larger', aggpartialpushdownsafe => 't',
+ aggsortop => '>(float4,float4)', aggtranstype => 'float4' },
{ aggfnoid => 'max(float8)', aggtransfn => 'float8larger',
- aggcombinefn => 'float8larger', aggsortop => '>(float8,float8)',
- aggtranstype => 'float8' },
+ aggcombinefn => 'float8larger', aggpartialpushdownsafe => 't',
+ aggsortop => '>(float8,float8)', aggtranstype => 'float8' },
{ aggfnoid => 'max(date)', aggtransfn => 'date_larger',
- aggcombinefn => 'date_larger', aggsortop => '>(date,date)',
- aggtranstype => 'date' },
+ aggcombinefn => 'date_larger', aggpartialpushdownsafe => 't',
+ aggsortop => '>(date,date)', aggtranstype => 'date' },
{ aggfnoid => 'max(time)', aggtransfn => 'time_larger',
- aggcombinefn => 'time_larger', aggsortop => '>(time,time)',
- aggtranstype => 'time' },
+ aggcombinefn => 'time_larger', aggpartialpushdownsafe => 't',
+ aggsortop => '>(time,time)', aggtranstype => 'time' },
{ aggfnoid => 'max(timetz)', aggtransfn => 'timetz_larger',
- aggcombinefn => 'timetz_larger', aggsortop => '>(timetz,timetz)',
- aggtranstype => 'timetz' },
+ aggcombinefn => 'timetz_larger', aggpartialpushdownsafe => 't',
+ aggsortop => '>(timetz,timetz)', aggtranstype => 'timetz' },
{ aggfnoid => 'max(money)', aggtransfn => 'cashlarger',
- aggcombinefn => 'cashlarger', aggsortop => '>(money,money)',
- aggtranstype => 'money' },
+ aggcombinefn => 'cashlarger', aggpartialpushdownsafe => 't',
+ aggsortop => '>(money,money)', aggtranstype => 'money' },
{ aggfnoid => 'max(timestamp)', aggtransfn => 'timestamp_larger',
- aggcombinefn => 'timestamp_larger', aggsortop => '>(timestamp,timestamp)',
- aggtranstype => 'timestamp' },
+ aggcombinefn => 'timestamp_larger', aggpartialpushdownsafe => 't',
+ aggsortop => '>(timestamp,timestamp)', aggtranstype => 'timestamp' },
{ aggfnoid => 'max(timestamptz)', aggtransfn => 'timestamptz_larger',
- aggcombinefn => 'timestamptz_larger',
+ aggcombinefn => 'timestamptz_larger', aggpartialpushdownsafe => 't',
aggsortop => '>(timestamptz,timestamptz)', aggtranstype => 'timestamptz' },
{ aggfnoid => 'max(interval)', aggtransfn => 'interval_larger',
- aggcombinefn => 'interval_larger', aggsortop => '>(interval,interval)',
- aggtranstype => 'interval' },
+ aggcombinefn => 'interval_larger', aggpartialpushdownsafe => 't',
+ aggsortop => '>(interval,interval)', aggtranstype => 'interval' },
{ aggfnoid => 'max(text)', aggtransfn => 'text_larger',
- aggcombinefn => 'text_larger', aggsortop => '>(text,text)',
- aggtranstype => 'text' },
+ aggcombinefn => 'text_larger', aggpartialpushdownsafe => 't',
+ aggsortop => '>(text,text)', aggtranstype => 'text' },
{ aggfnoid => 'max(numeric)', aggtransfn => 'numeric_larger',
- aggcombinefn => 'numeric_larger', aggsortop => '>(numeric,numeric)',
- aggtranstype => 'numeric' },
+ aggcombinefn => 'numeric_larger', aggpartialpushdownsafe => 't',
+ aggsortop => '>(numeric,numeric)', aggtranstype => 'numeric' },
{ aggfnoid => 'max(anyarray)', aggtransfn => 'array_larger',
aggcombinefn => 'array_larger', aggsortop => '>(anyarray,anyarray)',
aggtranstype => 'anyarray' },
{ aggfnoid => 'max(bpchar)', aggtransfn => 'bpchar_larger',
- aggcombinefn => 'bpchar_larger', aggsortop => '>(bpchar,bpchar)',
- aggtranstype => 'bpchar' },
+ aggcombinefn => 'bpchar_larger', aggpartialpushdownsafe => 't',
+ aggsortop => '>(bpchar,bpchar)', aggtranstype => 'bpchar' },
{ aggfnoid => 'max(tid)', aggtransfn => 'tidlarger',
- aggcombinefn => 'tidlarger', aggsortop => '>(tid,tid)',
- aggtranstype => 'tid' },
+ aggcombinefn => 'tidlarger', aggpartialpushdownsafe => 't',
+ aggsortop => '>(tid,tid)', aggtranstype => 'tid' },
{ aggfnoid => 'max(anyenum)', aggtransfn => 'enum_larger',
- aggcombinefn => 'enum_larger', aggsortop => '>(anyenum,anyenum)',
- aggtranstype => 'anyenum' },
+ aggcombinefn => 'enum_larger', aggpartialpushdownsafe => 't',
+ aggsortop => '>(anyenum,anyenum)', aggtranstype => 'anyenum' },
{ aggfnoid => 'max(inet)', aggtransfn => 'network_larger',
- aggcombinefn => 'network_larger', aggsortop => '>(inet,inet)',
- aggtranstype => 'inet' },
+ aggcombinefn => 'network_larger', aggpartialpushdownsafe => 't',
+ aggsortop => '>(inet,inet)', aggtranstype => 'inet' },
{ aggfnoid => 'max(pg_lsn)', aggtransfn => 'pg_lsn_larger',
- aggcombinefn => 'pg_lsn_larger', aggsortop => '>(pg_lsn,pg_lsn)',
- aggtranstype => 'pg_lsn' },
+ aggcombinefn => 'pg_lsn_larger', aggpartialpushdownsafe => 't',
+ aggsortop => '>(pg_lsn,pg_lsn)', aggtranstype => 'pg_lsn' },
{ aggfnoid => 'max(xid8)', aggtransfn => 'xid8_larger',
- aggcombinefn => 'xid8_larger', aggsortop => '>(xid8,xid8)',
- aggtranstype => 'xid8' },
+ aggcombinefn => 'xid8_larger', aggpartialpushdownsafe => 't',
+ aggsortop => '>(xid8,xid8)', aggtranstype => 'xid8' },
# min
{ aggfnoid => 'min(int8)', aggtransfn => 'int8smaller',
- aggcombinefn => 'int8smaller', aggsortop => '<(int8,int8)',
- aggtranstype => 'int8' },
+ aggcombinefn => 'int8smaller', aggpartialpushdownsafe => 't',
+ aggsortop => '<(int8,int8)', aggtranstype => 'int8' },
{ aggfnoid => 'min(int4)', aggtransfn => 'int4smaller',
- aggcombinefn => 'int4smaller', aggsortop => '<(int4,int4)',
- aggtranstype => 'int4' },
+ aggcombinefn => 'int4smaller', aggpartialpushdownsafe => 't',
+ aggsortop => '<(int4,int4)', aggtranstype => 'int4' },
{ aggfnoid => 'min(int2)', aggtransfn => 'int2smaller',
- aggcombinefn => 'int2smaller', aggsortop => '<(int2,int2)',
- aggtranstype => 'int2' },
+ aggcombinefn => 'int2smaller', aggpartialpushdownsafe => 't',
+ aggsortop => '<(int2,int2)', aggtranstype => 'int2' },
{ aggfnoid => 'min(oid)', aggtransfn => 'oidsmaller',
- aggcombinefn => 'oidsmaller', aggsortop => '<(oid,oid)',
- aggtranstype => 'oid' },
+ aggcombinefn => 'oidsmaller', aggpartialpushdownsafe => 't',
+ aggsortop => '<(oid,oid)', aggtranstype => 'oid' },
{ aggfnoid => 'min(float4)', aggtransfn => 'float4smaller',
- aggcombinefn => 'float4smaller', aggsortop => '<(float4,float4)',
- aggtranstype => 'float4' },
+ aggcombinefn => 'float4smaller', aggpartialpushdownsafe => 't',
+ aggsortop => '<(float4,float4)', aggtranstype => 'float4' },
{ aggfnoid => 'min(float8)', aggtransfn => 'float8smaller',
- aggcombinefn => 'float8smaller', aggsortop => '<(float8,float8)',
- aggtranstype => 'float8' },
+ aggcombinefn => 'float8smaller', aggpartialpushdownsafe => 't',
+ aggsortop => '<(float8,float8)', aggtranstype => 'float8' },
{ aggfnoid => 'min(date)', aggtransfn => 'date_smaller',
- aggcombinefn => 'date_smaller', aggsortop => '<(date,date)',
- aggtranstype => 'date' },
+ aggcombinefn => 'date_smaller', aggpartialpushdownsafe => 't',
+ aggsortop => '<(date,date)', aggtranstype => 'date' },
{ aggfnoid => 'min(time)', aggtransfn => 'time_smaller',
- aggcombinefn => 'time_smaller', aggsortop => '<(time,time)',
- aggtranstype => 'time' },
+ aggcombinefn => 'time_smaller', aggpartialpushdownsafe => 't',
+ aggsortop => '<(time,time)', aggtranstype => 'time' },
{ aggfnoid => 'min(timetz)', aggtransfn => 'timetz_smaller',
- aggcombinefn => 'timetz_smaller', aggsortop => '<(timetz,timetz)',
- aggtranstype => 'timetz' },
+ aggcombinefn => 'timetz_smaller', aggpartialpushdownsafe => 't',
+ aggsortop => '<(timetz,timetz)', aggtranstype => 'timetz' },
{ aggfnoid => 'min(money)', aggtransfn => 'cashsmaller',
- aggcombinefn => 'cashsmaller', aggsortop => '<(money,money)',
- aggtranstype => 'money' },
+ aggcombinefn => 'cashsmaller', aggpartialpushdownsafe => 't',
+ aggsortop => '<(money,money)', aggtranstype => 'money' },
{ aggfnoid => 'min(timestamp)', aggtransfn => 'timestamp_smaller',
- aggcombinefn => 'timestamp_smaller', aggsortop => '<(timestamp,timestamp)',
- aggtranstype => 'timestamp' },
+ aggcombinefn => 'timestamp_smaller', aggpartialpushdownsafe => 't',
+ aggsortop => '<(timestamp,timestamp)', aggtranstype => 'timestamp' },
{ aggfnoid => 'min(timestamptz)', aggtransfn => 'timestamptz_smaller',
- aggcombinefn => 'timestamptz_smaller',
+ aggcombinefn => 'timestamptz_smaller', aggpartialpushdownsafe => 't',
aggsortop => '<(timestamptz,timestamptz)', aggtranstype => 'timestamptz' },
{ aggfnoid => 'min(interval)', aggtransfn => 'interval_smaller',
- aggcombinefn => 'interval_smaller', aggsortop => '<(interval,interval)',
- aggtranstype => 'interval' },
+ aggcombinefn => 'interval_smaller', aggpartialpushdownsafe => 't',
+ aggsortop => '<(interval,interval)', aggtranstype => 'interval' },
{ aggfnoid => 'min(text)', aggtransfn => 'text_smaller',
- aggcombinefn => 'text_smaller', aggsortop => '<(text,text)',
- aggtranstype => 'text' },
+ aggcombinefn => 'text_smaller', aggpartialpushdownsafe => 't',
+ aggsortop => '<(text,text)', aggtranstype => 'text' },
{ aggfnoid => 'min(numeric)', aggtransfn => 'numeric_smaller',
- aggcombinefn => 'numeric_smaller', aggsortop => '<(numeric,numeric)',
- aggtranstype => 'numeric' },
+ aggcombinefn => 'numeric_smaller', aggpartialpushdownsafe => 't',
+ aggsortop => '<(numeric,numeric)', aggtranstype => 'numeric' },
{ aggfnoid => 'min(anyarray)', aggtransfn => 'array_smaller',
aggcombinefn => 'array_smaller', aggsortop => '<(anyarray,anyarray)',
aggtranstype => 'anyarray' },
{ aggfnoid => 'min(bpchar)', aggtransfn => 'bpchar_smaller',
- aggcombinefn => 'bpchar_smaller', aggsortop => '<(bpchar,bpchar)',
- aggtranstype => 'bpchar' },
+ aggcombinefn => 'bpchar_smaller', aggpartialpushdownsafe => 't',
+ aggsortop => '<(bpchar,bpchar)', aggtranstype => 'bpchar' },
{ aggfnoid => 'min(tid)', aggtransfn => 'tidsmaller',
- aggcombinefn => 'tidsmaller', aggsortop => '<(tid,tid)',
- aggtranstype => 'tid' },
+ aggcombinefn => 'tidsmaller', aggpartialpushdownsafe => 't',
+ aggsortop => '<(tid,tid)', aggtranstype => 'tid' },
{ aggfnoid => 'min(anyenum)', aggtransfn => 'enum_smaller',
- aggcombinefn => 'enum_smaller', aggsortop => '<(anyenum,anyenum)',
- aggtranstype => 'anyenum' },
+ aggcombinefn => 'enum_smaller', aggpartialpushdownsafe => 't',
+ aggsortop => '<(anyenum,anyenum)', aggtranstype => 'anyenum' },
{ aggfnoid => 'min(inet)', aggtransfn => 'network_smaller',
- aggcombinefn => 'network_smaller', aggsortop => '<(inet,inet)',
- aggtranstype => 'inet' },
+ aggcombinefn => 'network_smaller', aggpartialpushdownsafe => 't',
+ aggsortop => '<(inet,inet)', aggtranstype => 'inet' },
{ aggfnoid => 'min(pg_lsn)', aggtransfn => 'pg_lsn_smaller',
- aggcombinefn => 'pg_lsn_smaller', aggsortop => '<(pg_lsn,pg_lsn)',
- aggtranstype => 'pg_lsn' },
+ aggcombinefn => 'pg_lsn_smaller', aggpartialpushdownsafe => 't',
+ aggsortop => '<(pg_lsn,pg_lsn)', aggtranstype => 'pg_lsn' },
{ aggfnoid => 'min(xid8)', aggtransfn => 'xid8_smaller',
- aggcombinefn => 'xid8_smaller', aggsortop => '<(xid8,xid8)',
- aggtranstype => 'xid8' },
+ aggcombinefn => 'xid8_smaller', aggpartialpushdownsafe => 't',
+ aggsortop => '<(xid8,xid8)', aggtranstype => 'xid8' },
# count
{ aggfnoid => 'count(any)', aggtransfn => 'int8inc_any',
@@ -497,46 +498,55 @@
# boolean-and and boolean-or
{ aggfnoid => 'bool_and', aggtransfn => 'booland_statefunc',
- aggcombinefn => 'booland_statefunc', aggmtransfn => 'bool_accum',
- aggminvtransfn => 'bool_accum_inv', aggmfinalfn => 'bool_alltrue',
- aggsortop => '<(bool,bool)', aggtranstype => 'bool',
- aggmtranstype => 'internal', aggmtransspace => '16' },
+ aggcombinefn => 'booland_statefunc', aggpartialpushdownsafe => 't',
+ aggmtransfn => 'bool_accum', aggminvtransfn => 'bool_accum_inv',
+ aggmfinalfn => 'bool_alltrue', aggsortop => '<(bool,bool)',
+ aggtranstype => 'bool', aggmtranstype => 'internal',
+ aggmtransspace => '16' },
{ aggfnoid => 'bool_or', aggtransfn => 'boolor_statefunc',
- aggcombinefn => 'boolor_statefunc', aggmtransfn => 'bool_accum',
- aggminvtransfn => 'bool_accum_inv', aggmfinalfn => 'bool_anytrue',
- aggsortop => '>(bool,bool)', aggtranstype => 'bool',
- aggmtranstype => 'internal', aggmtransspace => '16' },
+ aggcombinefn => 'boolor_statefunc', aggpartialpushdownsafe => 't',
+ aggmtransfn => 'bool_accum', aggminvtransfn => 'bool_accum_inv',
+ aggmfinalfn => 'bool_anytrue', aggsortop => '>(bool,bool)',
+ aggtranstype => 'bool', aggmtranstype => 'internal',
+ aggmtransspace => '16' },
{ aggfnoid => 'every', aggtransfn => 'booland_statefunc',
- aggcombinefn => 'booland_statefunc', aggmtransfn => 'bool_accum',
- aggminvtransfn => 'bool_accum_inv', aggmfinalfn => 'bool_alltrue',
- aggsortop => '<(bool,bool)', aggtranstype => 'bool',
- aggmtranstype => 'internal', aggmtransspace => '16' },
+ aggcombinefn => 'booland_statefunc', aggpartialpushdownsafe => 't',
+ aggmtransfn => 'bool_accum', aggminvtransfn => 'bool_accum_inv',
+ aggmfinalfn => 'bool_alltrue', aggsortop => '<(bool,bool)',
+ aggtranstype => 'bool', aggmtranstype => 'internal',
+ aggmtransspace => '16' },
# bitwise integer
{ aggfnoid => 'bit_and(int2)', aggtransfn => 'int2and',
- aggcombinefn => 'int2and', aggtranstype => 'int2' },
-{ aggfnoid => 'bit_or(int2)', aggtransfn => 'int2or', aggcombinefn => 'int2or',
+ aggcombinefn => 'int2and', aggpartialpushdownsafe => 't',
aggtranstype => 'int2' },
+{ aggfnoid => 'bit_or(int2)', aggtransfn => 'int2or', aggcombinefn => 'int2or',
+ aggpartialpushdownsafe => 't', aggtranstype => 'int2' },
{ aggfnoid => 'bit_xor(int2)', aggtransfn => 'int2xor',
- aggcombinefn => 'int2xor', aggtranstype => 'int2' },
+ aggcombinefn => 'int2xor', aggpartialpushdownsafe => 't',
+ aggtranstype => 'int2' },
{ aggfnoid => 'bit_and(int4)', aggtransfn => 'int4and',
- aggcombinefn => 'int4and', aggtranstype => 'int4' },
-{ aggfnoid => 'bit_or(int4)', aggtransfn => 'int4or', aggcombinefn => 'int4or',
+ aggcombinefn => 'int4and', aggpartialpushdownsafe => 't',
aggtranstype => 'int4' },
+{ aggfnoid => 'bit_or(int4)', aggtransfn => 'int4or', aggcombinefn => 'int4or',
+ aggpartialpushdownsafe => 't', aggtranstype => 'int4' },
{ aggfnoid => 'bit_xor(int4)', aggtransfn => 'int4xor',
- aggcombinefn => 'int4xor', aggtranstype => 'int4' },
+ aggcombinefn => 'int4xor', aggpartialpushdownsafe => 't',
+ aggtranstype => 'int4' },
{ aggfnoid => 'bit_and(int8)', aggtransfn => 'int8and',
- aggcombinefn => 'int8and', aggtranstype => 'int8' },
-{ aggfnoid => 'bit_or(int8)', aggtransfn => 'int8or', aggcombinefn => 'int8or',
+ aggcombinefn => 'int8and', aggpartialpushdownsafe => 't',
aggtranstype => 'int8' },
+{ aggfnoid => 'bit_or(int8)', aggtransfn => 'int8or', aggcombinefn => 'int8or',
+ aggpartialpushdownsafe => 't', aggtranstype => 'int8' },
{ aggfnoid => 'bit_xor(int8)', aggtransfn => 'int8xor',
- aggcombinefn => 'int8xor', aggtranstype => 'int8' },
+ aggcombinefn => 'int8xor', aggpartialpushdownsafe => 't',
+ aggtranstype => 'int8' },
{ aggfnoid => 'bit_and(bit)', aggtransfn => 'bitand', aggcombinefn => 'bitand',
- aggtranstype => 'bit' },
+ aggpartialpushdownsafe => 't', aggtranstype => 'bit' },
{ aggfnoid => 'bit_or(bit)', aggtransfn => 'bitor', aggcombinefn => 'bitor',
- aggtranstype => 'bit' },
+ aggpartialpushdownsafe => 't', aggtranstype => 'bit' },
{ aggfnoid => 'bit_xor(bit)', aggtransfn => 'bitxor', aggcombinefn => 'bitxor',
- aggtranstype => 'bit' },
+ aggpartialpushdownsafe => 't', aggtranstype => 'bit' },
# xml
{ aggfnoid => 'xmlagg', aggtransfn => 'xmlconcat2', aggtranstype => 'xml' },
diff --git a/src/include/catalog/pg_aggregate.h b/src/include/catalog/pg_aggregate.h
index 490f645469..54481b0ac2 100644
--- a/src/include/catalog/pg_aggregate.h
+++ b/src/include/catalog/pg_aggregate.h
@@ -55,6 +55,13 @@ CATALOG(pg_aggregate,2600,AggregateRelationId)
/* function to convert bytea to transtype (0 if none) */
regproc aggdeserialfn BKI_DEFAULT(-) BKI_LOOKUP_OPT(pg_proc);
+ /* true if partial aggregate is fine to push down */
+ bool aggpartialpushdownsafe BKI_DEFAULT(f);
+
+ /* function to check validity of aggregate's transition (state)
+ data and to convert it from standard format to local format (0 if none) */
+ regproc aggpartialimportfn BKI_DEFAULT(-) BKI_LOOKUP_OPT(pg_proc);
+
/* forward function for moving-aggregate mode (0 if none) */
regproc aggmtransfn BKI_DEFAULT(-) BKI_LOOKUP_OPT(pg_proc);
@@ -163,6 +170,8 @@ extern ObjectAddress AggregateCreate(const char *aggName,
List *aggcombinefnName,
List *aggserialfnName,
List *aggdeserialfnName,
+ bool aggpartialpushdownsafe,
+ List *aggpartialimportfnName,
List *aggmtransfnName,
List *aggminvtransfnName,
List *aggmfinalfnName,
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6a5476d3c4..db8c293b7c 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4969,6 +4969,9 @@
{ oid => '1964', descr => 'aggregate final function',
proname => 'int8_avg', prorettype => 'numeric', proargtypes => '_int8',
prosrc => 'int8_avg' },
+{ oid => '6400', descr => 'aggregate import function',
+ proname => 'int4_avg_import', prorettype => '_int8',
+ proargtypes => '_int8', prosrc => 'int4_avg_import' },
{ oid => '3572', descr => 'aggregate final function',
proname => 'int2int4_sum', prorettype => 'int8', proargtypes => '_int8',
prosrc => 'int2int4_sum' },
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 85a62b538e..9e32531682 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -431,6 +431,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 */
ParseLoc location; /* token location, or -1 if unknown */
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 2ba297c117..ba164b39cc 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -3284,6 +3284,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
{
@@ -3298,6 +3301,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 4830efc573..90b2234b42 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -473,6 +473,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 f7fe834cf4..2ef0d0692e 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -334,6 +334,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, UNRESERVED_KEYWORD, AS_LABEL)
PG_KEYWORD("partition", PARTITION, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("partitions", PARTITIONS, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("passing", PASSING, 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);
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index 1c1ca7573a..a1aa40c822 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -3396,6 +3396,14 @@ set work_mem to default;
----+----+----
(0 rows)
+-- PARTIAL_AGGREGATE tests
+-- Check return type of partial aggregate
+select pg_typeof(avg(a::int4) partial_aggregate), pg_typeof(avg(a::int4)) from aggtest;
+ pg_typeof | pg_typeof
+-----------+-----------
+ bigint[] | numeric
+(1 row)
+
drop table agg_group_1;
drop table agg_group_2;
drop table agg_group_3;
diff --git a/src/test/regress/expected/create_aggregate.out b/src/test/regress/expected/create_aggregate.out
index dcf6909423..a7ad2ba631 100644
--- a/src/test/regress/expected/create_aggregate.out
+++ b/src/test/regress/expected/create_aggregate.out
@@ -322,3 +322,13 @@ WARNING: aggregate attribute "Finalfunc_extra" not recognized
WARNING: aggregate attribute "Finalfunc_modify" not recognized
WARNING: aggregate attribute "Parallel" not recognized
ERROR: aggregate stype must be specified
+-- invalid: aggpartialpushdownsafe is false and importfunc is specified
+CREATE AGGREGATE udf_avg_invalid_aggpartialpushdownsafe(int4) (
+ sfunc = int4_avg_accum,
+ stype = _int8,
+ finalfunc = int8_avg,
+ combinefunc = int4_avg_combine,
+ initcond = '{0,0}',
+ importfunc = int4_avg_import
+);
+ERROR: aggpartialpushdownsafe must be true when aggpartialimportfunc is specified
diff --git a/src/test/regress/expected/oidjoins.out b/src/test/regress/expected/oidjoins.out
index 215eb899be..da0cd81c08 100644
--- a/src/test/regress/expected/oidjoins.out
+++ b/src/test/regress/expected/oidjoins.out
@@ -145,6 +145,7 @@ NOTICE: checking pg_aggregate {aggfinalfn} => pg_proc {oid}
NOTICE: checking pg_aggregate {aggcombinefn} => pg_proc {oid}
NOTICE: checking pg_aggregate {aggserialfn} => pg_proc {oid}
NOTICE: checking pg_aggregate {aggdeserialfn} => pg_proc {oid}
+NOTICE: checking pg_aggregate {aggpartialimportfn} => pg_proc {oid}
NOTICE: checking pg_aggregate {aggmtransfn} => pg_proc {oid}
NOTICE: checking pg_aggregate {aggminvtransfn} => pg_proc {oid}
NOTICE: checking pg_aggregate {aggmfinalfn} => pg_proc {oid}
diff --git a/src/test/regress/expected/partition_aggregate.out b/src/test/regress/expected/partition_aggregate.out
index 5f2c0cf578..cfde38c6ed 100644
--- a/src/test/regress/expected/partition_aggregate.out
+++ b/src/test/regress/expected/partition_aggregate.out
@@ -399,6 +399,35 @@ SELECT a, sum(b order by a) FROM pagg_tab GROUP BY a ORDER BY 1, 2;
-> Seq Scan on pagg_tab_p3 pagg_tab_3
(10 rows)
+-- PARTIAL_AGGREGATE tests
+-- Check partial aggregate over partitioned table
+explain (verbose, costs off)
+select avg(a) partial_aggregate, avg(a) from pagg_tab;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
+ Finalize Aggregate
+ Output: avg(PARTIAL_AGGREGATE pagg_tab.a), avg(pagg_tab.a)
+ -> Append
+ -> Partial Aggregate
+ Output: PARTIAL avg(PARTIAL_AGGREGATE pagg_tab.a), PARTIAL avg(pagg_tab.a)
+ -> Seq Scan on public.pagg_tab_p1 pagg_tab
+ Output: pagg_tab.a
+ -> Partial Aggregate
+ Output: PARTIAL avg(PARTIAL_AGGREGATE pagg_tab_1.a), PARTIAL avg(pagg_tab_1.a)
+ -> Seq Scan on public.pagg_tab_p2 pagg_tab_1
+ Output: pagg_tab_1.a
+ -> Partial Aggregate
+ Output: PARTIAL avg(PARTIAL_AGGREGATE pagg_tab_2.a), PARTIAL avg(pagg_tab_2.a)
+ -> Seq Scan on public.pagg_tab_p3 pagg_tab_2
+ Output: pagg_tab_2.a
+(15 rows)
+
+select avg(a) partial_aggregate, avg(a) from pagg_tab;
+ avg | avg
+--------------+--------------------
+ {3000,28500} | 9.5000000000000000
+(1 row)
+
-- JOIN query
CREATE TABLE pagg_tab1(x int, y int) PARTITION BY RANGE(x);
CREATE TABLE pagg_tab1_p1 PARTITION OF pagg_tab1 FOR VALUES FROM (0) TO (10);
diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql
index 1a18ca3d8f..c7923bbd71 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -1565,6 +1565,10 @@ set work_mem to default;
union all
(select * from agg_group_4 except select * from agg_hash_4);
+-- PARTIAL_AGGREGATE tests
+-- Check return type of partial aggregate
+select pg_typeof(avg(a::int4) partial_aggregate), pg_typeof(avg(a::int4)) from aggtest;
+
drop table agg_group_1;
drop table agg_group_2;
drop table agg_group_3;
diff --git a/src/test/regress/sql/create_aggregate.sql b/src/test/regress/sql/create_aggregate.sql
index d4b4036fd7..ec15049985 100644
--- a/src/test/regress/sql/create_aggregate.sql
+++ b/src/test/regress/sql/create_aggregate.sql
@@ -328,3 +328,14 @@ CREATE AGGREGATE case_agg(float8)
"Finalfunc_modify" = read_write,
"Parallel" = safe
);
+
+-- invalid: aggpartialpushdownsafe is false and importfunc is specified
+
+CREATE AGGREGATE udf_avg_invalid_aggpartialpushdownsafe(int4) (
+ sfunc = int4_avg_accum,
+ stype = _int8,
+ finalfunc = int8_avg,
+ combinefunc = int4_avg_combine,
+ initcond = '{0,0}',
+ importfunc = int4_avg_import
+);
diff --git a/src/test/regress/sql/partition_aggregate.sql b/src/test/regress/sql/partition_aggregate.sql
index ab070fee24..60356a89f1 100644
--- a/src/test/regress/sql/partition_aggregate.sql
+++ b/src/test/regress/sql/partition_aggregate.sql
@@ -92,6 +92,11 @@ SELECT c, sum(b order by a) FROM pagg_tab GROUP BY c ORDER BY 1, 2;
EXPLAIN (COSTS OFF)
SELECT a, sum(b order by a) FROM pagg_tab GROUP BY a ORDER BY 1, 2;
+-- PARTIAL_AGGREGATE tests
+-- Check partial aggregate over partitioned table
+explain (verbose, costs off)
+select avg(a) partial_aggregate, avg(a) from pagg_tab;
+select avg(a) partial_aggregate, avg(a) from pagg_tab;
-- JOIN query
--
2.31.1
view thread (45+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: RE: Partial aggregates pushdown
In-Reply-To: <TY2PR01MB3835CB1D110362A3213E5F6795D22@TY2PR01MB3835.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