## Summary In https://gitlab.com/groups/gitlab-org/-/epics/3166 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](https://docs.gitlab.com/ee/administration/scaling/#up-to-10000-users): ``` 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 <details> <summary>Queries</summary> ``` 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' ``` </details> A separate queries for `ProjectFeature` and `Route` are made for each project in the results. #### Group <details> <summary>Queries</summary> ``` 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' ``` </details> Three queries to the `projects` table before the search is performed. Can they be combined somehow? #### Project <details> <summary>Queries</summary> ``` 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' ``` </details> Same observation about three queries to the `projects` table.