Skip MR comment metrics refresh for notes that cannot set it
What does this MR do and why?
Every MR note and commit note runs Analytics::RefreshCommentsData from NewNoteWorker, a high-urgency worker on the primary. Each run upserts the MR's merge_request_metrics row. While first_comment_at is still null, it also reruns the first_comment_at UNION query (max 13.9 s on GitLab.com). Bot, service account and MR author notes can never set first_comment_at, so on bot-heavy MRs the cost grows with every note.
Behind the optimize_mr_comment_metrics_refresh flag (gitlab_com_derisk, project actor), this MR:
- Skips notes that can't set
first_comment_at.Notes::PostProcessServicenow callsRefreshCommentsData.for_note(note, eligible_only: true). With the flag on, that returns nil when the note author isn'thuman?, which matches the calculator'sby_humansfilter exactly. It also drops MRs the note author wrote, because their own notes never count, and returns nil when no MRs remain. The author check uses a newMergeRequest.not_authored_by_user_idscope (author_id IS DISTINCT FROM ?), so MRs whose author was deleted (author_idset to NULL) are kept. A plainauthor_id != xwould drop them. - Only upserts when the metrics row is missing. The shared
MergeRequestMetricsRefresh#executeuses the metrics row that's already loaded, and callsensure_metrics(theINSERT ... ON CONFLICTupsert) only when there is none. The flag is checked withProject.actor_from_id(mr.target_project_id), so the loop doesn't load each target project. Because the module is shared, this applies to all three refresh classes:RefreshCommentsData,RefreshApprovalsData(fromCreateApprovalEventWorker) andRefreshReassignData(fromCodeReviewMetricsWorker).
Note deletion still forces a recompute. Notes::DestroyService calls for_note(note) without eligible_only, so the filters don't apply there. This matters when a user is deleted: their notes move to the ghost user, and deleting one of them must still recompute first_comment_at from the remaining human notes.
Rollout scope: the flag changes the approval and reassign write paths too, not only comments. When enabling it, also watch merge_request_metrics writes from CreateApprovalEventWorker and Analytics::CodeReviewMetricsWorker. The computed values don't change. Both workers load the MR fresh, and nothing resets first_comment_at, first_approved_at or first_reassigned_at to nil, so skipping the upsert when a value is present can't leave stale data.
Why there's no licence check
The issue proposes skipping the refresh when the project lacks code_review_analytics. I left it out. first_comment_at also feeds the ClickHouse merge request aggregation engine (time_to_first_review, code_review_time), which is gated on read_cycle_analytics, not code_review_analytics. Merged MRs get the value recomputed at merge, but open MRs in projects without that licence would lose it. The controls above already remove all bot-driven cost, so the licence check would save little extra.
NewNoteWorker belongs to team_planning. The refresh code belongs to value_stream_management.
Database
With the flag on, RefreshCommentsData.for_note adds author_id IS DISTINCT FROM <note_author_id> (the new MergeRequest.not_authored_by_user_id scope) to the query that loads the note's merge requests. The only change from master is the trailing author_id predicate. It runs once per human note in NewNoteWorker. The predicate only filters rows already narrowed by id or by commit SHA.
1. MR note
SELECT "merge_requests".*
FROM "merge_requests"
WHERE "merge_requests"."id" = <merge_request_id>
AND "merge_requests"."author_id" IS DISTINCT FROM <note_author_id>Plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/74847/commands/179035
2. Commit note
Note#merge_requests is project.merge_requests.by_commit_sha(project, commit_id), which has two forms depending on mr_by_commit_sha_use_array_subquery.
SELECT "merge_requests".* FROM "merge_requests"
WHERE "merge_requests"."target_project_id" = 278964
AND (latest_merge_request_diff_id = ANY(ARRAY(
SELECT "merge_request_diffs"."id" FROM ((SELECT "merge_request_diffs".* FROM "merge_request_diffs"
INNER JOIN merge_request_diff_commits ON merge_request_diffs.id = merge_request_diff_commits.merge_request_diff_id
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 IN (278964)
WHERE "merge_request_commits_metadata"."sha" = '\x95d78f9409cf71b3435cbc573eeee027ba0090a0'
AND "merge_request_diff_commits"."project_id" = 278964)) merge_request_diffs)))
AND "merge_requests"."author_id" IS DISTINCT FROM 255Index Scan using index_merge_requests_on_latest_merge_request_diff_id on public.merge_requests (cost=193.38..224.73 rows=1 width=1041) (actual time=111.375..113.607 rows=1 loops=1)
Index Cond: (merge_requests.latest_merge_request_diff_id = ANY ((InitPlan 1).col1))
Filter: ((merge_requests.author_id IS DISTINCT FROM 255) AND (merge_requests.target_project_id = 278964))
Buffers: shared hit=28 read=114 dirtied=27
I/O Timings: read=104.082 write=0.000
InitPlan 1
-> Nested Loop (cost=1.71..192.81 rows=2 width=8) (actual time=17.821..105.650 rows=2 loops=1)
Buffers: shared hit=27 read=106 dirtied=26
-> Nested Loop (cost=1.14..191.12 rows=2 width=8) (actual time=9.683..65.011 rows=10 loops=1)
Buffers: shared hit=1 read=73 dirtied=11
-> Index Scan using merge_request_commits_metadata_1_project_id_sha_idx on gitlab_partitions_dynamic.merge_request_commits_metadata_1 merge_request_commits_metadata (cost=0.56..3.58 rows=1 width=8) (actual time=4.648..4.649 rows=1 loops=1)
Index Cond: ((merge_request_commits_metadata.project_id = 278964) AND (merge_request_commits_metadata.sha = '\x95d78f9409cf71b3435cbc573eeee027ba0090a0'::bytea))
Buffers: shared read=5
-> Index Scan using merge_request_diff_commits_b5_merge_request_commits_metadat_idx on gitlab_partitions_dynamic.merge_request_diff_commits_1 merge_request_diff_commits (cost=0.57..187.32 rows=21 width=16) (actual time=5.032..60.349 rows=10 loops=1)
Index Cond: (merge_request_diff_commits.merge_request_commits_metadata_id = merge_request_commits_metadata.id)
Filter: (merge_request_diff_commits.project_id = 278964)
Buffers: shared hit=1 read=68 dirtied=11
-> Index Only Scan using merge_request_diffs_pkey on public.merge_request_diffs (cost=0.58..0.84 rows=1 width=8) (actual time=4.061..4.061 rows=0 loops=10)
Index Cond: (merge_request_diffs.id = merge_request_diff_commits.merge_request_diff_id)
Heap Fetches: 17
Buffers: shared hit=26 read=33 dirtied=14
Settings: work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB'Plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/78056/commands/185557
References
- Resolves https://gitlab.com/gitlab-org/gitlab/-/work_items/636163
- Rollout issue: https://gitlab.com/gitlab-org/gitlab/-/work_items/636497
Screenshots or screen recordings
Not applicable: backend-only change with no UI.
How to set up and validate locally
Run these in the Rails console. Make sure the bot and the reviewer can comment on the MR.
- Enable the flag, pick an open MR, and clear its first comment:
Feature.enable(:optimize_mr_comment_metrics_refresh) mr = MergeRequest.opened.last mr.metrics.update!(first_comment_at: nil) - Add a note as a non-human user. Record
updated_atafterNotes::CreateServiceand beforeNotes::PostProcessService. Creating the note touches the MR, and the MR'safter_commit :ensure_metrics!callback bumpsupdated_aton its own. This MR only changes whatPostProcessServicedoes. With the flag on,PostProcessServicewrites nothing tomerge_request_metrics, soupdated_atdoesn't change:bot = User.project_bot.first # or any service account, for example the Duo review bot note = Notes::CreateService.new(mr.project, bot, noteable: mr, note: 'bot note').execute before = mr.metrics.reload.updated_at Notes::PostProcessService.new(note).execute mr.metrics.reload.updated_at == before # => true mr.metrics.first_comment_at # => nil - Add a note as a human who isn't the MR author.
first_comment_atgets set:reviewer = User.human.active.where.not(id: mr.author_id).find { |u| u.can?(:create_note, mr) } note = Notes::CreateService.new(mr.project, reviewer, noteable: mr, note: 'review').execute Notes::PostProcessService.new(note).execute mr.metrics.reload.first_comment_at # => note.created_at - To compare with master, run
Feature.disable(:optimize_mr_comment_metrics_refresh)and repeat steps 1 and 2. This timePostProcessServicebumpsupdated_atfor the bot note, somr.metrics.reload.updated_at == beforereturnsfalse.
Pick the reviewer with User.human, not project.team.members. Team members can include service accounts such as the Duo review bot, and those notes never set first_comment_at, with or without the flag.
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.