Skip to content

Add blocking issues count on issues metadata

Felipe Cardozo requested to merge issue_217569 into master

What does this MR do?

Includes blocking issues count on issuable metadata.

Backend portion of #217569 (closed).

Uses feature flag blocking_issues_counts.

Query to fetch blocking issues count for collection

  • I used SELECT id FROM issues ORDER BY id DESC LIMIT 100 as issues_ids on database lab. On production the query will be executed for each page of issues on the list. In this scenario it took 4ms to execute.

  • Query plan: plan-text.txt

SELECT   blocking_issue_id, 
         Sum(count) AS count 
FROM     ( 
         ( 
                    SELECT     Count(*), 
                               issue_links.source_id AS blocking_issue_id 
                    FROM       "issue_links" 
                    INNER JOIN "issues" 
                    ON         "issues"."id" = "issue_links"."target_id" 
                    WHERE      "issues"."state_id" = 1 
                    AND        "issue_links"."link_type" = 1 
                    AND        "issue_links"."source_id" IN (SELECT source_id FROM issue_links WHERE link_type = 1 ORDER BY source_id DESC LIMIT 100 ) 
                    GROUP BY   "blocking_issue_id")

                    UNION ALL

         ( 
                    SELECT     count(*), 
                               issue_links.target_id AS blocking_issue_id 
                    FROM       "issue_links" 
                    INNER JOIN "issues" 
                    ON         "issues"."id" = "issue_links"."source_id" 
                    WHERE      "issues"."state_id" = 1 
                    AND        "issue_links"."link_type" = 2 
                    AND        "issue_links"."target_id" IN (SELECT target_id FROM issue_links WHERE link_type = 2 ORDER BY target_id DESC LIMIT 100) 
                    GROUP BY   "blocking_issue_id")) issue_links 
GROUP BY blocking_issue_id

Query Plan 

           QUERY PLAN                                                                                     



GroupAggregate  (cost=1777.60..1783.81 rows=113 width=36) (actual time=65.079..65.241 rows=163 loops=1)
Group Key: "SELECT 1".blocking_issue_id
->  Merge Append  (cost=1777.60..1781.83 rows=113 width=12) (actual time=65.067..65.166 rows=164 loops
=1)
Sort Key: "SELECT 1".blocking_issue_id
->  Subquery Scan on "SELECT 1"  (cost=810.68..811.67 rows=36 width=12) (actual time=53.566..5
3.610 rows=87 loops=1)
->  GroupAggregate  (cost=810.68..811.31 rows=36 width=12) (actual time=53.565..53.600 row
s=87 loops=1)
Group Key: issue_links.source_id
->  Sort  (cost=810.68..810.77 rows=36 width=4) (actual time=53.559..53.569 rows=97
loops=1)
Sort Key: issue_links.source_id
Sort Method: quicksort  Memory: 29kB
->  Nested Loop  (cost=272.70..809.75 rows=36 width=4) (actual time=46.472..53
.517 rows=97 loops=1)
->  Nested Loop  (cost=272.13..543.72 rows=75 width=8) (actual time=46.3
71..47.023 rows=100 loops=1)
->  HashAggregate  (cost=271.71..272.28 rows=57 width=4) (actual t
ime=46.307..46.333 rows=90 loops=1)
Group Key: issue_links_1.source_id
->  Limit  (cost=0.42..270.46 rows=100 width=4) (actual time
=0.786..46.209 rows=100 loops=1)
->  Index Scan Backward using index_issue_links_on_sou
rce_id_and_target_id on issue_links issue_links_1  (cost=0.42..24122.39 rows=8933 width=4) (actual time=0
.785..46.186 rows=100 loops=1)
Filter: (link_type = 1)
Rows Removed by Filter: 1054
->  Index Scan using index_issue_links_on_source_id on issue_links
(cost=0.42..4.75 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=90)
Index Cond: (source_id = issue_links_1.source_id)
Filter: (link_type = 1)
Rows Removed by Filter: 0
->  Index Scan using issues_pkey on issues  (cost=0.56..3.55 rows=1 widt
h=4) (actual time=0.065..0.065 rows=1 loops=100)
Index Cond: (id = issue_links.target_id)
Filter: (state_id = 1)
Rows Removed by Filter: 0
->  Subquery Scan on "SELECT 2"  (cost=966.90..969.02 rows=77 width=12) (actual time=11.498..1
1.534 rows=77 loops=1)
->  GroupAggregate  (cost=966.90..968.25 rows=77 width=12) (actual time=11.497..11.525 row
s=77 loops=1)
Group Key: issue_links_2.target_id
->  Sort  (cost=966.90..967.10 rows=77 width=4) (actual time=11.490..11.497 rows=104
loops=1)
Sort Key: issue_links_2.target_id
Sort Method: quicksort  Memory: 29kB
->  Nested Loop  (cost=112.82..964.49 rows=77 width=4) (actual time=3.757..11.
438 rows=104 loops=1)
->  Nested Loop  (cost=112.26..398.73 rows=162 width=8) (actual time=3.6
77..4.191 rows=106 loops=1)
->  HashAggregate  (cost=111.83..112.43 rows=60 width=4) (actual t
ime=3.653..3.685 rows=79 loops=1)
Group Key: issue_links_3.target_id
->  Limit  (cost=0.42..110.58 rows=100 width=4) (actual time
=0.020..3.617 rows=100 loops=1)
->  Index Scan Backward using index_issue_links_on_tar
get_id on issue_links issue_links_3  (cost=0.42..24856.15 rows=22564 width=4) (actual time=0.019..3.606 r
ows=100 loops=1)
Filter: (link_type = 2)
Rows Removed by Filter: 265
->  Index Scan using index_issue_links_on_target_id on issue_links
issue_links_2  (cost=0.42..4.76 rows=1 width=8) (actual time=0.004..0.006 rows=1 loops=79)
Index Cond: (target_id = issue_links_3.target_id)
Filter: (link_type = 2)
Rows Removed by Filter: 0
->  Index Scan using issues_pkey on issues issues_1  (cost=0.56..3.49 ro
ws=1 width=4) (actual time=0.068..0.068 rows=1 loops=106)
Index Cond: (id = issue_links_2.source_id)
Filter: (state_id = 1)
Rows Removed by Filter: 0
Planning Time: 6.084 ms
Execution Time: 65.654 ms

Edited by Justin Farris

Merge request reports