Query Performance Investigation - Query ID 9095629593792855100 (merge_requests and projects tables)
Description
This query is frequently reported as top 10 in total time taken during the monitoring period.
SELECT COUNT(*)
FROM "merge_requests" INNER JOIN "projects" ON "projects"."id" = "merge_requests"."target_project_id" LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE (EXISTS (SELECT $1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = $2 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= $3)) OR projects.visibility_level IN ($4,$5)) AND ("project_features"."merge_requests_access_level" IS NULL OR "project_features"."merge_requests_access_level" IN ($6,$7) OR ("project_features"."merge_requests_access_level" = $8 AND EXISTS (SELECT $9
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = $10 AND (project_authorizations.project_id = projects.id) AND (project_authorizations.access_level >= $11)))) AND ("merge_requests"."state_id" IN ($12)) AND (EXISTS (SELECT $13
FROM "merge_request_assignees"
WHERE "merge_request_assignees"."user_id" IN ($14) AND merge_request_id = merge_requests.id)) AND "projects"."archived" = $15
--application:web,correlation_id:txoCxRFrosa/
https://log.gprd.gitlab.net/goto/3253964f29a6c336410681888d5d4844
Impact
Improvements were made and we are seeing a 25% reduction of these slow queries, see more details #325458 (comment 536792387).
Edited by Chun Du