Improve MergeRequests#discussions query performance

Problem

While !221236 (merged) and !221643 (merged) 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

Failed query sample

/*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

See https://log.gprd.gitlab.net/app/r/s/sFDMe for more information.

Possible solutions

1. Add index similar to !219465 (closed). 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