PostgreSQL高效查询：判断Profile是否经历指定状态组

针对你的需求，这里提供两种高效的查询方案，替代你尝试的exists_in_groupby写法，同时解决大表查询超时的问题：

方案1：使用BOOL_OR聚合函数（PostgreSQL原生推荐） #

利用PostgreSQL的BOOL_OR函数，直接判断分组内是否存在符合状态组的记录，只需扫描一次dynamic表：

SELECT 
    p.id, 
    p.some_other_column,
    -- 处理无动态数据的用户，默认返回FALSE
    COALESCE(sub.has_group1, FALSE) AS has_group1,
    COALESCE(sub.has_group2, FALSE) AS has_group2
FROM profile p
LEFT JOIN (
    SELECT 
        profile_id,
        -- 只要分组内有state1/state2，就返回TRUE
        BOOL_OR(state IN ('state1', 'state2')) AS has_group1,
        -- 只要分组内有state3/state4，就返回TRUE
        BOOL_OR(state IN ('state3', 'state4')) AS has_group2
    FROM dynamic
    GROUP BY profile_id
) sub ON sub.profile_id = p.id;

方案2：使用MAX(CASE...)兼容写法 #

如果需要兼容更早版本的PostgreSQL，可以用CASE转换值后取最大值：

SELECT 
    p.id, 
    p.some_other_column,
    -- 转换为布尔值，无数据则返回FALSE
    COALESCE(sub.has_group1, 0)::BOOLEAN AS has_group1,
    COALESCE(sub.has_group2, 0)::BOOLEAN AS has_group2
FROM profile p
LEFT JOIN (
    SELECT 
        profile_id,
        -- 存在符合状态则返回1，否则0，取最大值判断是否存在
        MAX(CASE WHEN state IN ('state1', 'state2') THEN 1 ELSE 0 END) AS has_group1,
        MAX(CASE WHEN state IN ('state3', 'state4') THEN 1 ELSE 0 END) AS has_group2
    FROM dynamic
    GROUP BY profile_id
) sub ON sub.profile_id = p.id;

性能优化补充 #

你之前的子查询写法效率极低，原因是每个用户都会触发4次独立的子查询，相当于对dynamic表做了N次全表扫描（N为用户数量）。上面的方案仅需扫描dynamic表一次，聚合后再关联profile表，IO和计算量大幅降低。

为进一步提升速度，建议给dynamic表创建复合索引：

CREATE INDEX idx_dynamic_profile_state ON dynamic(profile_id, state);

该索引可以让分组和状态判断直接走索引，避免全表扫描。

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