Improve the performance of `Security::FindingsFinder`
Why are we doing this work
Query optimization: This comes during auditing the performance for security tab in pipeline as part of this
The query we run here from https://gitlab.com/gitlab-org/gitlab/-/blob/master/ee/app/finders/security/findings_finder.rb#L43 ( "ee/app/finders/security/findings_finder.rb:43:in `findings'")
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_sourcing_milestone_id" AS t0_r7,
"vulnerabilities"."due_date_sourcing_milestone_id" AS t0_r8,
"vulnerabilities"."last_edited_at" AS t0_r9,
"vulnerabilities"."created_at" AS t0_r10,
"vulnerabilities"."updated_at" AS t0_r11,
"vulnerabilities"."start_date" AS t0_r12,
"vulnerabilities"."due_date" AS t0_r13,
"vulnerabilities"."state" AS t0_r14,
"vulnerabilities"."severity" AS t0_r15,
"vulnerabilities"."confidence" AS t0_r16,
"vulnerabilities"."severity_overridden" AS t0_r17,
"vulnerabilities"."confidence_overridden" AS t0_r18,
"vulnerabilities"."title" AS t0_r19,
"vulnerabilities"."title_html" AS t0_r20,
"vulnerabilities"."description" AS t0_r21,
"vulnerabilities"."description_html" AS t0_r22,
"vulnerabilities"."report_type" AS t0_r23,
"vulnerabilities"."cached_markdown_version" AS t0_r24,
"vulnerabilities"."resolved_by_id" AS t0_r25,
"vulnerabilities"."resolved_at" 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,
"vulnerability_occurrences"."id" AS t1_r0,
"vulnerability_occurrences"."created_at" AS t1_r1,
"vulnerability_occurrences"."updated_at" AS t1_r2,
"vulnerability_occurrences"."severity" AS t1_r3,
"vulnerability_occurrences"."confidence" AS t1_r4,
"vulnerability_occurrences"."report_type" AS t1_r5,
"vulnerability_occurrences"."project_id" AS t1_r6,
"vulnerability_occurrences"."scanner_id" AS t1_r7,
"vulnerability_occurrences"."primary_identifier_id" AS t1_r8,
"vulnerability_occurrences"."project_fingerprint" AS t1_r9,
"vulnerability_occurrences"."location_fingerprint" AS t1_r10,
"vulnerability_occurrences"."uuid" AS t1_r11,
"vulnerability_occurrences"."name" AS t1_r12,
"vulnerability_occurrences"."metadata_version" AS t1_r13,
"vulnerability_occurrences"."raw_metadata" AS t1_r14,
"vulnerability_occurrences"."vulnerability_id" AS t1_r15,
"vulnerability_occurrences"."details" AS t1_r16,
"vulnerability_occurrences"."description" AS t1_r17,
"vulnerability_occurrences"."message" AS t1_r18,
"vulnerability_occurrences"."solution" AS t1_r19,
"vulnerability_occurrences"."cve" AS t1_r20,
"vulnerability_occurrences"."location" AS t1_r21
FROM "vulnerabilities"
INNER JOIN "vulnerability_occurrences" ON "vulnerability_occurrences"."vulnerability_id" = "vulnerabilities"."id"
WHERE "vulnerabilities"."project_id" = $1
AND "vulnerabilities"."report_type" = $2
AND "vulnerability_occurrences"."project_fingerprint" IN ($3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22)
Cold:
Time: 3.672 s
- planning: 5.780 ms
- execution: 3.667 s
- I/O read: 6.918 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 7075 (~55.30 MiB) from the buffer pool
- reads: 6040 (~47.20 MiB) from the OS file cache, including disk I/O
- dirtied: 375 (~2.90 MiB)
- writes: 0
Warm:
Time: 21.628 ms
- planning: 1.352 ms
- execution: 20.276 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 13049 (~101.90 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
We may need to think about more indexing or specialized index so that it better fits with our query.
Edited by Subashis Chakraborty