PostgreSQL查询：实现全Job双周期运行统计并优化查询性能

原来的查询固定了单个Job的名称过滤条件，仅返回单Job统计结果。改造核心是移除单Job过滤条件，增加按Job维度分组，即可一次查询返回所有Job的统计数据，返回结果每一行对应一个Job，包含原有单Job查询的全部统计维度。另外count()函数本身不会返回null，可移除冗余的coalesce()调用简化代码：

WITH results AS (
SELECT
        prow_jobs.id AS job_id,
        prow_jobs.name AS job_name,
        COUNT(CASE WHEN succeeded = true AND timestamp BETWEEN NOW() - INTERVAL '14 DAY' AND NOW() - INTERVAL '7 DAY' THEN 1 END) AS previous_passes,
        COUNT(CASE WHEN succeeded = false AND timestamp BETWEEN NOW() - INTERVAL '14 DAY' AND NOW() - INTERVAL '7 DAY' THEN 1 END) AS previous_failures,
        COUNT(CASE WHEN timestamp BETWEEN NOW() - INTERVAL '14 DAY' AND NOW() - INTERVAL '7 DAY' THEN 1 END) AS previous_total_runs,
        COUNT(CASE WHEN infrastructure_failure = true AND timestamp BETWEEN NOW() - INTERVAL '14 DAY' AND NOW() - INTERVAL '7 DAY' THEN 1 END) AS previous_infrastructure_failures,
        
        COUNT(CASE WHEN succeeded = true AND timestamp > NOW() - INTERVAL '7 DAY' THEN 1 END) AS current_passes,
        COUNT(CASE WHEN succeeded = false AND timestamp > NOW() - INTERVAL '7 DAY' THEN 1 END) AS current_failures,        
        COUNT(CASE WHEN timestamp > NOW() - INTERVAL '7 DAY' THEN 1 END) AS current_total_runs,
        COUNT(CASE WHEN infrastructure_failure = true AND timestamp > NOW() - INTERVAL '7 DAY' THEN 1 END) AS current_infrastructure_failures
FROM
        prow_job_runs 
JOIN prow_jobs ON prow_jobs.id = prow_job_runs.prow_job_id
GROUP BY prow_jobs.id, prow_jobs.name
)
SELECT *,
        previous_passes * 100.0 / NULLIF(previous_total_runs, 0) AS previous_pass_percentage,
        previous_failures * 100.0 / NULLIF(previous_total_runs, 0) AS previous_failure_percentage,
        current_passes * 100.0 / NULLIF(current_total_runs, 0) AS current_pass_percentage,
        current_failures * 100.0 / NULLIF(current_total_runs, 0) AS current_failure_percentage       
FROM results;

从执行计划可以看到当前prow_job_runs表走了全表扫描，是性能瓶颈的核心来源，可以通过建覆盖索引消除全表扫描和回表开销：

CREATE INDEX idx_prow_job_runs_time_stats ON prow_job_runs (prow_job_id, timestamp)
INCLUDE (succeeded, infrastructure_failure);

这个索引可以同时满足：

• 关联prow_jobs表时的prow_job_id匹配需求
• 时间范围过滤的快速定位需求
• 直接从索引中读取统计所需的succeeded、infrastructure_failure字段，不需要回表查询原表数据
  优化后全Job统计的耗时可以控制在毫秒级，远优于循环查询单Job的方案。

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