Prepare index for optimized searches from the vulnerability_reads table
What does this MR do and why?
We need to be able to find all vulnerabilities for a project that are on the default branch and were detected by a specific scanner.
To accomplish that, this MR introduces a post migration to begin generation of a composite database index on vulnerability_reads(project_id, scanner_id, vulnerability_id)
. Additional context can be found in #382625 (comment 1259314425).
This MR follows the guidance found in Database development guidance.
SELECT "vulnerabilities".*
FROM "vulnerabilities"
INNER JOIN "vulnerability_reads" "vulnerability_read"
ON "vulnerability_read"."vulnerability_id" = "vulnerabilities"."id"
WHERE "vulnerability_read"."project_id" = 1216
AND "vulnerability_read"."scanner_id" = 489;
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/15140/commands/52583
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.
Edited by mo khan