PostgreSQL 14中通用表表达式（CTE）性能骤降问题求助

将PostgreSQL从V11升级至V14后，部分原本仅需数毫秒的CTE查询现在耗时长达数秒。

复现查询 #

WITH MyTable (id) AS (
    SELECT 1
    UNION ALL SELECT 1
    ... repeat 2000 times
    UNION ALL SELECT 1
)
SELECT count(*) from MyTable;

该查询在PostgreSQL 14中耗时约3秒，而在V11中仅需80毫秒。从执行计划可见，规划时间占比极高：

原始执行计划 #

Aggregate  (cost=33.71..33.72 rows=1 width=8) (actual time=105.145..140.802 rows=1 loops=1)
  ->  Append  (cost=0.00..28.89 rows=1926 width=0) (actual time=0.024..123.153 rows=1926 loops=1)
        ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.008..0.024 rows=1 loops=1)
        ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.008..0.024 rows=1 loops=1)
....
        ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.008..0.024 rows=1 loops=1)
Planning Time: 3066.151 ms
Execution Time: 148.261 ms

补充：使用MATERIALIZED关键字可将耗时从3秒降至1秒，但仍远不及V11中的80毫秒。添加该关键字后的执行计划如下：

添加MATERIALIZED后的执行计划 #

Aggregate  (cost=72.23..72.24 rows=1 width=8) (actual time=137.779..171.381 rows=1 loops=1)
  CTE mytable
    ->  Append  (cost=0.00..28.89 rows=1926 width=4) (actual time=0.025..119.213 rows=1926 loops=1)
          ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.009..0.024 rows=1 loops=1)
          ...
          ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.008..0.023 rows=1 loops=1)
  ->  CTE Scan on mytable  (cost=0.00..38.52 rows=1926 width=0) (actual time=0.042..120.818 rows=1926 loops=1)
Planning Time: 1123.003 ms
Execution Time: 181.167 ms

原因分析 #

PostgreSQL 12开始，默认CTE改为可内联优化（inlineable），而V11及之前版本中CTE默认是强制物化的。对于这种由2000个UNION ALL分支组成的CTE，优化器会尝试将其完全展开并进行优化，展开大量分支的过程会消耗极长的规划时间，导致整体查询耗时飙升。

解决方法 #

• 强制CTE物化：使用MATERIALIZED关键字，能降低部分规划时间，但仍不如V11高效。
• 关闭CTE内联优化：通过设置会话级或全局参数cte_inline = off，让CTE回到V11的默认物化行为，规划时间会大幅降低，查询耗时可接近V11水平。执行命令：

  SET cte_inline = off;
  

• 重构查询：避免大量UNION ALL拼接，改用更简洁的写法，比如用generate_series生成数据：

  WITH MyTable (id) AS (
      SELECT 1 FROM generate_series(1, 2000)
  )
  SELECT count(*) from MyTable;
  

  这种写法结构简单，优化器处理速度快，耗时会远低于原查询。

内容的提问来源于stack exchange，提问作者Drico