Skip to content

Improve closed/merged events queries performance on Projects::MergeRequestsController#show.json

Oswaldo Ferreira requested to merge osw-introduce-merge-request-statistics into master

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.

What are the relevant issue numbers?

Related to https://gitlab.com/gitlab-org/gitlab-ce/issues/36876

Edited by Oswaldo Ferreira

Merge request reports