Skip to content

Utilize de-normalized attributes to improve the group level report page

Mehmet Emin INAC requested to merge minac_438288_utilize_denormalized_attributes_to_improve_the_group-level_queries into master

What does this MR do and why?

This MR introduces a feature flag and changes the queries to utilize the new de-normalized columns.

Database review

Old queries
Query to load the first page 
SELECT
    "vulnerability_reads".*
FROM
    "vulnerability_reads"
    INNER JOIN "projects" ON "projects"."id" = "vulnerability_reads"."project_id"
WHERE
    "vulnerability_reads"."namespace_id" IN (
        SELECT
            "namespaces"."id"
        FROM
            "namespaces"
        WHERE
            "namespaces"."type" = 'Group'
            AND (traversal_ids @> ('{9970}')))
    AND "projects"."archived" = FALSE
    AND "vulnerability_reads"."report_type" IN (6, 2, 5, 3, 1, 0, 4, 99)
    AND "vulnerability_reads"."state" IN (1, 4)
    AND "vulnerability_reads"."resolved_on_default_branch" = FALSE
ORDER BY
    "vulnerability_reads"."severity" DESC,
    "vulnerability_reads"."vulnerability_id" DESC
LIMIT 21

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/27594/commands/86064

Query to load the second page 
SELECT
    "vulnerability_reads".*
FROM
    "vulnerability_reads"
    INNER JOIN "projects" ON "projects"."id" = "vulnerability_reads"."project_id"
WHERE
    "vulnerability_reads"."namespace_id" IN (
        SELECT
            "namespaces"."id"
        FROM
            "namespaces"
        WHERE
            "namespaces"."type" = 'Group'
            AND (traversal_ids @> ('{9970}')))
    AND "projects"."archived" = FALSE
    AND "vulnerability_reads"."report_type" IN (6, 2, 5, 3, 1, 0, 4, 99)
    AND "vulnerability_reads"."state" IN (1, 4)
    AND "vulnerability_reads"."resolved_on_default_branch" = FALSE
    AND (("vulnerability_reads"."severity",
            "vulnerability_reads"."vulnerability_id") < (7,
            109241050))
ORDER BY
    "vulnerability_reads"."severity" DESC,
    "vulnerability_reads"."vulnerability_id" DESC
LIMIT 21

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/27594/commands/86065

New queries
Query to load the first page 
SELECT
    "vulnerability_reads".*
FROM
    "vulnerability_reads"
WHERE
    "vulnerability_reads"."vulnerability_id" IN (
        SELECT
            "vulnerability_reads"."vulnerability_id"
        FROM
            unnest('{6,2,5,3,1,0,4,99}'::smallint[]) AS "report_types" ("report_type"),
            unnest('{1,2,4,5,6,7}'::smallint[]) AS "severities" ("severity"),
            unnest('{1,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/27577/commands/85914

Query to load the second page 
SELECT
    "vulnerability_reads".*
FROM
    "vulnerability_reads"
WHERE
    "vulnerability_reads"."vulnerability_id" IN (
        SELECT
            "vulnerability_reads"."vulnerability_id"
        FROM
            unnest('{6,2,5,3,1,0,4,99}'::smallint[]) AS "report_types" ("report_type"),
            unnest('{1,2,4,5,6,7}'::smallint[]) AS "severities" ("severity"),
            unnest('{1,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"."severity",
                            "vulnerability_reads"."traversal_ids",
                            "vulnerability_reads"."vulnerability_id") < (7,
                            '{9970}',
                            109241050))
                    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/27577/commands/85916

Related to Change `vulnerability_reads` query to utilize d... (#438288)

Edited by Mehmet Emin INAC

Merge request reports