Improve closed/merged events queries performance on Projects::MergeRequestsController#show.json
EE port: https://gitlab.com/gitlab-org/gitlab-ee/merge_requests/3679
This merge request simplifies the way we query for merged and closed events information of a merge request when loading the merge request widget, while making it more performant.
Instead of querying for events
of a given merge request, we cache these values in merge_request_metrics
, which already has a 1-1
relation with merge_requests
.
Current queries we'll be replacing, in production:
Event Load (8.7ms) SELECT "events".* FROM "events" WHERE ("events"."author_id" IS NOT NULL) AND "events"."project_id" = 13083 AND "events"."target_id" = 3985770 AND "events"."target_type" = 'MergeRequest' AND "events"."action" = 7 ORDER BY "events"."id" DESC LIMIT 1 [["project_id", 13083], ["target_id", 3985770], ["target_type", "MergeRequest"], ["action", 7]]
Event Load (1.4ms) SELECT "events".* FROM "events" WHERE ("events"."author_id" IS NOT NULL) AND "events"."project_id" = 13083 AND "events"."target_id" = 3985770 AND "events"."target_type" = 'MergeRequest' AND "events"."action" = 3 ORDER BY "events"."id" DESC LIMIT 1 [["project_id", 13083], ["target_id", 3985770], ["target_type", "MergeRequest"], ["action", 3]]
Comparison
From:
explain analyze SELECT "events".* FROM "events" WHERE ("events"."author_id" IS NOT NULL) AND "events"."project_id" = 13083 AND "events"."target_id" = 3985770 AND "events"."target_type" = 'MergeRequest' AND "events"."action" = 3 ORDER BY "events"."id" DESC LIMIT 1;
Limit (cost=4.60..4.61 rows=1 width=42) (actual time=0.076..0.076 rows=1 loops=1)
-> Sort (cost=4.60..4.61 rows=1 width=42) (actual time=0.075..0.075 rows=1 loops=1)
Sort Key: id DESC
Sort Method: quicksort Memory: 25kB
-> Index Scan using index_events_on_target_type_and_target_id on events (cost=0.57..4.59 rows=1 width=42) (actual time=0.036..0.043 rows=1 loops=1)
Index Cond: (((target_type)::text = 'MergeRequest'::text) AND (target_id = 3985770))
Filter: ((author_id IS NOT NULL) AND (project_id = 13083) AND (action = 7))
Rows Removed by Filter: 1
Planning time: 0.166 ms
Execution time: 0.115 ms
explain analyze SELECT "events".* FROM "events" WHERE ("events"."author_id" IS NOT NULL) AND "events"."project_id" = 13083 AND "events"."target_id" = 3985770 AND "events"."target_type" = 'MergeRequest' AND "events"."action" = 7 ORDER BY "events"."id" DESC LIMIT 1;
Limit (cost=4.60..4.61 rows=1 width=42) (actual time=0.125..0.125 rows=0 loops=1)
-> Sort (cost=4.60..4.61 rows=1 width=42) (actual time=0.124..0.124 rows=0 loops=1)
Sort Key: id DESC
Sort Method: quicksort Memory: 25kB
-> Index Scan using index_events_on_target_type_and_target_id on events (cost=0.57..4.59 rows=1 width=42) (actual time=0.080..0.080 rows=0 loops=1)
Index Cond: (((target_type)::text = 'MergeRequest'::text) AND (target_id = 3985770))
Filter: ((author_id IS NOT NULL) AND (project_id = 13083) AND (action = 3))
Rows Removed by Filter: 2
Planning time: 0.267 ms
Execution time: 0.163 ms
To:
explain analyze SELECT "merge_request_metrics".* FROM "merge_request_metrics" WHERE "merge_request_metrics"."merge_request_id" = 3985770 LIMIT 1;
Limit (cost=0.43..4.45 rows=1 width=76) (actual time=0.019..0.019 rows=1 loops=1)
-> Index Scan using index_merge_request_metrics on merge_request_metrics (cost=0.43..4.45 rows=1 width=76) (actual time=0.018..0.018 rows=1 loops=1)
Index Cond: (merge_request_id = 3985770)
Planning time: 0.080 ms
Execution time: 0.045 ms
Results on staging
- Before:
0.707ms
- After:
0.125ms
- Which is around a
465%
improvement.
-
Changelog entry added, if necessary -
Documentation created/updated -
API support added -
Tests added for this feature/bug - Review
-
Has been reviewed by Frontend -
Has been reviewed by Backend -
Has been reviewed by Database
-
-
Conform by the merge request performance guides -
Conform by the style guides -
Squashed related commits together -
Internationalization required/considered
What are the relevant issue numbers?
Related to https://gitlab.com/gitlab-org/gitlab-ce/issues/36876