PostgreSQL高效查询构建及超大规模数据下的性能优化问询

先给你理清楚需求和数据背景：你有两张核心表，master_object存待评估人员信息，record_indikator存他们的指标回答记录，最终要算的是完成所有活跃指标回答的人员占总人员的比例。先把你的示例数据整理成清晰的表格：

一、表结构与示例数据 #

1. master_object（待评估人员表） #

2. record_indikator（指标回答记录表） #

（注：我把你原数据里的object_id排版调整了下，贴合你举的示例逻辑）

你的需求逻辑其实可以拆成三步：

1. 先算出全局活跃指标的总数（也就是所有is_active=1的不重复指标ID数量）
2. 对每个用户，统计他们已完成的活跃指标数（该用户名下is_active=1的不重复指标ID数量）
3. 对比这两个数：如果用户完成数等于全局总数，就算达标（记1），否则不达标（记0）；最后算达标用户数占总用户数的比例

二、高效SQL查询实现 #

方法一：逻辑清晰的CTE写法 #

这种写法可读性强，PostgreSQL的优化器能很好地处理，适合日常维护：

WITH active_indicators AS (
    -- 先一次性算出全局活跃指标总数，避免重复计算
    SELECT COUNT(DISTINCT indicator_id) AS total_active
    FROM record_indikator
    WHERE is_active = 1
),
user_completion AS (
    -- 统计每个用户的完成情况
    SELECT 
        mo.id,
        -- 对比用户完成数和全局总数，标记是否达标
        CASE 
            WHEN COUNT(DISTINCT ri.indicator_id) = ai.total_active THEN 1
            ELSE 0
        END AS is_complete
    FROM master_object mo
    -- 把全局活跃指标数关联到每个用户行
    CROSS JOIN active_indicators ai
    -- 左连接确保没有任何回答记录的用户也被统计（直接记0）
    LEFT JOIN record_indikator ri 
        ON mo.id = ri.object_id 
        AND ri.is_active = 1
    GROUP BY mo.id, ai.total_active
)
-- 最终计算达标比例：达标用户数/总用户数
SELECT AVG(is_complete) AS completion_ratio
FROM user_completion;

方法二：更紧凑的聚合写法 #

如果不需要单独查看每个用户的完成情况，用这个写法可以减少CTE的开销，速度更快一点：

SELECT 
    -- 统计达标用户数，转成浮点型避免整数除法
    SUM(CASE 
            WHEN user_active_count = total_active THEN 1 
            ELSE 0 
        END)::FLOAT / COUNT(DISTINCT mo.id) AS completion_ratio
FROM master_object mo
-- 先拿全局活跃指标总数
CROSS JOIN (
    SELECT COUNT(DISTINCT indicator_id) AS total_active
    FROM record_indikator
    WHERE is_active = 1
) ai
-- 预统计每个用户完成的活跃指标数
LEFT JOIN (
    SELECT 
        object_id,
        COUNT(DISTINCT indicator_id) AS user_active_count
    FROM record_indikator
    WHERE is_active = 1
    GROUP BY object_id
) ri ON mo.id = ri.object_id;

这两种写法的核心都是避免重复计算全局活跃指标数，同时用左连接覆盖所有用户，确保统计准确。

三、3亿+数据量下的性能优化建议 #

当你的record_indikator表有3亿+记录时，单纯的SQL优化可能不够，得从索引、存储、架构多维度入手：

1. 索引优化（最基础也最见效） #

• 给record_indikator建复合索引：CREATE INDEX idx_ri_active_obj_ind ON record_indikator(is_active, object_id, indicator_id);
  这个索引能快速过滤出活跃指标，同时按用户分组统计指标数，直接命中查询需要的字段，避免全表扫描
• 确保master_object的id是主键：ALTER TABLE master_object ADD PRIMARY KEY (id);，关联时能快速定位用户

2. 数据分区 #

• 按时间分区：如果数据是按年/月累积的，给record_indikator加个时间字段（比如create_time），然后按时间分区，查询时只扫描需要的分区，能大幅减少扫描的数据量
• 按object_id范围分区：如果用户ID是连续的，按ID范围分片，适合批量查询用户的场景

3. 物化视图预计算 #

如果不需要实时数据，用物化视图预计算每个用户的完成情况，查询时直接取结果：

CREATE MATERIALIZED VIEW mv_user_completion AS
SELECT 
    mo.id,
    CASE 
        WHEN COUNT(DISTINCT ri.indicator_id) = (
            SELECT COUNT(DISTINCT indicator_id) FROM record_indikator WHERE is_active = 1
        ) THEN 1
        ELSE 0
    END AS is_complete
FROM master_object mo
LEFT JOIN record_indikator ri 
    ON mo.id = ri.object_id 
    AND ri.is_active = 1
GROUP BY mo.id;

可以每天凌晨定时刷新物化视图，如果需要准实时数据，加个唯一索引后用REFRESH MATERIALIZED VIEW CONCURRENTLY刷新，不影响查询。

4. 架构与引擎升级 #

• 如果PostgreSQL单库扛不住，试试Citus集群，把数据分片到多个节点，并行处理查询，能线性提升性能
• 对于这种大规模统计分析场景，把数据同步到列式存储引擎（比如ClickHouse）会更高效，列式存储在聚合查询时的性能比行式存储高几个数量级，特别适合3亿+数据的统计需求

备注：内容来源于stack exchange，提问作者yuanganteng