Skip to content

Fix another timeout when searching for pipelines

Sean McGivern requested to merge fix-502-mrs-with-lots-of-versions into master

Database Checklist

When adding or modifying queries to improve performance:

  • Included the raw SQL queries of the relevant queries
  • Included the output of EXPLAIN ANALYZE and execution timings of the relevant queries

Using the same MR locally from https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/15063:

Benchmark.realtime { MergeRequestDiffCommit.where(merge_request_diff: mr.merge_request_diffs.order(id: :desc).limit(100)).limit(10_000).pluck('sha').uniq }
#   (5.1ms)  SELECT  "merge_request_diff_commits"."sha" FROM "merge_request_diff_commits" WHERE "merge_request_diff_commits"."merge_request_diff_id" IN (SELECT  "merge_request_diffs"."id" FROM "merge_request_diffs" WHERE "merge_request_diffs"."merge_request_id" = $1  ORDER BY "merge_request_diffs"."id" DESC LIMIT 100) LIMIT 10000  [["merge_request_id", 8120]]
# => 0.06469900000956841

Using fdroid/fdroiddata!1853 (merged), which hits the statement timeout currently:

EXPLAIN ANALYZE SELECT  "merge_request_diff_commits"."sha" FROM "merge_request_diff_commits" WHERE "merge_request_diff_commits"."merge_request_diff_id" IN (SELECT "merge_request_diffs"."id" FROM "merge_request_diffs" WHERE "merge_request_diffs"."merge_request_id" = 1331882 ORDER BY id DESC LIMIT 100) LIMIT 10000;
                                                                                                 QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------
 Limit  (cost=6929.36..20521.95 rows=10000 width=21) (actual time=8.447..9.885 rows=400 loops=1)
   ->  Nested Loop  (cost=6929.36..2733289.60 rows=2005769 width=21) (actual time=8.446..9.708 rows=400 loops=1)
         ->  HashAggregate  (cost=6928.78..6929.78 rows=100 width=4) (actual time=8.427..8.453 rows=100 loops=1)
               Group Key: merge_request_diffs.id
               ->  Limit  (cost=6927.28..6927.53 rows=100 width=4) (actual time=8.302..8.349 rows=100 loops=1)
                     ->  Sort  (cost=6927.28..6935.98 rows=3479 width=4) (actual time=8.301..8.326 rows=100 loops=1)
                           Sort Key: merge_request_diffs.id DESC
                           Sort Method: top-N heapsort  Memory: 29kB
                           ->  Bitmap Heap Scan on merge_request_diffs  (cost=55.39..6794.32 rows=3479 width=4) (actual time=0.884..7.327 rows=2626 loops=1)
                                 Recheck Cond: (merge_request_id = 1331882)
                                 Heap Blocks: exact=2569
                                 ->  Bitmap Index Scan on index_merge_request_diffs_on_merge_request_id  (cost=0.00..54.52 rows=3479 width=0) (actual time=0.496..0.496 rows=2626 loo
ps=1)
                                       Index Cond: (merge_request_id = 1331882)
         ->  Index Scan using index_merge_request_diff_commits_on_mr_diff_id_and_order on merge_request_diff_commits  (cost=0.57..27063.02 rows=20058 width=25) (actual time=0.008..0
.010 rows=4 loops=100)
               Index Cond: (merge_request_diff_id = merge_request_diffs.id)
 Planning time: 0.359 ms
 Execution time: 10.036 ms
(17 rows)

Time: 11.488 ms

With a limit of 1,000 MR diffs:

EXPLAIN ANALYZE SELECT  "merge_request_diff_commits"."sha" FROM "merge_request_diff_commits" WHERE "merge_request_diff_commits"."merge_request_diff_id" IN (SELECT "merge_request_diffs"."id" FROM "merge_request_diffs" WHERE "merge_request_diffs"."merge_request_id" = 1331882 ORDER BY id DESC LIMIT 1000) LIMIT 10000;
ERROR:  canceling statement due to statement timeout

General Checklist

Closes https://gitlab.com/gitlab-org/gitlab-ce/issues/39054 (again).

Edited by Yorick Peterse

Merge request reports