Optimize StuckCiJobsWorker running builds query

Merged Allison Browne requested to merge optimize-running-stuck-5 into master

What does this MR do and why?

In !68891 (merged) we merged a fix that was reverted due to some failing tests.

I believe the tests were flaky because created_at was not set only updated_at. This MR sets created_at in those tests. It also freezes time.


explain SELECT "ci_builds".* 
FROM "ci_builds" 
WHERE "ci_builds"."type" = 'Ci::Build' 
AND "ci_builds"."id" 
IN (
    SELECT "ci_builds"."id" 
    FROM "ci_builds" 
    "ci_builds"."type" = 'Ci::Build' 
    AND ("ci_builds"."status" IN ('running')) 
    AND (ci_builds.created_at < '2021-08-24 17:41:01.993524')
    order by created_at, project_id
) AND (updated_at < '2021-08-24 17:41:01.994189') limit 100;
                                                                                      QUERY PLAN

 Limit  (cost=472222.26..472594.62 rows=100 width=1289) (actual time=2.670..2.672 rows=0 loops=1)
   Buffers: shared hit=821
   ->  Nested Loop  (cost=472222.26..1629886.42 rows=310895 width=1289) (actual time=2.669..2.670 rows=0 loop
         Buffers: shared hit=821
         ->  HashAggregate  (cost=472221.68..475475.50 rows=325382 width=4) (actual time=0.828..2.394 rows=30
               Group Key: ci_builds_1.id
               Buffers: shared hit=665
               ->  Index Scan using ci_builds_gitlab_monitor_metrics on ci_builds ci_builds_1  (cost=0.58..46
8154.40 rows=325382 width=16) (actual time=0.031..0.801 rows=30 loops=1)
                     Index Cond: (((status)::text = 'running'::text) AND (created_at < '2021-08-24 17:41:01.9
93524'::timestamp without time zone))
                     Buffers: shared hit=665
         ->  Index Scan using ci_builds_pkey on ci_builds  (cost=0.58..3.55 rows=1 width=1289) (actual time=0
.009..0.009 rows=0 loops=30)
               Index Cond: (id = ci_builds_1.id)
               Filter: ((updated_at < '2021-08-24 17:41:01.994189'::timestamp without time zone) AND ((type):
:text = 'Ci::Build'::text))
               Rows Removed by Filter: 1
               Buffers: shared hit=156
 Planning Time: 0.818 ms
 Execution Time: 10.773 ms
(17 rows)

Time: 13.633 ms

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Allison Browne