Parameter · Query Tuning / Planner Method Configuration
enable_group_by_reordering
Enables reordering of GROUP BY keys.
- Default:
on - Type: bool
- Applies: Session (any user)
In plain words
GROUP BY a, b and GROUP BY b, a produce the same groups. Since PostgreSQL 17 the planner may pick the column order that matches an index or an earlier sort, so it does not have to sort again. Turn it off and the keys are only reordered to match an ORDER BY, as before 17.
For DBAs and operators
For sort-based grouping (GroupAggregate, incremental sort) the input must be ordered on all grouping keys, in any order. With this setting on, the planner also tries an order that matches the input path's sort order (an index, a merge join output) by putting the matching keys first. Reordering the keys to match the query's ORDER BY happens whether this is on or off. Hash aggregation does not care about order, so the effect shows when sorting is cheaper than hashing or when hashing is not possible.
Measured on 18.6 with an index on (b, a) and enable_hashagg = off: GROUP BY a, b planned GroupAggregate, Group Key: b, a → Index Only Scan using t_b_a (cost 5,376). With reordering off: Group Key: a, b over a parallel Sort (cost 19,418).
Notes: the rows come out grouped in the new key order, so results without ORDER BY may be ordered differently than before an upgrade to 17. (An earlier, cost-based version of this feature was reverted during development; what shipped in 17 only matches the input's existing ordering.) The DISTINCT version arrived in 18 as enable_distinct_reordering.
How to change it
Any user can change it for their session with SET.
See it in context
Related parameters
enable_distinct_reordering enable_hashagg enable_incremental_sort enable_presorted_aggregate