`Deployments::UpdateEnvironmentWorker` is running a slow, unindexed update query on every successful production deployment. In a 10,000-query sample of slow queries (each over ~1s) from gitlab.com, this worker was the single biggest contributor: 3,571 slow queries totaling about 6,066 seconds of database time. Mean 1.70s, p95 3.31s, max 14.49s. ## Top slow-query endpoints in the sample Ranked by total database time (count times cost), the biggest contributors (https://log.gprd.gitlab.net/app/r/s/30Ixc): | Endpoint | Slow queries | Total s | Mean s | p95 | Max | |---|--:|--:|--:|--:|--:| | Deployments::UpdateEnvironmentWorker | 3571 | 6066 | 1.70 | 3.31 | 14.5 | | Deployments::LinkMergeRequestWorker | 1606 | 5529 | 3.44 | 9.19 | 14.9 | | Projects::ImportExport::RelationExportWorker | 1099 | 1687 | 1.54 | 2.35 | 11.4 | | UpdateProjectStatisticsWorker | 468 | 1135 | 2.43 | 5.18 | 14.1 | | RepositoryUpdateRemoteMirrorWorker | 541 | 1032 | 1.91 | 4.58 | 11.2 | | WorkItems::RolledupDates::UpdateRolledupDatesEventHandler | 97 | 593 | 6.11 | 13.1 | 14.9 | ## Query Generated by `Deployment#update_merge_request_metrics!` in `app/models/deployment.rb` (around line 329), called from `Deployments::UpdateEnvironmentService` on each successful production deployment. ```sql UPDATE merge_request_metrics SET first_deployed_to_production_at = $1 WHERE merge_request_id IN ( SELECT merge_requests.id FROM merge_requests JOIN merge_request_metrics ON merge_request_metrics.merge_request_id = merge_requests.id WHERE merge_requests.target_project_id = $2 AND merge_requests.target_branch = $3 AND merge_request_metrics.first_deployed_to_production_at IS NULL AND merge_request_metrics.merged_at <= $4 AND merge_request_metrics.merged_at >= $5 ) AND merge_request_metrics.first_deployed_to_production_at IS NULL ``` Ruby source: ```ruby def update_merge_request_metrics! return unless environment.production? && success? merge_requests = project.merge_requests .joins(:metrics) .where(target_branch: ref, merge_request_metrics: { first_deployed_to_production_at: nil }) .where("merge_request_metrics.merged_at <= ?", finished_at) if previous_deployment merge_requests = merge_requests.where("merge_request_metrics.merged_at >= ?", previous_deployment.finished_at) end MergeRequest::Metrics .where(merge_request_id: merge_requests.select(:id), first_deployed_to_production_at: nil) .update_all(first_deployed_to_production_at: finished_at) end ``` ## Why it's slow Two separate problems: 1. **Missing lower bound in some cases.** The `merged_at >= $5` clause only gets added when `previous_deployment` exists. On the first production deployment, or when the previous deployment record is missing, that clause is dropped and the range covers every merge request ever merged into the branch. On a busy default branch, that is the entire history. 2. **No index supports the selective part of the query.** The set we actually want is "metrics in this project, merged in the range, not yet deployed to production." That not-yet-deployed set stays small per project, because a row leaves it permanently once deployed. But the existing index, `index_merge_request_metrics_on_first_deployed_to_production_at`, is a plain single-column index on a column that is NULL for almost every row, so the planner can't use it usefully. Instead, the plan drives off a `target_project_id` index on `merge_requests`, filters `target_branch` as a residual, then probes `metrics` per merge request. On a large branch, that's a lot of probes. ## Proposed fix Add a partial composite index, and consider dropping the near-useless single-column one: ```sql CREATE INDEX index_mr_metrics_on_project_merged_at_not_deployed ON merge_request_metrics (target_project_id, merged_at) WHERE first_deployed_to_production_at IS NULL; ``` `merge_request_metrics` already carries `target_project_id`, so this lets the planner start from the metrics side, land on the small not-yet-deployed set in the `merged_at` range, then join `merge_requests` only to check `target_branch`. It also helps the no-lower-bound case, since the partial predicate keeps the scanned set small regardless of range. Note for implementer: this needs a standard GitLab async index migration (`add_concurrent_index`, post-deploy), following the database migration style guide. The numbers above come from a 10k sample, so they are a lower bound on real impact. ## Update: the fix was the query, not a new index (!256809) The slow query does not need a new index. The problem is that it filtered only `merge_requests.target_project_id`, so Postgres had no predicate on `merge_request_metrics.target_project_id` and could not seek any `(target_project_id, merged_at)` index on the metrics table. It drove the plan from `merge_requests` and probed metrics once per MR in the project. Adding `merge_request_metrics.target_project_id` to the query lets the planner use the existing `index_mr_metrics_on_target_project_id_merged_at_nulls_last`. On `gitlab-org/gitlab` this took the query from ~31s to ~5ms. !256809 does this by scoping through the existing `MergeRequest.join_metrics(project.id)` scope. The partial index prepared in !254915 turns out to be unnecessary; the fixed query does not use it. Before/after, verified with `rails runner` (only the one `target_project_id` predicate differs): <details><summary>Before !256809</summary> ```sql UPDATE merge_request_metrics SET first_deployed_to_production_at = $1 WHERE merge_request_id IN ( SELECT merge_requests.id FROM merge_requests INNER JOIN merge_request_metrics ON merge_request_metrics.merge_request_id = merge_requests.id WHERE merge_requests.target_project_id = $2 AND merge_requests.target_branch = $3 AND merge_request_metrics.first_deployed_to_production_at IS NULL AND merge_request_metrics.merged_at <= $4 AND merge_request_metrics.merged_at >= $5 ) AND merge_request_metrics.first_deployed_to_production_at IS NULL ``` Plan ~31s: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/58244/commands/162915 </details> <details><summary>After !256809</summary> ```sql UPDATE merge_request_metrics SET first_deployed_to_production_at = $1 WHERE merge_request_id IN ( SELECT merge_requests.id FROM merge_requests INNER JOIN merge_request_metrics ON merge_request_metrics.merge_request_id = merge_requests.id WHERE merge_requests.target_project_id = $2 AND merge_request_metrics.target_project_id = $2 AND merge_requests.target_branch = $3 AND merge_request_metrics.first_deployed_to_production_at IS NULL AND merge_request_metrics.merged_at <= $4 AND merge_request_metrics.merged_at >= $5 ) AND merge_request_metrics.first_deployed_to_production_at IS NULL ``` Plan ~5ms, uses `index_mr_metrics_on_target_project_id_merged_at_nulls_last`: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/58244/commands/162917 </details> ## Table is over the size limit The `merge_request_metrics` table is past the large-table size threshold. The database dictionary (`db/docs/merge_request_metrics.yml`) records `table_size: over_limit`. There is an open alert issue tracking this: https://gitlab.com/gitlab-org/gitlab/-/issues/521859 There has been prior work to cut the number of indexes on this table, because it is oversized: https://gitlab.com/gitlab-org/gitlab/-/issues/331930 (closed, "Reduce the number of indexes used by merge_request_metrics"). No open issue proposes partitioning or re-sharding this table. The Cells sharding key is already set (`target_project_id` maps to `projects`), and the table lives in the `gitlab_main_org` schema. Because the table is over the limit, and there is a standing goal to cut indexes, adding a new index needs justification. Two points support this one: 1. The partial predicate `WHERE first_deployed_to_production_at IS NULL` keeps the index small. It only covers merge requests not yet deployed to production, and a row leaves that set permanently once deployed. 2. We can keep the table's index count flat by dropping an existing near-useless index in the same effort: `index_merge_request_metrics_on_first_deployed_to_production_at`. It is a plain single-column index on a column that is NULL for almost every row. A code check supports the drop. `first_deployed_to_production_at` is referenced in only three places, and none needs a standalone index on that column: - `Deployment#update_merge_request_metrics!`, the query this issue fixes, uses it as an `IS NULL` filter combined with `target_project_id`, `target_branch`, and a `merged_at` range. The new partial index serves this. - Value Stream Analytics stage events (`merge_request_first_deployed_to_production`, `issue_deployed_to_production`) use it as a stage timestamp inside project- or namespace-scoped queries. Those use the `target_project_id`-leading composite indexes, not a standalone one. There is no ORDER BY on the column anywhere. - A read-only API field (`lib/api/entities/merge_request.rb`) reads it per record, with no query on the column. Recommendation: drop `index_merge_request_metrics_on_first_deployed_to_production_at` as part of the synchronous follow-up MR. First confirm its scan count is effectively zero on GitLab.com via `pg_stat_user_indexes`, to be safe before removing it. ## Related follow-up (not part of this fix) `Deployments::LinkMergeRequestWorker` is the second biggest contributor in the same sample: 1,606 slow queries, about 5,529 seconds total. Its main queries already have good indexes (`index_merge_requests_for_latest_diffs_with_state_merged` and `index_on_merge_request_diffs_head_commit_sha`), so this looks like a volume and data-skew problem rather than a missing index. Now tracked separately in https://gitlab.com/gitlab-org/gitlab/-/work_items/628637, with production query plans and a proposed fix. ## Checklist - [ ] Prepare the async index with `prepare_async_index` (this MR) - [ ] After the async index is built on GitLab.com, add the synchronous `add_concurrent_index` migration in a follow-up MR - [ ] Verify the new plan uses the partial index via Postgres.ai on a large project - [ ] Confirm `index_merge_request_metrics_on_first_deployed_to_production_at` scan count is near zero on GitLab.com via `pg_stat_user_indexes` - [ ] Drop `index_merge_request_metrics_on_first_deployed_to_production_at` in the synchronous follow-up MR to keep the table's index count flat