Parameter · Query Tuning / Planner Method Configuration
enable_presorted_aggregate
Enables the planner's ability to produce plans that provide presorted input for ORDER BY / DISTINCT aggregate functions. Allows the query planner to build plans that provide presorted input for aggregate functions with an ORDER BY / DISTINCT clause. When disabled, implicit sorts are always performed during execution.
- Default:
on - Type: bool
- Applies: Session (any user)
In plain words
Aggregates like string_agg(x, ',' ORDER BY x) need their input sorted. Since PostgreSQL 16 the planner can sort the rows once, below the aggregate, so that it can use an index or incremental sort. With this off, each aggregate sorts its own input privately.
For DBAs and operators
Before 16, an aggregate with ORDER BY or DISTINCT inside it always sorted its rows internally, and the planner did not count that cost. Since 16, the planner can request the input already sorted by the GROUP BY keys plus the aggregate's ORDER BY keys, so an explicit Sort or Incremental Sort appears in the plan and can use an index.
Measured on 18.6, SELECT a, string_agg(pad, ',' ORDER BY pad) FROM t GROUP BY a: on, GroupAggregate over Incremental Sort (Sort Key: a, pad; Presorted Key: a), shown cost 23,944. Off: GroupAggregate directly over the index scan, shown cost 13,780. The second plan is not really cheaper: the per-group sorts inside the aggregate are simply not included in the estimate.
Turn it off if a query got slower after upgrading to 16 or later because the planner now sorts a large input up front when only a few groups needed sorting, or when several aggregates in one query need different orders (only one order can be provided).
How to change it
Any user can change it for their session with SET.
See it in context
Related parameters
enable_incremental_sort enable_sort enable_group_by_reordering work_mem