Skip to content

Add index for author_id and created_at on merge_requests

What does this MR do and why?

We have found out that for few metrics from govern stage we have invalid values (https://gitlab.com/gitlab-org/gitlab/-/issues/378449) caused by timeouts on database (https://log.gprd.gitlab.net/app/discover#/?_g=h@9a10b8e&_a=h@6d8225f):

  • usage_activity_by_stage_monthly.govern.user_merge_requests_with_applied_scan_result_policies
  • usage_activity_by_stage_monthly.govern.user_merge_requests_for_projects_with_assigned_security_policy_project
  • usage_activity_by_stage_monthly.govern.count_distinct_merge_request_id_from_scan_finding_approval_merge_request_rules

After adding this index, these queries are performing as expected:

  • usage_activity_by_stage_monthly.govern.user_merge_requests_with_applied_scan_result_policies
explain SELECT DISTINCT "merge_requests"."author_id" FROM "merge_requests" INNER JOIN "approval_merge_request_rules" ON "approval_merge_request_rules"."merge_request_id" = "merge_requests"."id" WHERE "approval_merge_request_rules"."report_type" = 4 AND "merge_requests"."created_at" BETWEEN '2022-10-16 01:10:49.374014' AND '2022-11-13 01:10:49.374149' AND "merge_requests"."author_id" >= 156251 AND "merge_requests"."author_id" < 157501;
Time: 64.484 ms
  - planning: 1.636 ms
  - execution: 62.848 ms
    - I/O read: 57.814 ms
    - I/O write: 0.000 ms

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/13315/commands/46728

  • usage_activity_by_stage_monthly.govern.user_merge_requests_for_projects_with_assigned_security_policy_project
explain SELECT DISTINCT "merge_requests"."author_id" FROM "merge_requests" WHERE "merge_requests"."created_at" BETWEEN '2022-10-16 03:12:17.879158' AND '2022-11-13 03:12:17.879291' AND "merge_requests"."author_id" >= 1 AND "merge_requests"."author_id" < 1251;
Time: 21.633 ms
  - planning: 5.411 ms
  - execution: 16.222 ms
    - I/O read: 10.968 ms
    - I/O write: 0.000 ms```

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/13315/commands/46726


- `usage_activity_by_stage_monthly.govern.count_distinct_merge_request_id_from_scan_finding_approval_merge_request_rules`



```sql
explain SELECT DISTINCT "merge_requests"."author_id" FROM "merge_requests" INNER JOIN security_orchestration_policy_configurations ON merge_requests.target_project_id = security_orchestration_policy_configurations.project_id WHERE "merge_requests"."created_at" BETWEEN '2022-10-16 01:18:28.369547' AND '2022-11-13 01:18:28.369706' AND "merge_requests"."author_id" >= 72501 AND "merge_requests"."author_id" < 73751;
Time: 132.534 ms
  - planning: 1.840 ms
  - execution: 130.694 ms
    - I/O read: 120.854 ms
    - I/O write: 0.000 ms

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/13315/commands/46727

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #377717 (closed)

Merge request reports

Loading