Use a materialized CTE for by_commit_sha's metadata lookup

What does this MR do and why?

For target projects reading the new partitioned merge_request_diff_commits table, MergeRequestDiff.by_commit_sha joined merge_request_commits_metadata as a plain INNER JOIN. Postgres inlines that join and applies sha as a per-row Filter, doing one metadata lookup per commit in the diff instead of using the (project_id, sha) index once. Cost scales with the number of commits in the diff.

This MR lets the metadata lookup be built as a WITH ... AS MATERIALIZED CTE instead. MATERIALIZED is an optimisation fence; without it Postgres re-inlines the CTE and the plan is unchanged.

The fence is opt-in, via a materialize_metadata: keyword argument on MergeRequestDiff.by_commit_sha, defaulting to false and forwarded through MergeRequest.by_commit_sha. It only pays off when the outer query already narrows to a handful of merge_requests rows. When the outer query is unselective, the fence blocks the EXISTS from being pulled into a semi-join and the plan loses its parallel workers, a net loss.

Call sites

Call site	Outer predicate	Fence
Ci::Pipeline#all_merge_requests	source_project_id + source_branch	yes
MergeRequest.by_related_commit_sha (via MergeRequestsFinder)	target_project_id	no
Note#merge_requests	target_project_id	no
Projects::CleanupService	merge_request_diffs.project_id	no

Only Ci::Pipeline#all_merge_requests opts in. The other three emit SQL byte-identical to master, so their plans are unchanged.

Database

Query shape for the opted-in call site, with binds in place of literals:

Query shape, before and after

Before:

SELECT merge_request_diffs.*
FROM (
  (SELECT merge_request_diffs.*
   FROM merge_request_diffs
   INNER JOIN merge_request_diff_commits
     ON merge_request_diffs.id = merge_request_diff_commits.merge_request_diff_id
   INNER JOIN merge_request_commits_metadata
     ON merge_request_commits_metadata.id = merge_request_diff_commits.merge_request_commits_metadata_id
    AND merge_request_commits_metadata.project_id IN ($1)
   WHERE merge_request_commits_metadata.sha = $2
     AND merge_request_diff_commits.project_id = $1)
) merge_request_diffs;

After:

WITH target_metadata AS MATERIALIZED (
  SELECT merge_request_commits_metadata.id
  FROM merge_request_commits_metadata
  WHERE merge_request_commits_metadata.project_id = $1
    AND merge_request_commits_metadata.sha = $2
)
SELECT merge_request_diffs.*
FROM (
  (SELECT merge_request_diffs.*
   FROM merge_request_diffs
   INNER JOIN merge_request_diff_commits
     ON merge_request_diffs.id = merge_request_diff_commits.merge_request_diff_id
    AND merge_request_diff_commits.project_id IN ($1)
   WHERE merge_request_diff_commits.merge_request_commits_metadata_id IN (SELECT id FROM target_metadata))
) merge_request_diffs;

Measured effect on the opted-in query, using shared buffer hits on a warm cache:

Case	Before	After
SHA absent from metadata	111	10
SHA present, 19-commit diff	21	25
SHA present in metadata but not in a 686-commit diff	3975	22

The point is that cost becomes independent of diff size. The middle row costs 4 buffers more, which is the constant CTE setup.

Query plans

Database Lab, gitlab-production-main:

Query	Before	After
Ci::Pipeline#all_merge_requests, SHA absent	https://postgres.ai/console/gitlab/gitlab-production-main/sessions/55974/commands/160025	https://postgres.ai/console/gitlab/gitlab-production-main/sessions/55974/commands/160027
Ci::Pipeline#all_merge_requests, SHA present, small diff	https://postgres.ai/console/gitlab/gitlab-production-main/sessions/55974/commands/160029	https://postgres.ai/console/gitlab/gitlab-production-main/sessions/55974/commands/160032
Ci::Pipeline#all_merge_requests, SHA present, 686-commit diff	https://postgres.ai/console/gitlab/gitlab-production-main/sessions/56045/commands/160035	https://postgres.ai/console/gitlab/gitlab-production-main/sessions/56045/commands/160037

References

• related to https://gitlab.com/gitlab-org/gitlab/-/work_items/627135
• https://gitlab.com/gitlab-org/gitlab/-/work_items/527241#note_3766434518

MR acceptance checklist

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