Query Performance Investigation - [project approval usage data]

Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.

• Close this issue

Description

This is a follow up to !81823 (merged). 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

• 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
• If this requires immediate attention cc @gitlab-org/database-team and reach out in the #g_database slack channel

SQL Statement

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

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