Improve performance of API query to "get count of recently created merge requests for group"

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

• Close this issue

Problem

The query to get count of recently created merge requests for group is non-performant (too slow).

Proposal

• Implement 24-hour caching per #325580 (comment 536912237); see !57341 (merged)
• Fix 24-hour caching per #325580 (comment 549435349)
• Create a follow-up issue for showing "last updated at": #325870 (closed)

SQL Statement

SQL statement

```sql 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... ```

(Full SQL unavailable as I do not have blessed access to postgres.ai)

Data from Elastic

https://log.gprd.gitlab.net/app/discover#/?_g=h@82bf1c3&_a=h@4931bbc

Requested Data points

Kibana