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

Summary

Following the work done in #377484 (closed) to improve the group vulnerabilities query performance it has been discovered that while the query now performs well (and the vulnerability dashboard now loads without error) when one or more Status filters are applied, a query timeout error still occurs when All statuses is selected as a filter.

This has been reported by a customer ZD internal link and is easily reproducible against their project in gitlab.com.

It also occurs for the main GitLab project at https://gitlab.com/groups/gitlab-org.

A workaround is to select all 4 of the available Status values from the filter dropdown, as presumably this returns the same result set as All statuses.

GraphQL query used: ee/app/assets/javascripts/security_dashboard/graphql/queries/group_vulnerabilities.query.graphql

(Please note the following query has been obtained from a test instance, not gitlab.com).

Resulting SQL

/*application:web,correlation_id:01GGVQVTRDJAGGY6WXWRFFFZ4Z,endpoint_id:GraphqlController#execute,db_config_name:main*/ 

SELECT "vulnerability_reads".* 
  FROM "vulnerability_reads" 
 WHERE "vulnerability_reads"."namespace_id" IN 
  (SELECT "namespaces"."id" 
     FROM "namespaces"  
    WHERE "namespaces"."type" = $1 
      AND (traversal_ids @> ($2)))
   AND "vulnerability_reads"."report_type" IN ($3) 
 ORDER BY "vulnerability_reads"."severity" DESC, "vulnerability_reads"."vulnerability_id" DESC LIMIT $5

Steps to reproduce

For a group with a very large number vulnerability_reads rows associated with it, go to the group-level Vulnerability Report and set the Status filter to All statuses.

The total counts of each severity will be displayed, but no vulnerabilities will be listed below them, and a Error fetching the vulnerability list. Please check your network connection and try again. error will be displayed at the top of the screen.

Example Project

This issue is reproducible at https://gitlab.com/groups/gitlab-org/-/security/vulnerabilities/?state=all.

What is the current bug behavior?

What is the expected correct behavior?

Relevant logs and/or screenshots

Output of checks

This bug happens on GitLab.com /label reproduced on GitLab.com

Results of GitLab environment info

Expand for output related to GitLab environment info

(For installations with omnibus-gitlab package run and paste the output of:
`sudo gitlab-rake gitlab:env:info`)

(For installations from source run and paste the output of:
`sudo -u git -H bundle exec rake gitlab:env:info RAILS_ENV=production`)

Results of GitLab application Check

Expand for output related to the GitLab application check

(For installations with omnibus-gitlab package run and paste the output of:
sudo gitlab-rake gitlab:check SANITIZE=true)
(For installations from source run and paste the output of:
sudo -u git -H bundle exec rake gitlab:check RAILS_ENV=production SANITIZE=true)
(we will only investigate if the tests are passing)

Possible fixes

• Implement a compound index on namespace_id, report_type, severity, and vulnerability_id for the vulnerability_reads table