Parameter · Query Tuning / Planner Method Configuration
enable_distinct_reordering
Enables reordering of DISTINCT keys.
- Default:
on - Type: bool
- Applies: Session (any user)
In plain words
SELECT DISTINCT a, b gives the same rows as SELECT DISTINCT b, a. New in PostgreSQL 18, the planner may swap the column order internally if an existing index or sort is already in the other order, saving a sort. This switch turns that trick off.
For DBAs and operators
Removing duplicates with a sort-based plan (Unique or a sorted GroupAggregate) needs input sorted on all DISTINCT keys, but any key order works. Before 18 the planner always used the written order (or the order of a matching ORDER BY). With this setting on (the default) it also considers orders that match the input's existing pathkeys, for example an index on (b, a).
Measured on PostgreSQL 18.6 with an index on (b, a) and hash aggregation disabled: SELECT DISTINCT a, b FROM t became Unique → Index Only Scan using t_b_a (cost 4,676). With enable_distinct_reordering = off the plan needed an explicit Sort Key: a, b under a Gather Merge (cost 18,638).
The output order of a DISTINCT without ORDER BY was never guaranteed; this feature makes that more visible. If an application relied on DISTINCT returning rows sorted in the written order, the plan change after upgrading can look like a bug: the fix is to add ORDER BY. This is the DISTINCT twin of enable_group_by_reordering (added in 17).
How to change it
Any user can change it for their session with SET.
See it in context
Related parameters
enable_group_by_reordering enable_sort enable_hashagg enable_incremental_sort