enable_group_by_reordering — PostgreSQL configuration parameter

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.

At a glance

What it does

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.

(Description quoted from the official PostgreSQL documentation.)

How to apply a change

Can be set per session with SET, per role/database with ALTER ROLE/DATABASE ... SET, or globally in postgresql.conf.

Inspect the current value and source with SHOW enable_group_by_reordering; or SELECT name, setting, unit, context, source FROM pg_settings WHERE name = 'enable_group_by_reordering';.

Tuning guidance

This is a diagnostic switch, not a production tuning knob. Turn it off briefly (per session) to confirm why the planner avoids or prefers a plan, then leave it on. Forcing it off in production masks bad estimates instead of fixing them — fix statistics, costs or indexes instead.

Reference

PostgreSQL documentation — enable_group_by_reordering.