PostgreSQL CTE 与递归查询

做后端的同学,几乎都被「层级数据」折磨过。查某员工的所有下属(包括下属的下属)、渲染多级菜单、拉出一条评论的整棵回复树、分析依赖包的传递链路——这些问题有一个共同特征:深度未知,可能 N 层。

你的第一反应可能是 JOIN。但 JOIN 是「定深」的,查 3 层要写 3 个 JOIN,查 10 层你就疯了。换个思路在应用层循环查库?经典的 N+1 查询,性能原地爆炸。

PostgreSQL 给的答案是递归 CTE:一条 SQL 把任意深度的树/图遍历干完。本文把 CTE 的用途、递归写法、实战陷阱和版本改动一次讲清。

一、CTE 是什么:给子查询起个名字

CTE(Common Table Expression,通用表表达式)就是 WITH 子句。它的第一个用途不是递归,而是可读性——把一个子查询命名,然后在主查询里反复引用,相当于 SQL 里的「局部变量」。

来看一个不递归的例子,「找出每个部门薪资最高的员工」:

WITH dept_max AS (
  SELECT department_id, MAX(salary) AS max_salary
  FROM employees
  GROUP BY department_id
)
SELECT e.name, e.department_id, e.salary
FROM employees e
JOIN dept_max d
  ON e.department_id = d.department_id
 AND e.salary = d.max_salary;

dept_max 就是命名的子查询。好处有三:SQL 不再是「一坨嵌套」、可以在多处引用、给中间结果起了有意义的名字。经验法则:子查询超过两层嵌套,或者同一子查询要被引用两次以上,就该上 CTE。 它不必然改变执行计划,纯粹是写法和维护性的胜利。

二、递归 CTE:anchor + recursive 两段式

递归 CTE 是 CTE 的进阶用法,专门处理层级、树、图这类自引用数据。语法骨架是:

WITH RECURSIVE name AS (
  -- anchor:基准查询,递归的起点
  SELECT ...
  UNION ALL
  -- recursive part:自引用,迭代展开
  SELECT ... FROM name WHERE ...
)
SELECT * FROM name;

两个关键部分:

• anchor(基准):不引用自身的初始查询,递归从这开始,就像函数里的 base case。
• recursive part(递归部分):FROM name 引用自己,把上一轮的结果当输入,生成下一层。UNION ALL 把每一轮的结果拼起来。

工作原理是迭代,不是真正的递归调用:先跑 anchor 得到第 0 层 → 把第 0 层喂给 recursive part 得到第 1 层 → 再喂得第 2 层……直到某轮没有新行产生,迭代停止,所有层 UNION ALL 合并后返回。所以递归 CTE 不会无限递归——只要数据有底,它自己会收敛。

三、实战一:组织架构,挖出某人的全部下属 N 层

经典场景。表结构自引用,manager_id 指向上级:

CREATE TABLE employees (
  id         SERIAL PRIMARY KEY,
  name       TEXT NOT NULL,
  manager_id INTEGER REFERENCES employees(id)
);

任务:给定一个领导 id,查出他下面所有人(直接下属 + 下属的下属,不限层数),并标出层级。

WITH RECURSIVE subordinates AS (
  -- anchor:起点就是这个领导本人
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE id = 1

  UNION ALL

  -- recursive:以上一轮的人为上级,找他们的直接下属
  SELECT e.id, e.name, e.manager_id, s.depth + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates ORDER BY depth, id;

depth 列让你知道每个人在第几层,前端渲染组织树时直接用。想反过来「往上查所有祖先」?把 anchor 换成最底层员工,recursive part 的 JOIN 条件换成 JOIN subordinates s ON e.id = s.manager_id 即可。方向由 JOIN 条件决定,这是递归 CTE 最容易绕晕的地方。

四、实战二:评论树的层级展开

帖子下面的评论是一棵树:帖子 → 回复 → 回复的回复。表里 parent_id 指向父评论:

CREATE TABLE comments (
  id        SERIAL PRIMARY KEY,
  post_id   INTEGER NOT NULL,
  parent_id INTEGER REFERENCES comments(id),
  author    TEXT,
  content   TEXT
);

查某个帖子下的整棵评论树,并按「父评论紧跟所有子孙、同层按 id」的方式排出顺序:

WITH RECURSIVE comment_tree AS (
  SELECT id, post_id, parent_id, author, content,
         0      AS depth,
         ARRAY[id] AS path
  FROM comments
  WHERE post_id = 42 AND parent_id IS NULL   -- 根评论

  UNION ALL

  SELECT c.id, c.post_id, c.parent_id, c.author, c.content,
         ct.depth + 1,
         ct.path || c.id                       -- 路径累加
  FROM comments c
  JOIN comment_tree ct ON c.parent_id = ct.id
)
SELECT * FROM comment_tree ORDER BY path;

这里有个值得记住的小技巧:ARRAY[id] 维护从根到当前节点的路径,path || c.id 把父路径接上当前 id。最后 ORDER BY path 一次排序,就能得到「父评论紧跟所有子孙、兄弟按 id 排」的顺序,前端做缩进展示几乎是免费的。没有 path 列的话,跨层排序要多花不少功夫。

五、其他场景:分类树与依赖链

模式高度一致,换个表名就是新功能:

• 多级分类树:商品分类 category(parent_id),anchor 取顶级分类(parent_id IS NULL),recursive 沿 parent_id 往下扩,渲染成菜单。
• 依赖链:包 package(depends_on),A 依赖 B、B 依赖 C。anchor 从 A 开始,recursive 沿 depends_on 跳到下一跳,直到没有依赖。同样用 path 列记录完整链路,排查循环依赖时一目了然。

只要数据是「自己指向自己」,递归 CTE 几乎都能上。

六、MATERIALIZED 与 PG 12+ 的内联改动

这是写 CTE 必须知道的一个版本分水岭(实际从 PostgreSQL 12 引入内联,到 14+ 已是稳定行为,也是社区常说的「PG 14+ 默认行为」)。

• PG 12 之前:CTE 是**物化(MATERIALIZED)**的——整段 CTE 先执行一次,结果存成临时表,外层再读它。相当于一道优化屏障,外层 WHERE 条件推不进去。
• PG 12 起:CTE 默认内联(inlined)——优化器把它当视图展开,能下推谓词、能合并、能选更好的计划。绝大多数场景更快。

但有些情况你就是想要物化(比如某个昂贵的 CTE 要被引用多次,物化后只算一次)。这时显式加关键字:

WITH expensive AS MATERIALIZED (
  SELECT ... -- 复杂聚合,只想算一次
),
cheap AS NOT MATERIALIZED (
  SELECT ... -- 简单过滤,内联更划算
)
SELECT ... FROM expensive e1, expensive e2 WHERE ...;

MATERIALIZED 强制物化、NOT MATERIALIZED 强制内联,优化器听你的。升级到 12+ 后如果发现某条 CTE 查询计划变差,第一反应就是想想是不是该手动 MATERIALIZED。

七、三个坑:死循环、性能、深度

递归 CTE 不会自己检测环。如果你的数据有环(比如 A 的上级是 B,B 的上级又是 A),递归会一直生成新行,直到撑爆资源。防护手段:

1. path 列 + 防环条件:用前面评论树那个 path 数组,recursive part 加 WHERE NOT c.id = ANY(ct.path)(或对 path && ARRAY[c.id] 取反),访问过的节点不再进。这是生产里最实用的防环写法。
2. 加 LIMIT:调试时 SELECT * FROM ... LIMIT 1000,先止血再说。
3. 数据层面保证无环:组织架构、分类树这类业务上本就该是 DAG/树,源头加约束最干净。

性能上要有预期:递归 CTE 不是银弹。 当层级极深、表数据巨大时,每一轮迭代都是一次扫描,总成本可能不如「应用层批量查 + 内存里组树」划算。判断标准很简单——看 EXPLAIN ANALYZE 的实际行数和耗时,别凭感觉。

八、CTE vs 子查询 vs 窗口函数

三者经常被混为一谈,实际定位差很多:

• CTE:强在可读、可复用、能递归。复杂查询拆解、层级遍历,首选它。
• 子查询:简单的一次性过滤/聚合(比如 WHERE id IN (...)),没必要上 CTE,直接写更利索。
• 窗口函数:跨行计算排名、累加、分组 Top-N(ROW_NUMBER() OVER (...)),解决的是「行与行的关系」,跟层级无关。

一句话:要递归、要拆分复杂查询,用 CTE;要简单过滤,用子查询;要跨行聚合,用窗口函数。 别拿着窗口函数当递归用,也别给一行子查询硬套 WITH。

小结

• CTE 本质是命名的、可复用的子查询;非递归用法解决可读性,递归用法解决层级遍历。
• 递归 CTE = anchor + UNION ALL + 自引用 recursive part,底层是迭代收敛,不是栈式递归。
• 方向由 JOIN 条件决定,改 JOIN 就能从「往下查子孙」切换到「往上查祖先」。
• 维护一个 path 数组列,既防环又能一次性排出层级顺序,生产里几乎标配。
• PG 12 起默认内联,到 14+ 已是稳定默认,要强制物化就加 MATERIALIZED——升级后执行计划变差先查这个。

金句:JOIN 是定深度的钻头,递归 CTE 才是能自己往下找的钻头——给 SQL 一点「记忆」,它就能遍历整棵树。