如何优化PostgreSQL中条件相似的CASE语句？

你的原代码会对group_views表执行5次独立查询，存在冗余且效率较低。可以通过条件聚合的方式，仅执行一次表扫描就能获取所有结果，优化后的脚本如下：

SELECT
    MAX(CASE WHEN cwgv_table = 0 THEN '1' ELSE '0' END) AS existCondition0,
    MAX(CASE WHEN cwgv_table = 1 THEN '1' ELSE '0' END) AS existCondition1,
    MAX(CASE WHEN cwgv_table = 2 THEN '1' ELSE '0' END) AS existCondition2,
    MAX(CASE WHEN cwgv_table = 3 THEN '1' ELSE '0' END) AS existCondition3,
    MAX(CASE WHEN cwgv_table = 4 THEN '1' ELSE '0' END) AS existCondition4
INTO
    existCondition0, existCondition1, existCondition2, existCondition3, existCondition4
FROM group_views
WHERE cwgv_group = vGroup;

优化说明 #

• 仅对group_views表执行一次查询，过滤出cwgv_group = vGroup的所有记录，避免重复扫描表
• 使用MAX()聚合函数：如果存在对应cwgv_table值的记录，CASE会返回'1'，MAX()会保留这个'1'；如果不存在，所有行都返回'0'，最终结果就是'0'
• 也可以用SUM()替代MAX()，此时需要把返回值改成数字类型的1和0，逻辑是只要有符合条件的记录，求和结果至少为1，再转成'1'或'0'，效果一致：

  SELECT
      CASE WHEN SUM(CASE WHEN cwgv_table = 0 THEN 1 ELSE 0 END) > 0 THEN '1' ELSE '0' END AS existCondition0,
      CASE WHEN SUM(CASE WHEN cwgv_table = 1 THEN 1 ELSE 0 END) > 0 THEN '1' ELSE '0' END AS existCondition1,
      CASE WHEN SUM(CASE WHEN cwgv_table = 2 THEN 1 ELSE 0 END) > 0 THEN '1' ELSE '0' END AS existCondition2,
      CASE WHEN SUM(CASE WHEN cwgv_table = 3 THEN 1 ELSE 0 END) > 0 THEN '1' ELSE '0' END AS existCondition3,
      CASE WHEN SUM(CASE WHEN cwgv_table = 4 THEN 1 ELSE 0 END) > 0 THEN '1' ELSE '0' END AS existCondition4
  INTO
      existCondition0, existCondition1, existCondition2, existCondition3, existCondition4
  FROM group_views
  WHERE cwgv_group = vGroup;
  

另外，建议在group_views表的(cwgv_group, cwgv_table)字段上建立复合索引，能进一步提升查询速度。

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