Skip to content

Use `unnested_in_filters` for Group-level vulnerability reads finder

What does this MR do and why?

This MR extends the UnnestedInFilters to rewrite the queries which contain subqueries to speed up querying the vulnerability_reads table by a group and its subgroups without loading the group IDs into memory.

This MR also changes the query generated by the Group#vulnerability_reads method to utilize namespace_id instead of using the project_id. This forces us to remove the logic of checking archived and pending_delete projects to improve the performance which was already communicated with the group PM(Thread: !93373 (comment 1044936494)).

Related to [Proposal] Add `namespace_id` to `vulnerability... (#367300 - closed).

The below query analysis is based on !93535 (merged) which adds an index. Without this index the query rewriter will not rewrite the query so this is not dependent on that MR but we won't see performance improvements without that index.

Database review

Old query
SELECT
    "vulnerability_reads".*
FROM
    "vulnerability_reads"
WHERE
    "vulnerability_reads"."project_id" IN (
        SELECT
            "projects"."id"
        FROM
            "projects"
        WHERE
            "projects"."namespace_id" IN (
                SELECT
                    "namespaces"."id"
                FROM
                    "namespaces"
                WHERE
                    "namespaces"."type" = 'Group'
                    AND (traversal_ids @> ('{9970}')))
                AND "projects"."archived" = FALSE
                AND "projects"."pending_delete" = FALSE)
        AND "vulnerability_reads"."severity" = 5
        AND "vulnerability_reads"."state" IN (1, 4)
        AND "vulnerability_reads"."report_type" IN (1, 2, 3)
ORDER BY
    "vulnerability_reads"."severity" DESC,
    "vulnerability_reads"."vulnerability_id" DESC
LIMIT 20

Around 7 secs: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/11256/commands/40293

New query
SELECT
    "vulnerability_reads".*
FROM
    unnest(ARRAY (
            SELECT
                "namespaces"."id"
            FROM "namespaces"
            WHERE
                "namespaces"."type" = 'Group'
                AND (traversal_ids @> ('{9970}')))::bigint[]) AS "namespace_ids" ("namespace_id"),
    unnest('{1,4}'::smallint[]) AS "states" ("state"),
    unnest('{1,2,3}'::smallint[]) AS "report_types" ("report_type"),
    LATERAL (
        SELECT
            "vulnerability_reads".*
        FROM
            "vulnerability_reads"
        WHERE
            "vulnerability_reads"."severity" = 5
            AND (vulnerability_reads."namespace_id" = "namespace_ids"."namespace_id")
            AND (vulnerability_reads."state" = "states"."state")
            AND (vulnerability_reads."report_type" = "report_types"."report_type")
        ORDER BY
            "vulnerability_reads"."severity" DESC,
            "vulnerability_reads"."vulnerability_id" DESC
        LIMIT 20) AS vulnerability_reads
ORDER BY
    "vulnerability_reads"."severity" DESC,
    "vulnerability_reads"."vulnerability_id" DESC
LIMIT 20

Around 13 ms: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/11256/commands/40299

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Mehmet Emin INAC

Merge request reports

Loading