Skip to content

Improve performance of Search API (Advanced): milestones scope

Summary

In &3166 (closed) a performance issue was raised around the response time of the Advanced Search API. This issue deals specifically with the milestones scope, at the Global, Group, and Project levels. Below are results of a performance test that was run on the 10k reference architecture:

API - Global Search
    ✓ { endpoint:milestones }.......: avg=12379.72ms min=79.11ms  med=14761.05ms max=17656.42ms p(90)=16746.65ms p(95)=16977.56ms
API - Group Search
    ✓ { endpoint:milestones }.......: avg=7943.49ms  min=797.08ms  med=7299.13ms  max=19660.94ms p(90)=13509.44ms p(95)=16494.89ms
API - Project Search
    ✓ { endpoint:milestones }.......: avg=3578.55ms  min=314.06ms  med=3846.76ms  max=7358.59ms  p(90)=4900.50ms  p(95)=5362.33ms

Improvements

Global

Queries
Started GET "/api/v4/search?scope=milestones&search=*"
  PersonalAccessToken Load (0.3ms)  SELECT "personal_access_tokens".* FROM "personal_access_tokens" WHERE "personal_access_tokens"."token_digest" = '' 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:103:in `find_user_from_access_token'
  License Load (0.2ms)  SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT 1
  ↳ ee/app/models/license.rb:268:in `load_license'
  Milestone Search (6.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: [{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, 39, 38, 37, 36, 35, 34, 33, 32, 31, 30, 29, 28, 27, 26, 25, 24, 23, 22, 21)
  ↳ ee/lib/gitlab/elastic/search_results.rb:172:in `eager_load'
  Milestone Load (0.3ms)  SELECT "milestones".* FROM "milestones" WHERE "milestones"."id" IN (40, 39, 38, 37, 36, 35, 34, 33, 32, 31, 30, 29, 28, 27, 26, 25, 24, 23, 22, 21)
  ↳ ee/lib/gitlab/elastic/search_results.rb:172:in `eager_load'
  Project Load (0.8ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" IN (5, 6, 7, 8)
  ↳ ee/lib/gitlab/elastic/search_results.rb:172:in `eager_load'
  Route Load (0.3ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Project' AND "routes"."source_id" IN (8, 7, 5, 6)
  ↳ ee/lib/gitlab/elastic/search_results.rb:172:in `eager_load'
  Namespace Load (0.4ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" IN (28, 27, 25, 26)
  ↳ ee/lib/gitlab/elastic/search_results.rb:172:in `eager_load'
  ProjectFeature Load (0.3ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 8 LIMIT 1
  ↳ app/policies/project_policy.rb:539:in `feature_available?'
  ProjectFeature Load (0.3ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 5 LIMIT 1
  ↳ app/policies/project_policy.rb:539:in `feature_available?'
  ProjectFeature Load (0.3ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 6 LIMIT 1
  ↳ app/policies/project_policy.rb:539:in `feature_available?'
  ProjectFeature Load (0.3ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 7 LIMIT 1
  ↳ app/policies/project_policy.rb:539:in `feature_available?'
  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" = 25 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" = 26 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" = 27 AND "routes"."source_type" = 'Namespace' LIMIT 1
  ↳ app/models/concerns/routable.rb:77:in `full_path'

A separate queries for ProjectFeature and Route are made for each project in the results.

Group

Queries
Started GET "/api/v4/groups/28/search?scope=milestones&search=*"
  PersonalAccessToken Load (0.3ms)  SELECT "personal_access_tokens".* FROM "personal_access_tokens" WHERE "personal_access_tokens"."token_digest" = '' 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:103:in `find_user_from_access_token'
  License Load (0.2ms)  SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT 1
  ↳ ee/app/models/license.rb:268:in `load_license'
  Group Load (0.4ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 28 LIMIT 1
  ↳ lib/api/helpers.rb:128:in `find_group'
  IpRestriction Load (0.2ms)  SELECT "ip_restrictions".* FROM "ip_restrictions" WHERE "ip_restrictions"."group_id" = 28
  ↳ 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" = 28 LIMIT 1
  ↳ app/services/search_service.rb:34:in `group'
  Route Load (0.4ms)  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'
   (1.0ms)  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 'h5bp/%') ORDER BY "projects"."id" DESC
  ↳ ee/app/services/ee/search/group_service.rb:15:in `elastic_projects'
   (0.7ms)  SELECT "projects"."id" FROM "projects" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "projects"."id" = 8 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:17: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" = 8 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:17:in `pluck_primary_key'
  Milestone Search (3.3ms) {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: [8]}}, {terms: {"issues_access_level"=>[20, 10]}}]}}, {bool: {filter: [{terms: {id: [8]}}, {terms: {"merge_requests_access_level"=>[20, 10]}}]}}]]}}}}]}}, sort: [{updated_at: {order: :desc}}, :_score], highlight: {fields: {title: {}, description: {}}}}, routing: "project_8", size: 20, from: 0}
   (0.3ms)  SELECT COUNT(*) FROM "milestones" WHERE "milestones"."id" IN (40, 39, 38, 37, 36, 52)
  ↳ ee/lib/gitlab/elastic/search_results.rb:172:in `eager_load'
  Milestone Load (0.3ms)  SELECT "milestones".* FROM "milestones" WHERE "milestones"."id" IN (40, 39, 38, 37, 36, 52) ORDER BY "milestones"."id" ASC LIMIT 6
  ↳ ee/lib/gitlab/elastic/search_results.rb:172:in `eager_load'
  Project Load (0.5ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 8
  ↳ ee/lib/gitlab/elastic/search_results.rb:172:in `eager_load'
  Route Load (0.3ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Project' AND "routes"."source_id" = 8
  ↳ ee/lib/gitlab/elastic/search_results.rb:172:in `eager_load'
  Namespace Load (0.4ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 28
  ↳ ee/lib/gitlab/elastic/search_results.rb:172:in `eager_load'
  ProjectFeature Load (0.3ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 8 LIMIT 1
  ↳ app/policies/project_policy.rb:539:in `feature_available?'
  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'

Three queries to the projects table before the search is performed. Can they be combined somehow?

Project

Queries
Started GET "/api/v4/groups/28/search?scope=milestones&search=*"
   (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.2ms)  SELECT "personal_access_tokens".* FROM "personal_access_tokens" WHERE "personal_access_tokens"."token_digest" = '' 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:103:in `find_user_from_access_token'
   (0.2ms)  SELECT "features"."key" FROM "features"
  ↳ lib/feature.rb:15:in `feature_names'
  License Load (0.2ms)  SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT 1
  ↳ ee/app/models/license.rb:268:in `load_license'
  Group Load (0.4ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 28 LIMIT 1
  ↳ lib/api/helpers.rb:128:in `find_group'
  IpRestriction Load (0.2ms)  SELECT "ip_restrictions".* FROM "ip_restrictions" WHERE "ip_restrictions"."group_id" = 28
  ↳ ee/lib/gitlab/ip_restriction/enforcer.rb:31:in `allows_address?'
  Group Load (0.3ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 28 LIMIT 1
  ↳ app/services/search_service.rb:34:in `group'
  Route Load (0.2ms)  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'
   (0.8ms)  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 'h5bp/%') ORDER BY "projects"."id" DESC
  ↳ ee/app/services/ee/search/group_service.rb:15:in `elastic_projects'
   (0.7ms)  SELECT "projects"."id" FROM "projects" LEFT JOIN project_features ON projects.id = project_features.project_id WHERE "projects"."id" = 8 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:17: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" = 8 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:17:in `pluck_primary_key'
  Milestone Search (4.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: [8]}}, {terms: {"issues_access_level"=>[20, 10]}}]}}, {bool: {filter: [{terms: {id: [8]}}, {terms: {"merge_requests_access_level"=>[20, 10]}}]}}]]}}}}]}}, sort: [{updated_at: {order: :desc}}, :_score], highlight: {fields: {title: {}, description: {}}}}, routing: "project_8", size: 20, from: 0}
   (0.3ms)  SELECT COUNT(*) FROM "milestones" WHERE "milestones"."id" IN (40, 39, 38, 37, 36, 52)
  ↳ ee/lib/gitlab/elastic/search_results.rb:172:in `eager_load'
  Milestone Load (0.3ms)  SELECT "milestones".* FROM "milestones" WHERE "milestones"."id" IN (40, 39, 38, 37, 36, 52) ORDER BY "milestones"."id" ASC LIMIT 6
  ↳ ee/lib/gitlab/elastic/search_results.rb:172:in `eager_load'
  Project Load (0.5ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 8
  ↳ ee/lib/gitlab/elastic/search_results.rb:172:in `eager_load'
  Route Load (0.3ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Project' AND "routes"."source_id" = 8
  ↳ ee/lib/gitlab/elastic/search_results.rb:172:in `eager_load'
  Namespace Load (0.4ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 28
  ↳ ee/lib/gitlab/elastic/search_results.rb:172:in `eager_load'
  ProjectFeature Load (0.3ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 8 LIMIT 1
  ↳ app/policies/project_policy.rb:539:in `feature_available?'
  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'

Same observation about three queries to the projects table.

Edited by Alishan Ladhani