PostgreSQL优化含计算列的分组统计慢查询

场景说明 #

需要在PostgreSQL中创建带计算列的统计视图，基于"Suvita".survey_call_details表按调研轮次统计呼叫相关指标，初始写法执行耗时极长。
涉及核心字段：

• sno：被呼叫人员序列号
• survey_round：调研轮次，统计时需按该字段分组
• call_status：调研呼叫状态
• iscomplete：调研完成标识
  需输出的统计维度包括：去重被呼叫用户数、完成调研的呼叫数、总呼叫尝试数、各呼叫状态对应的呼叫计数等。

原写法性能问题 #

初始实现采用多层相关子查询搭配外层DISTINCT的写法，执行效率极低，原SQL如下：

select distinct  sc.survey_round, 
(select  count(distinct sno) FROM "Suvita".survey_call_details sc1 where sc.survey_round =sc1.survey_round  ) as "Total respondents call",
(select count(*) fROM "Suvita".survey_call_details sc2 where sc.survey_round =sc2.survey_round and sc2.iscomplete = 'Yes') as "Survey Completed",
(select count(*) FROM "Suvita".survey_call_details sc3 where sc.survey_round =sc3.survey_round)  as "Calls Attempted",
(select count(*) FROM "Suvita".survey_call_details sc4 where sc.survey_round =sc4.survey_round and sc4.call_status = 'Picked Up')  as "Calls Answered"  
FROM "Suvita".survey_call_details sc

性能瓶颈分析：相关子查询会对外层查询返回的每一行单独触发一次子查询扫描，外层的DISTINCT属于冗余计算，数据量较大时会产生数倍甚至数十倍的重复IO开销，是导致查询慢的核心原因。

优化实现方案 #

直接使用GROUP BY按调研轮次分组，搭配基础聚合函数+条件聚合一次性计算所有指标，仅需对源表做一次扫描即可完成全部统计，执行效率会有量级提升。
优化后SQL如下：

SELECT sc.survey_round, 
    COUNT(distinct sc.sno) as "Total respondents call",
    COUNT(*) as "Calls Attempted",
    SUM(CASE WHEN sc.call_status = 'Picked Up' THEN 1 ELSE 0 END) as "Calls Answered",
    SUM(CASE WHEN sc.iscomplete = 'Yes' THEN 1 ELSE 0 END) as "Survey Completed",    
    SUM(CASE WHEN sc.nominate = 'Yes' THEN 1 ELSE 0 END) as "Agreed to Nominate"
FROM "Suvita".survey_call_details sc
GROUP BY sc.survey_round 
ORDER BY sc.survey_round DESC

可选进阶优化 #

如果表数据量极大，可以建立(survey_round) INCLUDE (sno, call_status, iscomplete, nominate)的覆盖索引，查询时无需回表，直接走索引即可完成全部统计，性能还能进一步提升。

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