Load only the first note when building a discussion reply

Replying to a discussion made Notes::BuildService load every note in the thread, only to read the first note and the thread's resolved state, so every reply cost O(N) in the thread length.

This adds Note.first_note_of_discussion and Note#discussion_resolved?. A reply builds its discussion from the first note (to_discussion), and the resolved state still covers the whole thread: a reply to an open thread needs no further query, and when the first note is resolved one EXISTS query checks the other notes. Notes::BuildService uses them behind the reply_build_service_loads_first_note flag, which is off by default.

Locally, building a reply on a 4,529-note thread drops from 218 ms (open thread) and 5 s (fully resolved thread) to about 40 ms. Full numbers and plans are in the details below.

Database plans:

  • Best case, a reply to an open thread: one LIMIT 1 query for the first note (0.78 ms on a 3-note thread).
  • Worst case, a reply to a fully resolved 4,529-note thread: the first-note query (213 ms cold, the same buffers as master's whole-thread load) plus one unresolved.exists? that visits every row (23.8 ms).

Follow-ups planned to use the new methods:

  • !263496 - the post-save thread load in Notes::CreateService#update_discussions and TodoService.
  • GraphQL createNote replies, which load the whole thread only to check permissions against the first note (no MR yet).
  • Draft-note replies in DraftNotes::CreateService (no MR yet).
  • The first-note lookups already merged in !262926 (merged) (REST reply route) and !263097 (merged) (reply confidentiality check), to share one query.

Related to https://gitlab.com/gitlab-org/gitlab/-/work_items/634293

Detailed context for AI agents

Replaces !263491 (closed) (closed), rebased onto master after !263097 (merged) merged.

Background

Each reply to POST /projects/:id/merge_requests/:noteable_id/discussions/:discussion_id/notes cost O(N) in the thread length, so a long thread that keeps getting replies costs O(N^2) overall.

The reply path loaded the whole thread several times per request. A probe (request spec that subscribes to sql.active_record and logs every notes query without LIMIT, with backtraces) on current master, which already includes the API route fix, showed 4 whole-thread loads per reply:

  1. Notes::BuildService#find_discussion (app/services/notes/build_service.rb) - this MR
  2. and 3. Note#ensure_confidentiality_discussion_compliance via start_of_discussion?, once in note.valid? and again in note.save - fixed behind a flag by !263097 (merged) (merged)
  3. Notes::CreateService#update_discussions via note.discussion - !263496 (it also feeds the memoized discussion to TodoService#handle_note and when_saved, so those must change together)

With this MR the probe shows 3 loads (load 1 is gone).

The API route itself was fixed by !262926 (merged) (it used to render every note just to read the first).

The shared methods

The same "first note of a thread" query is repeated in the API route (!262926 (merged)), Note#discussion_first_note (!263097 (merged)) and Note#first_note_of_discussion (!263496). Two more reply paths still load the whole thread for first-note data: the GraphQL createNote reply authorization (app/graphql/mutations/notes/create/note.rb) and draft-note replies (app/services/draft_notes/create_service.rb). So the lookup and the resolved check live in the model, for those to reuse in follow-ups.

In Notes::Discussion (app/models/concerns/notes/discussion.rb):

  • .first_note_of_discussion(discussion_id), a class method called on a notes scope like find_discussion: with_discussion_ids(discussion_id).order_created_at_id_asc.first, one LIMIT 1 query.
  • #discussion_resolved?, called on the first note: the same answer as ResolvableDiscussion#resolved? for its thread, without loading it.
    • First note not potentially resolvable (individual notes, commit notes): false, no query.
    • First note open: false, no query. This is the common case, a reply into an active thread.
    • Otherwise (first_note.resolvable? || thread.resolvable.exists?) && !thread.unresolved.exists?: one unresolved EXISTS when the first note is resolved, and a resolvable EXISTS before it only when the first note is a system note.
  • It reads the other notes from noteable.notes.with_discussion_ids(discussion_id), the thread Note#discussion loads, so the answer does not depend on the caller's scope. potentially_resolvable? has already loaded the noteable, so this adds no query.
  • Before 119571b1 it was a class method that read the other notes from the scope it was called on, which only worked on the scope that found the first note (review feedback). With no project that scope was every note on the instance, and discussion ids are not globally unique (project import copies them).

Notes::BuildService with the flag on:

  • find_discussion returns first_note_of_discussion(id)&.to_discussion. Everything BuildService reads from the discussion apart from the resolved state comes from the first note: noteable (permission check), confidential?, can_reply_to_system_note?, can_convert_to_discussion? / convert_to_discussion!, and reply_attributes (type, noteable, commit, discussion id, and position and line code for diff threads), with the discussion class picked from the first note. to_discussion builds that one-note discussion without a query.
  • new_note asks discussion.first_note.discussion_resolved? instead of discussion.resolved?.
  • loads_first_note? is memoized, so find_discussion and the resolved check take the same side of the flag within a call.

Why the resolved check looks past the first note: single notes can still be resolved on their own (web route POST /-/notes/:id/resolve, REST PUT .../discussions/:discussion_id/notes/:note_id with resolved, and the work item endpoint from !259033 (merged)), so a thread can have a resolved first note and an open reply.

Earlier versions of this MR built a lazy Discussion over an unloaded relation and taught ResolvableDiscussion to answer in SQL. That was dropped: it changed a module every discussion uses, and anything that read notes on such a discussion (cache_key, resolved_at, iteration) silently loaded the whole thread again. ResolvableDiscussion is unchanged.

Why the SQL resolved check is equivalent

ResolvableDiscussion#resolved? is potentially_resolvable? && notes.any?(&:resolvable?) && notes.none?(&:to_be_resolved?).

  • potentially_resolvable? is delegated to the first note.
  • Note#resolvable? is potentially_resolvable? && !system?, and the resolvable scope is documented as kept in sync with it: type IN (DiffNote, DiscussionNote), noteable_type IN Noteable.resolvable_types, system = false. Noteable#supports_resolvable_notes? is resolvable_types.include?(base_class_name), and all notes in a thread share the noteable, so the per-note instance check and the scope agree.
  • to_be_resolved? is resolvable? && resolved_at.nil?, which is exactly the unresolved scope.
  • Checking the first note in Ruby before the SQL only skips a query; the SQL covers the first note's row too.

Converted individual notes: BuildService calls convert_to_discussion! before the resolved check, so the check gets the converted, unsaved DiscussionNote. It is resolvable and unresolved, so discussion_resolved? returns false with no query, as master's resolved? does on that one-note discussion.

Feature flag

  • reply_build_service_loads_first_note, gitlab_com_derisk, group::code review, milestone 19.6, actor project.
  • Rollout issue: #634690
  • Off: master exactly (find_discussion and discussion.resolved?). No shared code changes behaviour: ResolvableDiscussion is untouched, and the new model methods are only called with the flag on.
  • With no project (group-level noteables) the actor is nil, so those switch over only when the flag is fully on.

Tests

spec/models/note_spec.rb, "with a discussion of several notes":

  • .first_note_of_discussion: the first note in one query; ordered by created_at, then id (a reply created earlier wins); nil for an unknown discussion.
  • #discussion_resolved?: a table over the first note and a reply each being open, resolved or a system note (8 rows), each checked against the expected value and against find_discussion(...).resolved?; no notes query when the first note is open, and exactly one when it is resolved; a note on another merge request with the same discussion_id does not change the answer.

spec/services/notes/build_service_spec.rb, "when the discussion has several notes" and "when replying to an individual note on a merge request", flag on and off: whole-thread resolved state (including first note resolved but a later reply open, and system-note replies), one Note instantiated, no EXISTS when the first note is open, converted individual notes stay unresolved.

Run in an isolated rspec slot at 119571b1: build_service_spec.rb 42 examples, 0 failures; the note_spec.rb block 14 examples, 0 failures.

Mutation checks: dropping !thread.unresolved.exists? fails 3 examples (the "first resolved, reply open" and "first system, reply open" rows, and the one-query check); dropping the to_be_resolved? early return fails "does not query the thread when the first note is unresolved"; reading the thread from Note instead of noteable.notes fails the other-merge-request example.

Queries

These ran on a gitlab-production-main thin clone through Joe on 2026-11-16. The SQL is what Rails emits at 119571b1; the epic UNION ALL was captured on GDK2 from noteable.notes on a work item with a synced epic. The clone was reset first, each query ran once, and every number below is from that single run. The first query on each thread reads the thread's pages from disk; later queries on the same thread find them cached.

  • MR, typical: a 3-note, fully resolved thread on this MR.
  • MR, large: a 4,529-note, fully resolved thread on a private project.
  • Epic work item: an 8-note thread on a gitlab-org epic work item that has a synced legacy epic. Here noteable.notes is the EE unified-association UNION ALL, and project is nil. The clone's copy of the thread has an open note.
-- 1. first note, with a project (always)
SELECT "notes".* FROM "notes" WHERE "notes"."project_id" = <project_id> AND "notes"."discussion_id" = '<discussion_id>' ORDER BY "notes"."created_at" ASC, "notes"."id" ASC LIMIT 1

-- 1b. first note, no project (group-level noteables, such as epic work items)
SELECT "notes".* FROM "notes" WHERE "notes"."discussion_id" = '<discussion_id>' ORDER BY "notes"."created_at" ASC, "notes"."id" ASC LIMIT 1

-- 2. unresolved.exists? (only when the first note is resolved), single-table noteables such as merge requests
SELECT 1 AS one FROM "notes" WHERE "notes"."noteable_id" = <noteable_id> AND "notes"."noteable_type" = 'MergeRequest' AND "notes"."discussion_id" = '<discussion_id>' AND "notes"."type" IN ('DiffNote', 'DiscussionNote', 'Epic') AND "notes"."noteable_type" IN ('Issue', 'MergeRequest', 'DesignManagement::Design', 'WikiPage::Meta', 'Epic', 'ComplianceManagement::Projects::ComplianceViolation', 'Epic', 'ComplianceManagement::Projects::ComplianceViolation') AND "notes"."system" = FALSE AND "notes"."resolved_at" IS NULL LIMIT 1

-- 2b. unresolved.exists? on an epic work item with unified associations (EE). When the first note is on the legacy epic, the two branches swap order.
SELECT 1 AS one FROM ((SELECT "notes".* FROM "notes" WHERE "notes"."noteable_id" = <epic_id> AND "notes"."noteable_type" = 'Epic') UNION ALL (SELECT "notes".* FROM "notes" WHERE "notes"."noteable_id" = <work_item_id> AND "notes"."noteable_type" = 'Issue')) notes WHERE "notes"."discussion_id" = '<discussion_id>' AND "notes"."type" IN ('DiffNote', 'DiscussionNote', 'Epic') AND "notes"."noteable_type" IN ('Issue', 'MergeRequest', 'DesignManagement::Design', 'WikiPage::Meta', 'Epic', 'ComplianceManagement::Projects::ComplianceViolation', 'Epic', 'ComplianceManagement::Projects::ComplianceViolation') AND "notes"."system" = FALSE AND "notes"."resolved_at" IS NULL LIMIT 1

-- 3. resolvable.exists? (only when the first note is not resolvable, such as a system note): 2 or 2b without the resolved_at condition

-- master's find_discussion, the whole-thread load this replaces (without the project_id condition when there is no project)
SELECT "notes".* FROM "notes" WHERE "notes"."project_id" = <project_id> AND "notes"."discussion_id" = '<discussion_id>' ORDER BY "notes"."created_at" ASC, "notes"."id" ASC

Each cell links to its plan, then gives rows and shared buffers (hit / read).

Query MR, typical (3 notes) MR, large (4,529 notes) Epic work item (8 notes)
first note 0.78 ms, 1 row, 6 / 8 213 ms, 1 row, 7 / 4,168 1.4 ms (1b), 1 row, 7 / 12
unresolved.exists? 17.4 ms, 0 rows, 7 / 5 23.8 ms, 0 rows (4,529 filtered), 4,156 / 28 25.9 ms (2b), 1 row, 7 / 11
resolvable.exists? 0.11 ms, 1 row, 11 / 0 2.3 ms, 1 row, 40 / 0 0.19 ms (2b shape), 1 row, 18 / 0
master's find_discussion 0.10 ms, 3 rows, 14 / 0 23.2 ms, 4,529 rows, 4,175 / 0 0.13 ms (no project), 8 rows, 19 / 0

Notes on the plans:

  • No sequential scans. The MR EXISTS queries BitmapAnd index_notes_on_discussion_id with index_notes_on_noteable_id_and_noteable_type_system_author_id. The first-note and master queries use index_notes_on_discussion_id alone on the small threads, and BitmapAnd it with index_notes_on_project_id_and_noteable_type on the large one.
  • Epic union: Postgres pushes the thread conditions down into both UNION ALL branches, so each branch is its own index lookup and the union is never materialised. The work item branch uses the same BitmapAnd as the MR queries. The legacy epic branch scans the epic's own notes on index_notes_on_noteable_id_noteable_type_and_id and filters them by discussion. That branch stays small because epic notes now live on the work item. On both epics checked (this one, and an older one with 227 notes on its work item), the legacy Epic row has 2 notes.
  • The time in the small cases is first-touch I/O: 17.2 ms of the typical unresolved.exists? and 25.6 ms of the epic union are disk reads of 5 and 11 buffers.
  • The first-note query reads the same buffers as master's whole-thread load, because Postgres still visits every row of the thread to sort it. An index on (discussion_id, created_at, id) would make it a single index probe. Not part of this MR.

What a reply costs in the database on the large thread:

  • Open thread (first note unresolved): only the first-note query. It reads the same 4,175 buffers as master's load but returns 1 row instead of 4,529.
  • Fully resolved thread: the first-note query plus unresolved.exists?, which has to visit every row because none matches. That took 23.8 ms here and 12.3 ms in the 2026-11-13 run, with the same buffers; warm timings on the clone vary up to about 2x. In exchange Rails receives 1 row instead of 4,529 wide rows (about 2.7 kB each by the planner's width estimate), and EXPLAIN ANALYZE does not count sending those rows to the client.
Epic work item: unresolved.exists? (2b)
 Limit  (cost=0.70..21.27 rows=1 width=4) (actual time=25.807..25.809 rows=1 loops=1)
   Buffers: shared hit=7 read=11
   I/O Timings: read=25.638 write=0.000
   ->  Result  (cost=0.70..41.84 rows=2 width=4) (actual time=25.806..25.807 rows=1 loops=1)
         Buffers: shared hit=7 read=11
         I/O Timings: read=25.638 write=0.000
         ->  Append  (cost=0.70..41.82 rows=2 width=0) (actual time=25.805..25.806 rows=1 loops=1)
               Buffers: shared hit=7 read=11
               I/O Timings: read=25.638 write=0.000
               ->  Subquery Scan on "*SELECT* 1"  (cost=0.70..19.27 rows=1 width=0) (actual time=12.670..12.670 rows=0 loops=1)
                     Buffers: shared read=7
                     I/O Timings: read=12.598 write=0.000
                     ->  Index Scan using index_notes_on_noteable_id_noteable_type_and_id on public.notes notes_1  (cost=0.70..19.26 rows=1 width=1898) (actual time=12.669..12.669 rows=0 loops=1)
                           Index Cond: ((notes_1.noteable_id = <epic_id>) AND ((notes_1.noteable_type)::text = ANY ('{Issue,MergeRequest,DesignManagement::Design,WikiPage::Meta,Epic,ComplianceManagement::Projects::ComplianceViolation,Epic,ComplianceManagement::Projects::ComplianceViolation}'::text[])) AND ((notes_1.noteable_type)::text = 'Epic'::text))
                           Filter: ((NOT notes_1.system) AND (notes_1.resolved_at IS NULL) AND ((notes_1.discussion_id)::text = '<discussion_id>'::text) AND ((notes_1.type)::text = ANY ('{DiffNote,DiscussionNote,Epic}'::text[])))
                           Rows Removed by Filter: 2
                           Buffers: shared read=7
                           I/O Timings: read=12.598 write=0.000
               ->  Subquery Scan on "*SELECT* 2"  (cost=21.00..22.54 rows=1 width=0) (actual time=13.133..13.134 rows=1 loops=1)
                     Buffers: shared hit=7 read=4
                     I/O Timings: read=13.039 write=0.000
                     ->  Bitmap Heap Scan on public.notes notes_2  (cost=21.00..22.53 rows=1 width=1898) (actual time=13.132..13.133 rows=1 loops=1)
                           Recheck Cond: (((notes_2.discussion_id)::text = '<discussion_id>'::text) AND (notes_2.noteable_id = <work_item_id>) AND ((notes_2.noteable_type)::text = ANY ('{Issue,MergeRequest,DesignManagement::Design,WikiPage::Meta,Epic,ComplianceManagement::Projects::ComplianceViolation,Epic,ComplianceManagement::Projects::ComplianceViolation}'::text[])) AND ((notes_2.noteable_type)::text = 'Issue'::text) AND (NOT notes_2.system))
                           Filter: ((notes_2.resolved_at IS NULL) AND ((notes_2.type)::text = ANY ('{DiffNote,DiscussionNote,Epic}'::text[])))
                           Heap Blocks: exact=1
                           Buffers: shared hit=7 read=4
                           I/O Timings: read=13.039 write=0.000
                           ->  BitmapAnd  (cost=21.00..21.00 rows=1 width=0) (actual time=13.121..13.122 rows=0 loops=1)
                                 Buffers: shared hit=6 read=4
                                 I/O Timings: read=13.039 write=0.000
                                 ->  Bitmap Index Scan on index_notes_on_discussion_id  (cost=0.00..2.99 rows=105 width=0) (actual time=0.041..0.041 rows=8 loops=1)
                                       Index Cond: ((notes_2.discussion_id)::text = '<discussion_id>'::text)
                                       Buffers: shared hit=5
                                       I/O Timings: read=0.000 write=0.000
                                 ->  Bitmap Index Scan on index_notes_on_noteable_id_and_noteable_type_system_author_id  (cost=0.00..17.76 rows=11 width=0) (actual time=13.078..13.078 rows=15 loops=1)
                                       Index Cond: ((notes_2.noteable_id = <work_item_id>) AND ((notes_2.noteable_type)::text = ANY ('{Issue,MergeRequest,DesignManagement::Design,WikiPage::Meta,Epic,ComplianceManagement::Projects::ComplianceViolation,Epic,ComplianceManagement::Projects::ComplianceViolation}'::text[])) AND ((notes_2.noteable_type)::text = 'Issue'::text) AND (notes_2.system = false))
                                       Buffers: shared hit=1 read=4
                                       I/O Timings: read=13.039 write=0.000

Performance

Local measurement in the test environment (GDK2, isolated rspec slot), Notes::BuildService#execute for a reply with the flag off (master) and on, median of 15 runs after warm-up, inside ActiveRecord::Base.cache like a web request. Threads were built with realistic note bodies (about 640 characters, with cached HTML). These are test-environment times, not production latencies; the row counts, cache hits and ratios are what carry over. Measured on the earlier lazy-Discussion version of this MR; the current version sends the same queries and loads the same single note.

Thread master this branch
5 notes, open 38.8 ms, 5 Note rows 37.7 ms, 1 Note row
100 notes, resolved 105 ms, 100 rows, 109 cached queries 39.3 ms, 1 row
1,000 notes, open 74.8 ms, 1,000 rows 38.3 ms, 1 row
1,000 notes, resolved 746 ms, 1,000 rows, 1,009 cached queries 39.6 ms, 1 row
4,529 notes, open 218 ms, 4,529 rows 40.5 ms, 1 row
4,529 notes, resolved 5,040 ms, 4,529 rows, 4,538 cached queries, 4.0M allocations 43.4 ms, 1 row, 30k allocations

Why resolved threads are so expensive on master: find_discussion does not preload noteable, and ResolvableDiscussion#resolved? calls to_be_resolved? on every note, which calls potentially_resolvable?, which loads that note's noteable. On an open thread none? stops at the first unresolved note; on a fully resolved thread it walks all of them, so every reply instantiates the merge request once per note (served from the query cache, but each one is a new object). With the query cache off the same case ran 4,551 queries.

The whole Notes::CreateService reply (same harness, median of 7; the other reply flags at their spec default, on):

  • 4,529 notes, open: 598 ms on master, 427 ms on this branch (9,061 Note rows loaded against 4,533).
  • 4,529 notes, resolved: 6,194 ms on master, 462 ms on this branch.
  • 1,000 notes, open (15 runs, flag alternated three times): 187-194 ms on master, 148-153 ms on this branch.

The remaining whole-thread load in CreateService is update_discussions, which !263496 removes.

Break-it matrix

115 examples. For every thread below: first_note_of_discussion returns the first note of master's find_discussion; to_discussion on it matches master's discussion on every method BuildService reads (class, id, reply id, noteable, confidentiality, the system-note and conversion checks, reply attributes); and discussion_resolved? matches master's resolved? without any notes query lacking a LIMIT. Then Notes::BuildService, Notes::CreateService and DraftNotes::PublishService run with the flag on against off on fresh identical threads. Covered: every mix of open, resolved, system and resolved-by-push notes; diff and legacy diff threads; individual notes on MRs, issues and commits; issue, commit, project snippet, personal snippet and group work item threads (the last two with no project); internal threads replied to by a developer and a guest; ties on created_at, a reply created before the first note, and a deleted first note; unknown discussion ids and ids from another project. 0 failures at c65e2ff1, and with the flag on no notes query without LIMIT ran while building a reply. Not re-run since: a1ee4722 only changed the note_spec.rb setup, and 119571b1 only changes which scope the resolved check reads the other notes from.

Out of scope

  • Switching the other first-note lookups to first_note_of_discussion: the API route (merged), Note#discussion_first_note (merged), !263496, GraphQL createNote replies and draft-note replies. Follow-ups.
  • The validation loads (!263097 (merged)).
  • The post-save load in Notes::CreateService#update_discussions, and its memoized reuse by TodoService#handle_note and when_saved (!263496).
  • NewNoteWorker participants walk (https://gitlab.com/gitlab-org/gitlab/-/work_items/634351, !263500).
  • With no project, the first-note lookup still searches every note on the instance for the discussion id, as master's Note.find_discussion does.
  • Note#does_not_exceed_notes_limit? still runs a COUNT(*) over the noteable's notes on each validation. It is an index count, not a row load.
Edited by Marc Shaw

Merge request reports

Loading
Loading