Expose blocked by issue on IssueEntity
What does this MR do?
Expose blocking issues on IssueEntity.
This is the backend portion of #34239 (closed)
Query to retrieve blocked issues(executed on staging):.
gitlabhq_production=> explain analyze SELECT "issues".*
gitlabhq_production-> FROM "issues"
gitlabhq_production-> WHERE "issues"."project_id" = 278964
gitlabhq_production-> AND "issues"."iid" IN (SELECT "issues"."iid"
gitlabhq_production(> FROM "issues"
gitlabhq_production(> WHERE
gitlabhq_production(> "issues"."id" IN (SELECT "blocking_issue_id"
gitlabhq_production(> FROM ((SELECT
gitlabhq_production(> target_id AS blocked_issue_id,
gitlabhq_production(> source_id AS blocking_issue_id
gitlabhq_production(> FROM "issue_links"
gitlabhq_production(> INNER JOIN issues
gitlabhq_production(> ON
gitlabhq_production(> issues.id =
gitlabhq_production(> issue_links.source_id
gitlabhq_production(> WHERE "issue_links"."link_type" = 1
gitlabhq_production(> AND "issue_links"."target_id" = 7309705
gitlabhq_production(> AND "issues"."state_id" = 1)
gitlabhq_production(> UNION
gitlabhq_production(> (SELECT source_id AS blocked_issue_id,
gitlabhq_production(> target_id AS blocking_issue_id
gitlabhq_production(> FROM "issue_links"
gitlabhq_production(> INNER JOIN issues ON issues.id = issue_links.target_id
gitlabhq_production(> WHERE "issue_links"."link_type" = 2
gitlabhq_production(> AND "issue_links"."source_id" = 7309705
gitlabhq_production(> AND "issues"."state_id" = 1))
gitlabhq_production(> issue_links))
gitlabhq_production-> ORDER BY "issues"."id" DESC;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
Sort (cost=72.53..74.84 rows=925 width=768) (actual time=0.305..0.305 rows=1 loops=1)
Sort Key: issues.id DESC
Sort Method: quicksort Memory: 27kB
-> Nested Loop (cost=20.99..26.95 rows=925 width=768) (actual time=0.266..0.267 rows=1 loops=1)
-> Unique (cost=20.56..20.57 rows=2 width=4) (actual time=0.204..0.205 rows=1 loops=1)
-> Sort (cost=20.56..20.56 rows=2 width=4) (actual time=0.203..0.203 rows=1 loops=1)
Sort Key: issues_1.iid
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=14.05..20.55 rows=2 width=4) (actual time=0.195..0.196 rows=1 loops=1)
-> Unique (cost=13.62..13.63 rows=2 width=4) (actual time=0.192..0.193 rows=1 loops=1)
-> Sort (cost=13.62..13.62 rows=2 width=4) (actual time=0.191..0.191 rows=1 loops=1)
Sort Key: issue_links.blocking_issue_id
Sort Method: quicksort Memory: 25kB
-> Subquery Scan on issue_links (cost=13.57..13.61 rows=2 width=4) (actual time=0.179..0.179 rows=1 loops=1)
-> Unique (cost=13.57..13.59 rows=2 width=8) (actual time=0.179..0.179 rows=1 loops=1)
-> Sort (cost=13.57..13.58 rows=2 width=8) (actual time=0.179..0.179 rows=1 loops=1)
Sort Key: issue_links_1.target_id, issue_links_1.source_id
Sort Method: quicksort Memory: 25kB
-> Append (cost=0.72..13.56 rows=2 width=8) (actual time=0.150..0.169 rows=1 loops=1)
-> Nested Loop (cost=0.72..6.77 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=1)
-> Index Scan using index_issue_links_on_target_id on issue_links issue_links_1 (cost
=0.29..3.31 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1)
Index Cond: (target_id = 7309705)
Filter: (link_type = 1)
-> Index Scan using issues_pkey on issues issues_2 (cost=0.43..3.45 rows=1 width=4) (
never executed)
Index Cond: (id = issue_links_1.source_id)
Filter: (state_id = 1)
-> Nested Loop (cost=0.72..6.77 rows=1 width=8) (actual time=0.144..0.163 rows=1 loops=1)
-> Index Scan using index_issue_links_on_source_id_and_target_id on issue_links issue_
links_2 (cost=0.29..3.31 rows=1 width=8) (actual time=0.062..0.065 rows=1 loops=1)
Index Cond: (source_id = 7309705)
Filter: (link_type = 2)
Rows Removed by Filter: 3
-> Index Scan using issues_pkey on issues issues_3 (cost=0.43..3.45 rows=1 width=4) (
actual time=0.080..0.095 rows=1 loops=1)
Index Cond: (id = issue_links_2.target_id)
Filter: (state_id = 1)
-> Index Scan using issues_pkey on issues issues_1 (cost=0.43..3.45 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id = issue_links.blocking_issue_id)
-> Index Scan using index_issues_on_project_id_and_iid on issues (cost=0.43..3.18 rows=1 width=768) (actual time=0.061..0.061 rows=1 loops=1)
Index Cond: ((project_id = 278964) AND (iid = issues_1.iid))
Planning time: 10.850 ms
Execution time: 0.492 ms