Only include sbom_occurrences with CVS enabled (!131454) · Merge requests · GitLab.org / GitLab

Adam Cohen requested to merge 424629-only-include-sbom_occurrences-with-cvs-enabled into master Sep 11, 2023

What does this MR do and why?

Filter the list of sbom_occurrences to ignore any occurrences where the associated project does not have continuous vulnerability scanning enabled.

This MR is targeting the same branch as Add setting for enabling Continuous Vulnerabili... (!131305 - merged).

Query plans

The query plan is from executing:

batch_num = 1
Sbom::PossiblyAffectedOccurrencesFinder.new(package_name: 'semver', purl_type: 'npm').execute_in_batches(of: 100) do |batch|
  puts "Batch #{batch_num} "
  batch.each_with_index do |sbom_occurrence, idx|
    puts "  Record #{idx+1}"
    possibly_affected_component = Gitlab::VulnerabilityScanning::PossiblyAffectedComponent.from_sbom_occurrence(sbom_occurrence)
    possibly_affected_component.name
    possibly_affected_component.version
    possibly_affected_component.purl_type
    possibly_affected_component.source
    possibly_affected_component.pipeline
    possibly_affected_component.project
  end
  batch_num += 1
  break if batch_num == 3
end

Which introduces this query:

SELECT "sbom_occurrences".*
FROM "sbom_occurrences"
INNER JOIN "projects" ON "projects"."id" = "sbom_occurrences"."project_id"
INNER JOIN "project_security_settings" ON "project_security_settings"."project_id" = "projects"."id"
WHERE "sbom_occurrences"."component_id" = 1629
AND "sbom_occurrences"."id" >= 3439634
AND "sbom_occurrences"."id" < 4872581
AND "sbom_occurrences"."component_version_id" IS NOT NULL
AND (project_security_settings.continuous_vulnerability_scans_enabled = true)

However, since this MR relies on Add setting for enabling Continuous Vulnerabili... (!131305 - merged) which introduces the project_security_settings.continuous_vulnerability_scans_enabled column, I wasn't able to create a database plan in production that uses this new column. Instead, I used the existing project_security_settings.auto_fix_dependency_scanning column.

Click to expand query plan after this change

Same as old query before this change
Same as old query before this change
Same as old query before this change

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/22095/commands/71448

Before:

SELECT "sbom_occurrences".*
FROM "sbom_occurrences"
WHERE "sbom_occurrences"."component_id" = 1629
AND "sbom_occurrences"."id" >= 3439634
AND "sbom_occurrences"."id" < 4872581
AND "sbom_occurrences"."component_version_id" IS NOT NULL

Time: 80.739 ms
  - planning: 1.829 ms
  - execution: 78.910 ms
    - I/O read: 77.674 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 22 (~176.00 KiB) from the buffer pool
  - reads: 46 (~368.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

After:

SELECT "sbom_occurrences".*
FROM "sbom_occurrences"
INNER JOIN "projects" ON "projects"."id" = "sbom_occurrences"."project_id"
INNER JOIN "project_security_settings" ON "project_security_settings"."project_id" = "projects"."id"
WHERE "sbom_occurrences"."component_id" = 1629
AND "sbom_occurrences"."id" >= 3439634
AND "sbom_occurrences"."id" < 4872581
AND "sbom_occurrences"."component_version_id" IS NOT NULL
AND (project_security_settings.auto_fix_dependency_scanning = true)

Time: 328.798 ms
  - planning: 2.564 ms
  - execution: 326.234 ms
    - I/O read: 320.073 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 685 (~5.40 MiB) from the buffer pool
  - reads: 231 (~1.80 MiB) from the OS file cache, including disk I/O
  - dirtied: 6 (~48.00 KiB)
  - writes: 0

Same as old query before this change
Same as old query before this change
Same as old query before this change
Same as old query before this change

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/22095/commands/71448

Click to expand query plans before this change

MR acceptance checklist

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

I have evaluated the MR acceptance checklist for this MR.

Related to #424629 (closed)

Edited Sep 13, 2023 by Adam Cohen

Only include sbom_occurrences with CVS enabled

What does this MR do and why?

Query plans

MR acceptance checklist

Merge request reports