Resolve remaining N+1 queries in Search API (Advanced) projects scope
The following discussions from !32688 (merged) should be addressed:
-
@ali-gitlab started a discussion: (+1 comment) It might be worth looking into the
fork_network_members
queries as a follow-up. -
@terrichu responded in a discussion This was a good catch! There's more queries that I'm not sure I can resolve as part of this MR. I'll include them in the follow up Issue and make it more generalized
Details
The Search API call with projects
scope for Advanced Global Search has some remaining N+1 queries. These were not able to be resolved in the initial MR and should be investigated as to whether they can be resolved.
Specifically, the calls to fork_network_members
and namespaces
/saml_providers
GET /api/v4/search?scope=projects&search=*
42 Queries
Started GET "/api/v4/search?scope=projects&search=*" for 127.0.0.1 at 2020-05-20 14:42:18 -0400
(1.7ms) SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::float as lag
↳ lib/gitlab/middleware/basic_health_check.rb:25:in `call'
(0.8ms) SELECT "schema_migrations"."version" FROM "schema_migrations" ORDER BY "schema_migrations"."version" ASC
↳ lib/gitlab/middleware/basic_health_check.rb:25:in `call'
ApplicationSetting Load (2.3ms) 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.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 (2.2ms) SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1
↳ lib/gitlab/auth/auth_finders.rb:102:in `find_user_from_access_token'
(0.5ms) SELECT "features"."key" FROM "features"
↳ lib/feature.rb:15:in `feature_names'
License Load (0.5ms) SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT 100
↳ ee/app/models/license.rb:265:in `load_license'
Project Search (180.2ms) {index: "gitlab-development", type: "doc", body: {query: {bool: {must: [{simple_query_string: {fields: ["name^10", "name_with_namespace^2", "path_with_namespace", "path^9", "description"], query: "*", default_operator: :and}}], filter: [{terms: {type: ["project"]}}, {bool: {should: [{term: {visibility_level: 0}}, {term: {visibility_level: 10}}, {term: {visibility_level: 20}}]}}]}}, sort: [:_score], highlight: {fields: {name: {}, name_with_namespace: {}, path_with_namespace: {}, path: {}, description: {}}}}, size: 20, from: 0}
(3.9ms) SELECT COUNT(*) FROM "projects" WHERE "projects"."id" IN (16, 3, 9, 17, 60, 70, 75, 8, 6, 4, 76, 83, 84, 10, 2, 14, 15, 61, 62, 68)
↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
Project Load (1.8ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" IN (16, 3, 9, 17, 60, 70, 75, 8, 6, 4, 76, 83, 84, 10, 2, 14, 15, 61, 62, 68)
↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
ProjectFeature Load (1.0ms) SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" IN (2, 60, 4, 62, 84, 3, 6, 61, 8, 9, 83, 10, 16, 14, 15, 68, 75, 17, 70, 76)
↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
Route Load (0.7ms) SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Project' AND "routes"."source_id" IN (2, 60, 4, 62, 84, 3, 6, 61, 8, 9, 83, 10, 16, 14, 15, 68, 75, 17, 70, 76)
↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
ComplianceManagement::ComplianceFramework::ProjectSettings Load (1.4ms) SELECT "project_compliance_framework_settings".* FROM "project_compliance_framework_settings" WHERE "project_compliance_framework_settings"."project_id" IN (2, 60, 4, 62, 84, 3, 6, 61, 8, 9, 83, 10, 16, 14, 15, 68, 75, 17, 70, 76)
↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
ActsAsTaggableOn::Tagging Load (2.6ms) SELECT "taggings".* FROM "taggings" WHERE "taggings"."taggable_type" = 'Project' AND "taggings"."context" = 'tags' AND "taggings"."taggable_id" IN (2, 60, 4, 62, 84, 3, 6, 61, 8, 9, 83, 10, 16, 14, 15, 68, 75, 17, 70, 76) ORDER BY taggings.id
↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
Group Load (1.7ms) SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."type" = 'Group' AND "namespaces"."id" IN (22, 53, 24, 57, 23, 26, 28, 36, 1, 45, 16, 6, 35, 29)
↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
IpRestriction Load (0.4ms) SELECT "ip_restrictions".* FROM "ip_restrictions" WHERE "ip_restrictions"."group_id" IN (22, 23, 24, 26, 28, 53, 57)
↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
Namespace Load (0.7ms) SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" IN (22, 53, 24, 57, 23, 26, 28, 36, 1, 45, 16, 6, 35, 29)
↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
Route Load (0.4ms) SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Namespace' AND "routes"."source_id" IN (1, 6, 16, 22, 23, 24, 26, 28, 29, 35, 36, 45, 53, 57)
↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
User Load (0.7ms) SELECT "users".* FROM "users" WHERE "users"."id" IN (1, 6, 16, 22, 28, 29, 38)
↳ ee/lib/gitlab/elastic/search_results.rb:176:in `eager_load'
Group Load (0.7ms) SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 53 AND "namespaces"."parent_id" IS NULL LIMIT 1
↳ app/models/namespace.rb:285:in `block in root_ancestor'
SamlProvider Load (1.3ms) SELECT "saml_providers".* FROM "saml_providers" WHERE "saml_providers"."group_id" = 53 LIMIT 1
↳ ee/lib/gitlab/auth/group_saml/sso_enforcer.rb:35:in `group_access_restricted?'
Group Load (0.6ms) SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 23 AND "namespaces"."parent_id" IS NULL LIMIT 1
↳ app/models/namespace.rb:285:in `block in root_ancestor'
SamlProvider Load (0.2ms) SELECT "saml_providers".* FROM "saml_providers" WHERE "saml_providers"."group_id" = 23 LIMIT 1
↳ ee/lib/gitlab/auth/group_saml/sso_enforcer.rb:35:in `group_access_restricted?'
(2.6ms) SELECT COUNT(*) FROM "fork_network_members" WHERE "fork_network_members"."forked_from_project_id" = 76
↳ app/services/base_count_service.rb:26:in `uncached_count'
(0.3ms) SELECT COUNT(*) FROM "fork_network_members" WHERE "fork_network_members"."forked_from_project_id" = 60
↳ app/services/base_count_service.rb:26:in `uncached_count'
(0.3ms) SELECT COUNT(*) FROM "fork_network_members" WHERE "fork_network_members"."forked_from_project_id" = 4
↳ app/services/base_count_service.rb:26:in `uncached_count'
(0.3ms) SELECT COUNT(*) FROM "fork_network_members" WHERE "fork_network_members"."forked_from_project_id" = 62
↳ app/services/base_count_service.rb:26:in `uncached_count'
(0.4ms) SELECT COUNT(*) FROM "fork_network_members" WHERE "fork_network_members"."forked_from_project_id" = 84
↳ app/services/base_count_service.rb:26:in `uncached_count'
(0.3ms) SELECT COUNT(*) FROM "fork_network_members" WHERE "fork_network_members"."forked_from_project_id" = 3
↳ app/services/base_count_service.rb:26:in `uncached_count'
(0.3ms) SELECT COUNT(*) FROM "fork_network_members" WHERE "fork_network_members"."forked_from_project_id" = 6
↳ app/services/base_count_service.rb:26:in `uncached_count'
(0.3ms) SELECT COUNT(*) FROM "fork_network_members" WHERE "fork_network_members"."forked_from_project_id" = 61
↳ app/services/base_count_service.rb:26:in `uncached_count'
(0.3ms) SELECT COUNT(*) FROM "fork_network_members" WHERE "fork_network_members"."forked_from_project_id" = 8
↳ app/services/base_count_service.rb:26:in `uncached_count'
(0.6ms) SELECT COUNT(*) FROM "fork_network_members" WHERE "fork_network_members"."forked_from_project_id" = 9
↳ app/services/base_count_service.rb:26:in `uncached_count'
(0.3ms) SELECT COUNT(*) FROM "fork_network_members" WHERE "fork_network_members"."forked_from_project_id" = 83
↳ app/services/base_count_service.rb:26:in `uncached_count'
(0.5ms) SELECT COUNT(*) FROM "fork_network_members" WHERE "fork_network_members"."forked_from_project_id" = 10
↳ app/services/base_count_service.rb:26:in `uncached_count'
(0.3ms) SELECT COUNT(*) FROM "fork_network_members" WHERE "fork_network_members"."forked_from_project_id" = 16
↳ app/services/base_count_service.rb:26:in `uncached_count'
(0.4ms) SELECT COUNT(*) FROM "fork_network_members" WHERE "fork_network_members"."forked_from_project_id" = 14
↳ app/services/base_count_service.rb:26:in `uncached_count'
(0.3ms) SELECT COUNT(*) FROM "fork_network_members" WHERE "fork_network_members"."forked_from_project_id" = 15
↳ app/services/base_count_service.rb:26:in `uncached_count'
(0.4ms) SELECT COUNT(*) FROM "fork_network_members" WHERE "fork_network_members"."forked_from_project_id" = 68
↳ app/services/base_count_service.rb:26:in `uncached_count'
(0.3ms) SELECT COUNT(*) FROM "fork_network_members" WHERE "fork_network_members"."forked_from_project_id" = 75
↳ app/services/base_count_service.rb:26:in `uncached_count'
(0.4ms) SELECT COUNT(*) FROM "fork_network_members" WHERE "fork_network_members"."forked_from_project_id" = 17
↳ app/services/base_count_service.rb:26:in `uncached_count'
(0.3ms) SELECT COUNT(*) FROM "fork_network_members" WHERE "fork_network_members"."forked_from_project_id" = 70
↳ app/services/base_count_service.rb:26:in `uncached_count'
(0.3ms) SELECT COUNT(*) FROM "fork_network_members" WHERE "fork_network_members"."forked_from_project_id" = 2
↳ app/services/base_count_service.rb:26:in `uncached_count'
Edited by Terri Chu