PostgreSQL9.6：CTE关联更新不使用索引，临时表却可利用

针对你遇到的这个更新操作耗时超5分钟的问题，结合你已经做过VACUUM、REPACK和统计信息更新的前提，我来拆解下可能的原因和对应的解决思路：

核心原因推测 #

虽然你的CTE本身执行很快，但关联到session表做更新时变慢，主要可能和PostgreSQL 9.6对CTE的处理机制、连接方式选择有关：

• CTE的"优化栅栏"特性：在PostgreSQL 9.6里，CTE是被强制物化的——也就是优化器会先完整执行CTE并把结果存到临时空间，再和外层的session表做关联。哪怕session表的id有主键索引，优化器也没办法把CTE的逻辑和更新操作做联合优化，这就导致如果CTE返回的数据量很大，后续的连接操作会变得低效。
• 连接方式的低效选择：如果CTE物化后的临时表没有id索引，PostgreSQL可能会选择哈希连接或者合并连接，而不是利用session表主键索引的嵌套循环连接，这会大幅增加关联的时间开销。
• 批量更新的WAL开销：如果更新的行数很多，PostgreSQL需要为每一行更新写入WAL日志，加上表数据块的读写、元数据维护，累计起来的开销会非常大。

具体解决办法 #

1. 把CTE替换成子查询（绕过优化栅栏） #

既然9.6的CTE会被强制物化，那把CTE的逻辑改成普通子查询，让优化器可以将子查询和更新操作做联合优化，比如利用session表的主键索引做高效的嵌套循环：

update session S 
set S.foo = Z.foo 
from (
    -- 这里放你原来的复杂CTE逻辑内容
) as Z 
where Z.id = S.id;

这种方式能让优化器直接评估子查询和session表的关联成本，选择最优的连接方式，大概率能提升速度。

2. 手动创建带索引的临时表 #

如果必须保留CTE的逻辑（比如需要复用结果），可以把CTE的结果写入带主键索引的临时表，再做更新：

-- 创建带主键索引的临时表存储CTE结果
CREATE TEMP TABLE session_temp AS 
-- 原来的复杂CTE内容
WITH PRIMARY KEY (id);

-- 利用临时表的id索引和session表主键索引做高效关联更新
update session S 
set S.foo = Z.foo 
from session_temp as Z 
where Z.id = S.id;

-- 清理临时表
DROP TABLE session_temp;

临时表的主键索引会让后续的关联操作直接用嵌套循环，避免全表扫描或哈希连接的低效开销。

3. 调整WAL配置缓解批量写入压力 #

如果更新的行数特别多，WAL日志的写入可能成为瓶颈，可以临时调整以下参数（仅在当前会话生效，操作完记得改回）：

-- 增大WAL缓冲区
SET wal_buffers = 64MB;
-- 增大检查点段数（9.6专属参数，新版本是max_wal_size）
SET checkpoint_segments = 64;

调整后再执行更新，能减少WAL写入的频繁刷盘次数，提升批量更新的效率。如果数据量极大，也可以考虑分批更新，比如每次更新1000行，避免一次性产生大量WAL日志。

4. 强制指定连接方式验证效果 #

可以临时关闭哈希连接，让优化器选择嵌套循环，验证是否是连接方式选错导致的慢：

-- 临时关闭哈希连接
SET enable_hashjoin = off;

-- 执行你的更新语句
WITH session_cte as ( -- 复杂的大型CTE，但该CTE本身执行很快 )
update session S set S.foo = Z.foo from session_cte as Z where Z.id = S.id;

-- 恢复哈希连接设置
SET enable_hashjoin = on;

如果速度有明显提升，说明优化器的连接方式选择有误，这时候优先用子查询的方式让优化器重新评估。

长期建议 #

PostgreSQL 9.6已经是比较老旧的版本了，后续的版本（比如12及以上）对CTE的优化做了很大改进——支持MATERIALIZED/NOT MATERIALIZED关键字，可以让你手动控制CTE是否物化，优化器也能更灵活地处理CTE逻辑。如果业务允许，升级到新版本能从根本上避免这类CTE优化限制的问题。

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