如何用单条SQL查询获取层级结构中的父级与子级节点

嘿，针对你遇到的这个树形表查询性能问题，我来分享几个实用的优化方案，重点解决父节点查询的瓶颈：

核心问题分析 #

你的现有查询是同时匹配目标节点的子节点（path LIKE '/1/2/' || '%'）和父节点（'/1/2/' LIKE path || '%'）：

• 子节点的前缀匹配已经通过GIN索引搞定，性能没问题；
• 父节点的反向后缀匹配（判断目标路径是否包含某个节点的路径）是当前的性能痛点，常规索引无法直接支持这种反向LIKE操作。

方案1：用pg_trgm扩展的索引优化反向LIKE匹配 #

这是最直接且无需修改表结构的方案，利用PostgreSQL的pg_trgm扩展来创建支持任意模式LIKE查询的索引：

1. 先安装pg_trgm扩展（如果没装的话）：

   CREATE EXTENSION IF NOT EXISTS pg_trgm;
   

2. 创建trgm索引：
   针对path字段创建GIN或GIST索引，两者的区别是：

   • GIN索引查询速度更快，但写入（插入/更新）时开销稍大；
   • GIST索引写入开销小，适合写多读少的场景。
     选一个适合你的：

   -- GIN索引（推荐读多场景）
   CREATE INDEX idx_table_path_trgm ON "table" USING GIN (path gin_trgm_ops);
   
   -- 或者GIST索引（推荐写多场景）
   CREATE INDEX idx_table_path_trgm ON "table" USING GIST (path gist_trgm_ops);
   

3. 保留原有查询语句：
   现在执行你的原查询，PostgreSQL会自动利用trgm索引优化反向LIKE的部分：

   SELECT * FROM "table" WHERE ((path LIKE '/1/2/' || '%') OR ('/1/2/' LIKE path || '%'));
   

   你可以用EXPLAIN ANALYZE验证，会看到索引扫描被用到，不管是子节点还是父节点的匹配逻辑。

方案2：预计算路径元素数组（可选，需修改表结构） #

如果允许修改表结构，你可以新增一个数组字段来存储路径拆分后的所有节点ID，这样父节点查询可以用数组包含操作，性能更优：

1. 新增字段并初始化数据：

   ALTER TABLE "table" ADD COLUMN path_elements INT[];
   
   -- 初始化现有数据：拆分path为ID数组（比如"/1/2/"拆成[1,2]）
   UPDATE "table" 
   SET path_elements = string_to_array(trim(path, '/'), '/')::INT[];
   

2. 创建数组索引：

   CREATE INDEX idx_table_path_elements ON "table" USING GIN (path_elements);
   

3. 优化查询语句：
   目标节点/1/2/对应的path_elements是[1,2]，查询父/子节点可以改成：

   -- 子节点：path_elements以[1,2]开头
   SELECT * FROM "table" WHERE path_elements @> ARRAY[1,2];
   
   -- 父节点：path_elements是[1,2]的子集
   SELECT * FROM "table" WHERE path_elements <@ ARRAY[1,2];
   
   -- 合并父+子查询
   SELECT * FROM "table" WHERE path_elements @> ARRAY[1,2] OR path_elements <@ ARRAY[1,2];
   

   这种方式的性能会比trgm索引更优，但需要维护path_elements字段（可以用触发器自动更新，避免手动维护）。

为什么内连接会更慢？ #

你提到尝试内连接后速度更慢，大概率是因为自连接的写法没有利用到合适的索引，比如：

-- 这种写法如果没有trgm或数组索引，会做全表扫描匹配
SELECT parent.* 
FROM "table" child
JOIN "table" parent ON child.path LIKE parent.path || '%'
WHERE child.path = '/1/2/';

没有索引支撑的话，数据库需要逐个比对所有节点的路径，自然会慢，所以还是优先用上面的索引方案。

内容的提问来源于stack exchange，提问作者Alexander Tolkachev