On 04/09/2026 17:50, Tom Lane wrote: > Andres Freund <[email protected]> writes: >> Seems like if we do this - and I think we should - we should go broader than >> just doing this for int8[]. So yea, let's do it for float8 too. >> I think it might make sense to have an opr_sanity.sql check that verifies >> that >> we don't add new builtin aggregates that have an array transition state. Or >> perhaps even more strictly, test that aggregates either have an internal >> transition state, or the argument type's (for stuff like min/max). > > On reflection, I'm not sure that such a policy is a win. If you go > with an internal-type transition state, then (if you want parallel > aggregation support) you need serialize/deserialize functions, and > I think also some other stuff that comes for free if the transition > state is a real SQL type. So this is not so much a clear win as > a tradeoff of which code you want to write. >
As I see it, with an internal state, some aggregates gain, while others don't. Plain sum(int2/int4/float8) already has a trivial 8-byte transition state: minimal memory, passed by value, no detoast. The moving-aggregate mode of sum(int2/int4), on the other hand, reuses avg()'s int2/int4_avg_accum_inv functions, which pay for array overhead and a detoast check on every row. Internal state might reduce memory consumption there. float8 has no moving-aggregate mode. Also note that the regression tests currently call float8_regr_combine() directly, with literal array values. Not sure how much it matters, but introducing an internal state also means giving up initcond. Hence, sum(float8) with an internal state would lose its STRICT marking, pushing NULL-checking into the function body. Quick tests show sum() losing 1-3% in serial mode and a more significant ~7% in a parallel GROUP BY, due to serialize/deserialize across workers. This was run on my laptop, not a dedicated benchmarking box, but the overhead looks clearly detectable. Overall, in my opinion, only AVG() and other statistical aggregates, such as STDDEV(), benefit from moving to internal state. Basic aggregates like sum() should probably stay as they are, if we want to avoid a performance regression. -- regards, Andrei Lepikhov, pgEdge
