On 21/08/2026 17:20, Tom Lane wrote: > Andrei Lepikhov <[email protected]> writes:> >> What I want to optimise is the built-in functions like >> sum(), avg(), and similar ones, because those are what users >> actually use. > > For those, wouldn't we just modify the initial contents of pg_proc?
For the trivial ones, yes, and that is a fair point. sum(x) FILTER (WHERE true) -> sum(x) holds unconditionally; sum(x ORDER BY x) -> sum(x) holds for the exact types, though not for float, where the order is part of the result. They would also make a decent worked example of an aggregate support function - AFAICS int8inc_support is still the only in-core user of SupportRequestSimplifyAggref. Other optimisations of built-in aggregates in my pocket are an OLAP specialisation and would be pure overhead for other workloads. I doubt it makes sense to put a sum(numeric(N,M)) variant into core when it only pays off in a database whose numeric columns carry a typmod. >> With >> only CREATE OR REPLACE AGGREGATE available, an extension would have to >> restate >> the whole definition of pg_catalog.sum(numeric), including sfunc, >> combinefunc, >> and so on, and keep that copy updated with every major release. > > I'm not particularly on board with this goal: I think letting > extensions modify built-in aggregates is fraught with all sorts > of issues. (To name only one, there's no way for multiple > extensions to each attach new behaviors to the same aggregate.) Agreed as far as it goes, though the limitation is neither new nor specific to aggregates: prosupport is a single regproc column, so ALTER FUNCTION ... SUPPORT on a plain function is already last-one-wins. Whether a shared object should accept support functions from third parties at all is a fair question, and a larger one than this patch. So, with more evidence of practical need, we can return to it later. In my experience that is what applications ask for. An extension also back-ports the optimisation to already-released majors, which is a large part of why we write one. In the meantime we will do it by rewriting prosupport directly and adding the pg_depend entry - the only way I know to attach such an optimisation without touching the application. > I'm certainly not going to risk putting in half-considered > support for such a thing at this stage of the release cycle. > There's enough risk in adding what I proposed yesterday. No objection. It closes the case the thread started from and the pg_dump part is what v0 was missing. -- regards, Andrei Lepikhov, pgEdge
