Simplify SQL query used for getting issues/MRs by labels

What does this MR do?

Simplify SQL query used for getting issues/MRs by labels

Are there points in the code the reviewer needs to double check?

• SQL performance

Query generated by Issue.with_label(['bug, critical']).to_sql is following

Test data

SELECT COUNT(*) FROM issues;
 count
-------
 12337

SELECT COUNT(*) FROM label_links;
count
3060

SELECT COUNT(*) FROM labels;
count
1010

Query

SELECT "issues".*
FROM "issues"
WHERE "issues"."deleted_at" IS NULL
  AND (EXISTS
         ( SELECT TRUE
          FROM label_links
          INNER JOIN labels ON labels.id = label_links.label_id
          WHERE labels.title = 'bug'
            AND target_type = 'Issue'
            AND target_id = issues.id ))
  AND (EXISTS
         ( SELECT TRUE
          FROM label_links
          INNER JOIN labels ON labels.id = label_links.label_id
          WHERE labels.title = 'critical'
            AND target_type = 'Issue'
            AND target_id = issues.id ))
ORDER BY "issues"."id" DESC

EXPLAIN ANALYSE

                                                                                  QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=37.44..37.45 rows=1 width=347) (actual time=0.596..0.596 rows=1 loops=1)
   Sort Key: issues.id DESC
   Sort Method: quicksort  Memory: 25kB
   ->  Nested Loop Semi Join  (cost=24.84..37.43 rows=1 width=347) (actual time=0.445..0.565 rows=1 loops=1)
         ->  Nested Loop  (cost=24.29..32.57 rows=3 width=351) (actual time=0.107..0.217 rows=25 loops=1)
               ->  HashAggregate  (cost=24.00..24.03 rows=3 width=4) (actual time=0.100..0.105 rows=25 loops=1)
                     Group Key: label_links_1.target_id
                     ->  Nested Loop  (cost=4.59..24.00 rows=3 width=4) (actual time=0.055..0.091 rows=25 loops=1)
                           ->  Index Scan using index_labels_on_title on labels labels_1  (cost=0.28..8.29 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=1)
                                 Index Cond: ((title)::text = 'critical'::text)
                           ->  Bitmap Heap Scan on label_links label_links_1  (cost=4.31..15.66 rows=4 width=8) (actual time=0.043..0.073 rows=25 loops=1)
                                 Recheck Cond: (label_id = labels_1.id)
                                 Filter: ((target_type)::text = 'Issue'::text)
                                 Heap Blocks: exact=15
                                 ->  Bitmap Index Scan on index_label_links_on_label_id  (cost=0.00..4.31 rows=4 width=0) (actual time=0.031..0.031 rows=25 loops=1)
                                       Index Cond: (label_id = labels_1.id)
               ->  Index Scan using issues_pkey on issues  (cost=0.29..2.83 rows=1 width=347) (actual time=0.003..0.003 rows=1 loops=25)
                     Index Cond: (id = label_links_1.target_id)
                     Filter: (deleted_at IS NULL)
         ->  Nested Loop  (cost=0.56..1.35 rows=1 width=4) (actual time=0.013..0.013 rows=0 loops=25)
               ->  Index Scan using index_label_links_on_target_id_and_target_type on label_links  (cost=0.28..0.35 rows=3 width=8) (actual time=0.004..0.005 rows=3 loops=25)
                     Index Cond: ((target_id = issues.id) AND ((target_type)::text = 'Issue'::text))
               ->  Index Scan using labels_pkey on labels  (cost=0.28..0.32 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=79)
                     Index Cond: (id = label_links.label_id)
                     Filter: ((title)::text = 'bug'::text)
                     Rows Removed by Filter: 1
 Planning time: 1.316 ms
 Execution time: 0.825 ms

Why was this MR needed?

SQL query used for getting issues/MRs by labels is overly complex

Screenshots (if relevant)

None

Does this MR meet the acceptance criteria?

• Changelog entry added, if necessary
• Documentation created/updated
• API support added
• Tests added for this feature/bug
• Review
  • Has been reviewed by UX
  • Has been reviewed by Frontend
  • Has been reviewed by Backend
  • Has been reviewed by Database
• Conform by the merge request performance guides
• Conform by the style guides
• Squashed related commits together

What are the relevant issue numbers?

Closes #37137 (closed)