Calculating totals and percentages for each row, in a time boxed window, for a relation-CodePudding

Ok, so I've got two tables: jobs, and job runs. I'm using Postgres.

I want to look at 2 periods. 7 days ago until now, and 14 days ago to 7 days ago.

For each job, I want a total of the number of runs, and a percentage of successful and unsuccessful runs for each period. I've cooked up this terrible query:

WITH results AS (
select
        coalesce(count(case when succeeded = true AND timestamp BETWEEN NOW() - INTERVAL '14 DAY' AND NOW() - INTERVAL '7 DAY' then 1 end), 0) as previous_passes,
        coalesce(count(case when succeeded = false AND timestamp BETWEEN NOW() - INTERVAL '14 DAY' AND NOW() - INTERVAL '7 DAY' then 1 end), 0) as previous_failures,
        coalesce(count(case when timestamp BETWEEN NOW() - INTERVAL '14 DAY' AND NOW() - INTERVAL '7 DAY' then 1 end), 0) as previous_total_runs,
        coalesce(count(case when infrastructure_failure = true AND timestamp BETWEEN NOW() - INTERVAL '14 DAY' AND NOW() - INTERVAL '7 DAY' then 1 end), 0) as previous_infrastructure_failures,
        
        coalesce(count(case when succeeded = true AND timestamp > NOW() - INTERVAL '7 DAY' then 1 end), 0) as current_passes,
        coalesce(count(case when succeeded = false AND timestamp > NOW() - INTERVAL '7 DAY' then 1 end), 0) as current_failures,        
        coalesce(count(case when timestamp > NOW() - INTERVAL '7 DAY' then 1 end), 0) as current_total_runs,
        coalesce(count(case when infrastructure_failure = true AND timestamp > NOW() - INTERVAL '7 DAY' then 1 end), 0) as current_infrastructure_failures
FROM
        prow_job_runs JOIN prow_jobs ON prow_jobs.id = prow_job_runs.prow_job_id WHERE prow_jobs.name = 'promote-release-openshift-machine-os-content-e2e-aws-4.10'
)
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;

Which gets me the result I want:

-[ RECORD 1 ]-------------------- -----------------------
previous_passes                  | 591
previous_failures                | 4
previous_total_runs              | 595
previous_infrastructure_failures | 1
current_passes                   | 67
current_failures                 | 0
current_total_runs               | 67
current_infrastructure_failures  | 0
previous_pass_percentage         | 99.3277310924369748
previous_failure_percentage      | 0.67226890756302521008
current_pass_percentage          | 100.0000000000000000
current_failure_percentage       | 0.00000000000000000000

Here is the execution plan:

                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Subquery Scan on results  (cost=661.12..661.19 rows=1 width=192)
   ->  Aggregate  (cost=661.12..661.13 rows=1 width=64)
         ->  Hash Join  (cost=8.30..650.89 rows=93 width=10)
               Hash Cond: (prow_job_runs.prow_job_id = prow_jobs.id)
               ->  Seq Scan on prow_job_runs  (cost=0.00..603.60 rows=14460 width=18)
               ->  Hash  (cost=8.29..8.29 rows=1 width=8)
                     ->  Index Scan using prow_jobs_name_key on prow_jobs  (cost=0.27..8.29 rows=1 width=8)
                           Index Cond: (name = 'promote-release-openshift-machine-os-content-e2e-aws-4.10'::text)
(8 rows)

But it's only for a single job, how do I do get results for each job without doing a for loop in code?

I also think my query is really slow, it's > 8ms for just one job run.

CodePudding user response：

you need to provide the query execution plan. but you have to make sure you have necessary indices and also maybe you limit your number of rows in join it will be helpful:

WITH results AS (
        select prow_jobs.name,
                coalesce(count(case when succeeded = true AND timestamp BETWEEN NOW() - INTERVAL '14 DAY' AND NOW() - INTERVAL '7 DAY' then 1 end), 0) as previous_passes,
                coalesce(count(case when succeeded = false AND timestamp BETWEEN NOW() - INTERVAL '14 DAY' AND NOW() - INTERVAL '7 DAY' then 1 end), 0) as previous_failures,
                coalesce(count(case when timestamp BETWEEN NOW() - INTERVAL '14 DAY' AND NOW() - INTERVAL '7 DAY' then 1 end), 0) as previous_total_runs,
                coalesce(count(case when infrastructure_failure = true AND timestamp BETWEEN NOW() - INTERVAL '14 DAY' AND NOW() - INTERVAL '7 DAY' then 1 end), 0) as previous_infrastructure_failures,
                coalesce(count(case when succeeded = true AND timestamp > NOW() - INTERVAL '7 DAY' then 1 end), 0) as current_passes,
                coalesce(count(case when succeeded = false AND timestamp > NOW() - INTERVAL '7 DAY' then 1 end), 0) as current_failures,        
                coalesce(count(case when timestamp > NOW() - INTERVAL '7 DAY' then 1 end), 0) as current_total_runs,
                coalesce(count(case when infrastructure_failure = true AND timestamp > NOW() - INTERVAL '7 DAY' then 1 end), 0) as current_infrastructure_failures
        FROM prow_job_runs 
        JOIN prow_jobs 
                ON prow_jobs.id = prow_job_runs.prow_job_id                 
                and timestamp BETWEEN NOW() and now() - INTERVAL '14 DAY' 
        group by 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;

and seems like you don't have any index on prow_job_runs table, add an index on that table with columns (id,succeeded,infrastructure_failure,timestamp, prow_job_id)