enable_presorted_aggregate

• 19
• 18
• 17
• 16

使用 PostgreSQL 回归测试中的 tenk1 表，在 PostgreSQL 16 中，如下查询默认会生成一个带有排序键的计划。

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)

将 enable_presorted_aggregate 设置为 off 将恢复 PostgreSQL 15 及更早版本中的行为。

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)

• PostgreSQL 文档： enable_presorted_aggregate