Skip to content

Update for_targets scope on Label model

Alexandru Croitor requested to merge change_label_for_targets_scope into master

What does this MR do and why?

Follow-up fix from #376365 (closed)

Comparison between the 2 query plans

gitlabhq_development=# explain analyze SELECT "labels".*, "label_links"."target_id" FROM "labels" INNER JOIN "label_links" ON "label_links"."label_id" = "labels"."id" WHERE "label_links"."target_type" = 'Issue' AND "label_links"."target_id" IN (SELECT "issues"."id" FROM "issues" WHERE (id < 30)) ORDER BY "labels"."title" ASC;
                                                                                  QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=87.13..87.26 rows=54 width=65) (actual time=0.244..0.250 rows=82 loops=1)
   Sort Key: labels.title
   Sort Method: quicksort  Memory: 36kB
   ->  Nested Loop  (cost=0.84..85.57 rows=54 width=65) (actual time=0.051..0.190 rows=82 loops=1)
         ->  Nested Loop  (cost=0.56..68.59 rows=54 width=8) (actual time=0.044..0.108 rows=82 loops=1)
               ->  Index Only Scan using index_issues_on_id_and_weight on issues  (cost=0.28..1.79 rows=29 width=4) (actual time=0.019..0.024 rows=29 loops=1)
                     Index Cond: (id < 30)
                     Heap Fetches: 6
               ->  Index Scan using index_label_links_on_target_id_and_target_type on label_links  (cost=0.28..2.28 rows=2 width=8) (actual time=0.002..0.002 rows=3 loops=29)
                     Index Cond: ((target_id = issues.id) AND ((target_type)::text = 'Issue'::text))
         ->  Index Scan using labels_pkey on labels  (cost=0.28..0.31 rows=1 width=61) (actual time=0.001..0.001 rows=1 loops=82)
               Index Cond: (id = label_links.label_id)
 Planning Time: 0.928 ms
 Execution Time: 0.297 ms
(14 rows)

gitlabhq_development=# explain analyze SELECT "labels".*, "label_links"."target_id" FROM "labels" INNER JOIN "label_links" ON "label_links"."label_id" = "labels"."id" WHERE ("label_links"."target_type" = 'Issue' AND "label_links"."target_id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29)) ORDER BY "labels"."title" ASC
gitlabhq_development-# ;
                                                                                       QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=193.58..193.98 rows=160 width=65) (actual time=0.218..0.225 rows=82 loops=1)
   Sort Key: labels.title
   Sort Method: quicksort  Memory: 36kB
   ->  Nested Loop  (cost=0.56..187.72 rows=160 width=65) (actual time=0.016..0.163 rows=82 loops=1)
         ->  Index Scan using index_label_links_on_target_id_and_target_type on label_links  (cost=0.28..79.12 rows=160 width=8) (actual time=0.010..0.052 rows=82 loops=1)
               Index Cond: ((target_id = ANY ('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29}'::integer[])) AND ((target_type)::text = 'Issue'::text))
         ->  Index Scan using labels_pkey on labels  (cost=0.28..0.68 rows=1 width=61) (actual time=0.001..0.001 rows=1 loops=82)
               Index Cond: (id = label_links.label_id)
 Planning Time: 0.347 ms
 Execution Time: 0.254 ms

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Alexandru Croitor

Merge request reports