Remove cross-join from MergeRequest.by_blob_path
What does this MR do and why?
MergeRequest.by_blob_path joined merge_requests to merge_request_diffs and merge_request_diff_files in one statement. The MR diff tables are moving to their own database as part of the MR diffs decomposition, so the join becomes a cross-database query.
This MR resolves the match in three statements:
- Pluck the current relation's
latest_merge_request_diff_idvalues (mainonly). - Find which of those diffs touch the path through
merge_request_diff_files.old_path(diff tables only, a primary-key lookup on(merge_request_diff_id, relative_order)with partition pruning). - Filter the relation with the existing
by_latest_merge_request_diffsscope (mainonly).
The issue suggested driving the lookup from merge_request_diff_files scoped by project_id, but that table only has a bare project_id index: on a large project the lookup would have to test old_path on every diff-file row of the project, and a hot path (for example Gemfile.lock) would also return diff ids of closed merge requests and superseded diff versions. Driving from the already-filtered merge_requests relation keeps the old query's access pattern (bounded MR set, then primary-key probes into the diff files) and its result semantics.
The callers bound the candidate id list, but it can still exceed MAX_PLUCK (1,618 ids on gitlab-org/gitlab), so the diff files lookup runs in slices of MAX_PLUCK ids. The only caller is MergeRequestsFinder#by_blob_path, which returns none without a project, and both call sites (the blobPath GraphQL argument, whose validation requires state, targetBranches and createdAfter, and Ai::DuoWorkflow::ProjectReadiness) filter to opened merge requests targeting one branch and created in the last 30 days.
The rewrite is behind the merge_request_by_blob_path_without_cross_join feature flag (gitlab_com_derisk, disabled by default, checked with the Feature.current_request actor). The legacy join is unchanged and remains the default path. Rollout issue: #639523
One intentional behavior change: the old INNER JOIN returned a duplicate merge request row when one latest diff held two files with the same old_path (a rename plus a re-creation in the same diff). The rewrite returns each merge request once.
A query-shape spec guards the rewrite: with the flag enabled, no statement touching merge_request_diff_files may also reference merge_requests. It fails against the old join, and an inverse example pins the single-join statement with the flag disabled.
Related to https://gitlab.com/gitlab-org/gitlab/-/work_items/635369
Database review
Literals model the real callers: project 278964 (gitlab-org/gitlab), opened state, target branch master, a 30-day window and a hot path (Gemfile.lock). The IN lists shown below are illustrative placeholders; the linked S2 and S3 plans (from the reviewer's Database Lab session) use the full output of the preceding statement, 1,618 ids for this project. Since the review, S2 runs in slices of at most 1,000 ids (MAX_PLUCK) per statement, so the linked full-list plan is the upper bound for a single slice. The Before statement is also the flag-disabled (default) path; S1–S3 run with the flag enabled.
Before: one statement joining main and the diff tables
SELECT "merge_requests".* FROM "merge_requests"
INNER JOIN "merge_request_diffs" ON "merge_request_diffs"."id" = "merge_requests"."latest_merge_request_diff_id"
INNER JOIN "merge_request_diff_files" ON "merge_request_diff_files"."merge_request_diff_id" = "merge_request_diffs"."id"
WHERE "merge_requests"."target_project_id" = 278964
AND "merge_requests"."state_id" = 1
AND "merge_requests"."target_branch" = 'master'
AND merge_requests.created_at >= '2026-10-11'
AND "merge_request_diff_files"."old_path" = 'Gemfile.lock'https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/85631/commands/192468
After, S1: candidate latest diff ids (main only)
SELECT "merge_requests"."latest_merge_request_diff_id" FROM "merge_requests"
WHERE "merge_requests"."target_project_id" = 278964
AND "merge_requests"."state_id" = 1
AND "merge_requests"."target_branch" = 'master'
AND merge_requests.created_at >= '2026-10-11'https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/85631/commands/192469
After, S2: diffs touching the path (diff tables only)
SELECT DISTINCT "merge_request_diff_files"."merge_request_diff_id"
FROM "merge_request_diff_files"
WHERE "merge_request_diff_files"."merge_request_diff_id" IN (2531489276, 2531502318, 2531547790, 2531603124, 2531688457, 2531734209, 2531790612, 2531846935, 2531903348, 2531959761)
AND "merge_request_diff_files"."old_path" = 'Gemfile.lock'https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/86046/commands/192815
After, S3: final relation (main only)
SELECT "merge_requests".* FROM "merge_requests"
WHERE "merge_requests"."target_project_id" = 278964
AND "merge_requests"."state_id" = 1
AND "merge_requests"."target_branch" = 'master'
AND merge_requests.created_at >= '2026-10-11'
AND "merge_requests"."latest_merge_request_diff_id" IN (2531502318, 2531688457, 2531903348)https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/86046/commands/192820
S3 is shown with the caller's filters. The finder composes further filters, sorting and pagination on top, as before. When S2 matches nothing, S3 is skipped entirely and the scope returns none.
How to set up and validate locally
Feature.enable(:merge_request_by_blob_path_without_cross_join)
project = Project.find_by_full_path('<a project with merge requests>')
path = project.merge_requests.last.merge_request_diff.merge_request_diff_files.first.old_path
MergeRequestsFinder.new(User.first, project_id: project.id, blob_path: path).executeThe finder returns the merge requests whose latest diff touches path, and every statement in the query log touches either merge_requests or the diff tables, never both. With the flag disabled, the same call runs the legacy single-statement join.
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.