## Summary A 10,000-row sample of PostgreSQL slow queries (each over ~1s) from gitlab.com, grouped by endpoint_id, shows `Deployments::LinkMergeRequestWorker` as the second biggest contributor: 1,606 slow queries, about 5,529 seconds of total database time. Mean 3.44s, p95 9.19s, max 14.89s. Related issue for the biggest contributor: https://gitlab.com/gitlab-org/gitlab/-/work_items/628635 ## How the worker works On each successful deployment, the worker links merge requests to the deployment by matching the deployed commits. The service is `Deployments::LinkMergeRequestsService` (`app/services/deployments/link_merge_requests_service.rb`). For each slice of up to 5,000 commits it runs several separate queries: a merge-commit lookup, a head-commit lookup via `merge_request_diffs`, a squash-commit lookup, and a cherry-picked-notes lookup. ## What we profiled We ran `EXPLAIN (ANALYZE, BUFFERS)` on Postgres.ai for all four query shapes, using one real project (`target_project_id 21032278`) and a 51-commit slice. Result: three of the four shapes are fast and well-indexed. All the slow time is in one shape, the head-commit-via-diffs lookup. Fast shapes (not a problem, listed for completeness): - Merge-commit lookup: about 53 ms. - Squash-commit lookup: about 8.5 ms, uses `index_merge_requests_on_target_project_id_and_squash_commit_sha`. - Cherry-picked-notes lookup: about 0.1 ms, uses `index_notes_for_cherry_picked_merge_requests` then the primary key. The cross-join hazard once flagged in https://gitlab.com/gitlab-org/gitlab/-/issues/321032 is now handled by that dedicated index, so this path is fine. ## The slow shape: head-commit via merge_request_diffs Built by `merge_requests_by_head_commit_sha` in `app/services/deployments/link_merge_requests_service.rb` (around line 107): ```ruby def merge_requests_by_head_commit_sha(commits) merge_request_diffs = MergeRequestDiff.by_head_commit_sha(commits) project.merge_requests.merged.by_latest_merge_request_diffs(merge_request_diffs) end ``` The SQL (the SELECT that feeds an INSERT ... ON CONFLICT DO NOTHING): ```sql SELECT merge_requests.id FROM merge_requests WHERE target_project_id = 21032278 AND state_id = 3 AND latest_merge_request_diff_id IN ( SELECT id FROM merge_request_diffs WHERE head_commit_sha IN (...51 shas...)) ``` The query plan (cold cache on the Postgres.ai clone): ``` Hash Join (cost=978.84..2101.94 rows=1 width=8) (actual time=6283.416..6422.624 rows=15 loops=1) Hash Cond: (merge_requests.latest_merge_request_diff_id = merge_request_diffs.id) Buffers: shared hit=96 read=3605 dirtied=45 I/O Timings: read=6402.490 -> Index Scan using idx_mrs_on_target_id_and_created_at_and_state_id on merge_requests (cost=0.57..1121.49 rows=828 width=16) (actual time=10.308..6074.773 rows=3387 loops=1) Index Cond: ((target_project_id = 21032278) AND (state_id = 3)) Buffers: shared hit=6 read=3412 dirtied=42 I/O Timings: read=6057.969 -> Hash (cost=971.19..971.19 rows=566 width=8) (actual time=346.041..346.043 rows=28 loops=1) -> Index Scan using index_on_merge_request_diffs_head_commit_sha on merge_request_diffs (cost=0.70..971.19 rows=566 width=8) (actual time=9.842..345.985 rows=28 loops=1) Execution time: ~6.42 s (I/O read 6.40 s) ``` ## Why it is slow Read the plan this way: - The probe side scans every merged merge request in the project. `idx_mrs_on_target_id_and_created_at_and_state_id` returns 3,387 rows for `(target_project_id, state_id=3)` and reads about 3,412 buffers (~28 MB), taking about 6.05s. - The build side, the diff lookup by `head_commit_sha`, returns only 28 rows in 346 ms. - The hash join keeps 15 rows. So the query reads 3,387 rows to return 15. The selective predicate, `latest_merge_request_diff_id IN (28 ids)`, is applied only as the hash-join condition. It is never pushed into the `merge_requests` index scan. Two contributing points: 1. Bad plan from a cardinality misestimate. The planner guessed the diff subquery returns 566 rows (actual 28) and the merged-MR scan returns 828 (actual 3,387). With those estimates a hash join over a full merged-MR scan looked cheaper than a nested loop driven by the diffs. 2. The wall time is inflated by cold cache on the clone (almost all of the 6.4s is I/O read). Production with warm buffers is faster. The structural problem stands though: it reads all merged MRs in the project instead of the handful that match. The ideal index already exists: `index_merge_requests_for_latest_diffs_with_state_merged` on `(latest_merge_request_diff_id, target_project_id) WHERE state_id = 3`. The planner just did not pick it, because of the misestimate above. ## Proposed fix (app-side, no new index) Make the planner drive from the small set of diff ids into the existing partial index, instead of scanning all merged MRs. In `merge_requests_by_head_commit_sha`, materialize the diff ids first, then pass the concrete array: ```ruby def merge_requests_by_head_commit_sha(commits) diff_ids = MergeRequestDiff.by_head_commit_sha(commits).pluck(:id) project.merge_requests.merged.by_latest_merge_request_diffs(diff_ids) end ``` A literal IN list of about 28 ids nudges the planner into an index scan on `latest_merge_request_diff_id` using `index_merge_requests_for_latest_diffs_with_state_merged`, rather than a full merged-MR hash-join scan. The tradeoff is one extra small round trip per slice. ### Why the literal id list, and not a CTE Passing the diff ids as literals (what `pluck` produces) is a common code-review flag, so we tested whether a `WITH ... AS MATERIALIZED` CTE gives the same win without loading ids into Ruby. It does not. We ran all three shapes on Postgres.ai (same project, same 51-commit slice) and compared buffers touched, which is the cache-independent measure of work: | Variant | Join / access | merge_requests rows read | Buffers | |---|---|--:|--:| | Baseline (subquery) | Hash Join, full merged-MR scan | 3,387 | 3,701 (~28.9 MB) | | MATERIALIZED CTE | Hash Semi Join, full merged-MR scan | 3,387 | 3,701 (~28.9 MB) | | Literal id list (`pluck`) | Index Scan on `index_merge_requests_for_latest_diffs_with_state_merged` | 15 | 126 (~1 MB) | The CTE keeps the exact bad plan: it still scans all 3,387 merged MRs and hashes them. Materializing the CTE does not change the row estimate (566 estimated, 28 actual), so the planner still prefers the full scan. Only the literal id list makes the planner use the partial index, cutting buffers from 3,701 to 126, about 29x less work. That work then scales with the number of matching diffs, not with the project's total merged-MR count. The array is bounded: the `COMMITS_PER_QUERY` slice caps the input, and `head_commit_sha` yields at most one diff per SHA, so the usual unbounded-`pluck` memory concern does not really apply here. This needs verification with `EXPLAIN` on Postgres.ai against a large project after the change, to confirm the new plan uses the partial index. The numbers above come from a 10k sample, so they are a lower bound on real impact. ## Checklist - [ ] Materialize the diff ids in `merge_requests_by_head_commit_sha` and pass the array - [ ] Verify the new plan uses `index_merge_requests_for_latest_diffs_with_state_merged` via Postgres.ai on a large project - [ ] Confirm the other three link paths (merge-commit, squash-commit, cherry-picked notes) are unaffected