Skip to content
Advertisement

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

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:

Which gets me the result I want:

Here is the execution plan:

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.

TY

Advertisement

Answer

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:

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)

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement