Date: Tue, 20 Aug 2024 12:50:37 -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: <ZsTJXZq2pcx1Pst-@momjian.us>
References: <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>
 <Zr5vUoXA4sCyZjsX@momjian.us>
 <CAGECzQRFPBsKFk4D56M8yt2TrFdRu_0gDbbBE1887x7LWU_ukg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <CAGECzQRFPBsKFk4D56M8yt2TrFdRu_0gDbbBE1887x7LWU_ukg@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/ZsTJXZq2pcx1Pst-%40momjian.us>
Precedence: bulk

On Tue, Aug 20, 2024 at 10:07:32AM +0200, Jelte Fennema-Nio wrote:
> 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:

Okay, so we can do MAX easily, and AVG if the count can be represented
as the same data type as the sum?  Is that correct?  Our only problem is
that something like AVG(interval) can't use an array because arrays have
to have the same data type for all array elements, and an interval can't
represent a count?

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

  Only you can decide what is important to you.