enable_presorted_aggregate

A system parameter controlling whether to create a plan for presorted aggregates
This entry relates to a PostgreSQL feature which is part of PostgreSQL 16, due to be released in late 2023.

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

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)

Categories

GUC configuration item, Planner

See also

enable_partitionwise_aggregate