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