Skip to content

Resolve N+1 when serialising Vulnerability Finding links

Gregory Havenga requested to merge 390818-resolve-n-1-when-serialising-vulnerability-finding-links into master

What does this MR do and why?

@minac noted the possibility of N+1 queries in the vulnerability finder as a result of #390818 (closed). This MR is intended to mitigate the risk of these N+1's as we begin to serialise the Vulnerability::Feedback replacement models.

Database Impact

These changes don't affect the finder directly but require a downstream user to call to the linked data models. I've demonstrated this by representing the finding results with the finding entity, which triggers loads for the Vulnerability, Vulnerability::StateTransition, Vulnerability::IssueLink and Vulnerability::MergeRequestLink relationships with an inner join.

Local Represent Test 
Vulnerabilities::FindingEntity.represent(Security::FindingsFinder.new(Ci::Pipeline.last).execute.findings)
  Ci::Pipeline Load (0.6ms)  SELECT "ci_pipelines".* FROM "ci_pipelines" ORDER BY "ci_pipelines"."id" DESC LIMIT 1 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:ci,console_hostname:b016eb751cbb,line:(pry):8:in `__pry__'*/
  Security::Finding Exists? (0.5ms)  SELECT 1 AS one FROM "security_findings" INNER JOIN "security_scans" ON "security_findings"."scan_id" = "security_scans"."id" WHERE "security_scans"."pipeline_id" = 604 LIMIT 1 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/ee/app/models/ee/ci/pipeline.rb:213:in `has_security_findings?'*/
  Project Load (0.5ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 20 LIMIT 1 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/ee/app/finders/security/findings_finder.rb:44:in `project'*/
  Security::Finding Load (2.5ms)  SELECT "security_findings".* FROM "security_findings" INNER JOIN "security_scans" ON "security_findings"."scan_id" = "security_scans"."id" WHERE "security_scans"."pipeline_id" = 604 AND "security_findings"."deduplicated" = TRUE AND "security_scans"."latest" = TRUE AND "security_scans"."status" = 1 AND (NOT EXISTS (SELECT 1 FROM "security_scans" INNER JOIN "projects" ON "projects"."id" = "security_scans"."project_id" INNER JOIN "vulnerability_feedback" ON "vulnerability_feedback"."project_id" = "projects"."id" WHERE (vulnerability_feedback.category = (security_scans.scan_type - 1)) AND "vulnerability_feedback"."feedback_type" = 0 AND (security_scans.id = security_findings.scan_id) AND (vulnerability_feedback.finding_uuid = security_findings.uuid))) ORDER BY "security_findings"."severity" DESC, "security_findings"."id" ASC LIMIT 20 OFFSET 0 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/ee/app/finders/security/findings_finder.rb:47:in `map'*/
  Security::Scan Load (0.3ms)  SELECT "security_scans".* FROM "security_scans" WHERE "security_scans"."id" IN (27, 29, 30) /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/ee/app/finders/security/findings_finder.rb:47:in `map'*/
  Ci::Build Load (0.6ms)  SELECT "ci_builds".* FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."id" IN (531, 538, 539) /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:ci,console_hostname:b016eb751cbb,line:/ee/app/finders/security/findings_finder.rb:47:in `map'*/
  Ci::JobArtifact Load (0.3ms)  SELECT "ci_job_artifacts"."project_id", "ci_job_artifacts"."file_type", "ci_job_artifacts"."size", "ci_job_artifacts"."created_at", "ci_job_artifacts"."updated_at", "ci_job_artifacts"."expire_at", "ci_job_artifacts"."file", "ci_job_artifacts"."file_store", "ci_job_artifacts"."file_sha256", "ci_job_artifacts"."file_format", "ci_job_artifacts"."file_location", "ci_job_artifacts"."id", "ci_job_artifacts"."job_id", "ci_job_artifacts"."locked", "ci_job_artifacts"."partition_id", "ci_job_artifacts"."accessibility" FROM "ci_job_artifacts" WHERE "ci_job_artifacts"."job_id" IN (531, 538, 539) /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:ci,console_hostname:b016eb751cbb,line:/ee/app/finders/security/findings_finder.rb:47:in `map'*/
  Ci::Pipeline Load (0.4ms)  SELECT "ci_pipelines".* FROM "ci_pipelines" WHERE "ci_pipelines"."id" = 604 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:ci,console_hostname:b016eb751cbb,line:/ee/app/finders/security/findings_finder.rb:47:in `map'*/
  Vulnerabilities::Scanner Load (0.2ms)  SELECT "vulnerability_scanners".* FROM "vulnerability_scanners" WHERE "vulnerability_scanners"."id" IN (546, 547, 542) /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/ee/app/finders/security/findings_finder.rb:47:in `map'*/
  Vulnerabilities::Read Load (0.3ms)  SELECT "vulnerability_reads".* FROM "vulnerability_reads" WHERE "vulnerability_reads"."uuid" IN ('9487aa2e-6c62-5f90-82e5-6e9a29c5584d', '286cec10-4ab6-5137-babe-4601f1abc8ca', '9b0462f7-825e-5069-9fba-c78b42f07344', '14ae1a6a-a9b2-51c7-aa8b-417ecbe61fc1', 'b195d5eb-a250-5cc6-b19b-e53d87601694', 'a116d51e-5a59-5a29-9473-ee0471b6c580', '17b3c28c-7e76-5f4a-9d3b-c244246580eb', 'ad897581-0a42-53c3-87d7-06743a3254c2', '0a0db285-e190-5231-880d-a9ee102d2fb0', 'c25cac11-3f09-52b1-bef9-ad4a3c7a88fa', '46c79dc0-a9a1-5b4b-88bf-dcf3260c001d', '41f7e1d0-d9ca-5836-942d-21d7170e6ff9', 'daa41cad-2d5f-560e-ae59-ba853fc660da', 'd56e836e-c5ae-598b-8a96-b80fac68b0b7', 'd67df856-9bb7-51e2-beea-be45153bbdbb', 'cebcb03a-6fc4-5a48-af99-5b2d4df46dcd', '49b4e521-cb54-5b99-9084-0784f05a7d7a', 'b8ce48a2-598a-578e-a7c1-bc70f9e29349', '807050dd-3aeb-50f7-887b-69004e1310c7', 'de0d2711-36e7-5a78-af20-c016a98a4c46') /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/lib/unnested_in_filters/dsl.rb:62:in `load'*/
  Project Load (0.5ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 20 LIMIT 1 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/ee/app/models/ee/ci/job_artifact.rb:130:in `block in security_report'*/
  Project Load (0.8ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 20 LIMIT 1 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/lib/gitlab/ci/reports/security/report.rb:12:in `project'*/
  Project Load (0.6ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 20 LIMIT 1 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/ee/app/models/ee/ci/job_artifact.rb:130:in `block in security_report'*/
  Project Load (0.5ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 20 LIMIT 1 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/ee/app/models/ee/ci/job_artifact.rb:130:in `block in security_report'*/
  SQL (0.9ms)  SELECT "vulnerabilities"."id" AS t0_r0, "vulnerabilities"."milestone_id" AS t0_r1, "vulnerabilities"."epic_id" AS t0_r2, "vulnerabilities"."project_id" AS t0_r3, "vulnerabilities"."author_id" AS t0_r4, "vulnerabilities"."updated_by_id" AS t0_r5, "vulnerabilities"."last_edited_by_id" AS t0_r6, "vulnerabilities"."start_date" AS t0_r7, "vulnerabilities"."due_date" AS t0_r8, "vulnerabilities"."last_edited_at" AS t0_r9, "vulnerabilities"."created_at" AS t0_r10, "vulnerabilities"."updated_at" AS t0_r11, "vulnerabilities"."title" AS t0_r12, "vulnerabilities"."title_html" AS t0_r13, "vulnerabilities"."description" AS t0_r14, "vulnerabilities"."description_html" AS t0_r15, "vulnerabilities"."start_date_sourcing_milestone_id" AS t0_r16, "vulnerabilities"."due_date_sourcing_milestone_id" AS t0_r17, "vulnerabilities"."state" AS t0_r18, "vulnerabilities"."severity" AS t0_r19, "vulnerabilities"."severity_overridden" AS t0_r20, "vulnerabilities"."confidence" AS t0_r21, "vulnerabilities"."confidence_overridden" AS t0_r22, "vulnerabilities"."resolved_by_id" AS t0_r23, "vulnerabilities"."resolved_at" AS t0_r24, "vulnerabilities"."report_type" AS t0_r25, "vulnerabilities"."cached_markdown_version" AS t0_r26, "vulnerabilities"."confirmed_by_id" AS t0_r27, "vulnerabilities"."confirmed_at" AS t0_r28, "vulnerabilities"."dismissed_at" AS t0_r29, "vulnerabilities"."dismissed_by_id" AS t0_r30, "vulnerabilities"."resolved_on_default_branch" AS t0_r31, "vulnerabilities"."present_on_default_branch" AS t0_r32, "vulnerabilities"."detected_at" AS t0_r33, "findings"."id" AS t1_r0, "findings"."created_at" AS t1_r1, "findings"."updated_at" AS t1_r2, "findings"."severity" AS t1_r3, "findings"."confidence" AS t1_r4, "findings"."report_type" AS t1_r5, "findings"."project_id" AS t1_r6, "findings"."scanner_id" AS t1_r7, "findings"."primary_identifier_id" AS t1_r8, "findings"."project_fingerprint" AS t1_r9, "findings"."location_fingerprint" AS t1_r10, "findings"."uuid" AS t1_r11, "findings"."name" AS t1_r12, "findings"."metadata_version" AS t1_r13, "findings"."raw_metadata" AS t1_r14, "findings"."vulnerability_id" AS t1_r15, "findings"."details" AS t1_r16, "findings"."description" AS t1_r17, "findings"."message" AS t1_r18, "findings"."solution" AS t1_r19, "findings"."cve" AS t1_r20, "findings"."location" AS t1_r21, "findings"."detection_method" AS t1_r22 FROM "vulnerabilities" LEFT OUTER JOIN "vulnerability_occurrences" "findings" ON "findings"."vulnerability_id" = "vulnerabilities"."id" WHERE "vulnerabilities"."project_id" = 20 AND "findings"."uuid" IN ('9487aa2e-6c62-5f90-82e5-6e9a29c5584d', '286cec10-4ab6-5137-babe-4601f1abc8ca', '9b0462f7-825e-5069-9fba-c78b42f07344', '14ae1a6a-a9b2-51c7-aa8b-417ecbe61fc1', 'b195d5eb-a250-5cc6-b19b-e53d87601694', 'a116d51e-5a59-5a29-9473-ee0471b6c580', '17b3c28c-7e76-5f4a-9d3b-c244246580eb', 'ad897581-0a42-53c3-87d7-06743a3254c2', '0a0db285-e190-5231-880d-a9ee102d2fb0', 'c25cac11-3f09-52b1-bef9-ad4a3c7a88fa', '46c79dc0-a9a1-5b4b-88bf-dcf3260c001d', '41f7e1d0-d9ca-5836-942d-21d7170e6ff9', 'daa41cad-2d5f-560e-ae59-ba853fc660da', 'd56e836e-c5ae-598b-8a96-b80fac68b0b7', 'd67df856-9bb7-51e2-beea-be45153bbdbb', 'cebcb03a-6fc4-5a48-af99-5b2d4df46dcd', '49b4e521-cb54-5b99-9084-0784f05a7d7a', 'b8ce48a2-598a-578e-a7c1-bc70f9e29349', '807050dd-3aeb-50f7-887b-69004e1310c7', 'de0d2711-36e7-5a78-af20-c016a98a4c46') /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/ee/app/finders/security/findings_finder.rb:63:in `existing_vulnerabilities'*/
  Integrations::Jira Load (0.5ms)  SELECT "integrations".* FROM "integrations" WHERE "integrations"."type_new" = 'Integrations::Jira' AND "integrations"."project_id" = 20 LIMIT 1 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/ee/app/models/ee/project.rb:306:in `configured_to_create_issues_from_vulnerabilities?'*/
  Namespace Load (0.8ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 72 LIMIT 1 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/config/routes.rb:311:in `block (4 levels) in <main>'*/
  Route Load (0.3ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 72 AND "routes"."source_type" = 'Namespace' LIMIT 1 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/app/models/concerns/routable.rb:121:in `full_path'*/
  Route Load (0.3ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 20 AND "routes"."source_type" = 'Project' LIMIT 1 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/app/models/concerns/routable.rb:103:in `full_name'*/
  Issue Load (0.7ms)  SELECT "issues".* FROM "issues" INNER JOIN "vulnerability_issue_links" ON "issues"."id" = "vulnerability_issue_links"."issue_id" WHERE "vulnerability_issue_links"."vulnerability_id" = 543 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/ee/app/models/vulnerabilities/finding.rb:185:in `issue_feedback'*/
  Vulnerabilities::Feedback Load (0.8ms)  SELECT "vulnerability_feedback".* FROM "vulnerability_feedback" WHERE "vulnerability_feedback"."issue_id" IN (SELECT "issues"."id" FROM "issues" INNER JOIN "vulnerability_issue_links" ON "issues"."id" = "vulnerability_issue_links"."issue_id" WHERE "vulnerability_issue_links"."vulnerability_id" = 543) LIMIT 1 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/ee/app/models/vulnerabilities/finding.rb:185:in `issue_feedback'*/
  Issue Load (0.4ms)  SELECT "issues".* FROM "issues" WHERE "issues"."id" = 631 LIMIT 1 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/ee/app/serializers/vulnerabilities/feedback_entity.rb:28:in `block in <class:FeedbackEntity>'*/
  Project Load (0.6ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 20 LIMIT 1 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/app/policies/issuable_policy.rb:4:in `block in <class:IssuablePolicy>'*/
  Project Load (0.5ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 20 LIMIT 1 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/ee/app/policies/vulnerabilities/feedback_policy.rb:5:in `block in <class:FeedbackPolicy>'*/
  User Load (0.7ms)  SELECT "users"."id", "users"."email", "users"."encrypted_password", "users"."reset_password_token", "users"."reset_password_sent_at", "users"."remember_created_at", "users"."sign_in_count", "users"."current_sign_in_at", "users"."last_sign_in_at", "users"."current_sign_in_ip", "users"."last_sign_in_ip", "users"."created_at", "users"."updated_at", "users"."name", "users"."admin", "users"."projects_limit", "users"."failed_attempts", "users"."locked_at", "users"."username", "users"."can_create_group", "users"."can_create_team", "users"."state", "users"."color_scheme_id", "users"."password_expires_at", "users"."created_by_id", "users"."last_credential_check_at", "users"."avatar", "users"."confirmation_token", "users"."confirmed_at", "users"."confirmation_sent_at", "users"."unconfirmed_email", "users"."hide_no_ssh_key", "users"."admin_email_unsubscribed_at", "users"."notification_email", "users"."hide_no_password", "users"."password_automatically_set", "users"."encrypted_otp_secret", "users"."encrypted_otp_secret_iv", "users"."encrypted_otp_secret_salt", "users"."otp_required_for_login", "users"."otp_backup_codes", "users"."public_email", "users"."dashboard", "users"."project_view", "users"."consumed_timestep", "users"."layout", "users"."hide_project_limit", "users"."note", "users"."unlock_token", "users"."otp_grace_period_started_at", "users"."external", "users"."incoming_email_token", "users"."auditor", "users"."require_two_factor_authentication_from_group", "users"."two_factor_grace_period", "users"."last_activity_on", "users"."notified_of_own_activity", "users"."preferred_language", "users"."email_opted_in", "users"."email_opted_in_ip", "users"."email_opted_in_source_id", "users"."email_opted_in_at", "users"."theme_id", "users"."accepted_term_id", "users"."feed_token", "users"."private_profile", "users"."roadmap_layout", "users"."include_private_contributions", "users"."commit_email", "users"."group_view", "users"."managing_group_id", "users"."first_name", "users"."last_name", "users"."static_object_token", "users"."role", "users"."user_type", "users"."static_object_token_encrypted", "users"."otp_secret_expires_at", "users"."onboarding_in_progress" FROM "users" WHERE "users"."id" = 1 LIMIT 1 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/bin/rails:4:in `<main>'*/
  Vulnerabilities::StateTransition Load (0.6ms)  SELECT "vulnerability_state_transitions".* FROM "vulnerability_state_transitions" INNER JOIN "vulnerabilities" ON "vulnerability_state_transitions"."vulnerability_id" = "vulnerabilities"."id" WHERE "vulnerabilities"."id" = 543 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/bin/rails:4:in `<main>'*/
  Vulnerabilities::IssueLink Load (0.4ms)  SELECT "vulnerability_issue_links".* FROM "vulnerability_issue_links" INNER JOIN "vulnerabilities" ON "vulnerability_issue_links"."vulnerability_id" = "vulnerabilities"."id" WHERE "vulnerabilities"."id" = 543 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/bin/rails:4:in `<main>'*/
  Issue Load (0.4ms)  SELECT "issues".* FROM "issues" WHERE "issues"."id" = 631 LIMIT 1 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/ee/app/serializers/vulnerabilities/issue_link_entity.rb:28:in `can_read_issue?'*/
  Project Load (0.5ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 20 LIMIT 1 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/app/policies/issuable_policy.rb:4:in `block in <class:IssuablePolicy>'*/
  User Load (0.8ms)  SELECT "users"."id", "users"."email", "users"."encrypted_password", "users"."reset_password_token", "users"."reset_password_sent_at", "users"."remember_created_at", "users"."sign_in_count", "users"."current_sign_in_at", "users"."last_sign_in_at", "users"."current_sign_in_ip", "users"."last_sign_in_ip", "users"."created_at", "users"."updated_at", "users"."name", "users"."admin", "users"."projects_limit", "users"."failed_attempts", "users"."locked_at", "users"."username", "users"."can_create_group", "users"."can_create_team", "users"."state", "users"."color_scheme_id", "users"."password_expires_at", "users"."created_by_id", "users"."last_credential_check_at", "users"."avatar", "users"."confirmation_token", "users"."confirmed_at", "users"."confirmation_sent_at", "users"."unconfirmed_email", "users"."hide_no_ssh_key", "users"."admin_email_unsubscribed_at", "users"."notification_email", "users"."hide_no_password", "users"."password_automatically_set", "users"."encrypted_otp_secret", "users"."encrypted_otp_secret_iv", "users"."encrypted_otp_secret_salt", "users"."otp_required_for_login", "users"."otp_backup_codes", "users"."public_email", "users"."dashboard", "users"."project_view", "users"."consumed_timestep", "users"."layout", "users"."hide_project_limit", "users"."note", "users"."unlock_token", "users"."otp_grace_period_started_at", "users"."external", "users"."incoming_email_token", "users"."auditor", "users"."require_two_factor_authentication_from_group", "users"."two_factor_grace_period", "users"."last_activity_on", "users"."notified_of_own_activity", "users"."preferred_language", "users"."email_opted_in", "users"."email_opted_in_ip", "users"."email_opted_in_source_id", "users"."email_opted_in_at", "users"."theme_id", "users"."accepted_term_id", "users"."feed_token", "users"."private_profile", "users"."roadmap_layout", "users"."include_private_contributions", "users"."commit_email", "users"."group_view", "users"."managing_group_id", "users"."first_name", "users"."last_name", "users"."static_object_token", "users"."role", "users"."user_type", "users"."static_object_token_encrypted", "users"."otp_secret_expires_at", "users"."onboarding_in_progress" FROM "users" INNER JOIN "issues" ON "users"."id" = "issues"."author_id" WHERE "issues"."id" = 631 LIMIT 1 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/bin/rails:4:in `<main>'*/
  Vulnerabilities::MergeRequestLink Load (0.3ms)  SELECT "vulnerability_merge_request_links".* FROM "vulnerability_merge_request_links" INNER JOIN "vulnerabilities" ON "vulnerability_merge_request_links"."vulnerability_id" = "vulnerabilities"."id" WHERE "vulnerabilities"."id" = 543 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/bin/rails:4:in `<main>'*/
  Issue Load (0.9ms)  SELECT "issues".* FROM "issues" INNER JOIN "vulnerability_issue_links" ON "issues"."id" = "vulnerability_issue_links"."issue_id" WHERE "vulnerability_issue_links"."vulnerability_id" = 544 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/ee/app/models/vulnerabilities/finding.rb:185:in `issue_feedback'*/
  Vulnerabilities::Feedback Load (0.9ms)  SELECT "vulnerability_feedback".* FROM "vulnerability_feedback" WHERE "vulnerability_feedback"."issue_id" IN (SELECT "issues"."id" FROM "issues" INNER JOIN "vulnerability_issue_links" ON "issues"."id" = "vulnerability_issue_links"."issue_id" WHERE "vulnerability_issue_links"."vulnerability_id" = 544) LIMIT 1 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/ee/app/models/vulnerabilities/finding.rb:185:in `issue_feedback'*/
  Issue Load (0.4ms)  SELECT "issues".* FROM "issues" WHERE "issues"."id" = 632 LIMIT 1 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/ee/app/serializers/vulnerabilities/feedback_entity.rb:28:in `block in <class:FeedbackEntity>'*/
  Project Load (0.7ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 20 LIMIT 1 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/app/policies/issuable_policy.rb:4:in `block in <class:IssuablePolicy>'*/
  Project Load (0.5ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 20 LIMIT 1 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/ee/app/policies/vulnerabilities/feedback_policy.rb:5:in `block in <class:FeedbackPolicy>'*/
  User Load (0.6ms)  SELECT "users"."id", "users"."email", "users"."encrypted_password", "users"."reset_password_token", "users"."reset_password_sent_at", "users"."remember_created_at", "users"."sign_in_count", "users"."current_sign_in_at", "users"."last_sign_in_at", "users"."current_sign_in_ip", "users"."last_sign_in_ip", "users"."created_at", "users"."updated_at", "users"."name", "users"."admin", "users"."projects_limit", "users"."failed_attempts", "users"."locked_at", "users"."username", "users"."can_create_group", "users"."can_create_team", "users"."state", "users"."color_scheme_id", "users"."password_expires_at", "users"."created_by_id", "users"."last_credential_check_at", "users"."avatar", "users"."confirmation_token", "users"."confirmed_at", "users"."confirmation_sent_at", "users"."unconfirmed_email", "users"."hide_no_ssh_key", "users"."admin_email_unsubscribed_at", "users"."notification_email", "users"."hide_no_password", "users"."password_automatically_set", "users"."encrypted_otp_secret", "users"."encrypted_otp_secret_iv", "users"."encrypted_otp_secret_salt", "users"."otp_required_for_login", "users"."otp_backup_codes", "users"."public_email", "users"."dashboard", "users"."project_view", "users"."consumed_timestep", "users"."layout", "users"."hide_project_limit", "users"."note", "users"."unlock_token", "users"."otp_grace_period_started_at", "users"."external", "users"."incoming_email_token", "users"."auditor", "users"."require_two_factor_authentication_from_group", "users"."two_factor_grace_period", "users"."last_activity_on", "users"."notified_of_own_activity", "users"."preferred_language", "users"."email_opted_in", "users"."email_opted_in_ip", "users"."email_opted_in_source_id", "users"."email_opted_in_at", "users"."theme_id", "users"."accepted_term_id", "users"."feed_token", "users"."private_profile", "users"."roadmap_layout", "users"."include_private_contributions", "users"."commit_email", "users"."group_view", "users"."managing_group_id", "users"."first_name", "users"."last_name", "users"."static_object_token", "users"."role", "users"."user_type", "users"."static_object_token_encrypted", "users"."otp_secret_expires_at", "users"."onboarding_in_progress" FROM "users" WHERE "users"."id" = 1 LIMIT 1 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/bin/rails:4:in `<main>'*/
  Vulnerabilities::StateTransition Load (0.5ms)  SELECT "vulnerability_state_transitions".* FROM "vulnerability_state_transitions" INNER JOIN "vulnerabilities" ON "vulnerability_state_transitions"."vulnerability_id" = "vulnerabilities"."id" WHERE "vulnerabilities"."id" = 544 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/bin/rails:4:in `<main>'*/
  Vulnerabilities::IssueLink Load (0.3ms)  SELECT "vulnerability_issue_links".* FROM "vulnerability_issue_links" INNER JOIN "vulnerabilities" ON "vulnerability_issue_links"."vulnerability_id" = "vulnerabilities"."id" WHERE "vulnerabilities"."id" = 544 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/bin/rails:4:in `<main>'*/
  Issue Load (0.4ms)  SELECT "issues".* FROM "issues" WHERE "issues"."id" = 632 LIMIT 1 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/ee/app/serializers/vulnerabilities/issue_link_entity.rb:28:in `can_read_issue?'*/
  Project Load (0.5ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 20 LIMIT 1 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/app/policies/issuable_policy.rb:4:in `block in <class:IssuablePolicy>'*/
  User Load (1.0ms)  SELECT "users"."id", "users"."email", "users"."encrypted_password", "users"."reset_password_token", "users"."reset_password_sent_at", "users"."remember_created_at", "users"."sign_in_count", "users"."current_sign_in_at", "users"."last_sign_in_at", "users"."current_sign_in_ip", "users"."last_sign_in_ip", "users"."created_at", "users"."updated_at", "users"."name", "users"."admin", "users"."projects_limit", "users"."failed_attempts", "users"."locked_at", "users"."username", "users"."can_create_group", "users"."can_create_team", "users"."state", "users"."color_scheme_id", "users"."password_expires_at", "users"."created_by_id", "users"."last_credential_check_at", "users"."avatar", "users"."confirmation_token", "users"."confirmed_at", "users"."confirmation_sent_at", "users"."unconfirmed_email", "users"."hide_no_ssh_key", "users"."admin_email_unsubscribed_at", "users"."notification_email", "users"."hide_no_password", "users"."password_automatically_set", "users"."encrypted_otp_secret", "users"."encrypted_otp_secret_iv", "users"."encrypted_otp_secret_salt", "users"."otp_required_for_login", "users"."otp_backup_codes", "users"."public_email", "users"."dashboard", "users"."project_view", "users"."consumed_timestep", "users"."layout", "users"."hide_project_limit", "users"."note", "users"."unlock_token", "users"."otp_grace_period_started_at", "users"."external", "users"."incoming_email_token", "users"."auditor", "users"."require_two_factor_authentication_from_group", "users"."two_factor_grace_period", "users"."last_activity_on", "users"."notified_of_own_activity", "users"."preferred_language", "users"."email_opted_in", "users"."email_opted_in_ip", "users"."email_opted_in_source_id", "users"."email_opted_in_at", "users"."theme_id", "users"."accepted_term_id", "users"."feed_token", "users"."private_profile", "users"."roadmap_layout", "users"."include_private_contributions", "users"."commit_email", "users"."group_view", "users"."managing_group_id", "users"."first_name", "users"."last_name", "users"."static_object_token", "users"."role", "users"."user_type", "users"."static_object_token_encrypted", "users"."otp_secret_expires_at", "users"."onboarding_in_progress" FROM "users" INNER JOIN "issues" ON "users"."id" = "issues"."author_id" WHERE "issues"."id" = 632 LIMIT 1 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/bin/rails:4:in `<main>'*/
  Vulnerabilities::MergeRequestLink Load (0.3ms)  SELECT "vulnerability_merge_request_links".* FROM "vulnerability_merge_request_links" INNER JOIN "vulnerabilities" ON "vulnerability_merge_request_links"."vulnerability_id" = "vulnerabilities"."id" WHERE "vulnerabilities"."id" = 544 /*application:console,correlation_id:c02bb1f39536efe7781d2c768ae4d269,db_config_name:main,console_hostname:b016eb751cbb,line:/bin/rails:4:in `<main>'*/
Fetch Findings and Vulnerabilities

This exceeds our 100ms limit, but I do suspect that may in part be due to the use of UUID's that don't exist in production. Postgres.ai can't find any recommendations to speed this up, so it would likely only be improved by table partitioning.

EXPLAIN
SELECT
    "vulnerabilities"."id" AS t0_r0,
    "vulnerabilities"."milestone_id" AS t0_r1,
    "vulnerabilities"."epic_id" AS t0_r2,
    "vulnerabilities"."project_id" AS t0_r3,
    "vulnerabilities"."author_id" AS t0_r4,
    "vulnerabilities"."updated_by_id" AS t0_r5,
    "vulnerabilities"."last_edited_by_id" AS t0_r6,
    "vulnerabilities"."start_date" AS t0_r7,
    "vulnerabilities"."due_date" AS t0_r8,
    "vulnerabilities"."last_edited_at" AS t0_r9,
    "vulnerabilities"."created_at" AS t0_r10,
    "vulnerabilities"."updated_at" AS t0_r11,
    "vulnerabilities"."title" AS t0_r12,
    "vulnerabilities"."title_html" AS t0_r13,
    "vulnerabilities"."description" AS t0_r14,
    "vulnerabilities"."description_html" AS t0_r15,
    "vulnerabilities"."start_date_sourcing_milestone_id" AS t0_r16,
    "vulnerabilities"."due_date_sourcing_milestone_id" AS t0_r17,
    "vulnerabilities"."state" AS t0_r18,
    "vulnerabilities"."severity" AS t0_r19,
    "vulnerabilities"."severity_overridden" AS t0_r20,
    "vulnerabilities"."confidence" AS t0_r21,
    "vulnerabilities"."confidence_overridden" AS t0_r22,
    "vulnerabilities"."resolved_by_id" AS t0_r23,
    "vulnerabilities"."resolved_at" AS t0_r24,
    "vulnerabilities"."report_type" AS t0_r25,
    "vulnerabilities"."cached_markdown_version" AS t0_r26,
    "vulnerabilities"."confirmed_by_id" AS t0_r27,
    "vulnerabilities"."confirmed_at" AS t0_r28,
    "vulnerabilities"."dismissed_at" AS t0_r29,
    "vulnerabilities"."dismissed_by_id" AS t0_r30,
    "vulnerabilities"."resolved_on_default_branch" AS t0_r31,
    "vulnerabilities"."present_on_default_branch" AS t0_r32,
    "vulnerabilities"."detected_at" AS t0_r33,
    "findings"."id" AS t1_r0,
    "findings"."created_at" AS t1_r1,
    "findings"."updated_at" AS t1_r2,
    "findings"."severity" AS t1_r3,
    "findings"."confidence" AS t1_r4,
    "findings"."report_type" AS t1_r5,
    "findings"."project_id" AS t1_r6,
    "findings"."scanner_id" AS t1_r7,
    "findings"."primary_identifier_id" AS t1_r8,
    "findings"."project_fingerprint" AS t1_r9,
    "findings"."location_fingerprint" AS t1_r10,
    "findings"."uuid" AS t1_r11,
    "findings"."name" AS t1_r12,
    "findings"."metadata_version" AS t1_r13,
    "findings"."raw_metadata" AS t1_r14,
    "findings"."vulnerability_id" AS t1_r15,
    "findings"."details" AS t1_r16,
    "findings"."description" AS t1_r17,
    "findings"."message" AS t1_r18,
    "findings"."solution" AS t1_r19,
    "findings"."cve" AS t1_r20,
    "findings"."location" AS t1_r21,
    "findings"."detection_method" AS t1_r22
FROM
    "vulnerabilities"
    LEFT OUTER JOIN "vulnerability_occurrences" "findings" ON "findings"."vulnerability_id" = "vulnerabilities"."id"
WHERE
    "vulnerabilities"."project_id" = 20
    AND "findings"."uuid" IN ('9487aa2e-6c62-5f90-82e5-6e9a29c5584d', '286cec10-4ab6-5137-babe-4601f1abc8ca', '9b0462f7-825e-5069-9fba-c78b42f07344', '14ae1a6a-a9b2-51c7-aa8b-417ecbe61fc1', 'b195d5eb-a250-5cc6-b19b-e53d87601694', 'a116d51e-5a59-5a29-9473-ee0471b6c580', '17b3c28c-7e76-5f4a-9d3b-c244246580eb', 'ad897581-0a42-53c3-87d7-06743a3254c2', '0a0db285-e190-5231-880d-a9ee102d2fb0', 'c25cac11-3f09-52b1-bef9-ad4a3c7a88fa', '46c79dc0-a9a1-5b4b-88bf-dcf3260c001d', '41f7e1d0-d9ca-5836-942d-21d7170e6ff9', 'daa41cad-2d5f-560e-ae59-ba853fc660da', 'd56e836e-c5ae-598b-8a96-b80fac68b0b7', 'd67df856-9bb7-51e2-beea-be45153bbdbb', 'cebcb03a-6fc4-5a48-af99-5b2d4df46dcd', '49b4e521-cb54-5b99-9084-0784f05a7d7a', 'b8ce48a2-598a-578e-a7c1-bc70f9e29349', '807050dd-3aeb-50f7-887b-69004e1310c7', 'de0d2711-36e7-5a78-af20-c016a98a4c46')

373.349ms => https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/15503/commands/53734

State Transitions 
SELECT
    "vulnerability_state_transitions".*
FROM
    "vulnerability_state_transitions"
    INNER JOIN "vulnerabilities" ON "vulnerability_state_transitions"."vulnerability_id" = "vulnerabilities"."id"
WHERE
    "vulnerabilities"."id" = 543

0.741 ms => https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/15503/commands/53735

Issue Links 
SELECT
    "vulnerability_issue_links".*
FROM
    "vulnerability_issue_links"
    INNER JOIN "vulnerabilities" ON "vulnerability_issue_links"."vulnerability_id" = "vulnerabilities"."id"
WHERE
    "vulnerabilities"."id" = 543

10.650 ms => https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/15503/commands/53736

Merge Request Links 
SELECT
    "vulnerability_merge_request_links".*
FROM
    "vulnerability_merge_request_links"
    INNER JOIN "vulnerabilities" ON "vulnerability_merge_request_links"."vulnerability_id" = "vulnerabilities"."id"
WHERE
    "vulnerabilities"."id" = 543

4.369 ms => https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/15503/commands/53737

How to set up and validate locally

  • Test that the api provided at https://localhost/api/v4/projects/<id>/vulnerability_findings does not produce an N+1 log.
  • Test that the pipeline security tab does not produce a N+1 log.
  • Test that the vulnerability report does not produce a N+1 log.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #390818 (closed)

Edited by Gregory Havenga

Merge request reports