Fix N+1 queries for issues search

There are additional N+1 queries for issues search related to the namespace.route and project.project_features.
The spec ee/spec/features/search/elastic/global_search_spec.rb should also be updated to set the query_count_multiplier to 0.
Seen again while working on: !57284 (merged)
failing spec
Failures:

  1) Global elastic search I do not overload the database searching issues behaves like an efficient database result avoids N+1 database queries
     Failure/Error: expect { visit path }.not_to exceed_query_limit(control_count)

       Expected a maximum of 36 queries, got 42:

       SELECT "users".* FROM "users" WHERE "users"."id" = 1 ORDER BY "users"."id" ASC LIMIT 1 /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT 1 AS one FROM "identities" WHERE "identities"."user_id" = 1 AND (provider LIKE 'ldap%' AND extern_uid IS NOT NULL) LIMIT 1 /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "user_preferences".* FROM "user_preferences" WHERE "user_preferences"."user_id" = 1 LIMIT 1 /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "project_authorizations"."user_id" = 1 /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "projects"."id" FROM "projects" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "projects"."id" = 1 AND ("project_features"."issues_access_level" IS NULL OR "project_features"."issues_access_level" IN (20,30) OR ("project_features"."issues_access_level" = 10 AND EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 10)))) AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 10)) OR projects.visibility_level IN (10,20)) /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "projects"."id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.access_level >= 20) /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "issues".* FROM "issues" WHERE "issues"."id" IN (5, 1, 2, 6, 3, 4) /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "users".* FROM "users" WHERE "users"."id" IN (1, 2, 3, 4, 5, 6) /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "projects".* FROM "projects" WHERE "projects"."id" IN (1, 2, 3, 4, 5, 6) /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Project' AND "routes"."source_id" IN (1, 2, 3, 4, 5, 6) /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" IN (1, 2, 3, 4, 5, 6) /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "label_links".* FROM "label_links" WHERE "label_links"."target_type" = 'Issue' AND "label_links"."target_id" IN (1, 2, 3, 4, 5, 6) /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "timelogs".* FROM "timelogs" WHERE "timelogs"."issue_id" IN (1, 2, 3, 4, 5, 6) /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "issue_assignees".* FROM "issue_assignees" WHERE "issue_assignees"."issue_id" IN (1, 2, 3, 4, 5, 6) /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 5 LIMIT 1 /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 1 LIMIT 1 /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 1 AND "namespaces"."type" = 'Group' LIMIT 1 /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT MAX("project_authorizations"."access_level") AS maximum_access_level, "project_authorizations"."user_id" AS project_authorizations_user_id FROM "project_authorizations" WHERE "project_authorizations"."project_id" = 1 AND "project_authorizations"."user_id" = 1 GROUP BY "project_authorizations"."user_id" /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 2 LIMIT 1 /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 6 LIMIT 1 /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 3 LIMIT 1 /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 4 LIMIT 1 /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT 100 /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 5 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 1 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 2 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 6 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 3 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 4 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "appearances".* FROM "appearances" ORDER BY "appearances"."id" ASC LIMIT 1 /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "user_statuses".* FROM "user_statuses" WHERE "user_statuses"."user_id" = 1 LIMIT 1 /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT COUNT(*) FROM "projects" INNER JOIN "namespaces" ON "projects"."namespace_id" = "namespaces"."id" WHERE "namespaces"."owner_id" = 1 AND "namespaces"."type" IS NULL /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT 1 AS one FROM "users" WHERE "users"."id" = 1 LIMIT 1 /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "users".* FROM "users" WHERE "users"."id" = 1 /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT COUNT(*) FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (
             issues.confidential IS NOT TRUE
             OR (issues.confidential = TRUE
               AND (issues.author_id = 1
                 OR EXISTS (SELECT TRUE FROM issue_assignees WHERE user_id = 1 AND issue_id = issues.id)
                 OR EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = issues.project_id) AND (project_authorizations.access_level >= 20))))) AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 10)) OR projects.visibility_level IN (10,20)) AND ("project_features"."issues_access_level" IS NULL OR "project_features"."issues_access_level" IN (20,30) OR ("project_features"."issues_access_level" = 10 AND EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 10)))) AND ("issues"."state_id" IN (1)) AND (EXISTS (SELECT true FROM "issue_assignees" WHERE "issue_assignees"."user_id" IN (1) AND issue_id = issues.id)) AND "projects"."archived" = FALSE /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT COUNT(*) FROM "merge_requests" INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20)) OR projects.visibility_level IN (10,20)) AND ("project_features"."merge_requests_access_level" IS NULL OR "project_features"."merge_requests_access_level" IN (20,30) OR ("project_features"."merge_requests_access_level" = 10 AND EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20)))) AND ("merge_requests"."state_id" IN (1)) AND (EXISTS (SELECT true FROM "merge_request_assignees" WHERE "merge_request_assignees"."user_id" IN (1) AND merge_request_id = merge_requests.id)) AND "projects"."archived" = FALSE /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1 /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT COUNT(*) FROM "merge_requests" INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20)) OR projects.visibility_level IN (10,20)) AND ("project_features"."merge_requests_access_level" IS NULL OR "project_features"."merge_requests_access_level" IN (20,30) OR ("project_features"."merge_requests_access_level" = 10 AND EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20)))) AND ("merge_requests"."state_id" IN (1)) AND "projects"."archived" = FALSE AND EXISTS (SELECT true FROM "merge_request_reviewers" WHERE merge_request_id = merge_requests.id AND "merge_request_reviewers"."user_id" = 1) /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT COUNT(*) FROM "todos" WHERE "todos"."user_id" = 1 AND ("todos"."state" IN ('pending')) /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT 1 AS one FROM "geo_nodes" LIMIT 1 /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "broadcast_messages".* FROM "broadcast_messages" WHERE (ends_at > '2021-03-24 18:29:46.813230') AND "broadcast_messages"."broadcast_type" = 1 ORDER BY "broadcast_messages"."id" ASC /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/

       SELECT "broadcast_messages".* FROM "broadcast_messages" WHERE (ends_at > '2021-03-24 18:29:47.721294') AND "broadcast_messages"."broadcast_type" = 2 ORDER BY "broadcast_messages"."id" ASC /*application:test,correlation_id:ac532e5f-3095-4b81-968b-d105f71eaf49,endpoint_id:SearchController#show*/
     Shared Example Group: "an efficient database result" called from ./ee/spec/features/search/elastic/global_search_spec.rb:50
     # ./ee/spec/features/search/elastic/global_search_spec.rb:36:in `block (3 levels) in <main>'
     # ./spec/spec_helper.rb:365:in `block (3 levels) in <main>'
     # ./spec/support/sidekiq_middleware.rb:9:in `with_sidekiq_server_middleware'
     # ./spec/spec_helper.rb:356:in `block (2 levels) in <main>'
     # ./spec/spec_helper.rb:348:in `block (3 levels) in <main>'
     # ./lib/gitlab/application_context.rb:31:in `with_raw_context'
     # ./spec/spec_helper.rb:348:in `block (2 levels) in <main>'
     # ./spec/support/sidekiq.rb:21:in `block (3 levels) in <main>'
     # ./spec/support/sidekiq.rb:8:in `gitlab_sidekiq_inline'
     # ./spec/support/sidekiq.rb:21:in `block (2 levels) in <main>'
     # ./ee/spec/support/elastic.rb:21:in `block (2 levels) in <main>'
     # -e:1:in `<main>'
The following discussion from !36941 (merged) should be addressed:
@tigerwnz started a discussion: (+2 comments)
It looks like we should also preload the project's route and namespace, as each of these are N+1 too.
```
  scope :with_web_entity_associations, -> { preload(:author, project: [:route, :namespace] ) }
```
There is also an additional N+1 loading namespace.route. Ideally we would just add namespace: :route to the preloaded associations, but the single-table inheritance of Group and Namespace seems to prevent this working. A problem for another day perhaps?
Release Notes

Edited Apr 13, 2021 by John McGuire