如何优化PostgreSQL递归CTE的树形结构查询性能？

遇到递归CTE查询树形结构慢的情况确实挺头疼的，尤其是只有100条记录却要8秒，这明显不太正常，咱们可以从这几个方向入手优化：

1. 先给关键字段加索引 #

递归CTE的性能瓶颈大多出在递归关联时的全表扫描上，你得确保parent_lot_id和sadt_lot_id这两个字段有合适的索引：

-- 给parent_lot_id建单独索引
CREATE INDEX idx_sadt_lot_parent ON sadt_lot(parent_lot_id);
-- 主键sadt_lot_id一般默认有索引，若没有则手动创建
CREATE UNIQUE INDEX idx_sadt_lot_id ON sadt_lot(sadt_lot_id);

如果递归时需要过滤其他条件，还可以考虑建联合索引，比如把parent_lot_id和常用过滤字段组合在一起。

2. 精简递归CTE的字段和逻辑 #

你贴的递归CTE没写完，但要注意：递归部分只保留必要的字段，别把表中所有字段都拉进来，减少数据传输和处理的开销。另外，**把UNION改成UNION ALL**很关键——因为递归生成的树形数据不会有重复，用UNION会额外做去重，这可能是你查询慢的核心原因之一。优化后的递归CTE示例：

WITH RECURSIVE downlots as (
 SELECT s1.sadt_lot_id, 0 AS level, s1.sadt_lot_id as root_id 
 FROM sadt_lot s1 WHERE s1.parent_lot_id IS NULL
 UNION ALL -- 替换UNION，避免不必要的去重开销
 SELECT s2.sadt_lot_id, d.level + 1, d.root_id 
 FROM downlots d
 JOIN sadt_lot s2 ON d.sadt_lot_id = s2.parent_lot_id
)
SELECT * FROM downlots;

3. 改用PostgreSQL的ltree扩展 #

ltree是PostgreSQL专门为树形结构设计的扩展，性能比递归CTE好很多，步骤如下：

1. 先启用扩展：

CREATE EXTENSION ltree;

2. 在表中新增path字段，存储节点的树形路径（比如根节点是1，子节点是1.2，孙节点是1.2.3）：

ALTER TABLE sadt_lot ADD COLUMN path ltree;

3. 初始化路径数据（之后新增节点时维护这个字段即可）：

WITH RECURSIVE tree_paths AS (
 SELECT sadt_lot_id, parent_lot_id, text(sadt_lot_id)::ltree AS path
 FROM sadt_lot WHERE parent_lot_id IS NULL
 UNION ALL
 SELECT s.sadt_lot_id, s.parent_lot_id, tp.path || text(s.sadt_lot_id)::ltree
 FROM tree_paths tp
 JOIN sadt_lot s ON tp.sadt_lot_id = s.parent_lot_id
)
UPDATE sadt_lot s SET path = tp.path FROM tree_paths tp WHERE s.sadt_lot_id = tp.sadt_lot_id;

4. 快速查询树形数据：

-- 查询根节点1下的所有子节点
SELECT * FROM sadt_lot WHERE path @> '1'::ltree;
-- 查询节点2的所有后代
SELECT * FROM sadt_lot WHERE path <@ '1.2'::ltree;

记得给path字段建索引加速查询：

CREATE INDEX idx_sadt_lot_path ON sadt_lot USING gist(path);

4. 预计算树形路径缓存 #

如果你的树形结构不经常变动，可以预计算每个节点的路径（比如用数组存储从根到当前节点的ID），查询时直接用数组过滤：

1. 新增字段：

ALTER TABLE sadt_lot ADD COLUMN path_array INT[];

2. 初始化数据：

WITH RECURSIVE tree_paths AS (
 SELECT sadt_lot_id, parent_lot_id, ARRAY[sadt_lot_id] AS path_array
 FROM sadt_lot WHERE parent_lot_id IS NULL
 UNION ALL
 SELECT s.sadt_lot_id, s.parent_lot_id, tp.path_array || s.sadt_lot_id
 FROM tree_paths tp
 JOIN sadt_lot s ON tp.sadt_lot_id = s.parent_lot_id
)
UPDATE sadt_lot s SET path_array = tp.path_array FROM tree_paths tp WHERE s.sadt_lot_id = tp.sadt_lot_id;

3. 快速查询：

-- 查询根节点1下的所有节点
SELECT * FROM sadt_lot WHERE path_array[1] = 1;
-- 查询节点2的所有后代
SELECT * FROM sadt_lot WHERE 2 = ANY(path_array) AND array_length(path_array,1) > array_position(path_array,2);

给path_array建GIN索引可以进一步加速数组查询：

CREATE INDEX idx_sadt_lot_path_array ON sadt_lot USING gin(path_array);

最后，如果还是慢，建议用EXPLAIN ANALYZE跑一下你的查询，看看执行计划里有没有全表扫描、索引未命中的情况，针对性调整。

内容的提问来源于stack exchange，提问作者Tiago Mendes