Fix N+1 queries for milestone search
Summary
Noticed while working on: !57284 (merged)
Milestones search (web) has N+1 queries 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.
failing spec output
2) Global elastic search I do not overload the database searching milestones 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 33 queries, got 38:
SELECT "users".* FROM "users" WHERE "users"."id" = 25 ORDER BY "users"."id" ASC LIMIT 1 /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT 1 AS one FROM "identities" WHERE "identities"."user_id" = 25 AND (provider LIKE 'ldap%' AND extern_uid IS NOT NULL) LIMIT 1 /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT "user_preferences".* FROM "user_preferences" WHERE "user_preferences"."user_id" = 25 LIMIT 1 /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,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" = 25 /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT "projects"."id" FROM "projects" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "projects"."id" = 25 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" = 25 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" = 25 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 10)) OR projects.visibility_level IN (10,20)) /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT "projects"."id" FROM "projects" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "projects"."id" = 25 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" = 25 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20)))) AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 25 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= 20)) OR projects.visibility_level IN (10,20)) /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT "milestones".* FROM "milestones" WHERE "milestones"."id" IN (6, 1, 3, 2, 5, 4) /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT "projects".* FROM "projects" WHERE "projects"."id" IN (25, 26, 27, 28, 29, 30) /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Project' AND "routes"."source_id" IN (25, 26, 27, 28, 29, 30) /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" IN (25, 26, 27, 28, 29, 30) /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 30 LIMIT 1 /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 25 LIMIT 1 /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 25 AND "namespaces"."type" = 'Group' LIMIT 1 /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,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" = 25 AND "project_authorizations"."user_id" = 25 GROUP BY "project_authorizations"."user_id" /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 27 LIMIT 1 /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 26 LIMIT 1 /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 29 LIMIT 1 /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 28 LIMIT 1 /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT 100 /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 30 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 25 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 27 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 26 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 29 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 28 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT "appearances".* FROM "appearances" ORDER BY "appearances"."id" ASC LIMIT 1 /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT "user_statuses".* FROM "user_statuses" WHERE "user_statuses"."user_id" = 25 LIMIT 1 /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT COUNT(*) FROM "projects" INNER JOIN "namespaces" ON "projects"."namespace_id" = "namespaces"."id" WHERE "namespaces"."owner_id" = 25 AND "namespaces"."type" IS NULL /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT 1 AS one FROM "users" WHERE "users"."id" = 25 LIMIT 1 /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT "users".* FROM "users" WHERE "users"."id" = 25 /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,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 = 25
OR EXISTS (SELECT TRUE FROM issue_assignees WHERE user_id = 25 AND issue_id = issues.id)
OR EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 25 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" = 25 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" = 25 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 (25) AND issue_id = issues.id)) AND "projects"."archived" = FALSE /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,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" = 25 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" = 25 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 (25) AND merge_request_id = merge_requests.id)) AND "projects"."archived" = FALSE /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT "users".* FROM "users" WHERE "users"."id" = 25 LIMIT 1 /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,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" = 25 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" = 25 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" = 25) /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT COUNT(*) FROM "todos" WHERE "todos"."user_id" = 25 AND ("todos"."state" IN ('pending')) /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT 1 AS one FROM "geo_nodes" LIMIT 1 /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT "broadcast_messages".* FROM "broadcast_messages" WHERE (ends_at > '2021-03-24 18:30:29.923188') AND "broadcast_messages"."broadcast_type" = 1 ORDER BY "broadcast_messages"."id" ASC /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
SELECT "broadcast_messages".* FROM "broadcast_messages" WHERE (ends_at > '2021-03-24 18:30:29.924924') AND "broadcast_messages"."broadcast_type" = 2 ORDER BY "broadcast_messages"."id" ASC /*application:test,correlation_id:b209d967-617b-4deb-90fc-92dd4d1da1fa,endpoint_id:SearchController#show*/
Shared Example Group: "an efficient database result" called from ./ee/spec/features/search/elastic/global_search_spec.rb:81
# ./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>'
Improvements
reduce calls to database when loading search results
Risks
None known
Involved components
- ee/spec/features/search/elastic/global_search_spec.rb
-
with_web_entity_associations
scope in app/models/issue.rb