递归视图

组织架构树、物料 BOM、分类目录这类「自己引用自己」的数据，需要递归查询来处理。把 WITH RECURSIVE 封装进视图，就能给出一套稳定的「树查询接口」。本节讲解递归视图的写法、终止条件、UNION 与 UNION ALL 的取舍、PostgreSQL 14+ 的 CYCLE / SEARCH 子句，以及它与 ltree 的选型对比。

前置知识

阅读本节前，建议先了解：递归查询（WITH RECURSIVE）、CREATE / DROP VIEW

基础概念

递归视图就是「把递归 CTE 装进视图」

PostgreSQL 允许视图定义里包含 WITH RECURSIVE，语法上没有特殊之处：

text

CREATE VIEW 名称 AS
WITH RECURSIVE t AS (
    <非递归项：起点>
  UNION [ALL]
    <递归项：引用 t>
)
SELECT ... FROM t;

视图只是壳，真正干活的是递归 CTE。所以：

事实	说明
递归视图不可更新	含 `WITH` + `UNION`，天然不可自动更新
递归视图不缓存结果	每次查询都重跑递归
递归视图可以带参数吗	不可以。视图不能带参数，起点只能由调用方的 `WHERE` 过滤
递归视图能建索引吗	不可以，索引要建在基表（如 `parent_id`）上

「视图不能带参数」这一点决定了递归视图的通用写法：视图输出全量层级关系，调用方用 WHERE 筛选起点与范围。

递归 CTE 的执行模型

text

    ┌──────────────────┐
    │  非递归项（起点） │  执行一次 → 得到 working table（工作集）
    └────────┬─────────┘
             │
             ▼
    ┌──────────────────────────────┐
    │ 把工作集加入结果集             │◀────────────┐
    │ 用工作集去驱动递归项           │             │
    │ 递归项结果 → 新的工作集        │─────────────┘
    └────────┬─────────────────────┘   直到工作集为空
             │
             ▼
        返回结果集

关键点：递归项每次只看到「上一轮产生的新行」（working table），不是「目前为止的全部行」。这是理解「按层遍历」和「深度累加」的基础。

递归必须有终止条件

UNION ALL 不做去重，数据里一旦出现环就会无限递归直到内存/磁盘耗尽：

text

A → B → C → A → B → C → ...（无限）

三种防护手段：

手段	说明	版本
`UNION`（去重）	自己会终止于「无新行」，但代价高	全部
手动深度限制列	`depth < N`，最可控	全部
`CYCLE` 子句	递归 CTE 原生环检测，自动标记并停止	14+

`UNION` vs `UNION ALL`

维度	`UNION`	`UNION ALL`
去重	会去重（按整行比较）	不去重
结果	每个节点只出现一次	同一节点可多次出现（DAG 会重复）
防环	天然防环	不防环
性能	慢（每轮都要去重/排序）	快
适用	数据可能有环、需要去重	树结构确定无环、需要保留路径重复

实践结论：树结构（每个节点只有一个父）用 UNION ALL + 深度限制；图结构（可能多父、可能有环）要么用 UNION，要么（更好）用 CYCLE。

语法与用法

text

-- 递归 CTE 的完整语法
WITH [ RECURSIVE ] cte_name ( column_name [, ...] ) AS (
    non_recursive_term
  UNION [ ALL ]
    recursive_term
)
[, ...]
SELECT ... FROM cte_name;

-- 视图里封装递归 CTE
CREATE [ OR REPLACE ] VIEW name AS
WITH RECURSIVE cte AS ( ... )
SELECT ... FROM cte;

-- CYCLE 子句（PostgreSQL 14+）
WITH RECURSIVE cte AS ( ... )
CYCLE col SET is_cycle USING path

-- SEARCH 子句（PostgreSQL 14+）
WITH RECURSIVE cte AS ( ... )
SEARCH DEPTH FIRST BY col SET ordercol
SEARCH BREADTH FIRST BY col SET ordercol

最简可运行示例：

sql

CREATE TABLE categories (
  id        integer PRIMARY KEY,
  name      text    NOT NULL,
  parent_id integer REFERENCES categories(id)
);

INSERT INTO categories (id, name, parent_id) VALUES
  (1, '电子产品', NULL),
  (2, '手机',     1),
  (3, '笔记本',   1),
  (4, '智能手机', 2),
  (5, '折叠屏',   4);

CREATE VIEW v_category_tree AS
WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 1 AS depth,
         name::text AS path
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.depth + 1,
         (t.path || ' / ' || c.name)::text
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
)
SELECT id, name, parent_id, depth, path FROM tree;

text

CREATE TABLE
INSERT 0 5
CREATE VIEW

sql

SELECT * FROM v_category_tree ORDER BY depth, id;

text

 id |   name   | parent_id | depth |         path
----+----------+-----------+-------+-----------------------
  1 | 电子产品 |           |     1 | 电子产品
  2 | 手机     |         1 |     2 | 电子产品 / 手机
  3 | 笔记本   |         1 |     2 | 电子产品 / 笔记本
  4 | 智能手机 |         2 |     3 | 电子产品 / 手机 / 智能手机
  5 | 折叠屏   |         4 |     4 | 电子产品 / 手机 / 智能手机 / 折叠屏
(5 rows)

详细说明

从「任意节点」开始的子树查询

视图输出的是全量树，调用方用 WHERE 选起点。但「从某个节点向下递归」不能靠外层 WHERE 直接实现（因为递归的起点是在 CTE 内部固定的）——外层 WHERE 只能过滤已算出的结果。

若需要「以任意节点为根」的视图，把起点交给调用方过滤即可（前提是你能用 path 或后继关系判断）：

sql

-- 查询「手机」这个节点及其所有后代
SELECT * FROM v_category_tree
WHERE path LIKE '电子产品 / 手机%'
ORDER BY depth, id;

text

 id |   name   | parent_id | depth |              path
----+----------+-----------+-------+---------------------------------
  2 | 手机     |         1 |     2 | 电子产品 / 手机
  4 | 智能手机 |         2 |     3 | 电子产品 / 手机 / 智能手机
  5 | 折叠屏   |         4 |     4 | 电子产品 / 手机 / 智能手机 / 折叠屏
(3 rows)

更稳妥的做法是另建一个「以参数为起点」的递归视图，起点用一个可被外层过滤的列（这里用 start_id 配合自连接不行，因此常见手法是用函数代替视图）：

sql

-- 视图做不到「参数化」，若必须参数化，用 SQL 函数
CREATE OR REPLACE FUNCTION f_subtree(root_id integer)
RETURNS TABLE (id integer, name text, depth integer, path text)
LANGUAGE sql STABLE AS $$
  WITH RECURSIVE tree AS (
    SELECT c.id, c.name, 1 AS depth, c.name::text AS path
    FROM categories c
    WHERE c.id = root_id
    UNION ALL
    SELECT c.id, c.name, t.depth + 1, (t.path || ' / ' || c.name)::text
    FROM categories c
    JOIN tree t ON c.parent_id = t.id
    WHERE t.depth < 50                      -- 深度护栏
  )
  SELECT * FROM tree;
$$;

SELECT * FROM f_subtree(2) ORDER BY depth;

text

 id |   name   | depth |              path
----+----------+-------+---------------------------------
  2 | 手机     |     1 | 手机
  4 | 智能手机 |     2 | 手机 / 智能手机
  5 | 折叠屏   |     3 | 手机 / 智能手机 / 折叠屏
(3 rows)

选择建议：需要参数化递归时，用 STABLE 的 SQL 函数；需要「全量树快照」时，用递归视图。

向上递归：查祖先链（面包屑）

反向遍历同样是递归，只是把 JOIN 条件反过来：

sql

CREATE VIEW v_category_ancestors AS
WITH RECURSIVE up AS (
  SELECT id, name, parent_id, 1 AS level
  FROM categories
  UNION ALL
  SELECT p.id, p.name, p.parent_id, u.level + 1
  FROM categories p
  JOIN up u ON p.id = u.parent_id
  WHERE u.level < 50
)
SELECT id, name, level FROM up;

不过这个视图的语义是「每个节点到根的所有祖先」，使用时需要限定起点：

sql

-- 折叠屏（5）的祖先链：从自身逐级向上，level 越大越接近根
SELECT id, name, level
FROM v_category_ancestors
WHERE id IN (
  WITH RECURSIVE chain AS (
    SELECT id, parent_id, 1 AS lv FROM categories WHERE id = 5
    UNION ALL
    SELECT c.id, c.parent_id, ch.lv + 1
    FROM categories c JOIN chain ch ON c.id = ch.parent_id
  )
  SELECT id FROM chain
)
ORDER BY level;                      -- → 5,4,2,1

更简洁的做法是直接写「向下递归 + 累积路径」的视图，面包屑与祖先 ID 一目了然：

sql

CREATE OR REPLACE VIEW v_category_path AS
WITH RECURSIVE down AS (
  SELECT id, name, parent_id, 1 AS depth, name::text AS path, ARRAY[id] AS id_path
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, d.depth + 1,
         (d.path || ' / ' || c.name)::text,
         d.id_path || c.id
  FROM categories c JOIN down d ON c.parent_id = d.id
)
SELECT id, name, depth, path, id_path FROM down;

text

SELECT name, path, id_path FROM v_category_path WHERE id = 5;
   name   |              path               |    id_path
----------+---------------------------------+---------------
 折叠屏   | 电子产品 / 手机 / 智能手机 / 折叠屏 | {1,2,4,5}

深度限制：最可靠的防环手段

在递归项里加 WHERE depth < N，是成本最低、最可控的防护：

sql

CREATE VIEW v_tree_safe AS
WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 1 AS depth
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.depth + 1
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
  WHERE t.depth < 20                       -- 熔断：最多 20 层
)
SELECT * FROM tree;

为什么必须写：UNION ALL 不去重，一个 A → B → A 的脏数据就会让查询永不结束。深度限制把最坏情况从「挂死」变成「截断」：

sql

-- 制造一个环：把「电子产品」的父节点指向「折叠屏」
UPDATE categories SET parent_id = 5 WHERE id = 1;

-- 没有深度限制会无限递归；有 WHERE depth < 20 则安全返回
SELECT count(*) FROM v_tree_safe;

text

 count
-------
    20
(1 row)

sql

-- 复原
UPDATE categories SET parent_id = NULL WHERE id = 1;

`CYCLE` 子句（PostgreSQL 14+）

CYCLE 是标准 SQL 的环检测语法，PostgreSQL 14 引入。它自动为每行附带「是否成环」和「访问路径」两列，遇到环就停止扩展：

sql

CREATE OR REPLACE VIEW v_tree_cycle AS
WITH RECURSIVE tree AS (
  SELECT id, name, parent_id
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
)
CYCLE id SET is_cycle USING path
SELECT id, name, is_cycle, path FROM tree;

text

CREATE VIEW

CYCLE id SET is_cycle USING path 的语义：

部分	作用
`CYCLE id`	用 `id` 列判断是否重复出现（即是否成环）
`SET is_cycle`	生成布尔列 `is_cycle`，标记该行是否构成环
`USING path`	生成记录访问路径的列 `path`（数组类型）

sql

-- 正常情况（无环）：全部 is_cycle = false
SELECT id, name, is_cycle, path FROM v_tree_cycle ORDER BY id;

text

 id |   name   | is_cycle |    path
----+----------+----------+------------
  1 | 电子产品 | f        | {1}
  2 | 手机     | f        | {1,2}
  3 | 笔记本   | f        | {1,3}
  4 | 智能手机 | f        | {1,2,4}
  5 | 折叠屏   | f        | {1,2,4,5}

sql

-- 制造环后：环出现的那一行被标记 true，且不再继续展开
UPDATE categories SET parent_id = 5 WHERE id = 1;

SELECT id, name, is_cycle, path FROM v_tree_cycle ORDER BY id;

text

 id |   name   | is_cycle |      path
----+----------+----------+------------------
  5 | 折叠屏   | f        | {5}
  1 | 电子产品 | f        | {5,1}
  2 | 手机     | f        | {5,1,2}
  4 | 智能手机 | f        | {5,1,2,4}
  5 | 折叠屏   | t        | {5,1,2,4,5}   ← 检测到重复，标记并停止

sql

-- 复原
UPDATE categories SET parent_id = NULL WHERE id = 1;

环检测的成本

CYCLE 内部要维护并比对路径数组，比手动深度限制更贵。如果数据是「标准树 + 应用层保证无环」，用深度限制即可；只有当数据可能形成环（图、外部导入数据）时才用 CYCLE。

`SEARCH` 子句（PostgreSQL 14+）

SEARCH 定义遍历顺序，并生成一个可排序的列：

sql

-- 深度优先：生成按「路径顺序」排序的列
CREATE OR REPLACE VIEW v_tree_dfs AS
WITH RECURSIVE tree AS (
  SELECT id, name, parent_id FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id
  FROM categories c JOIN tree t ON c.parent_id = t.id
)
SEARCH DEPTH FIRST BY name SET dfs_order
SELECT id, name, dfs_order FROM tree;

sql

SELECT * FROM v_tree_dfs ORDER BY dfs_order;

text

 id |   name   |     dfs_order
----+----------+--------------------
  1 | 电子产品 | {(电子产品)}
  3 | 笔记本   | {(电子产品),(笔记本)}
  2 | 手机     | {(电子产品),(手机)}
  4 | 智能手机 | {(电子产品),(手机),(智能手机)}
  5 | 折叠屏   | {(电子产品),(手机),(智能手机),(折叠屏)}

sql

-- 广度优先
CREATE OR REPLACE VIEW v_tree_bfs AS
WITH RECURSIVE tree AS (
  SELECT id, name, parent_id FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id
  FROM categories c JOIN tree t ON c.parent_id = t.id
)
SEARCH BREADTH FIRST BY name SET bfs_order
SELECT id, name, bfs_order FROM tree;

text

SELECT * FROM v_tree_bfs ORDER BY bfs_order;

 id |   name   | bfs_order
----+----------+-----------
  1 | 电子产品 | {1}
  2 | 手机     | {2}
  3 | 笔记本   | {2}
  4 | 智能手机 | {3}
  5 | 折叠屏   | {4}

SEARCH 生成的是可排序的「深度/广度序号」，适合直接 ORDER BY 输出。注意：SEARCH 只影响排序，不影响遍历本身的终止行为（防环仍要靠 CYCLE 或深度限制）。

递归视图的性能与内存

递归 CTE 是逐层迭代执行的，代价主要在三处：

成本来源	说明	缓解手段
基表扫描次数	每层都要 JOIN 一次找子节点	在 `parent_id` 上建索引
working table 物化	每轮的工作集可能落盘	控制深度与起点范围
去重（若用 `UNION`）	每轮排序去重	树结构用 `UNION ALL`

最重要的一条索引：

sql

CREATE INDEX idx_categories_parent ON categories (parent_id);

sql

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT * FROM v_category_path WHERE id = 5;

text

 CTE Scan on down (actual rows=5 loops=1)
   Filter: (id = 5)
   Buffers: shared hit=21
   CTE down
     ->  Recursive Union (actual rows=5 loops=1)
           ->  Seq Scan on categories (actual rows=1 loops=1)
                 Filter: (parent_id IS NULL)
           ->  Nested Loop (actual rows=1 loops=5)
                 ->  WorkTable Scan on down d (actual rows=1 loops=5)
                 ->  Index Scan using idx_categories_parent on categories c
                       Index Cond: (parent_id = d.id)

WorkTable Scan 是递归 CTE 的特征节点。注意有索引时，递归项走的是 Index Scan——没有 idx_categories_parent 就会退化成 Seq Scan，每层全扫一遍，代价随深度线性放大。

深层递归（如 100 层）会把中间结果反复物化：

sql

-- 观察递归产生的磁盘用量
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT count(*) FROM v_tree_safe;

text

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   Buffers: shared hit=33
   CTE tree
     ->  Recursive Union (actual rows=5 loops=1)

Buffers: shared hit=33 说明全部在内存完成；若出现 read= 或 temp read= 就说明工作集落盘了。

递归视图 + `LIMIT` 的注意点

递归 CTE 上叠加 LIMIT 有个关键限制：LIMIT 不能下推到递归内部（递归 CTE 是「不可提升」结构）。

sql

-- 语义：先算完整棵树，再取前 3 行。不是「只递归 3 层」
SELECT * FROM v_category_tree ORDER BY depth, id LIMIT 3;

text

 id |   name   | parent_id | depth |         path
----+----------+-----------+-------+-----------------------
  1 | 电子产品 |           |     1 | 电子产品
  2 | 手机     |         1 |     2 | 电子产品 / 手机
  3 | 笔记本   |         1 |     2 | 电子产品 / 笔记本

如果树有 100 万节点、只想看前 10 个，这条查询仍会算完整棵树——EXPLAIN 中 Limit 节点之上才是 CTE Scan on down，递归量不受 LIMIT 影响。

「只想要浅层」的正确做法是把层数限制写进递归项：

sql

CREATE OR REPLACE VIEW v_tree_shallow AS
WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 1 AS depth FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.depth + 1
  FROM categories c JOIN tree t ON c.parent_id = t.id
  WHERE t.depth < 3                        -- 只展开 3 层
)
SELECT * FROM tree;

递归视图 + `UNION ALL` 在 DAG 上的重复问题

如果数据不是纯树（一个节点有多个父节点，即 DAG），UNION ALL 会让同一节点出现多次：

sql

-- 制造 DAG：让「折叠屏」同时挂在「手机」和「笔记本」下（示意）
-- 通常需要中间表，这里用额外表模拟
CREATE TABLE category_links (
  category_id integer NOT NULL REFERENCES categories(id),
  parent_id   integer NOT NULL REFERENCES categories(id),
  PRIMARY KEY (category_id, parent_id)
);

INSERT INTO category_links (category_id, parent_id) VALUES
  (2, 1), (3, 1), (4, 2), (5, 2), (5, 3);   -- 折叠屏(5) 同时挂在 手机(2) 与 笔记本(3) 下

sql

CREATE OR REPLACE VIEW v_dag_paths AS
WITH RECURSIVE tree AS (
  SELECT c.id, c.name, c.id AS cur, 1 AS depth, ARRAY[c.id] AS id_path
  FROM categories c
  WHERE NOT EXISTS (SELECT 1 FROM category_links l WHERE l.category_id = c.id)
  UNION ALL
  SELECT c.id, c.name, l.category_id, t.depth + 1, t.id_path || l.category_id
  FROM category_links l
  JOIN categories c ON c.id = l.category_id
  JOIN tree t ON t.cur = l.parent_id
  WHERE t.depth < 20
)
SELECT id, name, depth, id_path FROM tree;

sql

-- 折叠屏（5）会因两条路径各出现一次
SELECT * FROM v_dag_paths WHERE id = 5 ORDER BY depth;

text

 id |   name   | depth |   id_path
----+----------+-------+-------------
  5 | 折叠屏   |     3 | {1,2,5}
  5 | 折叠屏   |     3 | {1,3,5}
(2 rows)

这正是 DAG 的正确语义：同一个节点有多条到达路径。若只想「去重后的节点集合」，把 UNION ALL 换成 UNION，或在最外层 SELECT DISTINCT。

递归视图与 `ltree` 的对比选型

ltree 是 PostgreSQL 官方的树结构专用扩展，把路径存成一个紧凑的类型（如 Top.Science.Astronomy），配合 <@、@>、lquery、ltxtquery 做高效的子树匹配。

维度	递归视图	`ltree`
是否需要扩展	不需要	需要 `CREATE EXTENSION ltree`
存储	只存 `parent_id`	额外存一列路径
子树查询	每次重跑递归	一次 `WHERE path <@ 'root'` + GiST 索引
写入成本	低（只维护 `parent_id`）	高（移动节点要重写整个子树路径）
深度限制	`ltree` 标签总长有限制	路径长度受限（默认 65535 字节）
排序 / 层级	需自己拼 `depth` / `path`	天然有序，`ORDER BY path` 即层级序
适用	树较小、读多写少、不想引扩展	树大、子树查询高频、层级排序频繁

ltree 的典型用法：

sql

CREATE EXTENSION IF NOT EXISTS ltree;

CREATE TABLE org_ltree (
  id       integer PRIMARY KEY,
  name     text   NOT NULL,
  path     ltree  NOT NULL
);

CREATE INDEX idx_org_ltree_path ON org_ltree USING gist (path);

-- 路径匹配：一次性拿到整棵子树（无需递归）
-- SELECT * FROM org_ltree WHERE path <@ '1.2';

选型建议：

树节点在万级以内、查询以「取某节点子孙」为主 → 递归视图足够，简单、无额外存储。
节点百万级、子树查询与层级排序是核心需求 → 用 ltree，把递归开销转成索引查询。
需要频繁移动节点 → 慎用 ltree（每次移动都要重写子树路径）；递归视图只改 parent_id，成本低。

实战示例

场景：为 SaaS 系统实现「部门层级路径」查询，要求输出部门的完整路径、层级深度，并支持「查某部门及其所有下级」。

第 1 步：建表与数据

sql

DROP TABLE IF EXISTS departments CASCADE;

CREATE TABLE departments (
  id        integer PRIMARY KEY,
  code      text   NOT NULL UNIQUE,
  name      text   NOT NULL,
  parent_id integer REFERENCES departments(id) ON DELETE RESTRICT
);

INSERT INTO departments (id, code, name, parent_id) VALUES
  (1,  'HQ',    '总部',       NULL),
  (2,  'TECH',  '技术中心',   1),
  (3,  'SALES', '销售中心',   1),
  (4,  'DEV',   '研发部',     2),
  (5,  'QA',    '测试部',     2),
  (6,  'FE',    '前端组',     4),
  (7,  'BE',    '后端组',     4),
  (8,  'MOB',   '移动端组',   4),
  (9,  'EAST',  '华东大区',   3),
  (10, 'SH',    '上海办事处', 9),
  (11, 'HZ',    '杭州办事处', 9);

CREATE INDEX idx_departments_parent ON departments (parent_id);
ANALYZE departments;

text

DROP TABLE
CREATE TABLE
INSERT 0 11
CREATE INDEX
ANALYZE

第 2 步：创建递归视图（输出层级路径 + 深度 + 祖先 ID 数组）

sql

CREATE VIEW v_dept_path AS
WITH RECURSIVE tree AS (
  SELECT
    d.id, d.code, d.name, d.parent_id,
    1 AS depth,
    d.name::text          AS path,
    ARRAY[d.id]           AS id_path,
    d.code::text          AS code_path
  FROM departments d
  WHERE d.parent_id IS NULL

  UNION ALL

  SELECT
    c.id, c.code, c.name, c.parent_id,
    t.depth + 1,
    (t.path || ' / ' || c.name)::text,
    t.id_path || c.id,
    (t.code_path || '/' || c.code)::text
  FROM departments c
  JOIN tree t ON c.parent_id = t.id
  WHERE t.depth < 20
)
SELECT id, code, name, parent_id, depth, path, id_path, code_path FROM tree;

text

CREATE VIEW

第 3 步：验证全量层级路径

sql

SELECT depth, code, name, path
FROM v_dept_path
ORDER BY code_path;

text

 depth | code  |     name     |                    path
-------+-------+--------------+---------------------------------------------
     1 | HQ    | 总部         | 总部
     2 | SALES | 销售中心     | 总部 / 销售中心
     3 | EAST  | 华东大区     | 总部 / 销售中心 / 华东大区
     4 | HZ    | 杭州办事处   | 总部 / 销售中心 / 华东大区 / 杭州办事处
     4 | SH    | 上海办事处   | 总部 / 销售中心 / 华东大区 / 上海办事处
     2 | TECH  | 技术中心     | 总部 / 技术中心
     3 | DEV   | 研发部       | 总部 / 技术中心 / 研发部
     4 | BE    | 后端组       | 总部 / 技术中心 / 研发部 / 后端组
     4 | FE    | 前端组       | 总部 / 技术中心 / 研发部 / 前端组
     4 | MOB   | 移动端组     | 总部 / 技术中心 / 研发部 / 移动端组
     3 | QA    | 测试部       | 总部 / 技术中心 / 测试部

第 4 步：查「技术中心」及其所有下级

用 code_path 前缀匹配最直观；用 id_path 数组包含判断更精确（避免 TECH 与 TECH2 的前缀歧义）。两种写法都返回 TECH、DEV、BE、FE、MOB、QA 共 6 行：

sql

SELECT depth, code, name, path FROM v_dept_path
 WHERE code_path LIKE 'HQ/TECH%' ORDER BY code_path;

SELECT depth, code, name FROM v_dept_path
 WHERE id_path @> ARRAY[2] ORDER BY code_path;

第 5 步：EXPLAIN 验证执行计划

sql

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT * FROM v_dept_path WHERE id_path @> ARRAY[2];

text

 CTE Scan on tree (actual rows=6 loops=1)
   Filter: (id_path @> '{2}'::integer[])
   Rows Removed by Filter: 5
   Buffers: shared hit=45
   CTE tree
     ->  Recursive Union (actual rows=11 loops=1)
           ->  Seq Scan on departments d (actual rows=1 loops=1)
                 Filter: (parent_id IS NULL)
           ->  Nested Loop (actual rows=1 loops=10)
                 ->  WorkTable Scan on tree t (actual rows=1 loops=10)
                       Filter: (depth < 20)
                 ->  Index Scan using idx_departments_parent on departments c
                       Index Cond: (parent_id = t.id)

分析：

id_path @> ARRAY[2] 的 Filter 落在 CTE Scan 上——过滤发生在递归完成之后，递归本身仍遍历了全部 11 个节点。
递归项走 Index Scan using idx_departments_parent，说明 parent_id 索引生效。
对 11 行的表无所谓；对百万级表，这种「全树递归 + 后置过滤」会明显变慢。

第 6 步：参数化改造（用函数替代视图）

如果「查子树」是高频操作，改造成 STABLE 函数，让起点进入递归内部：

sql

CREATE OR REPLACE FUNCTION f_dept_subtree(root_id integer)
RETURNS TABLE (id integer, code text, name text, depth integer, path text)
LANGUAGE sql STABLE AS $$
  WITH RECURSIVE tree AS (
    SELECT d.id, d.code, d.name, 1 AS depth, d.name::text AS path
    FROM departments d
    WHERE d.id = root_id
    UNION ALL
    SELECT c.id, c.code, c.name, t.depth + 1, (t.path || ' / ' || c.name)::text
    FROM departments c
    JOIN tree t ON c.parent_id = t.id
    WHERE t.depth < 20
  )
  SELECT * FROM tree;
$$;

text

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT * FROM f_dept_subtree(2);

 CTE Scan on tree (actual rows=6 loops=1)
   Buffers: shared hit=31
   CTE tree
     ->  Recursive Union (actual rows=6 loops=1)
           ->  Index Scan using departments_pkey on departments d (actual rows=1 loops=1)
                 Index Cond: (id = 2)
           ->  Nested Loop (actual rows=1 loops=5)
                 ->  WorkTable Scan on tree t (actual rows=1 loops=5)
                 ->  Index Scan using idx_departments_parent on departments c
                       Index Cond: (parent_id = t.id)

改进点：递归行数从 11 降到 6，loops 从 10 降到 5——起点直接进入递归内部，只遍历目标子树。这是递归查询的正确优化方向。

第 7 步：清理

sql

DROP VIEW v_dept_path;
DROP FUNCTION f_dept_subtree(integer);
DROP TABLE departments;

注意事项

UNION ALL 不防环。 数据里一个环就能让递归永不结束，必须加深度限制或 CYCLE。

sql

-- 危险！没有 WHERE depth < N，遇到环会跑满磁盘
... UNION ALL SELECT ... FROM t JOIN categories c ON c.parent_id = t.id

视图不能带参数。 需要「以任意节点为起点」的递归时，用 STABLE 函数替代视图。
外层的 WHERE 不能改变递归起点。 它只过滤「递归完成后」的结果，递归本身仍从头跑完。
递归 CTE 上的 LIMIT 不会减少递归量。 想「只取浅层」必须把层数限制写进递归项。
sql
```
-- 不能少算：仍会算完整棵树
SELECT * FROM v_tree LIMIT 10;
```
UNION 虽能防环，但每轮去重代价高。树结构优先 UNION ALL + 深度限制。
CYCLE（PG 14+）比手动深度限制更贵，只在数据可能有环时使用。
SEARCH 只影响排序，不做防环。
递归视图不可更新，含 WITH + UNION 天然是只读的。
忘了在 parent_id 上建索引时，递归会退化成「每层全表扫描」，代价随深度线性放大。
ltree 需要额外安装扩展且移动节点成本高，不要为了「看起来高级」而引入它。
递归深度过大时工作集会落盘（EXPLAIN 出现临时文件），此时应考虑改用 ltree 或预计算路径列。

最佳实践

递归视图一律加深度护栏，把最坏情况从「挂死」变成「截断」：
sql
```
... FROM categories c JOIN tree t ON c.parent_id = t.id
WHERE t.depth < 20
```
树结构用 UNION ALL，图结构用 UNION 或 CYCLE，把性能与安全分开考虑。
在 parent_id 上建索引，这是递归查询唯一的「必须索引」：
sql
```
CREATE INDEX idx_departments_parent ON departments (parent_id);
```
视图里同时输出 depth、path（文本路径）与 id_path（数组），一次性满足「显示面包屑」与「子树过滤」两种需求：
sql
```
(t.path || ' / ' || c.name)::text,
t.id_path || c.id
```
子树过滤优先用 id_path @> ARRAY[...]，比 LIKE 'prefix%' 更精确，不受编码前缀歧义影响。

高频参数化递归改用 STRICT / STABLE 的 SQL 函数，让起点进入递归内部：

sql

CREATE OR REPLACE FUNCTION f_subtree(root_id integer)
RETURNS TABLE (...) LANGUAGE sql STABLE AS $$ ... $$;

给递归加 STABLE 标记，让优化器知道它不修改数据、可在查询内被合理规划。
节点数量大（十万级以上）且子树查询频繁时，改用 ltree + GiST 索引，把递归成本降到索引查找。
在应用层用 CHECK 或触发器防止出现环（如「父节点不能是自己或自己的后代」），比事后靠 CYCLE 兜底更划算。
对递归视图的输出做 ORDER BY code_path / dfs_order，让结果天然呈现层级顺序，便于直接渲染树形 UI。

继续学习：物化视图概述

递归视图 ​

基础概念 ​

递归视图就是「把递归 CTE 装进视图」 ​

递归 CTE 的执行模型 ​

递归必须有终止条件 ​

UNION vs UNION ALL ​

语法与用法 ​

详细说明 ​

从「任意节点」开始的子树查询 ​

向上递归：查祖先链（面包屑） ​

深度限制：最可靠的防环手段 ​

CYCLE 子句（PostgreSQL 14+） ​

SEARCH 子句（PostgreSQL 14+） ​

递归视图的性能与内存 ​

递归视图 + LIMIT 的注意点 ​

递归视图 + UNION ALL 在 DAG 上的重复问题 ​

递归视图与 ltree 的对比选型 ​

实战示例 ​

注意事项 ​

最佳实践 ​

参考链接 ​

递归视图

基础概念

递归视图就是「把递归 CTE 装进视图」

递归 CTE 的执行模型

递归必须有终止条件

`UNION` vs `UNION ALL`

语法与用法

详细说明

从「任意节点」开始的子树查询

向上递归：查祖先链（面包屑）

深度限制：最可靠的防环手段

`CYCLE` 子句（PostgreSQL 14+）

`SEARCH` 子句（PostgreSQL 14+）

递归视图的性能与内存

递归视图 + `LIMIT` 的注意点

递归视图 + `UNION ALL` 在 DAG 上的重复问题

递归视图与 `ltree` 的对比选型

实战示例

注意事项

最佳实践

参考链接