Avoid N+1 when filtering events visible_to_user?

The following discussion from !60676 (merged) should be addressed:
@stanhu started a discussion: (+7 comments)
Events::RenderService below is supposed to redact notes that aren't visible to the user: https://gitlab.com/gitlab-org/gitlab/blob/cea7ba513c6d623ef921fba07910e98cca71435b/lib/banzai/object_renderer.rb#L37
Plus, I think this line causes N+1 queries:
[ gprd ] production> EventCollection.new(projects, offset: 0, filter: nil).to_a.map(&:present).select { |event| event.visible_to_user?(nil) }
D, [2021-05-12T02:31:32.169798 #2051] DEBUG -- :   CACHE Event Load (0.1ms)  SELECT "events".* FROM (SELECT "projects"."id" FROM "projects" WHERE "projects"."id" = 278964) parents_for_lateral JOIN LATERAL (SELECT "events".* FROM "events" WHERE (events.project_id = parents_for_lateral.id) ORDER BY "events"."id" DESC LIMIT 20) AS events ON true ORDER BY "events"."id" DESC LIMIT 20 OFFSET 0
D, [2021-05-12T02:31:32.172184 #2051] DEBUG -- :   CACHE DiscussionNote Load (0.0ms)  SELECT "notes".* FROM "notes" WHERE "notes"."type" = 'DiscussionNote' AND "notes"."id" IN (573148024, 573146921, 573146272, 573145988, 573145580, 573144894, 573144676, 573143955, 573142607)
D, [2021-05-12T02:31:32.173901 #2051] DEBUG -- :   CACHE User Load (0.0ms)  SELECT "users".* FROM "users" WHERE "users"."id" IN (5407295, 655908, 4310743, 4415483, 4312863, 8110537, 758045)
D, [2021-05-12T02:31:32.176256 #2051] DEBUG -- :   CACHE MergeRequest Load (0.1ms)  SELECT "merge_requests".* FROM "merge_requests" WHERE "merge_requests"."id" IN (99329529, 99574551, 97076184)
D, [2021-05-12T02:31:32.178611 #2051] DEBUG -- :   CACHE Issue Load (0.0ms)  SELECT "issues".* FROM "issues" WHERE "issues"."id" IN (77158883, 76691518, 84883832, 85775777, 24651869)
D, [2021-05-12T02:31:32.181169 #2051] DEBUG -- :   CACHE Note Load (0.0ms)  SELECT "notes".* FROM "notes" WHERE "notes"."id" IN (573147284, 573147262, 573145995, 573143993, 573143388, 573143193)
D, [2021-05-12T02:31:32.182487 #2051] DEBUG -- :   CACHE User Load (0.0ms)  SELECT "users".* FROM "users" WHERE "users"."id" IN (1674572, 2535118, 1786152, 5749348, 3913516)
D, [2021-05-12T02:31:32.184595 #2051] DEBUG -- :   CACHE MergeRequest Load (0.0ms)  SELECT "merge_requests".* FROM "merge_requests" WHERE "merge_requests"."id" IN (99584930, 98913662, 98427974, 98457020)
D, [2021-05-12T02:31:32.187223 #2051] DEBUG -- :   CACHE Issue Load (0.0ms)  SELECT "issues".* FROM "issues" WHERE "issues"."id" = 86895774
D, [2021-05-12T02:31:32.188750 #2051] DEBUG -- :   CACHE MergeRequest Load (0.0ms)  SELECT "merge_requests".* FROM "merge_requests" WHERE "merge_requests"."id" = 98427974
D, [2021-05-12T02:31:32.190309 #2051] DEBUG -- :   CACHE User Load (0.0ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 8401507
D, [2021-05-12T02:31:32.191870 #2051] DEBUG -- :   CACHE DiffNote Load (0.0ms)  SELECT "notes".* FROM "notes" WHERE "notes"."type" = 'DiffNote' AND "notes"."id" = 573145014
D, [2021-05-12T02:31:32.192903 #2051] DEBUG -- :   CACHE User Load (0.0ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 5749348
D, [2021-05-12T02:31:32.194767 #2051] DEBUG -- :   CACHE MergeRequest Load (0.0ms)  SELECT "merge_requests".* FROM "merge_requests" WHERE "merge_requests"."id" = 98427974
D, [2021-05-12T02:31:32.197210 #2051] DEBUG -- :   CACHE PushEventPayload Load (0.0ms)  SELECT "push_event_payloads".* FROM "push_event_payloads" WHERE "push_event_payloads"."event_id" IN (1255740834, 1255738230, 1255738200, 1255738165, 1255737436, 1255735391, 1255734487, 1255734442, 1255732991, 1255731085, 1255730701, 1255730069, 1255729758, 1255729518, 1255727975, 1255727918, 1255725589, 1255724906, 1255724564, 1255722638)
D, [2021-05-12T02:31:32.198708 #2051] DEBUG -- :   CACHE User Load (0.0ms)  SELECT "users".* FROM "users" WHERE "users"."id" IN (758045, 3913516, 5749348, 8110537, 4310743, 1786152, 4312863, 4415483, 5407295, 4059254, 4907528, 655908, 2535118, 1674572, 3397881)
D, [2021-05-12T02:31:32.201831 #2051] DEBUG -- :   CACHE Project Load (0.0ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 278964
D, [2021-05-12T02:31:32.203766 #2051] DEBUG -- :   CACHE ProjectFeature Load (0.0ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 278964
D, [2021-05-12T02:31:32.205527 #2051] DEBUG -- :   CACHE ProjectImportData Load (0.0ms)  SELECT "project_import_data".* FROM "project_import_data" WHERE "project_import_data"."project_id" = 278964
D, [2021-05-12T02:31:32.206398 #2051] DEBUG -- :   CACHE Namespace Load (0.0ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 9970
D, [2021-05-12T02:31:32.208509 #2051] DEBUG -- :   CACHE Project Load (0.0ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 278964 LIMIT 1  [["id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.211593 #2051] DEBUG -- :   CACHE ProjectFeature Load (0.0ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 278964 LIMIT 1  [["project_id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.216606 #2051] DEBUG -- :   CACHE Project Load (0.0ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 278964 LIMIT 1  [["id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.219459 #2051] DEBUG -- :   CACHE ProjectFeature Load (0.0ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 278964 LIMIT 1  [["project_id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.224438 #2051] DEBUG -- :   CACHE Project Load (0.0ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 278964 LIMIT 1  [["id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.227735 #2051] DEBUG -- :   CACHE ProjectFeature Load (0.0ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 278964 LIMIT 1  [["project_id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.232978 #2051] DEBUG -- :   CACHE Project Load (0.0ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 278964 LIMIT 1  [["id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.236027 #2051] DEBUG -- :   CACHE ProjectFeature Load (0.0ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 278964 LIMIT 1  [["project_id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.241857 #2051] DEBUG -- :   CACHE Project Load (0.0ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 278964 LIMIT 1  [["id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.245225 #2051] DEBUG -- :   CACHE ProjectFeature Load (0.0ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 278964 LIMIT 1  [["project_id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.250087 #2051] DEBUG -- :   CACHE Project Load (0.1ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 278964 LIMIT 1  [["id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.253873 #2051] DEBUG -- :   CACHE ProjectFeature Load (0.0ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 278964 LIMIT 1  [["project_id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.258934 #2051] DEBUG -- :   CACHE Project Load (0.0ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 278964 LIMIT 1  [["id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.260653 #2051] DEBUG -- :   CACHE Route Load (0.0ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 278964 AND "routes"."source_type" = 'Project' LIMIT 1  [["source_id", 278964], ["source_type", "Project"], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.273477 #2051] DEBUG -- :   CACHE ProjectFeature Load (0.1ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 278964 LIMIT 1  [["project_id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.280338 #2051] DEBUG -- :   CACHE Project Load (0.0ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 278964 LIMIT 1  [["id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.285833 #2051] DEBUG -- :   CACHE ProjectFeature Load (0.1ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 278964 LIMIT 1  [["project_id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.292074 #2051] DEBUG -- :   CACHE Project Load (0.0ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 278964 LIMIT 1  [["id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.296188 #2051] DEBUG -- :   CACHE ProjectFeature Load (0.0ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 278964 LIMIT 1  [["project_id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.301380 #2051] DEBUG -- :   CACHE Project Load (0.0ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 278964 LIMIT 1  [["id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.304602 #2051] DEBUG -- :   CACHE ProjectFeature Load (0.0ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 278964 LIMIT 1  [["project_id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.309793 #2051] DEBUG -- :   CACHE Project Load (0.0ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 278964 LIMIT 1  [["id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.314199 #2051] DEBUG -- :   CACHE Project Load (0.1ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 278964 LIMIT 1  [["id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.318152 #2051] DEBUG -- :   CACHE ProjectFeature Load (0.0ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 278964 LIMIT 1  [["project_id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.330823 #2051] DEBUG -- :   CACHE Project Load (0.0ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 278964 LIMIT 1  [["id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.334053 #2051] DEBUG -- :   CACHE ProjectFeature Load (0.0ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 278964 LIMIT 1  [["project_id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.338965 #2051] DEBUG -- :   CACHE Project Load (0.0ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 278964 LIMIT 1  [["id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.341851 #2051] DEBUG -- :   CACHE ProjectFeature Load (0.0ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 278964 LIMIT 1  [["project_id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.347111 #2051] DEBUG -- :   CACHE Project Load (0.0ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 278964 LIMIT 1  [["id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.350498 #2051] DEBUG -- :   CACHE ProjectFeature Load (0.0ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 278964 LIMIT 1  [["project_id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.355522 #2051] DEBUG -- :   CACHE Project Load (0.0ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 278964 LIMIT 1  [["id", 278964], ["LIMIT", 1]]
D, [2021-05-12T02:31:32.358550 #2051] DEBUG -- :   CACHE ProjectFeature Load (0.0ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 278964 LIMIT 1  [["project_id", 278964], ["LIMIT", 1]]
/cc @stanhu
Edited Sep 29, 2025 by 🤖 GitLab Bot 🤖