Skip to content

Always use indexed attributes in group-level vulnerability reads queries

Mehmet Emin INAC requested to merge minac_always_use_state_and_report_type_in_group_level_vulnerability_reads_queries into master

What does this MR do and why?

We need to use report_types and states always in our queries to enable unnested filters.

An example query which uses all states and report types 
SELECT
    "vulnerability_reads".*
FROM
    "vulnerability_reads"
WHERE
    "vulnerability_reads"."vulnerability_id" IN (
        SELECT
            "vulnerability_reads"."vulnerability_id"
        FROM
            unnest('{0,1,2,3,4,5,6,7,90}'::smallint[]) AS "report_types" ("report_type"),
            unnest('{1,2,4,5,6,7}'::smallint[]) AS "severities" ("severity"),
            unnest('{1,2,3,4}'::smallint[]) AS "states" ("state"),
            LATERAL (
                SELECT
                    "vulnerability_reads"."archived",
                    "vulnerability_reads"."report_type",
                    "vulnerability_reads"."severity",
                    "vulnerability_reads"."state",
                    "vulnerability_reads"."resolved_on_default_branch",
                    "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"."resolved_on_default_branch" = FALSE
                    AND (vulnerability_reads."report_type" = "report_types"."report_type")
                    AND (vulnerability_reads."severity" = "severities"."severity")
                    AND (vulnerability_reads."state" = "states"."state")
                ORDER BY
                    "vulnerability_reads"."severity" DESC,
                    "vulnerability_reads"."traversal_ids" DESC,
                    "vulnerability_reads"."vulnerability_id" DESC
                LIMIT 21) AS vulnerability_reads
        ORDER BY
            "vulnerability_reads"."severity" DESC,
            "vulnerability_reads"."traversal_ids" DESC,
            "vulnerability_reads"."vulnerability_id" DESC
        LIMIT 21)
ORDER BY
    "vulnerability_reads"."severity" DESC,
    "vulnerability_reads"."traversal_ids" DESC,
    "vulnerability_reads"."vulnerability_id" DESC
LIMIT 21

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/27772/commands/86622

Edited by Mehmet Emin INAC

Merge request reports