Date: Thu, 15 Aug 2024 17:12:50 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Jelte Fennema-Nio <postgres@jeltef.nl>
Cc: "Fujii.Yuki@df.MitsubishiElectric.co.jp" <Fujii.Yuki@df.mitsubishielectric.co.jp>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>,
	Robert Haas <robertmhaas@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	Stephen Frost <sfrost@snowman.net>,
	Andres Freund <andres@anarazel.de>,
	Tomas Vondra <tomas.vondra@enterprisedb.com>,
	Julien Rouhaud <rjuju123@gmail.com>,
	Daniel Gustafsson <daniel@yesql.se>,
	vignesh C <vignesh21@gmail.com>,
	Alexander Pyhalov <a.pyhalov@postgrespro.ru>
Subject: Re: Partial aggregates pushdown
Message-ID: <Zr5vUoXA4sCyZjsX@momjian.us>
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>
 <Zogu7zHOvDNl1LsK@momjian.us>
 <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>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <CAGECzQSB-Y=WGe4bGA7+cxLTrkuz8t_x+kpgJS+NP4mP0gVSFg@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/Zr5vUoXA4sCyZjsX%40momjian.us>
Precedence: bulk

On Thu, Aug  8, 2024 at 01:48:49PM +0200, Jelte Fennema-Nio wrote:
> SUMMARY OF THREAD
> 
> The design of patch 0001 is agreed upon by everyone on the thread (so
> far). This adds the PARTIAL_AGGREGATE label for aggregates, which will
> cause the finalfunc not to run. It also starts using PARTIAL_AGGREGATE
> for pushdown of aggregates in postgres_fdw. In 0001 PARTIAL_AGGREGATE
> is only supported for aggregates with a non-internal/pseudo type as
> the stype.
> 
> The design for patch 0002 is still under debate. This would expand on
> the functionality added by adding support for PARTIAL_AGGREGATE for
> aggregates with an internal stype. This is done by returning a byte
> array containing the bytes that the serialfunc of the aggregate
> returns.
> 
> A competing proposal for 0002 is to instead change aggregates to not
> use an internal stype anymore, and create dedicated types. The main
> downside here is that infunc and outfunc would need to be added for
> text serialization, in addition to the binary serialization. An open
> question is: Can we change the requirements for CREATE TYPE, so that
> types can be created without infunc and outfunc.
> 
> WHAT IS NEEDED?
> 
> The things needed for this patch are that docs need to be added, and
> detailed codereview needs to be done.
> 
> Feedback from more people on the two competing proposals for 0002
> would be very helpful in making a decision.

First, I am sorry to be replying so late --- I have been traveling for
the past four weeks.  Second, I consider this feature a big part of
sharding, and I think sharding is Postgres's biggest missing feature. I
talk about this patch often when asked about what Postgres is working
on.

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?

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.