enable_group_by_reordering

Definition PG 18 manual

Controls if the query planner will produce a plan which will provide GROUP BY keys sorted in the order of keys of a child node of the plan, such as an index scan. When disabled, the query planner will produce a plan with GROUP BY keys only sorted to match the ORDER BY clause, if any. When enabled, the planner will try to produce a more efficient plan. The default value is on.

How it works

enable_group_by_reordering controls whether the planner may choose reordering GROUP BY keys to match a child's pathkeys and exploit preordered input.

It is consulted while a plan is built. A session-level change is useful for comparing EXPLAIN alternatives, but an already cached plan is not retroactively rewritten; invalidation or replanning is required to observe a different choice.

The switch changes which candidate paths the planner may cost; it does not make the chosen method faster by itself. Its user context permits session- or transaction-local changes; newly performed or newly planned work sees the value.

Operational considerations

Treating enable_group_by_reordering as a query hint even though it affects every plan built in its scope.

Testing an already cached prepared plan and concluding that the setting has no effect.

Masking stale statistics or cardinality errors by disabling a plan method globally.

The switch changes which candidate paths the planner may cost; it does not make the chosen method faster by itself.

Workload guidance

OLAP: Large groupings can benefit from index or presorted input order. Evaluate sort and aggregate nodes, spill, planning time, and result ordering requirements before changing the switch for a reporting role.

OLTP: Keep the default on. If a plan regresses, compare session-local on/off plans and check whether reordered GROUP BY keys exploit a child's existing pathkeys or add planning work without avoiding a sort.

SMALL: Keep on unless a repeatable regression is isolated. Resolve stale statistics, missing ordering paths, and work_mem pressure before using off as a scoped workaround.