Count only recent review requests in reviewer workload
What does this MR do and why?
Recommend reviewers picks approvers by how busy they are. Right now "busy" counts every open review request they have ever had, including stale ones, and takes two queries per approver to work out.
Behind the recommend_reviewers_windowed_workload flag (off by default):
- Only review requests from the last 15 days count.
- One query works out every approver's workload at once, instead of two per approver. It needs no Redis cache or Sidekiq-only limit.
With the flag off nothing changes, and the nav bar review count is never affected.
Detailed context for AI agents
Changes. In Ai::Catalog::FoundationalFlow::RecommendReviewers::Context, with the flag on:
windowed_review_countsruns onemerge_request_reviewersquery for all unique approvers: open MRs in non-archived projects, reviewer rows created in the last 15 days, grouped byuser_idandstate. It is memoized per build.pending_reviewsis theunapproved+unreviewed+review_startedtotal. The responded weight is half thereviewed+approved+requested_changestotal, rounded, as before.- This path has no Redis cache, no
MAX_UNCACHED_RESPONDED_COUNTScap and no Sidekiq-only guard. The query is cheap enough to run inside the Generate and Refresh API requests. - With the flag off,
pending_reviewsstaysuser.review_requested_open_merge_requests_countand the responded count keeps its cache, cap and Sidekiq-only guard from !265637 (merged).
Filters dropped compared with the per-reviewer finder count. The old counts went through MergeRequestsFinder as each reviewer, which also checks the reviewer's project and merge request access, archived ancestor namespaces and banned authors. On a production clone, for the 251 unique approvers of a gitlab-org/gitlab merge request (!266930 (merged)), the query returned the same 1,697 reviewer rows with and without each of those filters. The archived-project filter is kept because it is cheap.
Window semantics. A review request counts while its merge_request_reviewers row was created in the last 15 days, whether still pending or already responded to. Older requests on still-open MRs no longer add to the reviewer's workload. On the production clone, a 15-day window drops about 30% of heavy reviewers' open review assignments (small sample). Windowing on updated_at was considered: it saves only 1-2 assignments and cannot use an index without another one. A re-requested review keeps its row's original created_at, because RequestReviewService only resets state, so a re-request on a row older than 15 days is not counted. This gap is accepted: the updated_at comparison above, which would catch these rows, found only 1-2 of them. If it matters, the window can go up to 30 days.
Related. The replica-reads draft (recommend_reviewers_context_replica_reads, !266920 (merged)) moves these reads off the primary. The two are independent and can roll out in either order.
Verification.
ee/spec/models/ai/catalog/foundational_flow/recommend_reviewers/context_spec.rb: 43 examples pass. New examples cover the single grouped query for all approvers, responded reviews counted outside Sidekiq with the flag on, archived projects, and the 15-day window. The Sidekiq guard and cap examples now run with the flag off.- The generated SQL was explained on a production clone: a Bitmap Index Scan on
index_merge_request_reviewers_on_user_id_and_created_at.
Database
| Queries per build | Cold time | Buffers read cold | |
|---|---|---|---|
| Before (flag off) | Up to 2 per approver, cached in Redis | 5.6 s + 1.5 s for one heavy reviewer | 13.0k for one reviewer |
| After (flag on) | 1 for all approvers | 4.2 s for all 251 approvers (30 ms warm) | 14.9k for all 251 |
Each time links to its postgres.ai plan. All plans ran on a production clone, for the 251 approvers of a gitlab-org/gitlab merge request.
SQL and plan details
After: one query for all approvers (windowed_review_counts, once per context build with the flag on):
SELECT "merge_request_reviewers"."user_id", "merge_request_reviewers"."state", COUNT(*) FROM "merge_request_reviewers" INNER JOIN "merge_requests" ON "merge_requests"."id" = "merge_request_reviewers"."merge_request_id" INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id" WHERE "merge_requests"."state_id" = 1 AND "projects"."archived" = FALSE AND "merge_request_reviewers"."user_id" IN (<approver_ids>) AND "merge_request_reviewers"."created_at" >= '<15 days ago>' GROUP BY "merge_request_reviewers"."user_id", "merge_request_reviewers"."state"Cold plan: Bitmap Index Scan on index_merge_request_reviewers_on_user_id_and_created_at (from !266930 (merged), created on the clone), 553 groups from 1,697 rows, 4.18 s, buffers 34,720 hit / 14,901 read. Warm plan: 30.5 ms, 49,146 hit / 0 read.
The cold reads are 7.0k merge_request_reviewers heap pages and 7.5k merge_requests pages, one lookup per reviewer row in the window. An index on (user_id, created_at) INCLUDE (merge_request_id, state) would make the reviewer side index-only and remove the first 7.0k, if cold time needs to come down.
Before: pending count (User#review_requested_open_merge_requests_count, one query per approver, cached for 24 hours):
SELECT COUNT(*) FROM "merge_requests" INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id" LEFT OUTER JOIN "namespaces" ON "namespaces"."type" = 'Group' AND "namespaces"."id" = "projects"."namespace_id" AND "namespaces"."type" = 'Group' LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (NOT EXISTS (SELECT 1 FROM "banned_users" WHERE (banned_users.user_id = (merge_requests.author_id + 0)))) AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = <user_id> AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 15)) OR projects.visibility_level IN (10, 20)) AND ("project_features"."merge_requests_access_level" IS NULL OR "project_features"."merge_requests_access_level" IN (20, 30) OR ("project_features"."merge_requests_access_level" = 10 AND EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = <user_id> AND (project_authorizations.project_id = project_features.project_id) AND (project_authorizations.access_level >= 20)))) AND "merge_requests"."state_id" = 1 AND "projects"."archived" = FALSE AND NOT (EXISTS (SELECT 1 FROM "namespace_settings" WHERE "namespace_settings"."namespace_id" = ANY (namespaces.traversal_ids) AND "namespace_settings"."archived" = TRUE)) AND EXISTS (SELECT true FROM "merge_request_reviewers" WHERE merge_request_id = merge_requests.id AND "merge_request_reviewers"."user_id" = <user_id> AND "merge_request_reviewers"."state" IN (4, 0, 5))Plan for a heavy reviewer: Index Scan on index_merge_request_reviewers_on_user_id, 5.57 s cold, buffers 7,246 hit / 6,686 read.
Before: responded count (one query per approver that is kept, cached for 1 hour; on a miss, Sidekiq only and at most 10 per build):
SELECT COUNT(*) FROM "merge_requests" INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id" LEFT OUTER JOIN "namespaces" ON "namespaces"."type" = 'Group' AND "namespaces"."id" = "projects"."namespace_id" AND "namespaces"."type" = 'Group' LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (NOT EXISTS (SELECT 1 FROM "banned_users" WHERE (banned_users.user_id = (merge_requests.author_id + 0)))) AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = <user_id> AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 15)) OR projects.visibility_level IN (10, 20)) AND ("project_features"."merge_requests_access_level" IS NULL OR "project_features"."merge_requests_access_level" IN (20, 30) OR ("project_features"."merge_requests_access_level" = 10 AND EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = <user_id> AND (project_authorizations.project_id = project_features.project_id) AND (project_authorizations.access_level >= 20)))) AND "merge_requests"."state_id" = 1 AND "projects"."archived" = FALSE AND NOT (EXISTS (SELECT 1 FROM "namespace_settings" WHERE "namespace_settings"."namespace_id" = ANY (namespaces.traversal_ids) AND "namespace_settings"."archived" = TRUE)) AND EXISTS (SELECT true FROM "merge_request_reviewers" WHERE merge_request_id = merge_requests.id AND "merge_request_reviewers"."user_id" = <user_id> AND "merge_request_reviewers"."state" IN (1, 3, 2))Plan for the same reviewer: Index Scan on index_merge_request_reviewers_on_user_id, 1.51 s cold with part of it in the OS cache, buffers 6,519 hit / 6,311 read.
Both old counts read every reviewer row the user has ever had.
References
- Investigation (confidential): https://gitlab.com/gitlab-org/gitlab/-/work_items/637682
- Follow-up to: !265637 (merged)
- Related: !266920 (merged)
- Index (merged): !266930 (merged)
Screenshots or screen recordings
Not applicable: no UI change.
How to set up and validate locally
- In the Rails console, pick a merge request with approval rules and enable the flag for its project:
mr = MergeRequest.find(<id>) Feature.enable(:recommend_reviewers_windowed_workload, mr.project) - Make one of the approvers' open review requests older than 15 days:
MergeRequestReviewer.where(user_id: <approver_id>).first.update!(created_at: 20.days.ago) - Build the context and check that approver's
pending_reviewsno longer includes it, then disable the flag and compare:Ai::Catalog::FoundationalFlow::RecommendReviewers::Context.call(resource: mr)
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.