Query Performance Investigation - Query ID 4130712539834136000 (issues table)

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(*) AS count_all, "issues"."state_id" AS issues_state_id 
FROM "issues" 
WHERE "issues"."project_id" = $1 
AND "issues"."issue_type" IN ($2, $3) 
GROUP BY "issues"."state_id" 

This query is mentioned here !21554 (comment 293993859)

More info https://log.gprd.gitlab.net/goto/ae58b343a976c1aef126aaf65631492e

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

The related query id: 772977607269957400 , mentioned in the spreadsheet on 13.place. In this query the additional filter is confidentiality - for case users can't see the confidential issues.

SELECT COUNT(*) AS count_all, "issues"."state_id" 
AS issues_state_id FROM "issues" 
WHERE (issues.confidential IS NOT 
TRUE) AND "issues"."project_id" 
= $1 AND "issues"."issue_type" 
IN ($2, $3) GROUP BY "issues"."state_id"

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)
  • 7,392 total
  • 3.87/sec
• Database time relative to total database time
  • 262,741.37 ms
  • 137.582 ms/sec
  • 35.544 ms/call
  • 2.62%

Assigning to ~"group::release" based on the mr !21554 (comment 293993859)