PostgreSQL中相似EXISTS条件的合并方法及性能探讨

场景回顾 #

服务与活动为n-m关联，活动可分组至活动组，活动组归属特定组类型。需要筛选出同时关联多种指定组类型的服务（对应原问题中用AND关联多个EXISTS的场景）。

数组聚合方案的性能问题 #

你尝试的ARRAY_AGG + @>方案虽然逻辑简洁，但性能上存在明显短板：

1. 无短路机制：必须扫描该服务关联的所有活动/组数据，聚合出完整的类型数组后才能判断是否包含目标值，不像EXISTS找到匹配项就立即终止。
2. 内存与计算开销：如果服务关联的活动/组数量多，生成的数组会占用额外内存，@>操作的时间也会随数组大小线性增长。
3. 索引利用率低：无法借助(activitiy.id_service, activitiy_group.id_type)这类联合索引实现快速匹配，必须全量聚合后再判断。

更优替代方案 #

方案1：EXISTS + GROUP BY + HAVING（推荐） #

通过单个EXISTS子查询，过滤出目标类型后分组统计distinct类型数量，判断是否覆盖所有需要的类型：

SELECT *
FROM service s
WHERE EXISTS (
    SELECT 1
    FROM activitiy a
    JOIN activitiy_activitiy_group aag ON a.id = aag.id_activitiy
    JOIN activitiy_group ag ON aag.id_activitiy_group = ag.id
    WHERE a.id_service = s.id
      AND ag.id_type IN (1, 3)
    GROUP BY a.id_service
    HAVING COUNT(DISTINCT ag.id_type) = 2 -- 数值需与IN中的类型数量一致
);

• 优势：可以利用(ag.id_type, aag.id_activitiy_group, a.id_service)或(a.id_service, ag.id_type)联合索引快速过滤数据，分组计数的开销远低于全量数组聚合。
• 注意：如果同一服务的同一组类型对应多条活动记录，必须用COUNT(DISTINCT)避免重复计数。

方案2：INTERSECT 合并子查询 #

将每个组类型的筛选逻辑拆分为独立子查询，用INTERSECT取交集：

SELECT s.*
FROM service s
WHERE s.id IN (
    SELECT a.id_service
    FROM activitiy a
    JOIN activitiy_activitiy_group aag ON a.id = aag.id_activitiy
    JOIN activitiy_group ag ON aag.id_activitiy_group = ag.id
    WHERE ag.id_type = 1
    INTERSECT
    SELECT a.id_service
    FROM activitiy a
    JOIN activitiy_activitiy_group aag ON a.id = aag.id_activitiy
    JOIN activitiy_group ag ON aag.id_activitiy_group = ag.id
    WHERE ag.id_type = 3
);

• 优势：每个子查询可以独立优化，PostgreSQL会自动处理交集去重，适合需要筛选的组类型数量较多的场景。
• 适用场景：当每个子查询返回的服务数量较小时，哈希交集或合并交集的性能会优于多次EXISTS。

方案3：JOIN + GROUP BY + HAVING #

通过JOIN关联所有表后分组统计，最后去重得到结果：

SELECT DISTINCT s.*
FROM service s
JOIN activitiy a ON a.id_service = s.id
JOIN activitiy_activitiy_group aag ON a.id = aag.id_activitiy
JOIN activitiy_group ag ON aag.id_activitiy_group = ag.id
WHERE ag.id_type IN (1, 3)
GROUP BY s.id
HAVING COUNT(DISTINCT ag.id_type) = 2;

• 注意：如果服务关联的活动/组数据量很大，DISTINCT会带来额外开销，此时不如EXISTS方案高效。

方案选择建议 #

• 若筛选的组类型数量少（2-3个）：优先用多个EXISTS或EXISTS+GROUP BY+HAVING，逻辑清晰且性能稳定。
• 若筛选的组类型数量多：优先用INTERSECT方案，每个子查询独立利用索引，交集操作效率更高。
• 数组聚合方案仅适合小数据量场景或逻辑验证，不推荐用于生产环境的性能敏感查询。

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