Select only the attributes required by the Keyset iterator
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 - closed).