PostgreSQL：快速检查LTREE[]元素是否全包含于另一LTREE[]及查询设计

先明确一下我对表结构和需求的理解：

• groups表用ltree类型的path存储分组的层级路径，每个分组有唯一id；
• items表的每条记录代表一个item关联到一个分组（因为主键是(id, path)，同一个item会有20条左右的记录对应它所属的20个分组）；
• 需求是：给定一个父分组（记为P）和最多10个额外分组，找出父分组直接后代分组下的items（同时我也会给出关联额外分组的衍生场景方案）。

1. 核心查询逻辑 #

场景1：获取父分组直接后代分组下的所有items #

首先需要定位父分组P的直接后代分组——也就是路径是P的直接子节点（深度比P大1，且父路径等于P的分组），然后关联到这些分组对应的items。

基础查询：

-- 先获取父分组的深度，避免硬编码
WITH parent_group AS (
  SELECT path, nlevel(path) AS parent_depth
  FROM groups
  WHERE path = :parent_group_path -- 替换为你的父分组路径
),
direct_child_groups AS (
  SELECT g.path
  FROM groups g
  JOIN parent_group pg ON parent(g.path) = pg.path 
    AND nlevel(g.path) = pg.parent_depth + 1
)
SELECT DISTINCT i.id -- 去重，因为一个item可能属于多个直接后代分组
FROM items i
JOIN direct_child_groups dc ON i.path = dc.path;

场景2：获取同时属于父分组直接后代分组和至少一个额外分组的items #

如果需要找出那些既在父分组直接后代分组下，又属于给定的10个额外分组的items，可以用自连接或者EXISTS子查询：

自连接方案（性能更优）：

WITH parent_group AS (
  SELECT path, nlevel(path) AS parent_depth
  FROM groups
  WHERE path = :parent_group_path
),
direct_child_groups AS (
  SELECT g.path
  FROM groups g
  JOIN parent_group pg ON parent(g.path) = pg.path 
    AND nlevel(g.path) = pg.parent_depth + 1
)
SELECT DISTINCT i.id
FROM items i
JOIN direct_child_groups dc ON i.path = dc.path
-- 关联到同一个item在额外分组中的记录
JOIN items extra_i ON extra_i.id = i.id
WHERE extra_i.path IN (:extra_group_paths); -- 替换为你的额外分组路径列表，最多10个

EXISTS子查询方案（更简洁）：

WITH parent_group AS (
  SELECT path, nlevel(path) AS parent_depth
  FROM groups
  WHERE path = :parent_group_path
),
direct_child_groups AS (
  SELECT g.path
  FROM groups g
  JOIN parent_group pg ON parent(g.path) = pg.path 
    AND nlevel(g.path) = pg.parent_depth + 1
)
SELECT DISTINCT i.id
FROM items i
JOIN direct_child_groups dc ON i.path = dc.path
WHERE EXISTS (
  SELECT 1
  FROM items extra_i
  WHERE extra_i.id = i.id
    AND extra_i.path IN (:extra_group_paths)
);

场景3：获取父分组直接后代分组的items + 额外分组的items（合并结果） #

如果是要把两种items合并（不管是否重复），可以用OR条件：

WITH parent_group AS (
  SELECT path, nlevel(path) AS parent_depth
  FROM groups
  WHERE path = :parent_group_path
),
direct_child_groups AS (
  SELECT g.path
  FROM groups g
  JOIN parent_group pg ON parent(g.path) = pg.path 
    AND nlevel(g.path) = pg.parent_depth + 1
)
SELECT DISTINCT i.id
FROM items i
WHERE i.path IN (:extra_group_paths)
   OR i.path IN (SELECT path FROM direct_child_groups);

2. 性能优化建议（针对千万级items） #

因为你的items表数据量极大（千万级，每个item20条记录），必须做好索引和结构优化：

2.1 索引优化 #

• groups表：

  • 给path字段建GIN索引，支持ltree的各类层级查询：

    CREATE INDEX idx_groups_path_gin ON groups USING GIN (path);
    

  • 可选：生成parent_path和depth的存储列，避免每次调用parent()和nlevel()函数，提升查询速度：

    ALTER TABLE groups ADD COLUMN parent_path ltree GENERATED ALWAYS AS (parent(path)) STORED;
    ALTER TABLE groups ADD COLUMN depth INT GENERATED ALWAYS AS (nlevel(path)) STORED;
    -- 给生成列建索引
    CREATE INDEX idx_groups_parent_path_gin ON groups USING GIN (parent_path);
    CREATE INDEX idx_groups_depth_btree ON groups USING BTREE (depth);
    

    之后查询直接后代分组可以改成：

    SELECT path FROM groups WHERE parent_path = :parent_group_path AND depth = :parent_depth + 1;
    

• items表：

  • 给path字段建GIN索引，加速分组路径的匹配：

    CREATE INDEX idx_items_path_gin ON items USING GIN (path);
    

  • 因为经常按id关联查询，主键(id, path)本身是BTREE索引，已经足够支持按id快速查找。

2.2 查询优化 #

• 用WITH子句预计算直接后代分组，避免重复查询；
• 尽量用DISTINCT或GROUP BY去重，避免返回大量重复的item记录；
• 对于额外分组的IN查询，因为最多10个值，GIN索引可以很好地支持，无需担心性能。

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