Rendering of project sidebar is slow for users who cannot push to the project
When rendering the sidebar, we load a lot of MRs into memory and iterate over these to check if they can be merged by the current user.
This comes from the can_view_pipeline_editor? check which calls can_collaborate_with_project?(project).
When the user can push to the project, this method returns true. But if not, it falls back to checking all open merge requests with allow_collaboration: true and iterates over each one to check if the current user can merge them: https://gitlab.com/gitlab-org/gitlab/-/blob/802e2b85c8e2c202be3f8a5eecf1bd328301979c/app/models/project.rb#L3458-3460
For gitlab-org/gitlab, there are 1,256 of these MRs:
gitlabhq_dblab=# SELECT COUNT(*) FROM merge_requests WHERE source_project_id = 278964 AND state_id = 1 AND allow_maintainer_to_push = TRUE;
count
-------
1256
(1 row)This also leads to 1k+ (cached) queries because we have an N+1 here. This results in slow response times (>2s) when loading any page under the gitlab-org/gitlab project.
Stack trace
D, [2023-09-16T00:40:52.636599 #1918190] DEBUG -- : CACHE Project Load (0.1ms) SELECT "projects"."id", "projects"."name", "projects"."path", "projects"."description", "projects"."created_at", "projects"."updated_at", "projects"."creator_id", "projects"."namespace_id", "projects"."last_activity_at", "projects"."import_url", "projects"."visibility_level", "projects"."archived", "projects"."merge_requests_template", "projects"."star_count", "projects"."merge_requests_rebase_enabled", "projects"."import_type", "projects"."import_source", "projects"."avatar", "projects"."approvals_before_merge", "projects"."reset_approvals_on_push", "projects"."merge_requests_ff_only_enabled", "projects"."issues_template", "projects"."mirror", "projects"."mirror_last_update_at", "projects"."mirror_last_successful_update_at", "projects"."mirror_user_id", "projects"."shared_runners_enabled", "projects"."runners_token", "projects"."build_allow_git_fetch", "projects"."build_timeout", "projects"."mirror_trigger_builds", "projects"."public_builds", "projects"."pending_delete", "projects"."last_repository_check_failed", "projects"."last_repository_check_at", "projects"."only_allow_merge_if_pipeline_succeeds", "projects"."has_external_issue_tracker", "projects"."repository_storage", "projects"."request_access_enabled", "projects"."has_external_wiki", "projects"."repository_read_only", "projects"."lfs_enabled", "projects"."description_html", "projects"."only_allow_merge_if_all_discussions_are_resolved", "projects"."repository_size_limit", "projects"."service_desk_enabled", "projects"."printing_merge_request_link_enabled", "projects"."auto_cancel_pending_pipelines", "projects"."cached_markdown_version", "projects"."last_repository_updated_at", "projects"."ci_config_path", "projects"."disable_overriding_approvers_per_merge_request", "projects"."delete_error", "projects"."storage_version", "projects"."resolve_outdated_diff_discussions", "projects"."remote_mirror_available_overridden", "projects"."only_mirror_protected_branches", "projects"."pull_mirror_available_overridden", "projects"."jobs_cache_index", "projects"."external_authorization_classification_label", "projects"."mirror_overwrites_diverged_branches", "projects"."external_webhook_token", "projects"."pages_https_only", "projects"."packages_enabled", "projects"."merge_requests_author_approval", "projects"."pool_repository_id", "projects"."runners_token_encrypted", "projects"."bfg_object_map", "projects"."detected_repository_languages", "projects"."merge_requests_disable_committers_approval", "projects"."ci_id", "projects"."require_password_to_approve", "projects"."max_pages_size", "projects"."max_artifacts_size", "projects"."pull_mirror_branch_prefix", "projects"."remove_source_branch_after_merge", "projects"."marked_for_deletion_at", "projects"."marked_for_deletion_by_user_id", "projects"."suggestion_commit_message", "projects"."autoclose_referenced_issues", "projects"."project_namespace_id", "projects"."hidden", "projects"."organization_id" FROM "projects" WHERE "projects"."id" = 278964 LIMIT 1
D, [2023-09-16T00:40:52.639896 #1918190] DEBUG -- : ↳ app/models/merge_request.rb:1517:in `can_be_merged_by?'
D, [2023-09-16T00:40:52.639963 #1918190] DEBUG -- : ↳ app/models/project.rb:3459:in `block (3 levels) in fetch_branch_allows_collaboration'
D, [2023-09-16T00:40:52.640028 #1918190] DEBUG -- : ↳ app/models/project.rb:3458:in `block (2 levels) in fetch_branch_allows_collaboration'
D, [2023-09-16T00:40:52.640347 #1918190] DEBUG -- : ↳ lib/gitlab/gitaly_client.rb:336:in `allow_n_plus_1_calls'
D, [2023-09-16T00:40:52.640429 #1918190] DEBUG -- : ↳ app/models/project.rb:3457:in `block in fetch_branch_allows_collaboration'
D, [2023-09-16T00:40:52.640526 #1918190] DEBUG -- : ↳ app/models/project.rb:3453:in `fetch_branch_allows_collaboration'
D, [2023-09-16T00:40:52.640627 #1918190] DEBUG -- : ↳ app/models/project.rb:2751:in `branch_allows_collaboration?'
D, [2023-09-16T00:40:52.640718 #1918190] DEBUG -- : ↳ lib/gitlab/user_access.rb:106:in `branch_allows_collaboration_for?'
D, [2023-09-16T00:40:52.640817 #1918190] DEBUG -- : ↳ lib/gitlab/user_access.rb:100:in `can_collaborate?'
D, [2023-09-16T00:40:52.640908 #1918190] DEBUG -- : ↳ lib/gitlab/user_access.rb:69:in `can_push_to_branch?'
D, [2023-09-16T00:40:52.640996 #1918190] DEBUG -- : ↳ lib/gitlab/cache/request_cache.rb:43:in `block (3 levels) in request_cache'
D, [2023-09-16T00:40:52.641071 #1918190] DEBUG -- : ↳ lib/gitlab/cache/request_cache.rb:43:in `block (2 levels) in request_cache'
D, [2023-09-16T00:40:52.641515 #1918190] DEBUG -- : ↳ app/controllers/concerns/checks_collaboration.rb:12:in `can_collaborate_with_project?'
D, [2023-09-16T00:40:52.641666 #1918190] DEBUG -- : ↳ app/controllers/projects/application_controller.rb:14:in `can_collaborate_with_project?'
D, [2023-09-16T00:40:52.641752 #1918190] DEBUG -- : ↳ app/helpers/ci/pipeline_editor_helper.rb:8:in `can_view_pipeline_editor?'
D, [2023-09-16T00:40:52.641861 #1918190] DEBUG -- : ↳ app/helpers/sidebars_helper.rb:308:in `project_sidebar_context_data'
D, [2023-09-16T00:40:52.641952 #1918190] DEBUG -- : ↳ ee/app/helpers/ee/sidebars_helper.rb:11:in `project_sidebar_context_data'
D, [2023-09-16T00:40:52.642046 #1918190] DEBUG -- : ↳ app/helpers/sidebars_helper.rb:31:in `project_sidebar_context'
D, [2023-09-16T00:40:52.642346 #1918190] DEBUG -- : ↳ app/helpers/sidebars_helper.rb:121:in `super_sidebar_nav_panel'
D, [2023-09-16T00:40:52.642454 #1918190] DEBUG -- : ↳ app/views/layouts/_page.html.haml:8