Count responded reviews in recommend reviewers workload
What does this MR do and why?
Recommend reviewers ranks candidates partly by pending_reviews, which only counted open MRs a reviewer hasn't responded to yet. Reviewers who approve or comment quickly showed 0 even with a dozen open MRs, so they were recommended more often than their real workload justified. This MR adds open MRs they have reviewed, approved, or requested changes on, at half weight. The context schema and the AI Gateway prompt are unchanged.
Details
Root cause. pending_reviews came from User#review_requested_open_merge_requests_count, which uses review_states: %w[unapproved unreviewed review_started]. A reviewer reported on the dogfooding issue showed 0 pending while being a reviewer on 13 open MRs (9 approved, 3 reviewed, 1 requested changes). Combined with the gateway prompt ranking rule coverage above workload, multi-area maintainers were picked for most multi-area MRs.
Change (ee/app/models/ai/catalog/foundational_flow/recommend_reviewers/context.rb):
- New count of open, non-archived MRs where the user is a reviewer in
reviewed,approved, orrequested_changes, throughMergeRequestsFinder(same params as the existing count, other states). pending_reviews= existing count +(responded * 0.5).round. It stays an integer, so the 1.0.0 context schema and the flow are untouched.- Computed after per-rule truncation, so only approvers actually sent to the flow pay for the second query (at most
MAX_APPROVERS_PER_RULEper rule). Truncation still ranks by the existing cached count. - Cached for 1 hour under its own key. A new
Usermethod would have been the natural home next to the existing count and its invalidation, butGitlab/OmniscientClassMethodsdisallows newUsermethods, so the count lives in the flow's context.
Trade-offs.
- Rounding: one responded review counts as 1 (0.5 rounds up). From two upward it is roughly half.
- Truncation on rules with more than the per-rule limit still uses the undercounted signal, so a fast responder can stay in the list while a lighter reviewer is cut. Everyone kept is sent with the corrected number.
- The new count can be up to 1 hour stale. It is not cleared by
User#invalidate_merge_request_cache_counts. - Cold cache: only the MR-created run in Sidekiq computes missing counts, capped at 10 per build. Generate and Refresh read the cache only, so an approver not counted in the last hour gets the existing count alone, as before this MR. In a run that hits the cap, approvers past it look lighter than the ones counted. See Database below.
Docs. Updated the workload bullet in doc/user/project/merge_requests/reviews/automatic_reviewer_assignment.md, including that Generate and Refresh only use counts cached in the last hour.
Database
One new read query: a count of the approver's open MRs in a responded review state. It runs only in Sidekiq, while the MR-created trigger builds the flow context (ExecuteMergeRequestCreatedWorkflowTriggersWorker). The sidebar's Generate and Refresh build the same context inside POST /api/v4/ai/duo_workflows/workflows, and there it only reads the cache (Gitlab::Runtime.sidekiq? check). Counts are cached per user for 1 hour and read with one read_multi per rule. Each Sidekiq build runs the query for at most 10 uncached approvers (MAX_UNCACHED_RESPONDED_COUNTS), least loaded first, through Rails.cache.fetch with race_condition_ttl so concurrent jobs reuse a count that just expired. Approvers without a cached count get no responded count for that run, the same as before this MR.
It is the existing User#review_requested_open_merge_requests_count finder query with different review_states (reviewed, approved, requested_changes instead of unreviewed, unapproved, review_started), so both counts use the same plan.
SQL and plans
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 (EXISTS (SELECT 1 FROM project_authorizations WHERE project_authorizations.user_id = <user_id> AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0, 10, 20))
AND (project_features.merge_requests_access_level > 0 OR project_features.merge_requests_access_level IS NULL)
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))Measured on a gitlab-production-main Database Lab clone for a heavy reviewer (1,135 merge_request_reviewers rows, 9 matching open MRs):
| Query | Time | Buffers (hit / read) |
|---|---|---|
New count, states (1, 3, 2), cold |
1.45 s | 5,015 / 1,510 |
New count, states (1, 3, 2), fresh clone, cold |
4.56 s | 3,267 / 3,258 |
Existing count, states (4, 0, 5), same user |
650 ms | 3,571 / 687 |
Plan shape (both counts): Index Scan on index_merge_request_reviewers_on_user_id (1,135 rows), then a nested loop into idx_merge_requests_on_unmerged_state_id per reviewer row. That per-row merge request lookup is where the reads go. Neither count is under 100 ms cold for a heavy reviewer. The new count adds that cost again for each approver sent, on a cache miss.
Aggregate (actual time=1446.384..1446.388 rows=1 loops=1)
Buffers: shared hit=5015 read=1510 dirtied=5
-> Nested Loop Left Join (actual time=1297.027..1446.374 rows=9 loops=1)
-> Nested Loop Anti Join (rows=9 loops=1)
-> Nested Loop Left Join (rows=9 loops=1)
-> Nested Loop (rows=9 loops=1)
-> Nested Loop (actual time=1282.283..1431.207 rows=9 loops=1)
Buffers: shared hit=4838 read=1493 dirtied=4
-> Index Scan using index_merge_request_reviewers_on_user_id on merge_request_reviewers (actual time=0.060..4.955 rows=1135 loops=1)
Buffers: shared hit=1751
-> Index Scan using idx_merge_requests_on_unmerged_state_id on merge_requests (actual time=1.256..1.256 rows=0 loops=1135)
Buffers: shared hit=3087 read=1493 dirtied=4
-> Index Scan using projects_pkey on projects (rows=1 loops=9)
-> Index Only Scan using index_project_authorizations_on_project_user_access_level on project_authorizations (rows=1 loops=9)
-> Index Scan using namespaces_pkey on namespaces (rows=1 loops=9)
-> Index Only Scan using index_namespace_settings_on_namespace_id_where_archived_true on namespace_settings (rows=0 loops=9)
-> Index Scan using index_project_features_on_project_id on project_features (rows=1 loops=9)Batching the per-approver counts into one grouped query was considered. It saves round trips but reads the same reviewer and merge request rows. It would also drop the finder's per-user project visibility check, so the counts would no longer match the existing count.
References
- Feedback: #603172 (comment 4079459755)
- Coverage-first ranking was added in gitlab-ai-gateway
e4c06dc78and promoted to stable in1b89a3c95. - This MR is quick relief for the automated run on MR creation, with no migration. Phase 2 moves selection into Rails: it scores reviewer sets on load (with windowed counts backed by a
(user_id, created_at)index onmerge_request_reviewers, which also removes this cache), rule coverage, availability, and merge rights, and uses Orbit for domain knowledge beyond CODEOWNERS. That work gets its own database review and is tracked in &24364.
How to set up and validate locally
bin/rspec ee/spec/models/ai/catalog/foundational_flow/recommend_reviewers/context_spec.rb(38 examples).- The new examples cover responded reviews counting at half weight, the responded count being added only for approvers kept after truncation, the per-build uncached cap (including the query count on a cold cache), and web requests reading only cached counts.
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.