Skip to content

Preload `vulnerability_reads` for `vulnerability` records

Mehmet Emin INAC requested to merge minac_resolve_n+1_query_issue_for_group-level_vulnerability_report_exports into master

What does this MR do and why?

There was an N+1 query issue for dismissed vulnerabilities on export functionality. This commit fixes it. Also increases the batch size to 1K as it makes processing the records 1.5 times faster.

Old query with 500 batch size 
SELECT
    "vulnerability_reads".*
FROM
    "vulnerability_reads"
WHERE
    "vulnerability_reads"."traversal_ids" >= '{9970}'
    AND "vulnerability_reads"."traversal_ids" < '{9971}'
    AND "vulnerability_reads"."archived" = FALSE
ORDER BY
    "vulnerability_reads"."traversal_ids" ASC,
    "vulnerability_reads"."vulnerability_id" ASC
LIMIT 500

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/27483/commands/85634

New query with 1K batch size 
SELECT
    "vulnerability_reads".*
FROM
    "vulnerability_reads"
WHERE
    "vulnerability_reads"."traversal_ids" >= '{9970}'
    AND "vulnerability_reads"."traversal_ids" < '{9971}'
    AND "vulnerability_reads"."archived" = FALSE
ORDER BY
    "vulnerability_reads"."traversal_ids" ASC,
    "vulnerability_reads"."vulnerability_id" ASC
LIMIT 1000

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/27483/commands/85633

Note: Of course, the new query is slower than the first one but it halves the amount of network requests yet not slower than 2 times.

Related to Group Vulnerability Report CSV Export fails to ... (#440163).

Edited by Mehmet Emin INAC

Merge request reports