CTE：从优化栅栏到可内联

准备中…

WITH 子句最常被当作「给子查询起个名字」的可读性工具。但它在 Postgres 里还有一层执行语义，而且这层语义在 PG 12 发生过一次破坏性变化。

PG 12 之前：WITH 是一道优化栅栏

12 之前，WITH 定义的 CTE 总是被物化（materialized）——先完整算出结果存进临时结果集，再供主查询使用。优化器不会把外层的过滤条件推进去。

12 开始，如果一个 CTE 只被引用一次且没有副作用，Postgres 会把它内联（inline）进主查询，像普通子查询一样参与优化。

对比一下就很清楚。先看默认行为：

WITH o AS (SELECT * FROM orders) SELECT count(*) FROM o WHERE user_id = 42

计划里只有一个 Seq Scan on orders，实际行数 10——WHERE user_id = 42 被推进了 CTE 里面。

现在强制物化：

WITH o AS MATERIALIZED (SELECT * FROM orders) SELECT count(*) FROM o WHERE user_id = 42

多了一个 CTE Scan 节点，而 Seq Scan 的实际行数变成 10000——整张表被完整物化，然后才在外面过滤掉 9990 行。执行时间大约差 6 倍。

什么时候该主动物化

内联不总是更好。规则大致是：

情况	该怎么做
CTE 被引用一次，外层有强过滤条件	用默认（内联），让谓词下推
CTE 里是昂贵的聚合，且被引用多次	Postgres 自动物化，无需显式声明
CTE 结果很小，但重算很贵，只引用一次	显式 `MATERIALIZED`
CTE 里有 `INSERT` / `UPDATE` / `DELETE`	总是物化，且只执行一次

被引用多次时自动物化，可以在计划里看到两个 CTE Scan：

WITH per_user AS (SELECT user_id, sum(amount) AS s FROM orders GROUP BY user_id)
SELECT a.user_id FROM per_user a JOIN per_user b ON b.user_id = a.user_id + 1 WHERE a.s > b.s LIMIT 5

Aggregate 只算了一次，两个 CTE Scan 共享它的结果——这正是 CTE 相比重复写子查询的真正价值。

递归 CTE

递归 CTE 的结构永远是这三段：

WITH RECURSIVE 名字 AS (
    非递归项                -- ① 种子：从哪开始
  UNION ALL                 -- ② UNION ALL 保留重复，UNION 去重（去重会慢）
    递归项 ... JOIN 名字 ... -- ③ 每轮拿上一轮的结果继续推
)

先建一棵分类树：

DROP TABLE IF EXISTS categories;
CREATE TABLE categories (id int PRIMARY KEY, parent_id int REFERENCES categories(id), name text);
INSERT INTO categories VALUES
(1, NULL, '全部'), (2, 1, '图书'), (3, 1, '工具'),
(4, 2, '技术'), (5, 2, '文学'), (6, 4, '数据库'), (7, 6, 'PostgreSQL');

⌘/Ctrl + Enter

向下遍历，同时算出深度和路径：

WITH RECURSIVE tree AS (
SELECT id, parent_id, name, 1 AS depth, name::text AS path
FROM categories
WHERE parent_id IS NULL

UNION ALL

SELECT c.id, c.parent_id, c.name, t.depth + 1, t.path || ' > ' || c.name
FROM categories c
JOIN tree t ON c.parent_id = t.id
)
SELECT depth, repeat('　', depth - 1) || name AS 层级, path
FROM tree
ORDER BY path;

⌘/Ctrl + Enter

把 JOIN 的方向反过来，就是向上找祖先链：

WITH RECURSIVE ancestors AS (
SELECT id, parent_id, name FROM categories WHERE id = 7

UNION ALL

SELECT c.id, c.parent_id, c.name
FROM categories c
JOIN ancestors a ON c.id = a.parent_id
)
SELECT * FROM ancestors;

⌘/Ctrl + Enter

数据里只要有环（A 的父是 B，B 的父是 A），上面的查询就会永远跑下去。生产环境的标准防护是在路径里记录已访问节点：

SELECT ... , t.visited || c.id
FROM categories c JOIN tree t ON c.parent_id = t.id
WHERE NOT c.id = ANY(t.visited)

PG 14 起还有更简洁的 CYCLE id SET is_cycle USING path 语法。

递归 CTE 不止能遍历树，生成序列也很常用：

WITH RECURSIVE d(day) AS (
SELECT date '2024-06-01'
UNION ALL
SELECT day + 1 FROM d WHERE day < date '2024-06-07'
)
SELECT day FROM d;

⌘/Ctrl + Enter

（不过生成连续序列，generate_series 更直接——递归 CTE 的主场是形状不规则的推导。）

数据修改 CTE 与它的快照陷阱

WITH 里可以放 INSERT / UPDATE / DELETE，配合 RETURNING 能把「改数据」和「拿结果」合成一条语句：

WITH cancelled AS (
UPDATE orders SET status = 'cancelled'
WHERE id IN (2, 4) AND status <> 'cancelled'
RETURNING id, user_id, amount
)
SELECT count(*) AS 取消单数, sum(amount) AS 涉及金额 FROM cancelled;

⌘/Ctrl + Enter

这里有一个非常容易踩的陷阱：同一条语句里的所有子语句共享同一个快照。也就是说，主查询看不到 CTE 里的修改。

WITH del AS (
DELETE FROM order_items WHERE id <= 5 RETURNING id
)
SELECT (SELECT count(*) FROM del)                          AS CTE里删掉的行数,
     (SELECT count(*) FROM order_items WHERE id <= 5)     AS 主查询仍然看到;

⌘/Ctrl + Enter

删除确实发生了（del 返回 5 行），但主查询对 order_items 的扫描仍然看到那 5 行——因为它用的是语句开始时的快照。

本节验收

先自己回答，再点开对照。

PG 12 对 WITH 的执行行为做了什么改变？升级时可能踩到什么？

12 之前 WITH 总是物化，是一道优化栅栏，外层的过滤条件推不进去。12 之后，只被引用一次且无副作用的 CTE 会被内联，像普通子查询一样参与优化。

升级时的坑：老代码里可能故意用 WITH 当栅栏（「先把这段算完，别让优化器拆开重排」）。升到 12 之后这些 CTE 被内联，执行计划可能剧变——有时更快，有时因为优化器做了错误的重排而慢几个数量级。

常见错误：以为这是个纯粹的性能提升，升级前不用管。实际上它是行为变更，需要在升级前把依赖栅栏语义的 CTE 显式标上 AS MATERIALIZED。这类问题不会报错，只会在某天突然表现为「某个报表查询跑不完了」。

（回到「PG 12 之前：WITH 是一道优化栅栏」）

什么情况下应该显式写 AS MATERIALIZED？什么情况下 Postgres 会自动物化？

自动物化发生在两种情况：CTE 被引用多次，或者 CTE 里含 INSERT/UPDATE/DELETE。

该显式写 MATERIALIZED 的情况：CTE 只被引用一次，但内部计算很贵、结果很小，而内联会导致这段计算被重复执行或被优化器错误地重排。

常见错误：不加区分地给所有 CTE 加 MATERIALIZED，以为「物化 = 缓存 = 更快」。物化会阻止谓词下推——本节那个例子里，内联版只扫出 10 行，物化版扫了全部 10000 行再过滤，慢了约 6 倍。默认（内联）在绝大多数情况下是对的。

（回到「什么时候该主动物化」）

递归 CTE 的三个组成部分是什么？UNION 和 UNION ALL 在这里的区别是什么？

三部分：非递归项（种子，从哪开始）、UNION ALL 或 UNION、递归项（引用 CTE 自身，拿上一轮结果继续推）。

UNION 会对每轮结果去重，UNION ALL 不去重。去重要付出排序或哈希的代价，但在图有环时能防止无限递归。

常见错误一：以为 UNION 能安全地处理带环的图。它只能去掉完全相同的行——如果递归项里带了 depth 或 path 这类每轮都在变的列，环上的节点每轮产生的行都不同，UNION 一样拦不住。真正的防护是在路径数组里判重，或用 PG 14 的 CYCLE 子句。

常见错误二：把递归项写成引用 CTE 两次（比如自连接）。Postgres 要求递归项只能引用 CTE 自身一次，否则报错。

为什么数据修改 CTE 的结果对同一语句的主查询不可见？

因为一条语句只取一次快照，语句内部所有子部分共享它。CTE 里的 DELETE 产生的新行版本带的是当前事务号，而主查询用的是语句开始前的那个快照——按可见性规则（阶段三），那些修改对它不存在。

常见错误：据此写出「先删再查」「先插再统计」的单语句逻辑，本地小数据量测试时“看起来对了”。它从来就没对过——只是恰好没触发差异。真要顺序执行就用两条语句。

还有一个相关误解：以为多个数据修改 CTE 之间有执行顺序。它们的执行顺序是未定义的，都基于同一快照，互相看不见对方的修改。