合并PostgreSQL多布尔字段统计查询为何变慢及优化方法

SELECT COUNT(*) FROM tableA WHERE flagN = true;

我尝试将这些查询合并为单个查询，写法如下：
```sql
SELECT
    SUM((CASE WHEN flag1 = FALSE THEN 0 ELSE 1 END)) AS cnt1,
    SUM((CASE WHEN flag2 = FALSE THEN 0 ELSE 1 END)) AS cnt2,
    ...
    SUM((CASE WHEN flagN = FALSE THEN 0 ELSE 1 END)) AS cntN
FROM 
    tableA 

合并后的查询可以正常运行，但耗时约为所有单独查询总耗时的12倍。所有标记字段的索引设置一致，请问为何合并查询耗时这么久，是否有改写方法来缩短耗时？

• 单独查询的高效性：每个单独的COUNT(*)查询可以直接利用对应flagX字段的单列索引，数据库只需扫描索引中值为true的条目，部分数据库甚至会直接读取索引的统计元数据返回结果，无需实际扫描数据行，开销极低。
• 合并查询的性能瓶颈：合并后的查询需要执行全表扫描（或全索引扫描），因为要同时检查每一行的所有标记字段。数据库必须遍历表中每一条记录，对每行的N个字段逐一判断并累加统计值，当数据量较大时，IO和计算量都会远超过N次单独索引查询的总和，尤其是N越多，每行的计算开销被放大得越明显。

1. 用UNION ALL合并单索引查询 #

这种方式保留每个子查询的索引优势，同时一次性返回所有统计结果，总耗时与单独查询总和接近，甚至因为减少了连接开销略低：

SELECT 'flag1' AS flag_name, COUNT(*) AS cnt FROM tableA WHERE flag1 = true
UNION ALL
SELECT 'flag2' AS flag_name, COUNT(*) AS cnt FROM tableA WHERE flag2 = true
UNION ALL
...
SELECT 'flagN' AS flag_name, COUNT(*) AS cnt FROM tableA WHERE flagN = true;

2. 简化CASE表达式（仅优化计算开销） #

如果必须采用单表扫描的方式，先简化CASE表达式减少每行的计算量：

SELECT
    SUM(CASE WHEN flag1 THEN 1 ELSE 0 END) AS cnt1,
    SUM(CASE WHEN flag2 THEN 1 ELSE 0 END) AS cnt2,
    ...
    SUM(CASE WHEN flagN THEN 1 ELSE 0 END) AS cntN
FROM tableA;

在支持布尔值转数值的数据库（如PostgreSQL）中，还可以进一步简化：

SELECT
    SUM(flag1::int) AS cnt1,
    SUM(flag2::int) AS cnt2,
    ...
    SUM(flagN::int) AS cntN
FROM tableA;

注意：这种方式依然是全表扫描，仅能降低计算开销，无法解决IO的核心问题，仅适合小数据量场景。

3. 预计算统计值（适合高频查询场景） #

如果该统计查询需要频繁执行，可通过预计算避免重复扫描：

• 定时任务：定期执行单独的计数查询，将结果存入专门的统计表（如flag_stats），查询时直接读取该表数据。
• 触发器同步：在tableA的插入/更新/删除操作时，通过触发器同步更新flag_stats中的对应计数。
  这种方式能将查询耗时降到最低，适合高并发或大数据量场景。

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