Query Performance Investigation - Query ID 2298083782068675000 (issues and projects tables)

Description

This query is frequently reported as top 15 in total time taken during the monitoring period. The queries were taken from this report https://gitlab.com/gitlab-com/gl-infra/infrastructure/-/snippets/2090600#postgres-checkup_K003

SELECT COUNT(*)
FROM "issues" INNER JOIN "projects" ON "projects"."id" = "issues"."project_id" LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (      issues.confidential IS NOT TRUE OR (issues.confidential = $1 AND (issues.author_id = $2 OR EXISTS (SELECT $3
    FROM issue_assignees
    WHERE user_id = $4 AND issue_id = issues.id) OR EXISTS (SELECT $5
    FROM "project_authorizations"
    WHERE "project_authorizations"."user_id" = $6 
        AND (project_authorizations.project_id = issues.project_id) 
        AND (project_authorizations.access_level >= $7))))) 
        AND (EXISTS (SELECT $8
   
            FROM "project_authorizations"
            WHERE "project_authorizations"."user_id" = $9 
            AND (project_authorizations.project_id = projects.id) 
            AND (project_authorizations.access_level >= $10)) 
            OR projects.visibility_level IN ($11,$12)) 
            AND ("project_features"."issues_access_level" I
            S NULL OR "project_features"."issues_access_level" 
            IN ($13,$14) OR ("project_features"."issues_access_level

Details https://log.gprd.gitlab.net/goto/a76f705f446b62403294e02a07442163

Row 7 in this report https://gitlab.com/gitlab-com/gl-infra/infrastructure/-/snippets/2090600#k003-top-15-queries-by-total_time

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
  • 17,642
  • 9.24/sec
  • 1.00/call

Release Notes