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.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #377717 (closed)