owasp top 10 index on vulnerability reads to support group level report page

The owasp top 10 grouping feature is currently behind the FF vulnerability_owasp_top_10_group and for large groups we still do not have the index created with traversal_id.

We can observe below GraphQL query timing out.

query {
  group(fullPath: "gitlab-org") {
    vulnerabilitySeveritiesCount(owaspTopTen: [A1_2017], reportType: [SAST], state: [DETECTED, CONFIRMED], severity: [LOW, INFO, HIGH, CRITICAL, MEDIUM]) {
      critical
      high
      info
      low
      medium
      unknown

    }
  }
}

We also do not have index yet for owasp top 10 group level filtering, we have an index only on the project level.

---

The standard queries from the OWASP top 10 grouping features are

Vulnerability Severities Count:

SELECT
  COUNT(*) AS "count_all",
  "vulnerability_reads"."severity" AS "vulnerability_reads_severity"
FROM
  (
    (
      SELECT
        "vulnerability_reads"."id",
        "vulnerability_reads"."severity"
      FROM
        "vulnerability_reads"
      WHERE
        "vulnerability_reads"."traversal_ids" >= ?
        AND "vulnerability_reads"."traversal_ids" < ?
        AND "vulnerability_reads"."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"."owasp_top_10" = 1
        AND "vulnerability_reads"."severity" = 1
      LIMIT
        1001
    )
    UNION
    (
      SELECT
        "vulnerability_reads"."id",
        "vulnerability_reads"."severity"
      FROM
        "vulnerability_reads"
      WHERE
        "vulnerability_reads"."traversal_ids" >= '{9970}'
        AND "vulnerability_reads"."traversal_ids" < '{9971}'
        AND "vulnerability_reads"."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"."owasp_top_10" = 1
        AND "vulnerability_reads"."severity" = 2
      LIMIT
        1001
    )
    UNION
    (
      SELECT
        "vulnerability_reads"."id",
        "vulnerability_reads"."severity"
      FROM
        "vulnerability_reads"
      WHERE
        "vulnerability_reads"."traversal_ids" >= '{9970}'
        AND "vulnerability_reads"."traversal_ids" < '{9971}'
        AND "vulnerability_reads"."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"."owasp_top_10" = 1
        AND "vulnerability_reads"."severity" = 4
      LIMIT
        1001
    )
    UNION
    (
      SELECT
        "vulnerability_reads"."id",
        "vulnerability_reads"."severity"
      FROM
        "vulnerability_reads"
      WHERE
        "vulnerability_reads"."traversal_ids" >= '{9970}'
        AND "vulnerability_reads"."traversal_ids" < '{9971}'
        AND "vulnerability_reads"."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"."owasp_top_10" = 1
        AND "vulnerability_reads"."severity" = 5
      LIMIT
        1001
    )
    UNION
    (
      SELECT
        "vulnerability_reads"."id",
        "vulnerability_reads"."severity"
      FROM
        "vulnerability_reads"
      WHERE
        "vulnerability_reads"."traversal_ids" >= '{9970}'
        AND "vulnerability_reads"."traversal_ids" < '{9971}'
        AND "vulnerability_reads"."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"."owasp_top_10" = 1
        AND "vulnerability_reads"."severity" = 6
      LIMIT
        1001
    )
    UNION
    (
      SELECT
        "vulnerability_reads"."id",
        "vulnerability_reads"."severity"
      FROM
        "vulnerability_reads"
      WHERE
        "vulnerability_reads"."traversal_ids" >= '{9970}'
        AND "vulnerability_reads"."traversal_ids" < '{9971}'
        AND "vulnerability_reads"."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"."owasp_top_10" = 1
        AND "vulnerability_reads"."severity" = 7
      LIMIT
        1001
    )
  ) vulnerability_reads
GROUP BY
  "vulnerability_reads"."severity"
ORDER BY
  "vulnerability_reads"."severity" DESC

Show Vulnerabilities

SELECT
  "vulnerability_reads".*
FROM
  "vulnerability_reads"
WHERE
  "vulnerability_reads"."traversal_ids" >= '{?}'
  AND "vulnerability_reads"."traversal_ids" < '{?}'
  AND "vulnerability_reads"."archived" = FALSE
  AND "vulnerability_reads"."report_type" IN (6, 2, 5, 3, 1, 0, 4, 99)
  AND "vulnerability_reads"."severity" IN (1, 2, 4, 5, 6, 7)
  AND "vulnerability_reads"."state" IN (1, 4)
  AND "vulnerability_reads"."resolved_on_default_branch" = FALSE
  AND "vulnerability_reads"."owasp_top_10" = 5
ORDER BY
  "vulnerability_reads"."severity" DESC,
  "vulnerability_reads"."traversal_ids" DESC,
  "vulnerability_reads"."vulnerability_id" DESC
LIMIT
  101

Note also check the query performance for the condition "vulnerability_reads"."owasp_top_10" is NULL