Skip to content

Select only the attributes required by the Keyset iterator

Mehmet Emin INAC requested to merge use_reselect_to_utilize_index_only_scan_for_vulnerability_export_functionality into master

What does this MR do and why?

Selecting only the traversal_ids and vulnerability_id columns while iterating over vulnerability_reads records will help DB to utilize an index-only scan which is way faster than reading the records from the heap.

Old Query 
SELECT
    "vulnerability_reads".*
FROM
    "vulnerability_reads"
WHERE
    "vulnerability_reads"."traversal_ids" >= '{9970}'
    AND "vulnerability_reads"."traversal_ids" < '{9971}'
    AND "vulnerability_reads"."archived" = FALSE
    AND (("vulnerability_reads"."traversal_ids",
            "vulnerability_reads"."vulnerability_id") > ('{9970}',
            6715941))
ORDER BY
    "vulnerability_reads"."traversal_ids" ASC,
    "vulnerability_reads"."vulnerability_id" ASC
LIMIT 1000

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/27543/commands/85774

New Query 
SELECT
    "vulnerability_reads"."traversal_ids",
    "vulnerability_reads"."vulnerability_id"
FROM
    "vulnerability_reads"
WHERE
    "vulnerability_reads"."traversal_ids" >= '{9970}'
    AND "vulnerability_reads"."traversal_ids" < '{9971}'
    AND "vulnerability_reads"."archived" = FALSE
    AND (("vulnerability_reads"."traversal_ids",
            "vulnerability_reads"."vulnerability_id") > ('{9970}',
            6715941))
ORDER BY
    "vulnerability_reads"."traversal_ids" ASC,
    "vulnerability_reads"."vulnerability_id" ASC
LIMIT 1000

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/27543/commands/85775

This will especially improve the cold-cache case.

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

Merge request reports