Fix N+1 queries for Elastic Search milestones scope (!33327) · Merge requests · GitLab.org / GitLab

Terri Chu requested to merge 215710-improve-performance-of-search-api-advanced-milestones-scope into master May 28, 2020

What does this MR do?

Issue #215710 (closed)

Add a new scope to Milestone model with preloading for the API entity (based on the entity file and development logs).
Use the new scope in the Search API calls
Add a new test to for milestones scope and N+1 queries

Note: This change results in 1 extra query for the Project Search (due to the project_feature preloading) - I think that is OK considering the large reduction in queries for global search

Performance Changes

Global Search - /api/v4/search?scope=milestones&search=a

Before

27 Queries

Started GET "/api/v4/search?scope=milestones&search=a" for 127.0.0.1 at 2020-05-28 15:15:46 -0400
Creating scope :of_projects. Overwriting existing method MergeRequest.of_projects.
Creating scope :join_project. Overwriting existing method MergeRequest.join_project.
Creating scope :references_project. Overwriting existing method MergeRequest.references_project.
Creating scope :system. Overwriting existing method Note.system.
Creating scope :group_view_details. Overwriting existing method User.group_view_details.
Creating scope :without_statuses. Overwriting existing method CommitStatus.without_statuses.
Creating scope :opened. Overwriting existing method Epic.opened.
Creating scope :closed. Overwriting existing method Epic.closed.
An enum element in Ci::Runner uses the prefix 'not_'. This will cause a conflict with auto generated negative scopes.
Creating scope :with_files_stored_remotely. Overwriting existing method Ci::JobArtifact.with_files_stored_remotely.
Creating scope :order_created_desc. Overwriting existing method Packages::Package.order_created_desc.
Creating scope :order_name_desc. Overwriting existing method Packages::Package.order_name_desc.
   (0.3ms)  SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::float as lag
  ↳ app/models/concerns/cacheable_attributes.rb:19:in `current_without_cache'
  ApplicationSetting Load (2.4ms)  SELECT "application_settings".* FROM "application_settings" ORDER BY "application_settings"."id" DESC LIMIT 1
  ↳ app/models/concerns/cacheable_attributes.rb:19:in `current_without_cache'
  ApplicationSetting Load (0.5ms)  SELECT "application_settings".* FROM "application_settings" ORDER BY "application_settings"."id" DESC LIMIT $1  [["LIMIT", 1]]
  ↳ app/models/concerns/cacheable_attributes.rb:19:in `current_without_cache'
  License Load (0.2ms)  SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT $1  [["LIMIT", 100]]
  ↳ ee/app/models/license.rb:264:in `load_license'
   (0.2ms)  SELECT "features"."key" FROM "features"
  ↳ lib/feature.rb:13:in `feature_names'
  PersonalAccessToken Load (7.8ms)  SELECT "personal_access_tokens".* FROM "personal_access_tokens" WHERE "personal_access_tokens"."token_digest" = 'R27ZoLo1xAaezTWG/7z4uMnF9M8yUPN1lDKLEh0cQq4=' LIMIT 1
  ↳ app/models/concerns/token_authenticatable_strategies/digest.rb:8:in `find_token_authenticatable'
  User Load (13.9ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1
  ↳ lib/gitlab/auth/auth_finders.rb:102:in `find_user_from_access_token'
   (0.6ms)  SELECT "features"."key" FROM "features"
  ↳ lib/feature.rb:13:in `feature_names'
  License Load (0.5ms)  SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT 100
  ↳ ee/app/models/license.rb:264:in `load_license'
  Milestone Search (59.3ms) {index: "gitlab-development", type: "doc", body: {query: {bool: {must: [{simple_query_string: {fields: ["title^2", "description"], query: "a", default_operator: :and}}], filter: [{term: {type: "milestone"}}, {has_parent: {parent_type: "project", query: {bool: {should: [{bool: {filter: [{term: {visibility_level: 0}}, {terms: {"issues_access_level"=>[20, 10]}}]}}, {bool: {filter: [{term: {visibility_level: 0}}, {terms: {"merge_requests_access_level"=>[20, 10]}}]}}, {bool: {filter: [{term: {visibility_level: 10}}, {terms: {"issues_access_level"=>[20, 10]}}]}}, {bool: {filter: [{term: {visibility_level: 10}}, {terms: {"merge_requests_access_level"=>[20, 10]}}]}}, {bool: {filter: [{term: {visibility_level: 20}}, {terms: {"issues_access_level"=>[20, 10]}}]}}, {bool: {filter: [{term: {visibility_level: 20}}, {terms: {"merge_requests_access_level"=>[20, 10]}}]}}]}}}}]}}, sort: [{updated_at: {order: :desc}}, :_score], highlight: {fields: {title: {}, description: {}}}}, size: 20, from: 0}
   (6.9ms)  SELECT COUNT(*) FROM "milestones" WHERE "milestones"."id" IN (37, 183, 207, 177, 201, 213, 189, 195, 2)
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  Milestone Load (1.9ms)  SELECT "milestones".* FROM "milestones" WHERE "milestones"."id" IN (37, 183, 207, 177, 201, 213, 189, 195, 2) ORDER BY "milestones"."id" ASC LIMIT 9
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  Project Load (3.4ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" IN (1, 8, 66, 69, 71, 73, 68, 80, 82)
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  Route Load (0.9ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Project' AND "routes"."source_id" IN (82, 71, 66, 1, 80, 68, 69, 8, 73)
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  Namespace Load (3.4ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" IN (1, 22, 28)
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  ProjectFeature Load (0.7ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 1 LIMIT 1
  ↳ app/policies/project_policy.rb:624:in `feature_available?'
  ProjectFeature Load (0.3ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 8 LIMIT 1
  ↳ app/policies/project_policy.rb:624:in `feature_available?'
  ProjectFeature Load (0.3ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 66 LIMIT 1
  ↳ app/policies/project_policy.rb:624:in `feature_available?'
  ProjectFeature Load (0.3ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 69 LIMIT 1
  ↳ app/policies/project_policy.rb:624:in `feature_available?'
  ProjectFeature Load (0.2ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 71 LIMIT 1
  ↳ app/policies/project_policy.rb:624:in `feature_available?'
  ProjectFeature Load (0.3ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 73 LIMIT 1
  ↳ app/policies/project_policy.rb:624:in `feature_available?'
  ProjectFeature Load (0.4ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 68 LIMIT 1
  ↳ app/policies/project_policy.rb:624:in `feature_available?'
  ProjectFeature Load (0.3ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 80 LIMIT 1
  ↳ app/policies/project_policy.rb:624:in `feature_available?'
  ProjectFeature Load (0.3ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 82 LIMIT 1
  ↳ app/policies/project_policy.rb:624:in `feature_available?'
  GeoNode Exists? (2.4ms)  SELECT 1 AS one FROM "geo_nodes" LIMIT 1
  ↳ ee/lib/gitlab/geo.rb:36:in `block in enabled?'
  Route Load (0.4ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 22 AND "routes"."source_type" = 'Namespace' LIMIT 1
  ↳ app/models/concerns/routable.rb:77:in `full_path'
  Route Load (0.3ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 28 AND "routes"."source_type" = 'Namespace' LIMIT 1
  ↳ app/models/concerns/routable.rb:77:in `full_path'
  Route Load (0.3ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 1 AND "routes"."source_type" = 'Namespace' LIMIT 1
  ↳ app/models/concerns/routable.rb:77:in `full_path'

After

12 Queries

Started GET "/api/v4/search?scope=milestones&search=*" for 127.0.0.1 at 2020-05-28 19:01:37 -0400
Creating scope :of_projects. Overwriting existing method MergeRequest.of_projects.
Creating scope :join_project. Overwriting existing method MergeRequest.join_project.
Creating scope :references_project. Overwriting existing method MergeRequest.references_project.
Creating scope :system. Overwriting existing method Note.system.
Creating scope :group_view_details. Overwriting existing method User.group_view_details.
Creating scope :without_statuses. Overwriting existing method CommitStatus.without_statuses.
Creating scope :opened. Overwriting existing method Epic.opened.
Creating scope :closed. Overwriting existing method Epic.closed.
An enum element in Ci::Runner uses the prefix 'not_'. This will cause a conflict with auto generated negative scopes.
Creating scope :with_files_stored_remotely. Overwriting existing method Ci::JobArtifact.with_files_stored_remotely.
Creating scope :order_created_desc. Overwriting existing method Packages::Package.order_created_desc.
Creating scope :order_name_desc. Overwriting existing method Packages::Package.order_name_desc.
  ApplicationSetting Load (1.0ms)  SELECT "application_settings".* FROM "application_settings" ORDER BY "application_settings"."id" DESC LIMIT 1
  ↳ app/models/concerns/cacheable_attributes.rb:19:in `current_without_cache'
  PersonalAccessToken Load (0.3ms)  SELECT "personal_access_tokens".* FROM "personal_access_tokens" WHERE "personal_access_tokens"."token_digest" = 'R27ZoLo1xAaezTWG/7z4uMnF9M8yUPN1lDKLEh0cQq4=' LIMIT 1
  ↳ app/models/concerns/token_authenticatable_strategies/digest.rb:8:in `find_token_authenticatable'
  User Load (0.7ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1
  ↳ lib/gitlab/auth/auth_finders.rb:102:in `find_user_from_access_token'
   (0.2ms)  SELECT "features"."key" FROM "features"
  ↳ lib/feature.rb:13:in `feature_names'
  License Load (0.3ms)  SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT 100
  ↳ ee/app/models/license.rb:264:in `load_license'
  Milestone Search (24.2ms) {index: "gitlab-development", type: "doc", body: {query: {bool: {must: [{simple_query_string: {fields: ["title^2", "description"], query: "*", default_operator: :and}}], filter: [{term: {type: "milestone"}}, {has_parent: {parent_type: "project", query: {bool: {should: [{bool: {filter: [{term: {visibility_level: 0}}, {terms: {"issues_access_level"=>[20, 10]}}]}}, {bool: {filter: [{term: {visibility_level: 0}}, {terms: {"merge_requests_access_level"=>[20, 10]}}]}}, {bool: {filter: [{term: {visibility_level: 10}}, {terms: {"issues_access_level"=>[20, 10]}}]}}, {bool: {filter: [{term: {visibility_level: 10}}, {terms: {"merge_requests_access_level"=>[20, 10]}}]}}, {bool: {filter: [{term: {visibility_level: 20}}, {terms: {"issues_access_level"=>[20, 10]}}]}}, {bool: {filter: [{term: {visibility_level: 20}}, {terms: {"merge_requests_access_level"=>[20, 10]}}]}}]}}}}]}}, sort: [{updated_at: {order: :desc}}, :_score], highlight: {fields: {title: {}, description: {}}}}, size: 20, from: 0}
   (0.4ms)  SELECT COUNT(*) FROM "milestones" WHERE "milestones"."id" IN (40, 180, 204, 216, 186, 210, 192, 198, 39, 179, 203, 215, 185, 209, 191, 197, 38, 178, 202, 214)
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  Milestone Load (0.3ms)  SELECT "milestones".* FROM "milestones" WHERE "milestones"."id" IN (40, 180, 204, 216, 186, 210, 192, 198, 39, 179, 203, 215, 185, 209, 191, 197, 38, 178, 202, 214)
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  Project Load (0.7ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" IN (8, 66, 69, 71, 73, 68, 80, 82)
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  ProjectFeature Load (0.2ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" IN (82, 71, 66, 80, 68, 69, 8, 73)
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  Namespace Load (0.5ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" IN (1, 28)
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  Route Load (0.3ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Namespace' AND "routes"."source_id" IN (1, 28)
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  Route Load (0.4ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Project' AND "routes"."source_id" IN (8, 66, 69, 71, 73, 68, 80, 82)
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'

Project Search - /api/v4/projects/63/search?scope=milestones&search=*

Before

24 Queries

Started GET "/api/v4/projects/63/search?scope=milestones&search=*" for 127.0.0.1 at 2020-05-28 15:27:03 -0400
   (0.2ms)  SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::float as lag
  ↳ app/models/concerns/cacheable_attributes.rb:19:in `current_without_cache'
  ApplicationSetting Load (0.8ms)  SELECT "application_settings".* FROM "application_settings" ORDER BY "application_settings"."id" DESC LIMIT 1
  ↳ app/models/concerns/cacheable_attributes.rb:19:in `current_without_cache'
  PersonalAccessToken Load (0.3ms)  SELECT "personal_access_tokens".* FROM "personal_access_tokens" WHERE "personal_access_tokens"."token_digest" = 'R27ZoLo1xAaezTWG/7z4uMnF9M8yUPN1lDKLEh0cQq4=' LIMIT 1
  ↳ app/models/concerns/token_authenticatable_strategies/digest.rb:8:in `find_token_authenticatable'
  User Load (0.4ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1
  ↳ lib/gitlab/auth/auth_finders.rb:102:in `find_user_from_access_token'
   (0.2ms)  SELECT "features"."key" FROM "features"
  ↳ lib/feature.rb:13:in `feature_names'
  License Load (0.2ms)  SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT 100
  ↳ ee/app/models/license.rb:264:in `load_license'
  Project Load (0.6ms)  SELECT "projects".* FROM "projects" WHERE "projects"."pending_delete" = FALSE AND "projects"."id" = 63 LIMIT 1
  ↳ lib/api/helpers.rb:109:in `find_project'
  Group Load (0.5ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 54 AND "namespaces"."type" = 'Group' LIMIT 1
  ↳ ee/app/policies/ee/project_policy.rb:337:in `block (2 levels) in <module:ProjectPolicy>'
  IpRestriction Load (0.2ms)  SELECT "ip_restrictions".* FROM "ip_restrictions" WHERE "ip_restrictions"."group_id" = 54
  ↳ ee/lib/gitlab/ip_restriction/enforcer.rb:31:in `allows_address?'
  Project Load (0.6ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 63 LIMIT 1
  ↳ app/services/search_service.rb:22:in `project'
  Route Load (0.4ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 63 AND "routes"."source_type" = 'Project' LIMIT 1
  ↳ app/models/concerns/routable.rb:77:in `full_path'
   (0.8ms)  SELECT "projects"."id" FROM "projects" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "projects"."id" = 63 AND ("project_features"."issues_access_level" > 0 OR "project_features"."issues_access_level" IS NULL) AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0,10,20))
  ↳ app/models/application_record.rb:21:in `pluck_primary_key'
   (0.9ms)  SELECT "projects"."id" FROM "projects" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "projects"."id" = 63 AND ("project_features"."merge_requests_access_level" > 0 OR "project_features"."merge_requests_access_level" IS NULL) AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0,10,20))
  ↳ app/models/application_record.rb:21:in `pluck_primary_key'
  Milestone Search (11.0ms) {index: "gitlab-development", type: "doc", body: {query: {bool: {must: [{simple_query_string: {fields: ["title^2", "description"], query: "*", default_operator: :and}}], filter: [{term: {type: "milestone"}}, {has_parent: {parent_type: "project", query: {bool: {should: [{bool: {filter: [{terms: {id: [63]}}, {terms: {"issues_access_level"=>[20, 10]}}]}}, {bool: {filter: [{terms: {id: [63]}}, {terms: {"merge_requests_access_level"=>[20, 10]}}]}}]}}}}]}}, sort: [{updated_at: {order: :desc}}, :_score], highlight: {fields: {title: {}, description: {}}}}, routing: "project_63", size: 20, from: 0}
   (0.4ms)  SELECT COUNT(*) FROM "milestones" WHERE "milestones"."id" IN (161, 175, 174, 173, 172, 171, 170, 169, 168, 146, 167, 145, 166, 165, 164, 163, 162, 160, 159, 158)
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  Milestone Load (0.3ms)  SELECT "milestones".* FROM "milestones" WHERE "milestones"."id" IN (161, 175, 174, 173, 172, 171, 170, 169, 168, 146, 167, 145, 166, 165, 164, 163, 162, 160, 159, 158)
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  Project Load (0.5ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 63
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  Route Load (0.3ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Project' AND "routes"."source_id" = 63
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  Namespace Load (0.3ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 54
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  ProjectFeature Load (0.3ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 63 LIMIT 1
  ↳ app/policies/project_policy.rb:624:in `feature_available?'
  GeoNode Exists? (0.3ms)  SELECT 1 AS one FROM "geo_nodes" LIMIT 1
  ↳ ee/lib/gitlab/geo.rb:36:in `block in enabled?'
  Route Load (0.3ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 54 AND "routes"."source_type" = 'Namespace' LIMIT 1
  ↳ app/models/concerns/routable.rb:77:in `full_path'
  Route Load (0.4ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 63 AND "routes"."source_type" = 'Project' LIMIT 1
  ↳ app/models/concerns/routable.rb:77:in `full_path'

After

25 Queries

Started GET "/api/v4/projects/63/search?scope=milestones&search=*" for 127.0.0.1 at 2020-05-28 15:21:01 -0400
Creating scope :of_projects. Overwriting existing method MergeRequest.of_projects.
Creating scope :join_project. Overwriting existing method MergeRequest.join_project.
Creating scope :references_project. Overwriting existing method MergeRequest.references_project.
Creating scope :system. Overwriting existing method Note.system.
Creating scope :group_view_details. Overwriting existing method User.group_view_details.
Creating scope :without_statuses. Overwriting existing method CommitStatus.without_statuses.
Creating scope :opened. Overwriting existing method Epic.opened.
Creating scope :closed. Overwriting existing method Epic.closed.
An enum element in Ci::Runner uses the prefix 'not_'. This will cause a conflict with auto generated negative scopes.
Creating scope :with_files_stored_remotely. Overwriting existing method Ci::JobArtifact.with_files_stored_remotely.
Creating scope :order_created_desc. Overwriting existing method Packages::Package.order_created_desc.
Creating scope :order_name_desc. Overwriting existing method Packages::Package.order_name_desc.
   (0.3ms)  SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::float as lag
  ↳ app/models/concerns/cacheable_attributes.rb:19:in `current_without_cache'
  ApplicationSetting Load (1.1ms)  SELECT "application_settings".* FROM "application_settings" ORDER BY "application_settings"."id" DESC LIMIT 1
  ↳ app/models/concerns/cacheable_attributes.rb:19:in `current_without_cache'
  PersonalAccessToken Load (0.3ms)  SELECT "personal_access_tokens".* FROM "personal_access_tokens" WHERE "personal_access_tokens"."token_digest" = 'R27ZoLo1xAaezTWG/7z4uMnF9M8yUPN1lDKLEh0cQq4=' LIMIT 1
  ↳ app/models/concerns/token_authenticatable_strategies/digest.rb:8:in `find_token_authenticatable'
  User Load (0.6ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1
  ↳ lib/gitlab/auth/auth_finders.rb:102:in `find_user_from_access_token'
   (0.2ms)  SELECT "features"."key" FROM "features"
  ↳ lib/feature.rb:13:in `feature_names'
  License Load (0.3ms)  SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT 100
  ↳ ee/app/models/license.rb:264:in `load_license'
  Project Load (0.9ms)  SELECT "projects".* FROM "projects" WHERE "projects"."pending_delete" = FALSE AND "projects"."id" = 63 LIMIT 1
  ↳ lib/api/helpers.rb:109:in `find_project'
  Group Load (0.4ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 54 AND "namespaces"."type" = 'Group' LIMIT 1
  ↳ ee/app/policies/ee/project_policy.rb:337:in `block (2 levels) in <module:ProjectPolicy>'
  IpRestriction Load (3.2ms)  SELECT "ip_restrictions".* FROM "ip_restrictions" WHERE "ip_restrictions"."group_id" = 54
  ↳ ee/lib/gitlab/ip_restriction/enforcer.rb:31:in `allows_address?'
  Project Load (0.7ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 63 LIMIT 1
  ↳ app/services/search_service.rb:22:in `project'
  Route Load (0.3ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 63 AND "routes"."source_type" = 'Project' LIMIT 1
  ↳ app/models/concerns/routable.rb:77:in `full_path'
   (1.8ms)  SELECT "projects"."id" FROM "projects" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "projects"."id" = 63 AND ("project_features"."issues_access_level" > 0 OR "project_features"."issues_access_level" IS NULL) AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0,10,20))
  ↳ app/models/application_record.rb:21:in `pluck_primary_key'
   (0.8ms)  SELECT "projects"."id" FROM "projects" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "projects"."id" = 63 AND ("project_features"."merge_requests_access_level" > 0 OR "project_features"."merge_requests_access_level" IS NULL) AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0,10,20))
  ↳ app/models/application_record.rb:21:in `pluck_primary_key'
  Milestone Search (29.4ms) {index: "gitlab-development", type: "doc", body: {query: {bool: {must: [{simple_query_string: {fields: ["title^2", "description"], query: "*", default_operator: :and}}], filter: [{term: {type: "milestone"}}, {has_parent: {parent_type: "project", query: {bool: {should: [{bool: {filter: [{terms: {id: [63]}}, {terms: {"issues_access_level"=>[20, 10]}}]}}, {bool: {filter: [{terms: {id: [63]}}, {terms: {"merge_requests_access_level"=>[20, 10]}}]}}]}}}}]}}, sort: [{updated_at: {order: :desc}}, :_score], highlight: {fields: {title: {}, description: {}}}}, routing: "project_63", size: 20, from: 0}
   (0.6ms)  SELECT COUNT(*) FROM "milestones" WHERE "milestones"."id" IN (161, 175, 174, 173, 172, 171, 170, 169, 168, 146, 167, 145, 166, 165, 164, 163, 162, 160, 159, 158)
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  Milestone Load (0.4ms)  SELECT "milestones".* FROM "milestones" WHERE "milestones"."id" IN (161, 175, 174, 173, 172, 171, 170, 169, 168, 146, 167, 145, 166, 165, 164, 163, 162, 160, 159, 158)
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  Project Load (0.6ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 63
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  ProjectFeature Load (0.2ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 63
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  Namespace Load (0.4ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 54
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  Route Load (0.3ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Namespace' AND "routes"."source_id" = 54
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  Route Load (0.3ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Project' AND "routes"."source_id" = 63
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  ProjectFeature Load (0.3ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 63 LIMIT 1
  ↳ app/policies/project_policy.rb:624:in `feature_available?'
  GeoNode Exists? (0.2ms)  SELECT 1 AS one FROM "geo_nodes" LIMIT 1
  ↳ ee/lib/gitlab/geo.rb:36:in `block in enabled?'
  Route Load (0.4ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 63 AND "routes"."source_type" = 'Project' LIMIT 1
  ↳ app/models/concerns/routable.rb:77:in `full_path'

Group Search - /api/v4/groups/54/search?scope=milestones&search=*

Before

25 Queries

Started GET "/api/v4/groups/54/search?scope=milestones&search=*" for 127.0.0.1 at 2020-05-28 15:24:40 -0400
Creating scope :of_projects. Overwriting existing method MergeRequest.of_projects.
Creating scope :join_project. Overwriting existing method MergeRequest.join_project.
Creating scope :references_project. Overwriting existing method MergeRequest.references_project.
Creating scope :system. Overwriting existing method Note.system.
Creating scope :group_view_details. Overwriting existing method User.group_view_details.
Creating scope :without_statuses. Overwriting existing method CommitStatus.without_statuses.
Creating scope :opened. Overwriting existing method Epic.opened.
Creating scope :closed. Overwriting existing method Epic.closed.
An enum element in Ci::Runner uses the prefix 'not_'. This will cause a conflict with auto generated negative scopes.
Creating scope :with_files_stored_remotely. Overwriting existing method Ci::JobArtifact.with_files_stored_remotely.
Creating scope :order_created_desc. Overwriting existing method Packages::Package.order_created_desc.
Creating scope :order_name_desc. Overwriting existing method Packages::Package.order_name_desc.
   (0.2ms)  SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::float as lag
  ↳ app/models/concerns/cacheable_attributes.rb:19:in `current_without_cache'
  ApplicationSetting Load (1.1ms)  SELECT "application_settings".* FROM "application_settings" ORDER BY "application_settings"."id" DESC LIMIT 1
  ↳ app/models/concerns/cacheable_attributes.rb:19:in `current_without_cache'
  PersonalAccessToken Load (0.3ms)  SELECT "personal_access_tokens".* FROM "personal_access_tokens" WHERE "personal_access_tokens"."token_digest" = 'R27ZoLo1xAaezTWG/7z4uMnF9M8yUPN1lDKLEh0cQq4=' LIMIT 1
  ↳ app/models/concerns/token_authenticatable_strategies/digest.rb:8:in `find_token_authenticatable'
  User Load (0.5ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1
  ↳ lib/gitlab/auth/auth_finders.rb:102:in `find_user_from_access_token'
   (0.2ms)  SELECT "features"."key" FROM "features"
  ↳ lib/feature.rb:13:in `feature_names'
  License Load (0.2ms)  SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT 100
  ↳ ee/app/models/license.rb:264:in `load_license'
  Group Load (0.4ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 54 LIMIT 1
  ↳ lib/api/helpers.rb:129:in `find_group'
  IpRestriction Load (0.2ms)  SELECT "ip_restrictions".* FROM "ip_restrictions" WHERE "ip_restrictions"."group_id" = 54
  ↳ ee/lib/gitlab/ip_restriction/enforcer.rb:31:in `allows_address?'
  Group Load (0.4ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 54 LIMIT 1
  ↳ app/services/search_service.rb:36:in `group'
  Route Load (0.3ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 54 AND "routes"."source_type" = 'Namespace' LIMIT 1
  ↳ app/models/concerns/routable.rb:77:in `full_path'
   (0.9ms)  SELECT "projects"."id" FROM "projects" INNER JOIN routes rs ON rs.source_id = projects.id AND rs.source_type = 'Project' WHERE (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0,10,20)) AND "projects"."archived" = FALSE AND (rs.path LIKE 'qa-perf-test-land/%') ORDER BY "projects"."id" DESC
  ↳ ee/app/services/ee/search/group_service.rb:15:in `elastic_projects'
   (0.6ms)  SELECT "projects"."id" FROM "projects" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "projects"."id" = 63 AND ("project_features"."issues_access_level" > 0 OR "project_features"."issues_access_level" IS NULL) AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0,10,20))
  ↳ app/models/application_record.rb:21:in `pluck_primary_key'
   (0.6ms)  SELECT "projects"."id" FROM "projects" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "projects"."id" = 63 AND ("project_features"."merge_requests_access_level" > 0 OR "project_features"."merge_requests_access_level" IS NULL) AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0,10,20))
  ↳ app/models/application_record.rb:21:in `pluck_primary_key'
  Milestone Search (26.4ms) {index: "gitlab-development", type: "doc", body: {query: {bool: {must: [{simple_query_string: {fields: ["title^2", "description"], query: "*", default_operator: :and}}], filter: [{term: {type: "milestone"}}, {has_parent: {parent_type: "project", query: {bool: {should: [{bool: {filter: [{terms: {id: [63]}}, {terms: {"issues_access_level"=>[20, 10]}}]}}, {bool: {filter: [{terms: {id: [63]}}, {terms: {"merge_requests_access_level"=>[20, 10]}}]}}]}}}}]}}, sort: [{updated_at: {order: :desc}}, :_score], highlight: {fields: {title: {}, description: {}}}}, routing: "project_63", size: 20, from: 0}
   (0.4ms)  SELECT COUNT(*) FROM "milestones" WHERE "milestones"."id" IN (161, 175, 174, 173, 172, 171, 170, 169, 168, 146, 167, 145, 166, 165, 164, 163, 162, 160, 159, 158)
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  Milestone Load (0.3ms)  SELECT "milestones".* FROM "milestones" WHERE "milestones"."id" IN (161, 175, 174, 173, 172, 171, 170, 169, 168, 146, 167, 145, 166, 165, 164, 163, 162, 160, 159, 158)
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  Project Load (0.6ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 63
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  Route Load (0.3ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Project' AND "routes"."source_id" = 63
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  Namespace Load (0.3ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 54
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  ProjectFeature Load (0.4ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 63 LIMIT 1
  ↳ app/policies/project_policy.rb:624:in `feature_available?'
  GeoNode Exists? (0.2ms)  SELECT 1 AS one FROM "geo_nodes" LIMIT 1
  ↳ ee/lib/gitlab/geo.rb:36:in `block in enabled?'
  Route Load (0.4ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 54 AND "routes"."source_type" = 'Namespace' LIMIT 1
  ↳ app/models/concerns/routable.rb:77:in `full_path'
  Route Load (0.4ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 54 AND "routes"."source_type" = 'Namespace' LIMIT 1
  ↳ app/models/concerns/routable.rb:77:in `full_path'

After

24 Queries

Started GET "/api/v4/groups/54/search?scope=milestones&search=*" for 127.0.0.1 at 2020-05-28 15:23:01 -0400
   (0.2ms)  SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::float as lag
  ↳ app/models/concerns/cacheable_attributes.rb:19:in `current_without_cache'
  ApplicationSetting Load (0.9ms)  SELECT "application_settings".* FROM "application_settings" ORDER BY "application_settings"."id" DESC LIMIT 1
  ↳ app/models/concerns/cacheable_attributes.rb:19:in `current_without_cache'
  PersonalAccessToken Load (0.3ms)  SELECT "personal_access_tokens".* FROM "personal_access_tokens" WHERE "personal_access_tokens"."token_digest" = 'R27ZoLo1xAaezTWG/7z4uMnF9M8yUPN1lDKLEh0cQq4=' LIMIT 1
  ↳ app/models/concerns/token_authenticatable_strategies/digest.rb:8:in `find_token_authenticatable'
  User Load (0.4ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1
  ↳ lib/gitlab/auth/auth_finders.rb:102:in `find_user_from_access_token'
   (0.2ms)  SELECT "features"."key" FROM "features"
  ↳ lib/feature.rb:13:in `feature_names'
  License Load (0.2ms)  SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT 100
  ↳ ee/app/models/license.rb:264:in `load_license'
  Group Load (0.4ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 54 LIMIT 1
  ↳ lib/api/helpers.rb:129:in `find_group'
  IpRestriction Load (0.3ms)  SELECT "ip_restrictions".* FROM "ip_restrictions" WHERE "ip_restrictions"."group_id" = 54
  ↳ ee/lib/gitlab/ip_restriction/enforcer.rb:31:in `allows_address?'
  Group Load (0.4ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 54 LIMIT 1
  ↳ app/services/search_service.rb:36:in `group'
  Route Load (0.4ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 54 AND "routes"."source_type" = 'Namespace' LIMIT 1
  ↳ app/models/concerns/routable.rb:77:in `full_path'
   (2.1ms)  SELECT "projects"."id" FROM "projects" INNER JOIN routes rs ON rs.source_id = projects.id AND rs.source_type = 'Project' WHERE (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0,10,20)) AND "projects"."archived" = FALSE AND (rs.path LIKE 'qa-perf-test-land/%') ORDER BY "projects"."id" DESC
  ↳ ee/app/services/ee/search/group_service.rb:15:in `elastic_projects'
   (0.9ms)  SELECT "projects"."id" FROM "projects" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "projects"."id" = 63 AND ("project_features"."issues_access_level" > 0 OR "project_features"."issues_access_level" IS NULL) AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0,10,20))
  ↳ app/models/application_record.rb:21:in `pluck_primary_key'
   (0.7ms)  SELECT "projects"."id" FROM "projects" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "projects"."id" = 63 AND ("project_features"."merge_requests_access_level" > 0 OR "project_features"."merge_requests_access_level" IS NULL) AND (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0,10,20))
  ↳ app/models/application_record.rb:21:in `pluck_primary_key'
  Milestone Search (12.1ms) {index: "gitlab-development", type: "doc", body: {query: {bool: {must: [{simple_query_string: {fields: ["title^2", "description"], query: "*", default_operator: :and}}], filter: [{term: {type: "milestone"}}, {has_parent: {parent_type: "project", query: {bool: {should: [{bool: {filter: [{terms: {id: [63]}}, {terms: {"issues_access_level"=>[20, 10]}}]}}, {bool: {filter: [{terms: {id: [63]}}, {terms: {"merge_requests_access_level"=>[20, 10]}}]}}]}}}}]}}, sort: [{updated_at: {order: :desc}}, :_score], highlight: {fields: {title: {}, description: {}}}}, routing: "project_63", size: 20, from: 0}
   (0.4ms)  SELECT COUNT(*) FROM "milestones" WHERE "milestones"."id" IN (161, 175, 174, 173, 172, 171, 170, 169, 168, 146, 167, 145, 166, 165, 164, 163, 162, 160, 159, 158)
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  Milestone Load (0.3ms)  SELECT "milestones".* FROM "milestones" WHERE "milestones"."id" IN (161, 175, 174, 173, 172, 171, 170, 169, 168, 146, 167, 145, 166, 165, 164, 163, 162, 160, 159, 158)
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  Project Load (0.6ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 63
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  ProjectFeature Load (0.3ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 63
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  Namespace Load (0.4ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 54
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  Route Load (0.3ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Namespace' AND "routes"."source_id" = 54
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  Route Load (0.4ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Project' AND "routes"."source_id" = 63
  ↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
  Route Load (0.4ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 54 AND "routes"."source_type" = 'Namespace' LIMIT 1
  ↳ app/models/concerns/routable.rb:77:in `full_path

Database Queries

The scope adds the following queries, loading things in one query versus a single query per record. The SQL and EXPLAIN plan information for each query is below (gathered using #database-lab)

project load

SELECT "projects".* FROM "projects" WHERE "projects"."id" IN (13083, 278964)

 Index Scan using projects_pkey on public.projects  (cost=0.43..6.70 rows=2 width=749) (actual time=9.014..13.925 rows=2 loops=1)
   Index Cond: (projects.id = ANY ('{13083,278964}'::integer[]))
   Buffers: shared hit=1 read=7
   I/O Timings: read=13.780

project features load

SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" IN (13083, 278964)

 Index Scan using index_project_features_on_project_id on public.project_features  (cost=0.43..6.53 rows=2 width=60) (actual time=4.558..10.662 rows=2 loops=1)
   Index Cond: (project_features.project_id = ANY ('{13083,278964}'::integer[]))
   Buffers: shared hit=3 read=5
   I/O Timings: read=10.558

namespace load

SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" IN (9970)

 Index Scan using namespaces_pkey on public.namespaces  (cost=0.43..3.45 rows=1 width=329) (actual time=6.095..6.098 rows=1 loops=1)
   Index Cond: (namespaces.id = 9970)
   Buffers: shared hit=2 read=2
   I/O Timings: read=6.034

namespace route load

SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Namespace' AND "routes"."source_id" IN (9970)

 Index Scan using index_routes_on_source_type_and_source_id on public.routes  (cost=0.56..3.58 rows=1 width=78) (actual time=7.424..7.426 rows=1 loops=1)
   Index Cond: (((routes.source_type)::text = 'Namespace'::text) AND (routes.source_id = 9970))
   Buffers: shared hit=3 read=2
   I/O Timings: read=7.348

project route load

SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Project' AND "routes"."source_id" IN (13083, 278964)

 Index Scan using index_routes_on_source_type_and_source_id on public.routes  (cost=0.56..6.89 rows=2 width=78) (actual time=5.924..14.851 rows=2 loops=1)
   Index Cond: (((routes.source_type)::text = 'Project'::text) AND (routes.source_id = ANY ('{13083,278964}'::integer[])))
   Buffers: shared hit=5 read=5
   I/O Timings: read=14.718

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
[-] Tested in all supported browsers
[-] Informed Infrastructure department of a default or new setting change, if applicable per definition of done

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

[-] Label as security and @ mention @gitlab-com/gl-security/appsec
[-] The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
[-] Security reports checked/validated by a reviewer from the AppSec team

Edited May 31, 2022 by 🤖 GitLab Bot 🤖

Fix N+1 queries for Elastic Search milestones scope

What does this MR do?

Performance Changes

Global Search - /api/v4/search?scope=milestones&search=a

Before

After

Project Search - /api/v4/projects/63/search?scope=milestones&search=*

Before

After

Group Search - /api/v4/groups/54/search?scope=milestones&search=*

Before

After

Database Queries

project load

project features load

namespace load

namespace route load

project route load

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

Merge request reports