Skip to content

Resolve "query groupVulnerabilities timeout when "All statuses" filter applied against large set of vulnerabilities"

Gregory Havenga requested to merge 381245-query-groupvulnerabilities-timeout-when-all-statuses-filter-applied-against-large-set-of into master

What does this MR do and why?

Implement a compound index on vulnerability_reads for namespace_id, report_type, severity, vulnerability_id to optimise for vulnerability queries that don't filter the status, as this misses the UnnestedInFilter optimisation.

Database.ai test

Index Creation Took 6 Minutes 
exec CREATE INDEX CONCURRENTLY index_vulnerability_reads_on_namespace_type_severity_id ON vulnerability_reads (namespace_id) INCLUDE (report_type, severity, vulnerability_id);
Session: webui-i13066
The query has been executed. Duration: 6.051 min
 Completed
Intended Query for Optimisation 
explain SELECT
    "vulnerability_reads".*
FROM
    "vulnerability_reads"
WHERE
    "vulnerability_reads"."vulnerability_id" IN (
        SELECT
            "vulnerability_reads"."vulnerability_id"
        FROM
            unnest(ARRAY (
                    SELECT
                        "namespaces"."id"
                    FROM "namespaces"
                    WHERE
                        "namespaces"."type" = 'Group'
                        AND (traversal_ids @> ('{9970}')))::bigint[]) AS "namespace_ids" ("namespace_id"),
            unnest('{6,2,5,3,1,0,4,99}'::smallint[]) AS "report_types" ("report_type"),
            LATERAL (
                SELECT
                    "vulnerability_reads"."namespace_id",
                    "vulnerability_reads"."report_type",
                    "vulnerability_reads"."severity",
                    "vulnerability_reads"."vulnerability_id"
                FROM
                    "vulnerability_reads"
                WHERE (vulnerability_reads."namespace_id" = "namespace_ids"."namespace_id")
                AND (vulnerability_reads."report_type" = "report_types"."report_type")
            ORDER BY
                "vulnerability_reads"."severity" DESC,
                "vulnerability_reads"."vulnerability_id" DESC
            LIMIT 21) AS vulnerability_reads
    ORDER BY
        "vulnerability_reads"."severity" DESC,
        "vulnerability_reads"."vulnerability_id" DESC
    LIMIT 21)
ORDER BY
    "vulnerability_reads"."severity" DESC,
    "vulnerability_reads"."vulnerability_id" DESC
LIMIT 21

Note discussion at !103033 (comment 1162217145) . The query changes when you add the index because of UnnestedInFilters optimizing the query only if the index exists.

Old Query: ~1GB worth of buffers, https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/13150/commands/46095

New Query (without index): ~758 MiB worth of buffers, https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/13150/commands/46086

New Query (with index): ~120 MiB worth of buffers, https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/13150/commands/46088

MR acceptance checklist

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

Related to #381245 (closed)

Edited by Dylan Griffith

Merge request reports