postgresql 15 中递归查询必须用 with recursive，子查询本身不支持递归；递归部分嵌套相关子查询会导致每行重复执行、性能雪崩，正确优化方式是控制 cte 物化行为（materialized/not materialized）、建合适索引、加深度限制和防环路径检查。
直接说结论：在 PostgreSQL 15 中，递归查询只能用 WITH RECURSIVE，子查询本身不支持递归；所谓“用子查询加速递归”，本质是误用概念——真正可调、可优化的是 CTE 的物化行为和执行路径，而不是把子查询套进递归里。
为什么不能在递归部分里嵌套普通子查询
PostgreSQL 要求递归 CTE 的结构必须严格满足两段式：UNION ALL 左右两侧都得是完整、可独立解析的 SELECT 语句。你在递归分支里写一个 (SELECT ...) 作为某列值（比如 (SELECT COUNT(*) FROM logs WHERE logs.node_id = nt.id)），语法上允许，但会触发相关子查询（correlated subquery），导致每轮递归都重复执行该子查询，性能雪崩。
- 递归体中出现子查询 → 每一层结果集的每一行都会触发一次子查询执行
- 若递归深度为 10，某层有 100 行，则该子查询被执行 1000 次
- 这类写法在
EXPLAIN ANALYZE中表现为大量重复的Subquery Scan或Index Scan节点
PostgreSQL 15 中真正有效的加速手段
加速递归查询的核心，是减少每轮迭代的计算量 + 控制物化开销。PG 15 对 CTE 物化策略做了更细粒度控制，关键在 MATERIALIZED / NOT MATERIALIZED 提示。
- 默认行为仍是物化（尤其当 CTE 被多次引用或含聚合时），但你现在可以显式覆盖：
WITH RECURSIVE node_tree AS MATERIALIZED (...)或... AS NOT MATERIALIZED (...) - 对纯
JOIN驱动的递归（如parent_id → id），加NOT MATERIALIZED可让优化器尝试内联，启用谓词下推（比如把WHERE level 下推到每次 JOIN） - 若递归结果要被主查询多次扫描（例如同时做
COUNT和JSON_AGG），则保留物化反而更快，避免重复计算 - 务必建索引：
CREATE INDEX ON tree_nodes (parent_id, id);—— 递归 JOIN 的性能瓶颈几乎总在这里
常见错误：把 CTE 当成缓存，盲目复用
有人会写两个 CTE，第一个查子树，第二个基于第一个算统计，认为“反正前面算过了”。但在 PG 15 中，除非你显式声明 MATERIALIZED，否则第二个 CTE 并不会读第一个的中间结果，而是重新执行整套递归逻辑。
- 错误写法：
WITH RECURSIVE t AS (...), stats AS (SELECT COUNT(*) FROM t) SELECT * FROM t, stats;→t执行两次 - 正确写法：
WITH RECURSIVE t AS MATERIALIZED (...), stats AS (SELECT COUNT(*) FROM t) SELECT * FROM t, stats; - 更高效写法：把统计逻辑塞进递归体，用窗口函数或累积变量（如
SUM(1) OVER ()）一次完成
深度控制与循环防护必须手动加
PostgreSQL 不自动检测无限递归，超深树（比如误设的自环）会导致查询卡死或报错 ERROR: infinite recursion detected。PG 15 默认 max_recursive_depth = 100，但这个值只是熔断器，不是优化手段。
- 必须在递归体中加入显式深度限制：
WHERE nt.level （配合 <code>level字段） - 防自环：用
ARRAY[id]记录路径，检查NOT n.id = ANY(nt.path)，否则父子 ID 相同就会死循环 - 注意
level字段类型：用SMALLINT而非INTEGER，减少每行体积，对万级节点的递归结果集有实际内存收益
最易被忽略的一点：递归 CTE 的执行计划里，CTE Scan 节点的 Actual Loops 值等于递归层数，而每个 Loop 的 Actual Rows 是该层输出行数。盯着这个数字调索引和剪枝条件，比调任何配置参数都管用。
