Two of the youngest members of the enable_* family, and a natural pair: both let the planner reorder the keys of a multi-key operation to make sorting cheaper, and both default on, user context, with the family caveat from enable_async_append — diagnostic probes, not production knobs.
The idea behind both is the same, and it’s a nice one. When you write GROUP BY a, b, c or SELECT DISTINCT a, b, c, the order you listed those columns in carries no semantic meaning — grouping and distinct-ing produce the same result regardless of which key is compared first. But the order matters enormously to cost. If the operation is done by sorting, comparing the cheap-to-compare, high-cardinality column first means most comparisons resolve on the first key and never touch the others; lead with an expensive collatable text column and you pay its comparison cost on every row. And if some other part of the query — an ORDER BY, an index already producing rows in a particular order — wants the data sorted a certain way, matching that order lets the planner reuse a sort it was going to do anyway, or skip one entirely via an incremental sort. So the planner, given freedom to permute keys whose order doesn’t affect correctness, can sometimes find a markedly cheaper arrangement than the one you typed.
Both optimizations share a crucial safety property worth stating plainly: they always keep the ordering you specified as one of the candidates, on the assumption you might know something the planner doesn’t. Reordering is something the planner may do when it finds a cheaper permutation, not something it forces on you.
enable_group_by_reordering
This one governs reordering the keys of a GROUP BY. Its history is a little tangled: the work was first committed during the PostgreSQL 16 development cycle, ran into complications, and the feature as it actually shipped is credited to PostgreSQL 17 (Andrei Lepikhov and Teodor Sigaev), with follow-up pathkey fixes arriving even in the 17.1 minor release. If you’re on 17 or 18, you have it; on 16 its status is murky enough that the honest answer is “don’t rely on it being there.” The planner uses statistics — the number of distinct values per key, the cost of each key’s comparison function — to estimate which key ordering minimizes total comparison work for a Group Aggregate, and will also try orderings that line up with a downstream ORDER BY so a later sort can be cheapened or removed. A Hash Aggregate doesn’t care about key order at all, so this only matters for the sort-based Group Aggregate path.
enable_distinct_reordering
The same trick applied to SELECT DISTINCT (and DISTINCT aggregates), reordering those keys to match an input path’s existing sort order or to minimize comparison cost. This is the newer of the two — PostgreSQL 18, Richard Guo — so it exists only on 18 and later. When the planner can reorder the distinct keys to align with pathkeys already available from below, it can avoid an explicit sort the naive key order would have required.
Symptoms, and the honest caveat
Be candid about scale first: these are micro-optimizations. They matter on large GROUP BY or DISTINCT operations with multiple keys of differing comparison cost or cardinality, and they are invisible on small ones. You are unlikely to go looking for either parameter, and that is fine.
The symptom that would send you to one of these is a multi-key GROUP BY or SELECT DISTINCT that regressed after a major-version upgrade — specifically an upgrade into 17 (group by) or 18 (distinct), where the reordering logic newly became available and the planner started choosing a key order that, in your particular case, turned out worse than the one you wrote. The tell is in EXPLAIN: a Group Key or sort order in the plan that doesn’t match the column order in your query, on a query that got slower. To test whether the reordering is the culprit, set the relevant parameter off for the session, re-run EXPLAIN (ANALYZE), and see whether forcing your written key order back into place restores the old performance. If it does, you’ve found the cause — and the right fix is usually not to leave the GUC off but to address why the planner mis-estimated, which here most often means stale or insufficient statistics on the grouped columns, since the optimization leans directly on per-column distinct-value counts and comparison costs to pick its ordering. Run ANALYZE, consider raising statistics targets on the columns involved, and let the planner make a better-informed choice rather than blinding it.
That said, this is one of the rarer corners where the docs themselves acknowledge a legitimate niche for disabling: because the reordering search adds planning work, a workload dominated by cheap, high-frequency multi-key grouping queries might — measurably, after benchmarking — prefer to skip the search. But that is a per-workload tuning decision proven with numbers, not a default to distrust. For essentially everyone, leave both on; reach for them only as session-scoped probes when a multi-key grouping or distinct query regresses and the plan shows a key order you didn’t write, and treat what you learn as a pointer to your statistics rather than a reason to disable the optimization for good.