Skip to content

CI::Pipeline.latest_successful_for performs badly

Per http://performance.gitlab.net/dashboard/db/method-call-timings?var-process_type=rails&var-method=Ci::Pipeline.latest_successful_for&var-database=Production this method in many cases takes around 4 seconds to complete. This method is called on the project dashboard so it can be quite annoying for users.

The query executed by this method is as follows (for gitlab-org/gitlab-ce):

SELECT  "ci_commits".* 
FROM "ci_commits" 
WHERE "ci_commits"."gl_project_id" = 13083 
AND "ci_commits"."status" = 'success' 
AND "ci_commits"."ref" = 'a9cf230d96f024f18ab720178bb7f63bdadd6649' 
AND "ci_commits"."id" IN (
    SELECT max("ci_commits".id) 
    FROM "ci_commits" 
    WHERE "ci_commits"."gl_project_id" = 13083 
    AND "ci_commits"."status" = 'success' 
    GROUP BY "ci_commits"."ref", "ci_commits"."sha"
)
ORDER BY "ci_commits"."id" DESC LIMIT 1;

For GitLab.com this spits out the following plan:

                                                                                         QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=35029.87..35029.87 rows=1 width=1306) (actual time=200.177..200.177 rows=0 loops=1)
   ->  Sort  (cost=35029.87..35029.87 rows=1 width=1306) (actual time=200.175..200.175 rows=0 loops=1)
         Sort Key: ci_commits.id DESC
         Sort Method: quicksort  Memory: 25kB
         ->  Nested Loop  (cost=33338.79..35029.86 rows=1 width=1306) (actual time=200.162..200.162 rows=0 loops=1)
               ->  HashAggregate  (cost=33338.36..33340.36 rows=200 width=4) (actual time=69.476..79.330 rows=15193 loops=1)
                     Group Key: max(ci_commits_1.id)
                     ->  HashAggregate  (cost=33096.98..33204.26 rows=10728 width=56) (actual time=48.958..60.563 rows=15193 loops=1)
                           Group Key: ci_commits_1.ref, ci_commits_1.sha
                           ->  Bitmap Heap Scan on ci_commits ci_commits_1  (cost=302.68..33016.31 rows=10756 width=56) (actual time=5.777..32.369 rows=15213 loops=1)
                                 Recheck Cond: ((gl_project_id = 13083) AND ((status)::text = 'success'::text))
                                 Heap Blocks: exact=13289
                                 ->  Bitmap Index Scan on index_ci_commits_on_gl_project_id_and_status  (cost=0.00..299.99 rows=10756 width=0) (actual time=3.230..3.230 rows=15213 loops=1)
                                       Index Cond: ((gl_project_id = 13083) AND ((status)::text = 'success'::text))
               ->  Index Scan using ci_commits_pkey on ci_commits  (cost=0.43..8.44 rows=1 width=1306) (actual time=0.007..0.007 rows=0 loops=15193)
                     Index Cond: (id = (max(ci_commits_1.id)))
                     Filter: ((gl_project_id = 13083) AND ((status)::text = 'success'::text) AND ((ref)::text = 'a9cf230d96f024f18ab720178bb7f63bdadd6649'::text))
                     Rows Removed by Filter: 1
 Planning time: 0.360 ms
 Execution time: 200.412 ms

The problem appears to be ref not being indexed. We could add this column to the index index_ci_commits_on_gl_project_id_and_status since that already includes gl_project_id and status.

Visualised: https://explain.depesz.com/s/Qwe