Skip to content

Fix /admin/jobs failing to load due to statement timeout

Stan Hu requested to merge sh-fix-admin-jobs-controller-timing-out into master

The ORDER BY created_at DESC clause causes a sequential scan because there is no index on the created_at column. We can sort by id or by updated_at to make things fast.

Query plan:

gitlabhq_production=# explain analyze SELECT  "ci_builds".* FROM "ci_builds" WHERE "ci_builds"."type" IN ('Ci::Build')  ORDER BY id DESC LIMIT 30 OFFSET 0;        
                                                                        QUERY PLAN                                                                         
-----------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.57..16.57 rows=30 width=1195) (actual time=0.139..0.404 rows=30 loops=1)
   ->  Index Scan Backward using ci_builds_pkey on ci_builds  (cost=0.57..41504307.23 rows=77792430 width=1195) (actual time=0.138..0.401 rows=30 loops=1)
         Filter: ((type)::text = 'Ci::Build'::text)
 Planning time: 0.233 ms
 Execution time: 0.435 ms
(5 rows)

Closes #49767 (closed)

Edited by Stan Hu

Merge request reports