PostgreSQL中自关联表数据查询的最优方法探讨

你的原SQL逻辑是获取指定上级的二级下属ID（即直接汇报给该上级直接下属的用户），以下是几种更优的PostgreSQL实现方案：

1. 使用JOIN替代IN子查询 #

PostgreSQL对JOIN的执行计划优化通常优于IN子查询，尤其是当表存在合适索引时，性能提升更明显：

select u.id
from public.user u
join public.user u2 on u.reporting_id = u2.id
where u2.reporting_id = ?;

逻辑说明：通过两次关联用户表，u2代表指定上级的直接下属，u则是u2的下属，和原查询逻辑完全一致，但执行效率更高。

2. 使用CTE（WITH子句）提升可读性 #

如果需要更清晰的逻辑分层，或者后续要扩展查询逻辑，CTE可以让代码结构更易维护，性能和JOIN方案接近：

with direct_subordinates as (
    select id from public.user where reporting_id = ?
)
select u.id
from public.user u
join direct_subordinates ds on u.reporting_id = ds.id;

逻辑说明：先单独提取指定上级的直接下属集合，再关联查询该集合的下属，分层清晰，适合复杂场景的迭代。

3. 递归CTE支持任意层级下属查询 #

如果你的需求未来可能扩展为获取指定上级的所有层级下属，递归CTE是更灵活的方案，无需修改嵌套层级：

with recursive subordinate_hierarchy as (
    select id, reporting_id
    from public.user
    where reporting_id = ?  -- 起始节点：指定上级的直接下属
    union all
    select u.id, u.reporting_id
    from public.user u
    join subordinate_hierarchy sh on u.reporting_id = sh.id
)
select id from subordinate_hierarchy;

逻辑说明：递归遍历用户表的自关联关系，逐层获取所有下属，直到没有更深层级为止。

性能优化建议 #

为了让以上查询更快，确保public.user表的reporting_id字段创建索引：

create index idx_user_reporting_id on public.user(reporting_id);

（注：id作为主键默认已有索引，无需额外创建）

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