enable_group_by_reordering

enable_group_by_reordering — Enables reordering of GROUP BY keys. Observed in PG17–19 Beta 3; its last measured boot default is on in PG19 Beta 3, with user context. This is a beta-snapshot fact and can change before PostgreSQL 19 GA.

Note

Fact — official short description: “Enables reordering of GROUP BY keys.”

Identity

Type , Valuebool: Upstream pg_settings type
Context , Valueuser: Settable by an ordinary user
Unit , Value—: Raw unit
Range , Value—: Raw limits in the last observed version
Enum values , Value—: — for non-enum types
Category , ValueQuery Tuning / Planner Method Configuration: Upstream classification
Latest boot value , Valueon: on

Lifecycle

Fact	Value
First observed	PG17
Present in	PG17–19 Beta 3
Removed in	No
Introduction commit	`db0d67db2401` — Optimize order of GROUP BY keys
Commit date	2022-03-31
Discussion	thread 1 · thread 2

Default history

Measured PG9.0–19 Beta 3 boot defaults
Versions	Raw `boot_val`	Unit	Human value
PG17–19 Beta 3	`on`	—	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.

Tuning advice

Tip

Advice. These are workload-specific starting points and must be validated with measurements.

Workload	Guidance
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.
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.
Small nodes	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.

Pigsty

Values use the fixed 8-vCPU, 32-GiB, 100-GiB SSD fixture and render the current Pigsty templates for PG19 Beta 3; this does not assert current Pigsty support for that historical or beta release.

Template	Effective value	Versus upstream boot	Source expression
OLTP	Unmodified	—	—
OLAP	Unmodified	—	—
CRIT	Unmodified	—	—
TINY	Unmodified	—	—

Caution

Advice — pending human review. Fact from the current Pigsty template projection: OLTP: PG17–19 Beta 3 unmodified; OLAP: PG17–19 Beta 3 unmodified; CRIT: PG17–19 Beta 3 unmodified; TINY: PG17–19 Beta 3 unmodified. No Pigsty-specific rationale is inferred from an absent override.

Common pitfalls

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.

enable_sort · enable_incremental_sort · enable_hashagg · enable_presorted_aggregate