Optimizing merge_request search using latest_mr_diff_id

What does this MR do and why?

Before: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32831/commands/101199

After: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32831/commands/101197

\> 50% improvement

This is because of some database magic latest_merge_request_diff_id on the merge_requests table to skip the Nested Loop Semi Join

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

Before	After

How to set up and validate locally

1. Enable Feature flag: ::Feature.enable(:update_approval_rules_for_related_mrs)

2. Enable Coverage-check in settings

   

3. Configure .gitlab-ci.yml, set test coverage to 10%, and set job rules to manual

   test:
     script:
       - echo "Hello, World!"
       - echo 'Code coverage 10.0%'
     coverage: '/Code coverage \d+\.\d+/'
     when:
       manual

4. Trigger a main branch pipeline, do not manually execute the job

5. Create a new branch feature, increase the test coverage to 20%, create an MR of feature -> main, and execute the feature job manually

6. You can see that the Coverage-check on the MR page requires approval

7. Manually execute the pipeline of the main branch. You can see that the Coverage-check no longer requires approval

Related to #440185 (closed)

changed milestone to %Next 1-3 releases

added Category:Code Testing and Coverage automation:ml automation:ml wrong automation:self-triage-encouraged bugfunctional candidate17.6 cicdactive customer devopsverify feature flag grouppipeline execution missed:16.10 missed:16.11 missed:17.0 missed:17.1 missed:17.2 priority2 sectionci typebug workflowblocked labels

assigned to @mfanGitLab

added pipelinetier-1 label

added backend databasereview pending labels

	2 Warnings
	There were no new or modified feature flag YAML files detected in this MR. If the changes here are already controlled under an existing feature flag, please add the feature flagexists. Otherwise, if you think the changes here don't need to be under a feature flag, please add the label feature flagskipped, and add a short comment about why we skipped the feature flag. For guidance on when to use a feature flag, please see the documentation.
	The master pipeline status page reported failures in setup-test-env-fips If these jobs fail in your merge request with the same errors, then they are not caused by your changes. Please check for any on-going incidents in the incident issue tracker or in the #master-broken Slack channel.

	2 Messages
	CHANGELOG missing: If this merge request needs a changelog entry, add the Changelog trailer to the commit message you want to add to the changelog. If this merge request doesn't need a CHANGELOG entry, feel free to ignore this message.
	This merge request adds or changes files that require a review from the Database team.

This merge request requires a database review. To make sure these changes are reviewed, take the following steps:

1. Ensure the merge request has database and databasereview pending labels. If the merge request modifies database files, Danger will do this for you.
2. Prepare your MR for database review according to the docs.
3. Assign and mention the database reviewer suggested by Reviewer Roulette.

If you no longer require a database review, you can remove this suggestion by removing the database label and re-running the danger-review job.

Reviewer roulette

Category	Reviewer	Maintainer
backend	@daniel-prause (UTC+2)	@rkumar555 (UTC+2)
~"Verify"	Reviewer review is optional for ~"Verify"	@hfyngvason (UTC-4)

Please refer to documentation page for guidance on how you can benefit from the Reviewer Roulette, or use the GitLab Review Workload Dashboard to find other available reviewers.

If needed, you can retry the danger-review job that generated this comment.

Generated by Danger

added 1 commit

• 67aed0aa - Optimizing diff head search for only merge_heads

Compare with previous version

added 1 commit

• 93fa1c92 - Optimizing diff head search for only merge_heads

Compare with previous version

changed milestone to %17.6

added 1 commit

• 43404649 - Optimizing merge_request search from diffs

Compare with previous version

changed title from Optimizing diff head search for only merge_heads to Optimizing merge_request search using latest_mr_diff_id

changed the description

changed the description

Hey @alexbuijs ! Are you able to do the initial backend review here?

requested review from @alexbuijs

approved this merge request

requested review from @eugielimpin

added pipeline:mr-approved label

added pipelinetier-2 label and removed pipelinetier-1 label

Before you set this MR to auto-merge

This merge request will progress on pipeline tiers until it reaches the last tier: pipelinetier-3. We will trigger a new pipeline for each transition to a higher tier.

Before you set this MR to auto-merge, please check the following:

• You are the last maintainer of this merge request
• The latest pipeline for this merge request is pipelinetier-3 (You can find which tier it is in the pipeline name)
• This pipeline is recent enough (created in the last 8 hours)

If all the criteria above apply, please set auto-merge for this merge request.

See pipeline tiers and merging a merge request for more details.

E2E Test Result Summary

allure-report-publisher generated test report!

e2e-test-on-gdk: test report for 43404649

expand test summary

+--------------------------------------------------------------+
|                        suites summary                        |
+---------+--------+--------+---------+-------+-------+--------+
|         | passed | failed | skipped | flaky | total | result |
+---------+--------+--------+---------+-------+-------+--------+
| Package | 17     | 0      | 0       | 0     | 17    | ✅     |
| Govern  | 17     | 0      | 0       | 0     | 17    | ✅     |
| Plan    | 3      | 0      | 0       | 0     | 3     | ✅     |
| Verify  | 37     | 0      | 1       | 0     | 38    | ✅     |
| Secure  | 4      | 0      | 0       | 0     | 4     | ✅     |
| Release | 3      | 0      | 0       | 0     | 3     | ✅     |
| Manage  | 1      | 0      | 0       | 0     | 1     | ✅     |
| Create  | 6      | 0      | 0       | 0     | 6     | ✅     |
+---------+--------+--------+---------+-------+-------+--------+
| Total   | 88     | 0      | 1       | 0     | 89    | ✅     |
+---------+--------+--------+---------+-------+-------+--------+

e2e-test-on-cng: test report for 43404649

expand test summary

+------------------------------------------------------------------+
|                          suites summary                          |
+-------------+--------+--------+---------+-------+-------+--------+
|             | passed | failed | skipped | flaky | total | result |
+-------------+--------+--------+---------+-------+-------+--------+
| Create      | 139    | 0      | 21      | 17    | 160   | ✅     |
| Govern      | 79     | 0      | 11      | 8     | 90    | ✅     |
| Package     | 24     | 0      | 14      | 0     | 38    | ✅     |
| Verify      | 50     | 0      | 15      | 10    | 65    | ✅     |
| Plan        | 86     | 0      | 8       | 12    | 94    | ✅     |
| Data Stores | 33     | 0      | 10      | 0     | 43    | ✅     |
| Monitor     | 8      | 0      | 12      | 0     | 20    | ✅     |
| Manage      | 1      | 0      | 9       | 0     | 10    | ✅     |
| Ai-powered  | 0      | 0      | 2       | 0     | 2     | ➖     |
| Analytics   | 2      | 0      | 0       | 0     | 2     | ✅     |
| Secure      | 1      | 0      | 5       | 0     | 6     | ✅     |
| Configure   | 0      | 0      | 3       | 0     | 3     | ➖     |
| Release     | 5      | 0      | 1       | 0     | 6     | ✅     |
| Fulfillment | 2      | 0      | 7       | 0     | 9     | ✅     |
| ModelOps    | 0      | 0      | 1       | 0     | 1     | ➖     |
| Growth      | 0      | 0      | 2       | 0     | 2     | ➖     |
+-------------+--------+--------+---------+-------+-------+--------+
| Total       | 430    | 0      | 121     | 47    | 551   | ✅     |
+-------------+--------+--------+---------+-------+-------+--------+

e2e-test-on-omnibus: test report for 43404649

expand test summary

+---------------------------------------------------------------------+
|                           suites summary                            |
+----------------+--------+--------+---------+-------+-------+--------+
|                | passed | failed | skipped | flaky | total | result |
+----------------+--------+--------+---------+-------+-------+--------+
| Systems        | 6      | 0      | 1       | 0     | 7     | ✅     |
| Govern         | 44     | 0      | 2       | 0     | 46    | ✅     |
| Create         | 426    | 7      | 62      | 7     | 495   | ❌     |
| Verify         | 39     | 0      | 3       | 0     | 42    | ✅     |
| Release        | 3      | 0      | 0       | 0     | 3     | ✅     |
| Secure         | 5      | 0      | 0       | 1     | 5     | ✅     |
| Package        | 24     | 0      | 0       | 0     | 24    | ✅     |
| GitLab Metrics | 2      | 0      | 1       | 0     | 3     | ✅     |
| Manage         | 26     | 0      | 10      | 6     | 36    | ✅     |
| Analytics      | 2      | 0      | 0       | 0     | 2     | ✅     |
| Fulfillment    | 2      | 0      | 0       | 0     | 2     | ✅     |
| Plan           | 6      | 0      | 0       | 0     | 6     | ✅     |
| Data Stores    | 13     | 0      | 1       | 0     | 14    | ✅     |
| Monitor        | 4      | 0      | 1       | 0     | 5     | ✅     |
| Configure      | 1      | 0      | 0       | 0     | 1     | ✅     |
| Ai-powered     | 0      | 1      | 0       | 1     | 1     | ❌     |
+----------------+--------+--------+---------+-------+-------+--------+
| Total          | 603    | 8      | 81      | 15    | 692   | ❌     |
+----------------+--------+--------+---------+-------+-------+--------+

approved this merge request

requested review from @pedropombeiro

removed review request for @pedropombeiro

@mfanGitLab I don't understand how the steps from How to set up and validate locally link to what this MR does.

Before: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32831/commands/101199

(actual time=31.985..40.325 rows=19 loops=1)

After: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32831/commands/101197

(actual time=20.667..32.669 rows=10 loops=1)

Looks like we're losing 9 rows somewhere. Is this expected/correct?

requested review from @mbobin

approved this merge request

added pipelinetier-3 pipeline:run-e2e-omnibus-once labels and removed pipelinetier-2 label

added verify-reviewnot impacted label

enabled automatic add to merge train when checks pass

resolved all threads

removed pipeline:run-e2e-omnibus-once label

started a merge train

merged

mentioned in commit b2d7b72c

added workflowstaging-canary label and removed workflowblocked label

added workflowcanary label and removed workflowstaging-canary label

added workflowstaging label and removed workflowcanary label

added workflowproduction label and removed workflowstaging label

added workflowpost-deploy-db-staging label and removed workflowproduction label

added releasedcandidate label

added releasedpublished label and removed releasedcandidate label