enable_presorted_aggregate is on, it has been on since PostgreSQL 16 introduced it, and the single most useful thing you will ever do with it is turn it off for exactly one query.
Default on, context user: settable per session, per role, per database, or inline in a single transaction. That last option is the entire recommendation, so hold onto it.
The parameter governs aggregates that require sorted input: the ones with an ORDER BY or a DISTINCT inside the call. array_agg(x ORDER BY y), string_agg, the ordered-set aggregates such as percentile_cont, count(DISTINCT x). Each of those has to see its input in a particular order before it can compute anything. The question this parameter answers is not whether that sort happens. It is where it happens.
Before PostgreSQL 16, the sort lived inside the aggregate. The executor sorted each aggregate’s input as part of computing it, separately for each distinct ordering the aggregates asked for, and none of it appeared in EXPLAIN as a Sort node. You would see a bare Aggregate over a Seq Scan and simply have to know that sorting was going on underneath.
PostgreSQL 16 taught the planner to hoist one of those orderings out and satisfy it from below the aggregate, as presorted input the aggregate consumes directly. Counterintuitively, that makes the plan longer: with the optimization on you get a visible Sort feeding the Aggregate; with it off you get the old bare Aggregate and its hidden internal sorts.
1 -- enable_presorted_aggregate = on (default) 2 Aggregate 3 -> Sort 4 Sort Key: created_at 5 -> Seq Scan on ledger 6 7 -- enable_presorted_aggregate = off 8 Aggregate 9 -> Seq Scan on ledger
The reason to want the longer plan is that a hoisted ordering can be free. If a btree index already provides the order the aggregate needs, the planner can feed the aggregate from an ordered scan and skip the sort entirely; with the optimization off, the aggregate sorts internally even when an index sitting right there could have handed it the rows already ordered. For SELECT array_agg(amount ORDER BY created_at) FROM ledger with an index on created_at, that is a sort you stop paying for.
So why is there an off switch at all? Because the planner has to guess which ordering to hoist, and the cost model can guess badly. When a query has several ordered aggregates wanting different orderings, only one of them gets the presorted input; the rest still sort internally, and the planner has now committed you to a full sort of the entire input in the chosen order whether or not that was the cheaper arrangement. Occasionally it isn’t, and a query that ran fine on PostgreSQL 15 gets slower on 16 with nothing in the query having changed. That specific regression is why the parameter exists. Turning it off restores the pre-16 behavior exactly.
Which is what makes the user context the point. If you find the query, and EXPLAIN (ANALYZE, BUFFERS) confirms that the hoisted Sort is costing more than the internal sorts would, set enable_presorted_aggregate = off for that statement or that transaction and leave the rest of the system alone. Turning it off in postgresql.conf to fix one plan re-pessimizes every other ordered-aggregate query in the database, including all the ones quietly getting free orderings from an index, to buy back a single query you could have fixed in one line.
Leave it on. The reason it can be turned off is one misplanned query, not a configuration philosophy; if you ever meet that query, turn it off for that query and nothing else.