Aggregates in PostgreSQL aren't particularly efficient, computationally speaking. It shows most in a scenario where partial aggregation doesn't help: when aggregation only prepares data for the query, processing a large stream of rows and producing, at the output, a not-much-smaller set of groups and the aggregates computed over them. Variable-length types have it worst of all. And the typical example here is SUM(numeric). Built-in aggregates are obliged to handle values in their most general form, whereas in practice the data is often constrained; in the databases of ERP systems such as Microsoft Dynamics or NetSuite, monetary columns of type numeric are usually declared with a fixed scale.

Hence the idea of optimising aggregates by tuning them to the specific conditions under which they operate. Previously, this was possible only in a PostgreSQL fork. However, David Rowley recently added a new extension hook in core: SupportRequestSimplifyAggref (commit 42473b3b31, PostgreSQL 19, currently in beta), which lets you pass the planner custom aggregate-transformation logic via planner support functions (prosupport). The mechanism itself has existed since PostgreSQL 12, but it has only now been extended to aggregates. In core, the new request is applied modestly: it replaces COUNT(1) and COUNT(col) over a NOT NULL column with COUNT(*). For an extension, though, it allows almost anything to be done with an aggregate at planning time. That leaves room for interesting technical solutions.

So let's put it to work on a live example: a simple extension with a fairly primitive transformation.

The Redundant Sort

In real deployments, where queries are generated dynamically by an application, you occasionally meet redundant constructions like this one:

SUM(x ORDER BY x)

Indeed, the order of the values has no effect on the sum. So why perform a pointless sort?

To begin, let's check whether PostgreSQL really performs the unnecessary sort operation and estimate what removing it might give us. Below are two summation queries, with the sort and without it:

SELECT sum(x ORDER BY x) FROM
  (SELECT (random()*1E6)::numeric(16,2) AS x
     FROM generate_series(1,1E7))
OFFSET 1E7;
Time: 5716.916 ms (00:05.717)

SELECT sum(x) FROM
  (SELECT (random()*1E6)::numeric(16,2) AS x
     FROM generate_series(1,1E7))
OFFSET 1E7;
Time: 3664.739 ms (00:03.665)

A third of the query time is wasted, so in the ideal case we can gain a significant speedup. The transformation pays for itself: it fires once, at planning time, and shouldn't cost much. And for generic plans, the transformation result will also be reused from one execution to the next.

Writing a Prosupport Function

A support function is a C function with the SQL signature:

supportfn(internal) RETURNS internal.

The planner passes it a pointer to a request node, and it returns a result whose type depends on the request type, or a NULL pointer with the meaning "I can't help here". There are many request types: SupportRequestSimplify, SupportRequestCost, SupportRequestRows and others, all described in supportnodes.h.

What interests us is exactly SupportRequestSimplifyAggref: in it, the planner passes a pointer to the aggregate node Aggref and is ready to replace it with whatever we return. The rules of the game are simple: you must return a new node, you may not modify the original one, and if the transformation doesn't apply, return NULL. The set of request types grows from version to version, and receiving an input node with an unfamiliar structure is a normal occurrence for a support function.

Schematically, the function code looks quite simple:

Datum
sum_agg_support(PG_FUNCTION_ARGS)
{
    Node       *rawreq = (Node *) PG_GETARG_POINTER(0);

    if (IsA(rawreq, SupportRequestSimplifyAggref))

    {
        SupportRequestSimplifyAggref *req;
        Aggref     *aggref;
        Aggref     *newagg;
        ListCell   *lc;

        req = (SupportRequestSimplifyAggref *) rawreq;
        aggref = req->aggref;

        foreach(lc, aggref->args)
        {
            if (((TargetEntry *) lfirst(lc))->resjunk)
                PG_RETURN_POINTER(NULL);
        }

        switch (linitial_oid(aggref->aggargtypes))
        {
            case INT2OID:
            case INT4OID:
            case INT8OID:
            case NUMERICOID:
                newagg = copyObject(aggref);
                newagg->aggorder = NIL;

                foreach(lc, newagg->args)
                    ((TargetEntry *) lfirst(lc))->ressortgroupref = 0;

                PG_RETURN_POINTER(newagg);
            default:
                PG_RETURN_POINTER(NULL);
        }
    }
    PG_RETURN_POINTER(NULL);
}

In the simplest version, it's enough to determine that the aggregate sums values of a suitable type: integers or exact decimal types. In that case, we copy the node that implements the aggregate and return the copy, but without the ORDER BY clause. The old aggregate is left untouched, either for other extensions or in case the optimiser starts using the query tree in an alternative plan.

The resjunk check is a specific way to filter out expressions such as SUM(x ORDER BY y), where x and y are different columns. If the sort column doesn't appear in the summed expression, it shows up in the argument list with the resjunk flag, and such a case doesn't fit the current optimisation.

But that's not all. Production code, as usual, will be more complex, since it has to cover the various ways the function may be applied and survive incorrect ones as well. The code also has to be written so that the "fast path" - "I can't help here" - happens as early as possible. So the full code looks, of course, a bit more involved:

Datum
sum_agg_support(PG_FUNCTION_ARGS)
{
    Node       *rawreq = (Node *) PG_GETARG_POINTER(0);

    if (IsA(rawreq, SupportRequestSimplifyAggref))
    {
        SupportRequestSimplifyAggref *req;
        Aggref     *aggref;
        Aggref     *newagg;
        ListCell   *lc;

        req = (SupportRequestSimplifyAggref *) rawreq;
        aggref = req->aggref;

        Assert(aggref->aggkind == AGGKIND_NORMAL);

        if (aggref->aggorder == NIL || aggref->aggdistinct != NIL)
            PG_RETURN_POINTER(NULL);

        Assert(list_length(aggref->aggargtypes) == 1);

        if (list_length(aggref->aggargtypes) != 1)
            PG_RETURN_POINTER(NULL);

        switch (linitial_oid(aggref->aggargtypes))
        {
            case INT2OID:
            case INT4OID:
            case INT8OID:
            case NUMERICOID:
                break;
            default:
                PG_RETURN_POINTER(NULL);
        }

        foreach(lc, aggref->args)
        {
            if (((TargetEntry *) lfirst(lc))->resjunk)
                PG_RETURN_POINTER(NULL);
        }

        newagg = copyObject(aggref);
        newagg->aggorder = NIL;

        foreach(lc, newagg->args)
            ((TargetEntry *) lfirst(lc))->ressortgroupref = 0;

        PG_RETURN_POINTER(newagg);
    }
    PG_RETURN_POINTER(NULL);
}

Let's go through these checks.

The check for a DISTINCT clause. DISTINCT means the aggregate needs a sort in any case, so the optimisation would change nothing, at least until DISTINCT inside an aggregate learns to deduplicate by hashing. So far, no volunteers in sight. The comment:

We don't implement DISTINCT or ORDER BY aggs in the HASHED case (yet)

still sits in nodeAgg.c. ORDER BY inside aggregates arrived in commit 34d26872ed8, committed by Tom Lane in 2009 from a patch by Andrew Gierth.

Next, we verify that the support function was called for a "normal" aggregate. For ordered-set and hypothetical-set aggregates, for example:

percentile_disc(0.5) WITHIN GROUP (ORDER BY x)

The aggorder field can't be removed without risking changes to its semantics. Of course, the SUM() aggregate can't be used with WITHIN GROUP by definition, so here we guard against the case where a user attaches our prosupport function to an incompatible aggregate. The next check - that there's exactly one input argument - exists for the same reason.

The line zeroing the ressortgroupref field is needed to remove the "sorted" mark that was set on column x: there's no sort any more, so the mark must be cleared, or later consistency checks on the plan tree would find an inconsistency and abort the query with an error.

Attaching It to sum()

If an extension wants to add a custom prosupport helper, it simply runs the DDL: CREATE FUNCTION ... SUPPORT or ALTER FUNCTION ... SUPPORT. With aggregates, a surprise awaits us:

ALTER FUNCTION pg_catalog.sum(numeric) SUPPORT sum_agg_support;
ERROR:  "pg_catalog.sum" is an aggregate function

There's simply no DDL in community PostgreSQL that would let you hang a support function on a built-in aggregate: the feature formally exists in core, but from outside the core it's unreachable. A patch adding a SUPPORT option to CREATE AGGREGATE and an ALTER AGGREGATE ... SUPPORT form has been proposed on pgsql-hackers. Since it hasn't landed in core yet, we'll do the DDL's job by hand here. In addition to the C function, the extension declares a pair of plpgsql helpers - agg_support_attach() and agg_support_detach(). The essence of attach is two entries in the system catalogue - exactly the ones the DDL would have made:

UPDATE pg_catalog.pg_proc
   SET prosupport = 'sum_agg_support'::regproc
WHERE oid = 'pg_catalog.sum(numeric)'::regprocedure;

-- a NORMAL dependency: sum(numeric) now depends on sum_agg_support
INSERT INTO pg_catalog.pg_depend
(classid, objid, objsubid, refclassid, refobjid, refobjsubid, deptype)

VALUES ('pg_catalog.pg_proc'::regclass, 'pg_catalog.sum(numeric)'::regprocedure, 0,
  'pg_catalog.pg_proc'::regclass, 'sum_agg_support'::regproc, 0, 'n');

The dependency deptype = 'n' (NORMAL) in pg_depend means the object can't be dropped while something references it. We could get by without the dependency, but not for long, and we'll see why in a moment.

We attach our prosupport function directly to the built-in sum(numeric) and run our query:

SELECT agg_support_attach('pg_catalog.sum(numeric)'::regprocedure);
EXPLAIN (VERBOSE, COSTS OFF) SELECT sum(x ORDER BY x) FROM t;

Aggregate
   Output: sum(x)
   ->  Seq Scan on public.t

Now let's show why writing the dependency in pg_depend isn't a mere formality. Without the dependency, running DROP EXTENSION agg_support; leaves the prosupport reference of the sum(numeric) aggregate pointing into a void, after which every query containing sum(numeric) fails at planning with cache lookup failed for function NNNNN, until somebody zeroes the field back. With the dependency, the system itself won't let you shoot yourself in the foot:

DROP EXTENSION agg_support;
ERROR:  cannot drop function sum(numeric) because it is required by the database system

The message isn't the most informative - the dependency machinery followed our entry to the pinned object sum(numeric) and refused to touch it - but the protection is solid: even CASCADE won't help. You put things back in the usual order: first call agg_support_detach('pg_catalog.sum(numeric)') - the symmetric helper that zeroes prosupport and deletes the pg_depend entry - then DROP EXTENSION.

Note that custom prosupport functions don't survive pg_dump/restore or pg_upgrade in core. If the new cluster needs the same optimisation, the attach has to be repeated.

Looking at the Result

So, let's check whether our extension works. The build is standard for extensions (PostgreSQL 19+ is required). We create the extension in the database and fix the reference to it in the system catalogue:

psql -c "CREATE EXTENSION agg_support"
psql -c "SELECT agg_support_attach('pg_catalog.sum(numeric)'::regprocedure)"

Let's take a table with a numeric column and compare the plans. Before attaching, the built-in sum sorts, as promised:

EXPLAIN (VERBOSE, COSTS OFF) SELECT sum(x ORDER BY x) FROM t;

 Aggregate
   Output: sum(x ORDER BY x)
   ->  Sort
         Output: x
         Sort Key: t.x
         ->  Seq Scan on public.t
               Output: x

After the attach, the planner called our support function - and no trace of ORDER BY remains; the Sort node vanished with it:

EXPLAIN (VERBOSE, COSTS OFF) SELECT sum(x ORDER BY x) FROM t;

 Aggregate
   Output: sum(x)
   ->  Seq Scan on public.t
         Output: x

SELECT sum(x ORDER BY x) = sum(x) AS same FROM t;
 same
------
 t

What's Still Missing

The gain is exactly what we estimated at the start: the query from the introduction - the one over 10 million rows - fits into 3.7 seconds instead of 5.7 after the support function is attached; a third of the time is gone. And this isn't "almost as fast as without the sort" - it's literally the same time that sum(x) written without ORDER BY takes: the sort disappeared from the execution profile, not only from the plan. The query itself is untouched, the core isn't patched, and the application knows nothing.

By allowing the transformation of an aggregate function, PostgreSQL has opened the way for developers' imagination - you can do anything you like with an aggregate. This makes it possible to "clean up" poorly or redundantly generated queries and to tune aggregates to the specific conditions under which a DBMS runs. Here we've walked through a simple example that merely removes a query generator's sloppiness. A more serious example would be substituting an optimised version of SUM() when the input numeric is known in advance to have a provably small precision and scale - see the prototype extension pg_numeric_agg_support on GitHub.

Only one small thing is missing - the DDL, so that extensions can use this mechanism without reaching into the system catalogue by hand. If this is your area, join the patch discussion on pgsql-hackers.