<!--IssueSummary start--> <details> <summary> Everyone can contribute. [Help move this issue forward](https://handbook.gitlab.com/handbook/marketing/developer-relations/contributor-success/community-contributors-workflows/#contributor-links) while earning points, leveling up and collecting rewards. </summary> - [Close this issue](https://contributors.gitlab.com/manage-issue?action=close&projectId=278964&issueIid=355541) </details> <!--IssueSummary end--> ## Description This is a follow up to https://gitlab.com/gitlab-org/gitlab/-/merge_requests/81823. In that MR, it was noticed that a few existing queries regarding project approval usage were underperforming. The cold cache query is `6s` and the warm cache is `600ms`. ### Steps - [x] Rename the issue to - `Query Performance Investigation - [Query Snippet | Table info]` - For example - `Query Performance Investigation - SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = $1 LIMIT $2` - [ ] Provide information in the Requested Data Points table - [ ] Provide [priority and severity labels](https://about.gitlab.com/handbook/engineering/quality/issue-triage/#availability) - [ ] If this requires immediate attention cc `@gitlab-org/database-team` and reach out in the #g_database slack channel ### SQL Statement ```sql SELECT COUNT(*) FROM ( SELECT COUNT("approval_project_rules"."id") FROM "approval_project_rules" INNER JOIN approval_project_rules_users ON approval_project_rules_users.approval_project_rule_id = approval_project_rules.id WHERE "approval_project_rules"."rule_type" = 0 GROUP BY "approval_project_rules"."id" HAVING (COUNT(approval_project_rules_users) > approvals_required)) subquery ``` - Cold cache - `6s` https://console.postgres.ai/shared/9cef3076-30ae-4349-b991-71ed70942ea0 - Warm cache - `600ms` ### Data from Elastic Instructions on collecting data from [PostgreSQL slow logs stored in Elasticsearch](https://gitlab.com/gitlab-com/runbooks/-/merge_requests/3361/diffs) ### Requested Data points Please provide as many of these fields as possible when submitting a query performance report. - Queries per second (on average or peak) - Number of calls per second and relative to total number of calls - Query timings (on average or peak) - Database time relative to total database time - Source of calls (Sidekiq, WebAPI, etc) - Query ID - Query Plan - Query Example - Total number of calls (relative) - % of Total time <!-- - Example of a postgres checkup report - https://gitlab.com/gitlab-com/gl-infra/infrastructure/-/snippets/2056787 - Epic - Improving the Database resource usage (&365) - https://gitlab.com/groups/gitlab-com/gl-infra/-/epics/365#short-term-query-improvements - Past examples of query performance investigations that have led to this template creation. - Possible Index suggestion or query rewriting (#292454) - https://gitlab.com/gitlab-org/gitlab/-/issues/292454) - High number of Sessions to the database with the value SET parameter (#292022) - https://gitlab.com/gitlab-org/gitlab/-/issues/292022) - Query performance "Select 1" (#220055) - https://gitlab.com/gitlab-org/gitlab/-/issues/220055 - Select statements that are in execution during database CPU utilization peak times - licenses table (#292900) - https://gitlab.com/gitlab-org/gitlab/-/issues/292900 -->