PostgreSQL递归查询月度统计 双聚合列如何实现单次表扫描

原写法性能问题 #

原递归查询的性能瓶颈在于：递归的每一层都执行两个独立的关联子查询，每个子查询都会单独扫描一次tbl表，统计12个月数据就会触发24次表扫描，IO开销极高。
你提到的date_trunc('month', created_at)分组方案触发排序开销高的问题，本质是全表计算截断日期后再排序分组的成本，我们可以通过「先生成所有目标月份区间，再单次关联聚合」的思路，同时规避反复扫表和全表排序两个问题。

优化后实现（仅单次扫描表） #

核心逻辑是把「递归/生成月份」和「表数据聚合」两个步骤拆开：先生成需要统计的所有连续月份区间（这部分数据量极小，仅10余行常量级数据），再通过左连接一次性关联tbl表的符合条件数据，用条件聚合一次算出所有月份的统计值，全程仅扫描一次tbl表。

版本1：用generate_series生成月份（更简洁，推荐） #

不需要递归，用内置序列生成函数直接产出需要统计的月份区间：

WITH months AS (
  SELECT
    month_start AS start,
    month_start + INTERVAL '1 month' AS t_end
  FROM generate_series(
    date_trunc('month', current_date - INTERVAL '1 year'),
    date_trunc('month', current_date + INTERVAL '1 month'),
    INTERVAL '1 month'
  ) AS month_start
)
SELECT
  m.start,
  m.t_end,
  COUNT(*) FILTER (WHERE t.flag IS NULL) AS null_count,
  COUNT(*) FILTER (WHERE t.flag IS NOT NULL) AS not_null_count
FROM months m
LEFT JOIN tbl t
  ON t.created_at >= m.start
  AND t.created_at < m.t_end
  AND t.deleted_at < current_timestamp
GROUP BY m.start, m.t_end
ORDER BY m.start DESC;

版本2：保留递归生成月份的结构 #

如果有特殊的月份生成规则必须保留递归逻辑，只需要把查表逻辑从递归层移到外层即可，递归仅负责生成月份区间：

WITH RECURSIVE months(start, t_end) AS (
  VALUES (
    date_trunc('month', current_date + INTERVAL '1 month'),
    date_trunc('month', current_date + INTERVAL '2 months')
  )
  UNION ALL
  SELECT
    start - INTERVAL '1 month' AS start,
    start AS t_end
  FROM months
  WHERE start > date_trunc('month', current_date - INTERVAL '1 year')
)
SELECT
  m.start,
  m.t_end,
  COUNT(*) FILTER (WHERE t.flag IS NULL) AS null_count,
  COUNT(*) FILTER (WHERE t.flag IS NOT NULL) AS not_null_count
FROM months m
LEFT JOIN tbl t
  ON t.created_at >= m.start
  AND t.created_at < m.t_end
  AND t.deleted_at < current_timestamp
GROUP BY m.start, m.t_end
ORDER BY m.start DESC;

性能说明 #

• 全程仅对tbl表做一次扫描，不会出现原写法反复扫表的问题
• 完全规避全表排序开销：关联时会直接利用created_at字段上的B树索引做范围扫描，直接按索引顺序取出最近1年多的符合条件数据，不需要先计算每行的月份截断值再排序分组
• 可以通过创建覆盖索引进一步把性能拉满，实现纯索引扫描不需要回表：

CREATE INDEX idx_tbl_created_cover ON tbl (created_at, deleted_at) INCLUDE (flag);

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