求助：PostgreSQL下优化entity_a发生半天数统计的查询

优化后的查询语句 #

SELECT
  a.id,
  COALESCE(SUM(CASE WHEN daily_count > 1 THEN 1 ELSE 0.5 END), 0) AS total_days
FROM
  entity_a a
LEFT JOIN (
  SELECT
    a_id,
    date,
    COUNT(*) AS daily_count
  FROM entity_b
  GROUP BY a_id, date
) b_daily ON a.id = b_daily.a_id
GROUP BY a.id;

关键性能优化：添加索引 #

针对entity_b表的分组逻辑，创建复合索引可彻底解决百万级数据下的性能瓶颈：

CREATE INDEX idx_entity_b_aid_date ON entity_b(a_id, date);

优化说明 #

1. 索引作用：该复合索引让PostgreSQL直接通过索引扫描完成a_id和date的分组统计，避免全表扫描和昂贵的排序操作，大幅降低IO与CPU开销。
2. LEFT JOIN适配需求：替换原查询的JOIN为LEFT JOIN，确保无关联entity_b记录的entity_a也能返回结果（总计0天），满足“每个entity_a返回一行”的约束。
3. COALESCE处理空值：避免无数据时返回NULL，统一返回0，结果更规范。

窗口函数替代写法 #

若偏好窗口函数实现，可使用以下语句（性能仍依赖上述索引）：

SELECT
  DISTINCT a.id,
  COALESCE(
    SUM(CASE 
          WHEN COUNT(*) OVER (PARTITION BY a.id, b.date) > 1 THEN 1
          ELSE 0.5
        END) OVER (PARTITION BY a.id),
    0
  ) AS total_days
FROM entity_a a
LEFT JOIN entity_b b ON a.id = b.a_id;

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