Address N+1 queries in VulnerabilityFindingsActions#index method
Problem to solve
We have controllers that feed the Project Security Dashboard and Group Security Dashboard with Vulnerability Findings:
Projects::Security::VulnerabilitiesControllerProjects::Security::VulnerabilityFindingsControllerGroups::Security::VulnerabilitiesControllerGroups::Security::VulnerabilityFindingsController
All of them are using the VulnerabilityFindingsActions#index method to find and return Vulnerability Findings. This method is intended to be performant by using the Vulnerabilities::OccurrenceSerializer which in turn invokes the Gitlab::Vulnerabilities::OccurrencesPreloader.preload!. But the preloader doesn't seem to work correctly, at least for associated Identifiers and Scanners:
Processing by Groups::Security::VulnerabilitiesController#index as JSON
Parameters: {"group_id"=>"group1"}
User Load (0.8ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."id" ASC LIMIT $2 [["id", 1], ["LIMIT", 1]]
↳ app/controllers/concerns/enforces_two_factor_authentication.rb:26
Identity Exists (0.7ms) SELECT 1 AS one FROM "identities" WHERE "identities"."user_id" = $1 AND (provider = 'ultraauth' AND extern_uid IS NOT NULL) LIMIT $2 [["user_id", 1], ["LIMIT", 1]]
↳ app/models/user.rb:980
Identity Exists (0.3ms) SELECT 1 AS one FROM "identities" WHERE "identities"."user_id" = $1 AND (provider LIKE 'ldap%' AND extern_uid IS NOT NULL) LIMIT $2 [["user_id", 1], ["LIMIT", 1]]
↳ app/models/user.rb:972
Identity Exists (0.3ms) SELECT 1 AS one FROM "identities" WHERE "identities"."user_id" = $1 AND (provider = 'ultraauth' AND extern_uid IS NOT NULL) LIMIT $2 [["user_id", 1], ["LIMIT", 1]]
↳ app/models/user.rb:980
SQL (1.1ms) SELECT "namespaces"."id" AS t0_r0, "namespaces"."name" AS t0_r1, "namespaces"."path" AS t0_r2, "namespaces"."owner_id" AS t0_r3, "namespaces"."created_at" AS t0_r4, "namespaces"."updated_at" AS t0_r5, "namespaces"."type" AS t0_r6, "namespaces"."description" AS t0_r7, "namespaces"."avatar" AS t0_r8, "namespaces"."membership_lock" AS t0_r9, "namespaces"."share_with_group_lock" AS t0_r10, "namespaces"."visibility_level" AS t0_r11, "namespaces"."request_access_enabled" AS t0_r12, "namespaces"."ldap_sync_status" AS t0_r13, "namespaces"."ldap_sync_error" AS t0_r14, "namespaces"."ldap_sync_last_update_at" AS t0_r15, "namespaces"."ldap_sync_last_successful_update_at" AS t0_r16, "namespaces"."ldap_sync_last_sync_at" AS t0_r17, "namespaces"."description_html" AS t0_r18, "namespaces"."lfs_enabled" AS t0_r19, "namespaces"."parent_id" AS t0_r20, "namespaces"."shared_runners_minutes_limit" AS t0_r21, "namespaces"."repository_size_limit" AS t0_r22, "namespaces"."require_two_factor_authentication" AS t0_r23, "namespaces"."two_factor_grace_period" AS t0_r24, "namespaces"."cached_markdown_version" AS t0_r25, "namespaces"."plan_id" AS t0_r26, "namespaces"."project_creation_level" AS t0_r27, "namespaces"."runners_token" AS t0_r28, "namespaces"."trial_ends_on" AS t0_r29, "namespaces"."file_template_project_id" AS t0_r30, "namespaces"."saml_discovery_token" AS t0_r31, "namespaces"."runners_token_encrypted" AS t0_r32, "namespaces"."custom_project_templates_group_id" AS t0_r33, "namespaces"."auto_devops_enabled" AS t0_r34, "namespaces"."extra_shared_runners_minutes_limit" AS t0_r35, "namespaces"."last_ci_minutes_notification_at" AS t0_r36, "namespaces"."last_ci_minutes_usage_notification_level" AS t0_r37, "namespaces"."subgroup_creation_level" AS t0_r38, "namespaces"."emails_disabled" AS t0_r39, "namespaces"."max_pages_size" AS t0_r40, "namespaces"."max_artifacts_size" AS t0_r41, "routes"."id" AS t1_r0, "routes"."source_id" AS t1_r1, "routes"."source_type" AS t1_r2, "routes"."path" AS t1_r3, "routes"."created_at" AS t1_r4, "routes"."updated_at" AS t1_r5, "routes"."name" AS t1_r6 FROM "namespaces" LEFT OUTER JOIN "routes" ON "routes"."source_id" = "namespaces"."id" AND "routes"."source_type" = $1 WHERE "namespaces"."type" IN ('Group') AND "routes"."path" = $2 LIMIT $3 [["source_type", "Namespace"], ["path", "group1"], ["LIMIT", 1]]
↳ app/models/concerns/routable.rb:38
Group Load (0.5ms) SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" IN ('Group') AND "namespaces"."id" = $1 AND "namespaces"."parent_id" IS NULL LIMIT $2 [["id", 2], ["LIMIT", 1]]
↳ app/models/namespace.rb:264
SamlProvider Load (0.2ms) SELECT "saml_providers".* FROM "saml_providers" WHERE "saml_providers"."group_id" = $1 LIMIT $2 [["group_id", 2], ["LIMIT", 1]]
↳ ee/lib/gitlab/auth/group_saml/sso_enforcer.rb:30
License Load (0.1ms) SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT $1 [["LIMIT", 1]]
↳ ee/app/models/license.rb:254
GroupMember Load (0.7ms) SELECT "members".* FROM "members" LEFT OUTER JOIN "users" ON "members"."user_id" = "users"."id" WHERE "members"."type" IN ('GroupMember') AND "members"."source_type" = $1 AND "users"."state" = $2 AND "members"."requested_at" IS NULL AND "members"."source_id" = $3 AND "members"."user_id" = $4 ORDER BY "members"."access_level" DESC LIMIT $5 [["source_type", "Namespace"], ["state", "active"], ["source_id", 2], ["user_id", 1], ["LIMIT", 1]]
↳ app/models/group.rb:378
Unpermitted parameters: :group_id, :format
(2.1ms) SELECT COUNT(*) FROM (SELECT 1 AS one FROM "vulnerability_occurrences" INNER JOIN "vulnerability_occurrence_pipelines" ON "vulnerability_occurrence_pipelines"."occurrence_id" = "vulnerability_occurrences"."id" INNER JOIN "ci_pipelines" ON "ci_pipelines"."id" = "vulnerability_occurrence_pipelines"."pipeline_id" WHERE "ci_pipelines"."id" IN (SELECT max(id) as id FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" IN (SELECT "projects"."id" FROM "projects" INNER JOIN routes rs ON rs.source_id = projects.id AND rs.source_type = 'Project' WHERE (rs.path LIKE 'group1/%')) AND (EXISTS (SELECT 1 FROM "vulnerability_occurrence_pipelines" WHERE (ci_pipelines.id=vulnerability_occurrence_pipelines.pipeline_id))) AND ("ci_pipelines"."status" IN ('success')) GROUP BY "ci_pipelines"."project_id") LIMIT $1) subquery_for_count [["LIMIT", 10001]]
↳ config/initializers/kaminari_active_record_relation_methods_with_limit.rb:27
Vulnerabilities::Occurrence Load (1.9ms) SELECT vulnerability_occurrences.*, ci_pipelines.sha FROM "vulnerability_occurrences" INNER JOIN "vulnerability_occurrence_pipelines" ON "vulnerability_occurrence_pipelines"."occurrence_id" = "vulnerability_occurrences"."id" INNER JOIN "ci_pipelines" ON "ci_pipelines"."id" = "vulnerability_occurrence_pipelines"."pipeline_id" WHERE "ci_pipelines"."id" IN (SELECT max(id) as id FROM "ci_pipelines" WHERE "ci_pipelines"."project_id" IN (SELECT "projects"."id" FROM "projects" INNER JOIN routes rs ON rs.source_id = projects.id AND rs.source_type = 'Project' WHERE (rs.path LIKE 'group1/%')) AND (EXISTS (SELECT 1 FROM "vulnerability_occurrence_pipelines" WHERE (ci_pipelines.id=vulnerability_occurrence_pipelines.pipeline_id))) AND ("ci_pipelines"."status" IN ('success')) GROUP BY "ci_pipelines"."project_id") ORDER BY severity desc, "vulnerability_occurrences"."id" ASC LIMIT $1 OFFSET $2 [["LIMIT", 20], ["OFFSET", 0]]
↳ ee/lib/gitlab/vulnerabilities/occurrences_preloader.rb:17
Vulnerabilities::Identifier Load (5.4ms) SELECT "vulnerability_identifiers".* FROM "vulnerability_identifiers" INNER JOIN "vulnerability_occurrence_identifiers" ON "vulnerability_identifiers"."id" = "vulnerability_occurrence_identifiers"."identifier_id" WHERE "vulnerability_occurrence_identifiers"."occurrence_id" = $1 ORDER BY "vulnerability_identifiers"."id" ASC LIMIT $2 [["occurrence_id", 1], ["LIMIT", 1]]
HERE =========> ↳ ee/app/models/vulnerabilities/occurrence.rb:227
Vulnerabilities::Identifier Load (0.3ms) SELECT "vulnerability_identifiers".* FROM "vulnerability_identifiers" INNER JOIN "vulnerability_occurrence_identifiers" ON "vulnerability_identifiers"."id" = "vulnerability_occurrence_identifiers"."identifier_id" WHERE "vulnerability_occurrence_identifiers"."occurrence_id" = $1 ORDER BY "vulnerability_identifiers"."id" ASC LIMIT $2 [["occurrence_id", 2], ["LIMIT", 1]]
↳ ee/app/models/vulnerabilities/occurrence.rb:227
Vulnerabilities::Identifier Load (0.8ms) SELECT "vulnerability_identifiers".* FROM "vulnerability_identifiers" INNER JOIN "vulnerability_occurrence_identifiers" ON "vulnerability_identifiers"."id" = "vulnerability_occurrence_identifiers"."identifier_id" WHERE "vulnerability_occurrence_identifiers"."occurrence_id" = $1 ORDER BY "vulnerability_identifiers"."id" ASC LIMIT $2 [["occurrence_id", 1], ["LIMIT", 1]]
↳ ee/app/models/vulnerabilities/occurrence.rb:227
Vulnerabilities::Identifier Load (0.4ms) SELECT "vulnerability_identifiers".* FROM "vulnerability_identifiers" INNER JOIN "vulnerability_occurrence_identifiers" ON "vulnerability_identifiers"."id" = "vulnerability_occurrence_identifiers"."identifier_id" WHERE "vulnerability_occurrence_identifiers"."occurrence_id" = $1 ORDER BY "vulnerability_identifiers"."id" ASC LIMIT $2 [["occurrence_id", 2], ["LIMIT", 1]]
↳ ee/app/models/vulnerabilities/occurrence.rb:227
Vulnerabilities::Scanner Load (0.2ms) SELECT "vulnerability_scanners".* FROM "vulnerability_scanners" WHERE "vulnerability_scanners"."id" = $1 [["id", 1]]
↳ ee/lib/gitlab/vulnerabilities/occurrences_preloader.rb:17
Vulnerabilities::Identifier Load (0.3ms) SELECT "vulnerability_identifiers".* FROM "vulnerability_identifiers" INNER JOIN "vulnerability_occurrence_identifiers" ON "vulnerability_identifiers"."id" = "vulnerability_occurrence_identifiers"."identifier_id" WHERE "vulnerability_occurrence_identifiers"."occurrence_id" = $1 ORDER BY "vulnerability_identifiers"."id" ASC LIMIT $2 [["occurrence_id", 1], ["LIMIT", 1]]
↳ ee/app/models/vulnerabilities/occurrence.rb:227
Vulnerabilities::Identifier Load (0.3ms) SELECT "vulnerability_identifiers".* FROM "vulnerability_identifiers" INNER JOIN "vulnerability_occurrence_identifiers" ON "vulnerability_identifiers"."id" = "vulnerability_occurrence_identifiers"."identifier_id" WHERE "vulnerability_occurrence_identifiers"."occurrence_id" = $1 ORDER BY "vulnerability_identifiers"."id" ASC LIMIT $2 [["occurrence_id", 2], ["LIMIT", 1]]
↳ ee/app/models/vulnerabilities/occurrence.rb:227
Vulnerabilities::Identifier Load (0.1ms) SELECT "vulnerability_identifiers".* FROM "vulnerability_identifiers" INNER JOIN "vulnerability_occurrence_identifiers" ON "vulnerability_identifiers"."id" = "vulnerability_occurrence_identifiers"."identifier_id" WHERE "vulnerability_occurrence_identifiers"."occurrence_id" = $1 ORDER BY "vulnerability_identifiers"."id" ASC LIMIT $2 [["occurrence_id", 1], ["LIMIT", 1]]
↳ ee/app/models/vulnerabilities/occurrence.rb:227
Vulnerabilities::Identifier Load (0.1ms) SELECT "vulnerability_identifiers".* FROM "vulnerability_identifiers" INNER JOIN "vulnerability_occurrence_identifiers" ON "vulnerability_identifiers"."id" = "vulnerability_occurrence_identifiers"."identifier_id" WHERE "vulnerability_occurrence_identifiers"."occurrence_id" = $1 ORDER BY "vulnerability_identifiers"."id" ASC LIMIT $2 [["occurrence_id", 2], ["LIMIT", 1]]
↳ ee/app/models/vulnerabilities/occurrence.rb:227
Vulnerabilities::OccurrenceIdentifier Load (0.2ms) SELECT "vulnerability_occurrence_identifiers".* FROM "vulnerability_occurrence_identifiers" WHERE "vulnerability_occurrence_identifiers"."occurrence_id" = $1 [["occurrence_id", 1]]
↳ ee/lib/gitlab/vulnerabilities/occurrences_preloader.rb:17
Vulnerabilities::Identifier Load (0.1ms) SELECT "vulnerability_identifiers".* FROM "vulnerability_identifiers" INNER JOIN "vulnerability_occurrence_identifiers" ON "vulnerability_identifiers"."id" = "vulnerability_occurrence_identifiers"."identifier_id" WHERE "vulnerability_occurrence_identifiers"."occurrence_id" = $1 ORDER BY "vulnerability_identifiers"."id" ASC LIMIT $2 [["occurrence_id", 2], ["LIMIT", 1]]
↳ ee/app/models/vulnerabilities/occurrence.rb:227
Vulnerabilities::Identifier Load (0.1ms) SELECT "vulnerability_identifiers".* FROM "vulnerability_identifiers" INNER JOIN "vulnerability_occurrence_identifiers" ON "vulnerability_identifiers"."id" = "vulnerability_occurrence_identifiers"."identifier_id" WHERE "vulnerability_occurrence_identifiers"."occurrence_id" = $1 ORDER BY "vulnerability_identifiers"."id" ASC LIMIT $2 [["occurrence_id", 2], ["LIMIT", 1]]
↳ ee/app/models/vulnerabilities/occurrence.rb:227
Project Load (0.4ms) SELECT "projects".* FROM "projects" WHERE "projects"."id" = $1 [["id", 1]]
↳ ee/lib/gitlab/vulnerabilities/occurrences_preloader.rb:17
Namespace Load (0.3ms) SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = $1 [["id", 2]]
↳ ee/lib/gitlab/vulnerabilities/occurrences_preloader.rb:17
ProjectFeature Load (0.2ms) SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = $1 [["project_id", 1]]
↳ ee/lib/gitlab/vulnerabilities/occurrences_preloader.rb:17Proposal
- To re-evaluate how
Gitlab::Vulnerabilities::OccurrencesPreloaderworks and maybe resort to other preloading methods likejoinsetc. - Also, to find out why current
N+1query tests for theseindexcontroller actions do not detect this situation.