public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jelte Fennema-Nio <[email protected]>
To: Bruce Momjian <[email protected]>
Cc: [email protected] <[email protected]>
Cc: PostgreSQL-development <[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]>
Subject: Re: Partial aggregates pushdown
Date: Tue, 20 Aug 2024 10:07:32 +0200
Message-ID: <CAGECzQRFPBsKFk4D56M8yt2TrFdRu_0gDbbBE1887x7LWU_ukg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAGECzQTnfUdRO21CkwZeMowFZC3zQ9+dhNp3EB-G52=BXJvyPg@mail.gmail.com>
<TY2PR01MB3835DE59D4654A499A5A699795D52@TY2PR01MB3835.jpnprd01.prod.outlook.com>
<CAGECzQT=CjkORFaZuBn4Db9CRt9C_EpB2isadmx_iNS3ZzQMwg@mail.gmail.com>
<TY2PR01MB3835CB1D110362A3213E5F6795D22@TY2PR01MB3835.jpnprd01.prod.outlook.com>
<[email protected]>
<TY2PR01MB3835F90801CE766B89B255EA95D92@TY2PR01MB3835.jpnprd01.prod.outlook.com>
<CAGECzQSRWDyy2k9rrU7qSiHbJHG43+LAKZC7iFqwZcyZziLNwQ@mail.gmail.com>
<TY2PR01MB3835BA34BAFF196C0C6A81D195DA2@TY2PR01MB3835.jpnprd01.prod.outlook.com>
<CAGECzQR_qwYupAEEVeY6sC14hQ9V2BGLNQQdROWvF60QmMsZFQ@mail.gmail.com>
<CAGECzQSB-Y=WGe4bGA7+cxLTrkuz8t_x+kpgJS+NP4mP0gVSFg@mail.gmail.com>
<[email protected]>
On Thu, 15 Aug 2024 at 23:12, Bruce Momjian <[email protected]> wrote:
> Third, I would like to show a more specific example to clarify what is
> being considered above. If we look at MAX(), we can have FDWs return
> the max for each FDW, and the coordinator can chose the highest value.
> This is the patch 1 listed above. These can return the
> pg_aggregate.aggtranstype data type using the pg_type.typoutput text
> output.
>
> The second case is for something like AVG(), which must return the SUM()
> and COUNT(), and we currently have no way to return multiple text values
> on the wire. For patch 0002, we have the option of creating functions
> that can do this and record them in new pg_attribute columns, or we can
> create a data type with these functions, and assign the data type to
> pg_aggregate.aggtranstype.
>
> Is that accurate?
It's close to accurate, but not entirely. Patch 1 would actually
solves some AVG cases too, because some AVG implementations use an SQL
array type to store the transtype instead of an internal type. And by
using an SQL array type we *can* send multiple text values on the
wire. See below for a list of those aggregates:
> select p.oid::regprocedure
from pg_aggregate a join pg_proc p on a.aggfnoid = p.oid
where aggfinalfn != 0 and aggtranstype::regtype not in ('internal',
'anyenum', 'anyelement', 'anyrange', 'anyarray', 'anymultirange');
oid
───────────────────────────────────────────────────
avg(integer)
avg(smallint)
avg(real)
avg(double precision)
avg(interval)
var_pop(real)
var_pop(double precision)
var_samp(real)
var_samp(double precision)
variance(real)
variance(double precision)
stddev_pop(real)
stddev_pop(double precision)
stddev_samp(real)
stddev_samp(double precision)
stddev(real)
stddev(double precision)
regr_sxx(double precision,double precision)
regr_syy(double precision,double precision)
regr_sxy(double precision,double precision)
regr_avgx(double precision,double precision)
regr_avgy(double precision,double precision)
regr_r2(double precision,double precision)
regr_slope(double precision,double precision)
regr_intercept(double precision,double precision)
covar_pop(double precision,double precision)
covar_samp(double precision,double precision)
corr(double precision,double precision)
(28 rows)
And to be clear, these are in addition to the MAX type of aggregates
you were describing:
> select p.oid::regprocedure
from pg_aggregate a join pg_proc p on a.aggfnoid = p.oid
where aggfinalfn = 0 and aggtranstype::regtype not in ('internal',
'anyenum', 'anyelement', 'anyrange', 'anyarray', 'anymultirange');
oid
───────────────────────────────────────────────
sum(integer)
sum(smallint)
sum(real)
sum(double precision)
sum(money)
sum(interval)
max(bigint)
max(integer)
max(smallint)
max(oid)
max(real)
max(double precision)
max(date)
max(time without time zone)
max(time with time zone)
max(money)
max(timestamp without time zone)
max(timestamp with time zone)
max(interval)
max(text)
max(numeric)
max(character)
max(tid)
max(inet)
max(pg_lsn)
max(xid8)
min(bigint)
min(integer)
min(smallint)
min(oid)
min(real)
min(double precision)
min(date)
min(time without time zone)
min(time with time zone)
min(money)
min(timestamp without time zone)
min(timestamp with time zone)
min(interval)
min(text)
min(numeric)
min(character)
min(tid)
min(inet)
min(pg_lsn)
min(xid8)
count("any")
count()
regr_count(double precision,double precision)
bool_and(boolean)
bool_or(boolean)
every(boolean)
bit_and(smallint)
bit_or(smallint)
bit_xor(smallint)
bit_and(integer)
bit_or(integer)
bit_xor(integer)
bit_and(bigint)
bit_or(bigint)
bit_xor(bigint)
bit_and(bit)
bit_or(bit)
bit_xor(bit)
xmlagg(xml)
(65 rows)
view thread (13+ 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: <CAGECzQRFPBsKFk4D56M8yt2TrFdRu_0gDbbBE1887x7LWU_ukg@mail.gmail.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