Fix another timeout when searching for pipelines
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
-
Changelog entry added, if necessary - Review
-
Has been reviewed by Database
-
Closes https://gitlab.com/gitlab-org/gitlab-ce/issues/39054 (again).
Edited by Yorick Peterse