<!--- Please read this! Before opening a new issue, make sure to search for keywords in the issues filtered by the "regression" or "bug" label: - https://gitlab.com/gitlab-org/gitlab/issues?label_name%5B%5D=regression - https://gitlab.com/gitlab-org/gitlab/issues?label_name%5B%5D=bug and verify the issue you're about to submit isn't a duplicate. ---> ### Summary The group-level and project-level dashboards are both failing with an internal server error.  The instance query works:  ### Steps to reproduce Use graphql explorer to run the queries above. ### Example Project `gitlab-org/gitlab` <!-- If possible, please create an example project here on GitLab.com that exhibits the problematic behavior, and link to it here in the bug report. If you are using an older version of GitLab, this will also determine whether the bug is fixed in a more recent version. --> ### What is the current *bug* behavior? 500 error <!-- Describe what actually happens. --> ### What is the expected *correct* behavior? Response with data. <!-- Describe what you should see instead. --> ### Relevant logs and/or screenshots <!-- Paste any relevant logs - please use code blocks (```) to format console output, logs, and code as it's tough to read otherwise. --> ### Output of checks <!-- If you are reporting a bug on GitLab.com, write: This bug happens on GitLab.com --> #### Results of GitLab environment info <!-- Input any relevant GitLab environment information if needed. --> <details> <summary>Expand for output related to GitLab environment info</summary> <pre> (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`) </pre> </details> #### Results of GitLab application Check <!-- Input any relevant GitLab application check information if needed. --> <details> <summary>Expand for output related to the GitLab application check</summary> <pre> (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) </pre> </details> ### Possible fixes > [5:21 PM] I think the problem is, it’s trying to load all the vulnerability_occurrences records for all the scanners into the memory and then doing the distinct on them. Since we have lots of records in the vulnerability_occurrences table, it’s causing the read amplification. > > [5:21 PM] The original query is running around ~350ms on postgres.ai but the one with the lateral join is running around ~13ms. ```sql SELECT DISTINCT ON ("vulnerability_scanners"."external_id", "report_types"."report_type") "vulnerability_scanners".*, report_types.report_type FROM "vulnerability_scanners" INNER JOIN LATERAL (SELECT report_type FROM vulnerability_occurrences WHERE vulnerability_occurrences.scanner_id = vulnerability_scanners.id LIMIT 1) AS report_types ON TRUE WHERE "vulnerability_scanners"."project_id" IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."namespace_id" IN (WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 9970) UNION (SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."type" = 'Group' AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "id" FROM "base_and_descendants" AS "namespaces") AND "projects"."archived" = FALSE AND "projects"."pending_delete" = FALSE) ORDER BY "vulnerability_scanners"."external_id" ASC; ``` <!-- If you can, link to the line of code that might be responsible for the problem. --> /cc @mparuszewski @dftian @matt_wilson @lkerr