Viewing a MR can result to SQL timeout due to User#assigned_open_merge_requests_count query

It was reported that there are times that viewing a MR will result to query timeout. This is because the User#assigned_open_merge_requests_count query takes more than 15s.

Backtrace

lib/gitlab/database/load_balancing/connection_proxy.rb:111:in `public_send', lib/gitlab/database/load_balancing/connection_proxy.rb:111:in `block in read_using_load_balancer', lib/gitlab/database/load_balancing/load_balancer.rb:65:in `read', lib/gitlab/database/load_balancing/connection_proxy.rb:110:in `read_using_load_balancer', lib/gitlab/database/load_balancing/connection_proxy.rb:48:in `select_all', app/models/user.rb:2200:in `block in assigned_open_merge_requests_count', app/models/user.rb:2191:in `assigned_open_merge_requests_count', app/helpers/sidebars_helper.rb:89:in `super_sidebar_logged_in_context', app/helpers/sidebars_helper.rb:42:in `super_sidebar_context', ee/app/helpers/ee/sidebars_helper.rb:38:in `super_sidebar_context', app/views/layouts/_page.html.haml:8, app/views/layouts/application.html.haml:20, app/views/layouts/project.html.haml:31, app/controllers/application_controller.rb:154:in `render', app/controllers/projects/merge_requests_controller.rb:492:in `render_html_page', app/controllers/projects/merge_requests_controller.rb:421:in `block (2 levels) in show_merge_request', app/controllers/projects/merge_requests_controller.rb:416:in `show_merge_request', app/controllers/projects/merge_requests_controller.rb:99:in `show', app/controllers/application_controller.rb:542:in `block in allow_gitaly_ref_name_caching', lib/gitlab/gitaly_client.rb:479:in `allow_ref_name_caching', app/controllers/application_controller.rb:541:in `allow_gitaly_ref_name_caching', app/controllers/application_controller.rb:493:in `set_current_admin', lib/gitlab/session.rb:11:in `with_session', app/controllers/application_controller.rb:484:in `set_session_storage', lib/gitlab/i18n.rb:116:in `with_locale', lib/gitlab/i18n.rb:122:in `with_user_locale', app/controllers/application_controller.rb:475:in `set_locale', app/controllers/application_controller.rb:464:in `set_current_context', lib/gitlab/ip_address_state.rb:11:in `with', app/controllers/application_controller.rb:470:in `set_current_ip_address', lib/gitlab/middleware/action_controller_static_context.rb:23:in `call', config/initializers/action_dispatch_journey_router.rb:52:in `block in find_routes', config/initializers/action_dispatch_journey_router.rb:25:in `map!', config/initializers/action_dispatch_journey_router.rb:25:in `find_routes', ee/lib/omni_auth/strategies/group_saml.rb:41:in `other_phase', lib/gitlab/metrics/elasticsearch_rack_middleware.rb:16:in `call', lib/gitlab/middleware/sidekiq_shard_awareness_validation.rb:20:in `block in call', lib/gitlab/sidekiq_sharding/validator.rb:42:in `enabled', lib/gitlab/middleware/sidekiq_shard_awareness_validation.rb:20:in `call', lib/gitlab/middleware/memory_report.rb:13:in `call', lib/gitlab/middleware/speedscope.rb:13:in `call', lib/gitlab/database/load_balancing/rack_middleware.rb:23:in `call', lib/gitlab/middleware/rails_queue_duration.rb:33:in `call', lib/gitlab/etag_caching/middleware.rb:21:in `call', lib/gitlab/metrics/rack_middleware.rb:16:in `block in call', lib/gitlab/metrics/web_transaction.rb:46:in `run', lib/gitlab/metrics/rack_middleware.rb:16:in `call', lib/gitlab/middleware/go.rb:21:in `call', lib/gitlab/middleware/query_analyzer.rb:11:in `block in call', lib/gitlab/database/query_analyzer.rb:83:in `within', lib/gitlab/middleware/query_analyzer.rb:11:in `call', lib/ci/job_token/middleware.rb:11:in `call', lib/gitlab/middleware/multipart.rb:173:in `call', lib/gitlab/middleware/read_only/controller.rb:50:in `call', lib/gitlab/middleware/read_only.rb:18:in `call', lib/gitlab/middleware/unauthenticated_session_expiry.rb:18:in `call', lib/gitlab/middleware/secure_headers.rb:11:in `call', lib/gitlab/middleware/same_site_cookies.rb:27:in `call', lib/gitlab/middleware/path_traversal_check.rb:40:in `call', lib/gitlab/middleware/handle_malformed_strings.rb:21:in `call', lib/gitlab/middleware/basic_health_check.rb:25:in `call', lib/gitlab/middleware/handle_ip_spoof_attack_error.rb:25:in `call', lib/gitlab/middleware/request_context.rb:15:in `call', lib/gitlab/middleware/webhook_recursion_detection.rb:15:in `call', config/initializers/fix_local_cache_middleware.rb:11:in `call', lib/gitlab/middleware/compressed_json.rb:44:in `call', lib/gitlab/middleware/rack_multipart_tempfile_factory.rb:19:in `call', lib/gitlab/metrics/requests_rack_middleware.rb:83:in `call', lib/gitlab/middleware/sidekiq_web_static.rb:20:in `call', lib/gitlab/middleware/release_env.rb:12:in `call'

Based on the backtrace, this is happening when we render the sidebar. When we render the sidebar we show the MR count.

Need to investigate what makes the query slow. Take note that it can be cached. Based on the investigation, devise a fix for the issue.