Change `vulnerability_reads` query to utilize denormalized columns
Why are we doing this work
After populating the data for the recently introduced traversal_ids
and archived
columns, we can adjust our read queries to improve the performance of the group-level vulnerability report page.
In this issue, we will remove the join to the projects
table and use the archived
column instead. Also, we will use the traversal_ids
to reduce the amount of I/O.
Implementation plan
-
database Create the necessary indices -
backend Adjust the queries to remove the projects
table join and usetraversal_ids
instead of usingnamespace_id
. If this is done correctly and the necessary indices introduced, theunnested_in_filters
abstraction will rewrite the query and produce something similar to one below;
Example SQL
SELECT
"vulnerability_reads".*
FROM
"vulnerability_reads"
WHERE
"vulnerability_reads"."vulnerability_id" IN (
SELECT
"vulnerability_reads"."vulnerability_id"
FROM
unnest('{1,4}'::smallint[]) AS "states" ("state"),
unnest('{0,1,2,3,4}'::smallint[]) AS "report_types" ("report_type"),
unnest('{4,5,6,7}'::smallint[]) AS "severities" ("severity"),
LATERAL (
SELECT
"vulnerability_reads"."state",
"vulnerability_reads"."report_type",
"vulnerability_reads"."severity",
"vulnerability_reads"."archived",
"vulnerability_reads"."traversal_ids",
"vulnerability_reads"."vulnerability_id"
FROM
"vulnerability_reads"
WHERE (traversal_ids >= '{9970}'
AND traversal_ids < '{9971}')
AND "vulnerability_reads"."archived" = FALSE
AND (vulnerability_reads."state" = "states"."state")
AND (vulnerability_reads."report_type" = "report_types"."report_type")
AND (vulnerability_reads."severity" = "severities"."severity")
ORDER BY
"vulnerability_reads"."severity" ASC,
"vulnerability_reads"."traversal_ids" ASC,
"vulnerability_reads"."vulnerability_id" ASC
LIMIT 21) AS vulnerability_reads
ORDER BY
"vulnerability_reads"."severity" ASC,
"vulnerability_reads"."traversal_ids" ASC,
"vulnerability_reads"."vulnerability_id" ASC
LIMIT 21)
ORDER BY
"vulnerability_reads"."severity" ASC,
"vulnerability_reads"."traversal_ids" ASC,
"vulnerability_reads"."vulnerability_id" ASC
LIMIT 21
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/24662/commands/80146
Verification steps
Verify that the group-level vulnerability page is loading the vulnerabilities for gitlab-org
group.