Re: Partial aggregates pushdown - Mailing list pgsql-hackers

On Thu, 15 Aug 2024 at 23:12, Bruce Momjian <bruce@momjian.us> 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)