PostgreSQL ltree中间通配符查询与前缀查询的性能对比

我使用PostgreSQL的ltree扩展存储层级标签，路径结构为Main.Level1.Level2.Level3，表结构及索引如下：

CREATE TABLE hierarchy 
(
    id SERIAL PRIMARY KEY,
    path ltree
);

-- 创建GiST索引
CREATE INDEX path_gist_idx ON hierarchy USING GIST (path);

日常常用前缀查询（如Main.Level1.*）获取特定层级的所有子节点，这类查询速度较快。但现在需要执行跳过中间层级、匹配开头和结尾的通配符查询，比如：

SELECT * 
FROM hierarchy 
WHERE path ~ 'Main.*.Level3';

问题解答 #

性能差异对比

是的，中间带通配符的Main.*.Level3查询性能通常会比标准前缀查询Main.Level1.*差不少，核心原因在于两者对索引的利用效率存在明显差距。

索引利用情况

• 前缀查询的索引效率
  前缀查询Main.Level1.*属于左锚定的路径匹配，ltree的GiST/GIN索引会直接定位到以Main.Level1为前缀的所有路径节点——这完全贴合层级路径的前缀匹配特性，索引可以快速过滤出符合条件的条目，几乎不会扫描无关数据，性能达到最优。

• 中间通配符查询的索引效率
  对于Main.*.Level3这类中间带通配符的查询，索引的利用效率会大打折扣：

  • GiST/GIN索引无法通过前缀+后缀的组合精准定位，只能先利用Main这个前缀过滤出所有以Main开头的路径，再在这个结果集中逐一检查是否以Level3结尾。
  • 这意味着索引只能完成部分过滤工作，后续需要对索引返回的子集进行二次筛选，扫描的索引范围比前缀查询大得多；如果以Main开头的路径数量庞大，二次筛选的开销会非常明显。
  • 若查询是*.Level3这种前后都带通配符的情况，索引甚至完全无法利用，会直接退化为全表扫描。

优化建议

如果这类中间通配符查询是高频操作，可以考虑以下优化方式：

• 新增一个存储路径最后一级标签的字段（比如leaf_node text），并为其创建普通B树索引，结合前缀条件查询：

  SELECT * FROM hierarchy 
  WHERE path ~ 'Main.*' AND leaf_node = 'Level3';
  

• 对于更复杂的层级匹配需求，也可以考虑将路径拆分为多级字段存储，根据查询场景组合使用索引。

内容的提问来源于stack exchange，提问作者shaf shafiq