Update for_targets scope on Label model
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.
-
I have evaluated the MR acceptance checklist for this MR.
Edited by Alexandru Croitor