Draft: Optimize committer email query with LATERAL per-row lookup

Marc Shawrequested to merge
590452-committer-emails-lateral-query into master

What does this MR do and why?

MergeRequest#committer_emails_from_diff (used for approval-committer filtering, gated by approval_committer_emails_from_diff) resolves committer emails on the new-table read path. The previous query joined merge_request_diff_commits → merge_request_commits_metadata → merge_request_diff_commit_users in a way that led Postgres to hash-join the entire project's merge_request_commits_metadata partition. Its cost therefore scaled with the project's total metadata rows, not with the diff's commit count — the driver behind the high replica CPU this query is known for.

This MR resolves the emails through a LATERAL … LIMIT 1 per-row primary-key lookup on merge_request_commits_metadata (mirroring the existing MergeRequestDiffCommit.commit_shas_from_new_table) and returns DISTINCT emails. Cost becomes proportional to the diff's commit count. The returned set of committer emails is identical.

The legacy (pre-backfill) path — a UNION of the metadata and direct committer_id columns — is unchanged.

Query plans

Measured on the postgres.ai clone with merge_request_diff_id = 1579398920, project_id = 75793474 (~1,000 commits).

Before — whole-partition hash join (~15,499 buffers)

SELECT merge_request_diff_commit_users.email
FROM merge_request_diff_commits
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 = 75793474
INNER JOIN merge_request_diff_commit_users
  ON merge_request_diff_commit_users.id = merge_request_commits_metadata.committer_id
WHERE merge_request_diff_commits.merge_request_diff_id = 1579398920
  AND merge_request_diff_commit_users.email IS NOT NULL;

Plan reads the full project partition: Index Scan ... merge_request_commits_metadata_..._project_id_sha_idx (rows=11472), hashed, then probed — shared hit=15,499. Query plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/53537/commands/155650

After — LATERAL per-row PK lookup (~5,026 buffers)

SELECT DISTINCT merge_request_diff_commit_users.email
FROM merge_request_diff_commits
INNER JOIN LATERAL (
  SELECT committer_id
  FROM merge_request_commits_metadata
  WHERE merge_request_commits_metadata.id = merge_request_diff_commits.merge_request_commits_metadata_id
    AND merge_request_commits_metadata.project_id = 75793474
  LIMIT 1
) merge_request_commits_metadata ON TRUE
INNER JOIN merge_request_diff_commit_users
  ON merge_request_diff_commit_users.id = merge_request_commits_metadata.committer_id
WHERE merge_request_diff_commits.merge_request_diff_id = 1579398920
  AND merge_request_diff_commit_users.email IS NOT NULL;

Plan uses Index Scan using merge_request_commits_metadata_..._pkey per row (wrapped in Memoize) — shared hit≈5,026, and no longer scales with project size. Query plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/53537/commands/155649

Query Shared buffers
Before (hash join over project partition) ~15,499
After (LATERAL per-row PK) ~5,026

Notes

  • No new feature flag: this only changes the SQL shape of an already-flagged path (approval_committer_emails_from_diff + mr_diff_commits_read_new_table), returning identical results, so the existing flags remain the kill switch.
  • No changelog: internal query optimization behind existing, default-off flags.
  • Covered by the existing committer filtering equivalence with committers() specs plus a new same-committer dedup example.

References

Edited by Marc Shaw

Merge request reports