enable_presorted_aggregate
enable_presorted_aggregate is a configuration parameter determining whether the planner will produce a plan which will provide rows which are presorted in the order required for the query's ORDER BY / DISTINCT aggregate functions.
enable_presorted_aggregate was added in PostgreSQL 16.
Default value
The default value for enable_presorted_aggregate is: on.
Usage
In PostgreSQL 16, commit 1349d279 improves the performance of ORDER BY / DISTINCT aggregates. However in some cases this may lead to the planner generating a less efficient plan than it previously would have done, so enable_presorted_aggregate can be used to restore the same behavior as in PostgreSQL 15|] and earlier.
Change history
- PostgreSQL 16
- added (commit 3226f472)
Examples
Using the tenk1 table from the PostgreSQL regression tests, in PostgreSQL 16 a query such as the following by default generates a plan with a sort key:
postgres=# EXPLAIN (COSTS off) SELECT SUM(two ORDER BY two), MAX(four ORDER BY four), MIN(four ORDER BY four), MAX(two ORDER BY two) FROM tenk1; QUERY PLAN ------------------------------- Aggregate -> Sort Sort Key: two -> Seq Scan on tenk1 (4 rows)
Setting enable_presorted_aggregate to off restores the behaviour see in PostgreSQL 15|] and earlier:
postgres=# SET enable_presorted_aggregate TO off;
SET
postgres=# EXPLAIN (COSTS off)
SELECT SUM(two ORDER BY two),
MAX(four ORDER BY four),
MIN(four ORDER BY four),
MAX(two ORDER BY two)
FROM tenk1;
QUERY PLAN
-------------------------
Aggregate
-> Seq Scan on tenk1
(2 rows)
References
- PostgreSQL 16 documentation: enable_presorted_aggregate
