Skip to content

Add hasRemediations filter

Subashis Chakraborty requested to merge 358638-has-remediations-finder into master

This MR

  • adds has_remediations filter to VulnerabilityReadsFinder
  • adds hasRemediations field and argument to vulnerabilities query
  • adds hasRemediations field and argument to VulnerabilitySeveritiesCount query

Query analysis:

Finder performance with its consumer project vulnerabilities report page for gitlab-org/gitlab: https://gitlab.com/gitlab-org/gitlab/-/security/vulnerability_report

SELECT "vulnerability_reads".* FROM "vulnerability_reads" WHERE "vulnerability_reads"."project_id" = 278964 AND "vulnerability_reads"."report_type" IN (6, 2, 5, 3, 1, 0, 4, 99) AND "vulnerability_reads"."state" IN (4, 1) AND "vulnerability_reads"."has_remediations" = TRUE ORDER BY "vulnerability_reads"."severity" DESC, "vulnerability_reads"."vulnerability_id" DESC LIMIT 21

Cold cache:

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/23011/commands/74101

Time: 540.745 ms
  - planning: 4.114 ms
  - execution: 536.631 ms
    - I/O read: 530.259 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 90 (~720.00 KiB) from the buffer pool
  - reads: 271 (~2.10 MiB) from the OS file cache, including disk I/O
  - dirtied: 8 (~64.00 KiB)
  - writes: 0

Warm cache:

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/23011/commands/74103

Time: 4.651 ms
  - planning: 3.856 ms
  - execution: 0.795 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 361 (~2.80 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Related to #358638 (closed)

Edited by Subashis Chakraborty

Merge request reports