Improve MergeRequests#discussions query performance
## Problem
While !221236 and !221643 fixed the slow `related_notes` query in the merge request show action, similar performance issue still affects the **async discussions loading** after merge request gets loaded, causing query timeouts.
### Root Cause
The `Projects::MergeRequestsController#discussions` endpoint uses `discussion_notes` (aliased to `related_notes` for MRs), which performs a UNION ALL query including:
1. Notes directly on the merge request
2. **Commit notes for up to 100 commits**
<details>
<summary>Failed query sample</summary>
```sql
/*application:web,correlation_id:9c85f3019541f0d2-ATL,endpoint_id:Projects::MergeRequestsController#discussions,db_config_database:gitlabhq_production,db_config_name:main_replica*/
SELECT
"table_name",
"notes"."id",
"notes"."created_at",
"ids"
FROM (
-- Notes grouped by discussion_id
(
SELECT
$1 AS table_name,
MIN(id) AS id,
MIN(created_at) AS created_at,
ARRAY_AGG(id) AS ids
FROM (
-- Notes by noteable
(
SELECT "notes".*
FROM "notes"
WHERE "notes"."noteable_id" = $2
AND "notes"."noteable_type" = $3
)
UNION ALL
-- System notes by commit
(
SELECT "notes".*
FROM "notes"
WHERE "notes"."system" = $4
AND "notes"."project_id" IN ($5, $6)
AND "notes"."noteable_type" = $7
AND "notes"."commit_id" IN (
$8, $9, $10, $11, $12, $13, $14, $15, $16, $17,
$18, $19, $20, $21, $22, $23, $24, $25, $26, $27,
$28, $29, $30, $31, $32, $33, $34, $35, $36, $37,
$38, $39, $40, $41, $42, $43, $44, $45, $46, $47,
$48, $49, $50, $51, $52, $53, $54, $55, $56, $57,
$58, $59, $60, $61, $62, $63, $64, $65, $66, $67,
$68, $69, $70, $71, $72, $73, $74, $75, $76, $77,
$78, $79, $80, $81, $82, $83, $84, $85, $86, $87,
$88, $89, $90, $91, $92, $93, $94, $95, $96, $97,
$98, $99, $100, $101, $102, $103, $104, $105, $106, $107
)
)
) notes
GROUP BY "notes"."discussion_id"
)
UNION ALL
-- Resource label events
(
SELECT
$108 AS table_name,
MIN(id) AS id,
"resource_label_events"."created_at",
ARRAY_AGG(id) AS ids
FROM "resource_label_events"
WHERE "resource_label_events"."merge_request_id" = $109
GROUP BY
"resource_label_events"."created_at",
"resource_label_events"."user_id"
)
UNION ALL
-- Resource state events
(
SELECT
$110 AS table_name,
"resource_state_events"."id",
"resource_state_events"."created_at",
ARRAY_FILL(id, ARRAY[$111]) AS ids
FROM "resource_state_events"
WHERE "resource_state_events"."merge_request_id" = $112
)
UNION ALL
-- Resource milestone events
(
SELECT
$113 AS table_name,
"resource_milestone_events"."id",
"resource_milestone_events"."created_at",
ARRAY_FILL(id, ARRAY[$114]) AS ids
FROM "resource_milestone_events"
WHERE "resource_milestone_events"."merge_request_id" = $115
)
UNION ALL
-- Resource iteration events
(
SELECT
$116 AS table_name,
"resource_iteration_events"."id",
"resource_iteration_events"."created_at",
ARRAY_FILL(id, ARRAY[$117]) AS ids
FROM "resource_iteration_events"
WHERE "resource_iteration_events"."merge_request_id" = $118
)
) notes
ORDER BY
"notes"."created_at" ASC,
"notes"."id" ASC
LIMIT $119
```
</details>
See https://log.gprd.gitlab.net/app/r/s/sFDMe for more information.
### Possible solutions
1. Add index similar to https://gitlab.com/gitlab-org/gitlab/-/merge_requests/219465. We avoided adding index due to the index count limit, but perhaps we should still reconsider that if there are no other way to improve it.
2. Reduce the number of commits down from 100 down to say 20?. It means we may miss some notes, but perhaps that's reasonable in most cases?
3. Find another way to improve the page load performance
issue