postgresql 15中递归cte的性能问题主因是重复全表扫描、manager_id缺失索引及未设深度限制；需建idx_employees_manager_id索引、用level

直接说结论：PostgreSQL 15 中递归自连接（WITH RECURSIVE）的时空开销主要来自重复扫描、无索引连接和未设深度限制，不是语法本身慢，而是写法没对齐执行引擎特性。

为什么递归CTE会爆内存或超时

常见错误现象是查询跑着跑着卡住、OOM被kill，或者 EXPLAIN ANALYZE 显示 Recursive Union 节点下反复出现 Seq Scan on employees —— 这说明每次迭代都在全表扫一遍，而非走索引定位。

• 递归项里用 JOIN 但连接列（如 manager_id）没索引 → 每轮都 O(n) 扫描，k 层递归变成 O(k·n)
• 没加 MAX_RECURSION_DEPTH（虽 PostgreSQL 不原生支持，但必须手动加 level 条件）→ 遇到环形引用或深树直接无限展开
• 用了 UNION 而非 UNION ALL → 每轮做去重哈希，内存翻倍且可能提前中断
• 锚点返回多行（如 WHERE id IN (5,6,7)）→ 后续每轮结果集呈指数膨胀，不是线性增长

必须建的索引类型与字段组合

仅在 id 上建主键索引远远不够。递归的关键路径是“从父找子”，即 e.manager_id = st.id，所以索引必须让数据库能高效反查“谁的 manager_id 等于当前 id”。

• 核心索引：CREATE INDEX idx_employees_manager_id ON employees (manager_id) —— 解决递归项中 JOIN 的驱动效率
• 进阶优化：CREATE INDEX idx_employees_mgr_id_with_cols ON employees (manager_id, id, name, level) —— 若递归中还需读取这些字段，可避免回表
• 切忌只建 (id, manager_id)：这是为“从子查父”优化的，和递归方向相反，基本无效

如何安全控制递归深度与结果规模

PostgreSQL 不提供 MAXRECURSION 类似 SQL Server 的语法，必须靠显式条件拦截，否则生产环境极易因脏数据（如 manager_id 指向自己、环状汇报关系）导致死循环。

• 强制加层级计数：SELECT ..., 1 AS level 在锚点，st.level + 1 在递归项，并在 WHERE 中加 st.level （按业务最大合理层级设，通常 ≤6）
• 用 LIMIT 不起作用：它只限制最终输出，不阻止中间迭代；必须把约束写进递归项的 WHERE 子句
• 若需排除环路，可加路径数组记录已访问节点：ARRAY[id] AS path 和 NOT e.id = ANY(st.path)，但会显著增加内存占用，仅在必须时启用

物化还是内联？CTE 递归的执行模式陷阱

WITH RECURSIVE 在 PostgreSQL 中默认是“内联展开”（inlining），不是物化临时表。这意味着如果主查询多次引用该 CTE，每次都会重跑整个递归 —— 但更隐蔽的问题是：优化器有时会错误地将递归部分下推到 JOIN 下方，破坏迭代顺序。

• 验证是否真递归：看 EXPLAIN ANALYZE 输出中是否有 Recursive Union 节点，且其子节点包含 WorkTable Scan（表示用了工作表）
• 避免被误优化：递归 CTE 后不要紧跟复杂 GROUP BY 或窗口函数，否则优化器可能放弃递归计划，退化成多次自连接
• 若需复用结果，宁可先 CREATE TEMP TABLE AS 再查，也不要依赖多次引用同一递归 CTE —— PostgreSQL 不保证其物化

最易被忽略的一点：递归 CTE 的性能对初始锚点的选择极度敏感。一个返回 1 行的锚点和返回 100 行的锚点，后续迭代量可能差两个数量级。上线前务必用真实数据验证锚点过滤强度，而不是只测单个 ID。