Add a query optimisation for fetching group labels
What does this MR do and why?
Optimize SQL statement for fetching group level labels when transferring a project.
During transfer of project gitlabdemo-cloud-app from group gitlab-com/business-technology/engineering/infrastructure/managed-services/sales-cs-demo-systems/management-apps to a different one, while trying to transfer labels we've hit a statement timeout while trying to fetch issues labels for project and its ancestors, even though there are only 13 issues in that project, similar the second statement timeout was hit on fetching labels for merge requests with only 10 MRs.
Cold cache sql
SQL & Plan
explain (analyze, buffers) SELECT label_links.id
FROM labels
INNER JOIN label_links ON label_links.target_type = 'Issue' AND label_links.label_id = labels.id
INNER JOIN issues ON issues.id = label_links.target_id
WHERE issues.project_id = 26350347 AND labels.group_id IN (
SELECT namespaces.id FROM namespaces
WHERE namespaces.id IN (6543, 3587891, 12398626, 12398637, 12399085, 12515747, 11923824)
) ORDER BY labels.title ASC;
QUERY PLAN
--------------------------------------------------------------------------------------------
Sort (cost=355.19..355.20 rows=1 width=92) (actual time=24934.761..24934.766 rows=0 loops=1)
Sort Key: labels.title
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=189872 read=10353 dirtied=201
I/O Timings: read=7682.797
-> Hash Join (cost=15.31..355.18 rows=1 width=92) (actual time=24934.732..24934.736 rows=0 loops=1)
Hash Cond: (label_links.target_id = issues.id)
Buffers: shared hit=189869 read=10353 dirtied=201
I/O Timings: read=7682.797
-> Nested Loop (cost=1.56..341.30 rows=50 width=96) (actual time=0.065..24885.589 rows=203033 loops=1)
Buffers: shared hit=189848 read=10353 dirtied=201
I/O Timings: read=7682.797
-> Nested Loop (cost=1.00..234.85 rows=9 width=92) (actual time=0.035..166.098 rows=1602 loops=1)
Buffers: shared hit=1170 read=80
I/O Timings: read=50.061
-> Index Only Scan using namespaces_pkey on namespaces (cost=0.43..15.17 rows=7 width=4) (actual time=0.019..11.097 rows=7 loops=1)
Index Cond: (id = ANY ('{6543,3587891,12398626,12398637,12399085,12515747,11923824}'::integer[]))
Heap Fetches: 1
Buffers: shared hit=26 read=2
I/O Timings: read=3.497
-> Index Scan using index_labels_on_group_id on labels (cost=0.56..31.15 rows=23 width=92) (actual time=0.716..22.040 rows=229 loops=7)
Index Cond: (group_id = namespaces.id)
Buffers: shared hit=1144 read=78
I/O Timings: read=46.564
-> Index Scan using index_label_links_on_label_id_and_target_type on label_links (cost=0.57..8.12 rows=371 width=8) (actual time=0.457..15.379 rows=127 loops=1602)
Index Cond: ((label_id = labels.id) AND ((target_type)::text = 'Issue'::text))
Buffers: shared hit=188678 read=10273 dirtied=201
I/O Timings: read=7632.736
-> Hash (cost=11.58..11.58 rows=173 width=4) (actual time=0.050..0.051 rows=13 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=18
-> Index Only Scan using idx_issues_on_project_id_and_updated_at_and_id_and_state_id on issues (cost=0.56..11.58 rows=173 width=4) (actual time=0.021..0.036 rows=13 loops=1)
Index Cond: (project_id = 26350347)
Heap Fetches: 1
Buffers: shared hit=18
Planning Time: 5.776 ms
Execution Time: 24934.881 ms
(37 rows)looking at the query plan above it seems that we actually scan all labels for all issues in the given list of groups Nested Loop (cost=1.56..341.30 rows=50 width=96) (actual time=0.065..24885.589 rows=203033 loops=1)
Query that proves we are basically fetching labels for all issues in the list of group.
explain analyze SELECT "label_links"."id"
FROM "label_links"
INNER JOIN labels ON "label_links"."target_type" = 'Issue' AND "label_links"."label_id" = "labels"."id"
INNER JOIN "issues" ON "issues"."id" = "label_links"."target_id" AND "label_links"."target_type" = 'Issue'
WHERE "labels"."group_id" IN (
SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."id" IN (6543, 3587891, 12398626, 12398637, 12399085, 12515747, 11923824)
);
QUERY PLAN
------------------------------------------------------------------------------------------------------
Nested Loop (cost=2.13..370.70 rows=50 width=4) (actual time=0.110..1350.278 rows=202322 loops=1)
-> Nested Loop (cost=1.56..341.30 rows=50 width=8) (actual time=0.055..319.279 rows=203033 loops=1)
-> Nested Loop (cost=1.00..234.85 rows=9 width=4) (actual time=0.028..2.987 rows=1602 loops=1)
-> Index Only Scan using namespaces_pkey on namespaces (cost=0.43..15.17 rows=7 width=4) (actual time=0.010..0.045 rows=7 loops=1)
Index Cond: (id = ANY ('{6543,3587891,12398626,12398637,12399085,12515747,11923824}'::integer[]))
Heap Fetches: 1
-> Index Scan using index_labels_on_group_id on labels (cost=0.56..31.15 rows=23 width=8) (actual time=0.006..0.364 rows=229 loops=7)
Index Cond: (group_id = namespaces.id)
-> Index Scan using index_label_links_on_label_id_and_target_type on label_links (cost=0.57..8.12 rows=371 width=12) (actual time=0.011..0.166 rows=127 loops=1602)
Index Cond: ((label_id = labels.id) AND ((target_type)::text = 'Issue'::text))
-> Index Only Scan using issues_pkey on issues (cost=0.56..0.59 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=203033)
Index Cond: (id = label_links.target_id)
Heap Fetches: 5508
Planning Time: 2.302 ms
Execution Time: 1365.848 ms
(15 rows)Query change
Seems that we can slightly change the query to not have to scan for all labels in the set of groups. The difference: 267ms vs 0.17ms in this specific case with warm cache
All queries bellow are run with warm cache, cold cache query times will differ.
Original query and plan
explain analyze SELECT "label_links"."id"
FROM "label_links"
INNER JOIN labels ON "label_links"."target_type" = 'Issue' AND "label_links"."label_id" = "labels"."id"
INNER JOIN "issues" ON "issues"."id" = "label_links"."target_id" AND "label_links"."target_type" = 'Issue'
WHERE "labels"."group_id" IN (
SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."id" IN (6543, 3587891, 12398626, 12398637, 12399085, 12515747, 11923824)
);
QUERY PLAN
---------------------------------------------------------------------------------------------------
Sort (cost=355.19..355.20 rows=1 width=14) (actual time=267.828..267.832 rows=0 loops=1)
Sort Key: labels.title
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=200199
-> Hash Join (cost=15.31..355.18 rows=1 width=14) (actual time=267.820..267.823 rows=0 loops=1)
Hash Cond: (label_links.target_id = issues.id)
Buffers: shared hit=200199
-> Nested Loop (cost=1.56..341.30 rows=50 width=18) (actual time=0.057..243.467 rows=203033 loops=1)
Buffers: shared hit=200181
-> Nested Loop (cost=1.00..234.85 rows=9 width=14) (actual time=0.027..2.135 rows=1602 loops=1)
Buffers: shared hit=1247
-> Index Only Scan using namespaces_pkey on namespaces (cost=0.43..15.17 rows=7 width=4) (actual time=0.010..0.039 rows=7 loops=1)
Index Cond: (id = ANY ('{6543,3587891,12398626,12398637,12399085,12515747,11923824}'::integer[]))
Heap Fetches: 1
Buffers: shared hit=25
-> Index Scan using index_labels_on_group_id on labels (cost=0.56..31.15 rows=23 width=18) (actual time=0.005..0.249 rows=229 loops=7)
Index Cond: (group_id = namespaces.id)
Buffers: shared hit=1222
-> Index Scan using index_label_links_on_label_id_and_target_type on label_links (cost=0.57..8.12 rows=371 width=12) (actual time=0.008..0.122 rows=127 loops=1602)
Index Cond: ((label_id = labels.id) AND ((target_type)::text = 'Issue'::text))
Buffers: shared hit=198934
-> Hash (cost=11.58..11.58 rows=173 width=4) (actual time=0.030..0.031 rows=13 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=18
-> Index Only Scan using idx_issues_on_project_id_and_updated_at_and_id_and_state_id on issues (cost=0.56..11.58 rows=173 width=4) (actual time=0.012..0.025 rows=13 loops=1)
Index Cond: (project_id = 26350347)
Heap Fetches: 1
Buffers: shared hit=18
Planning Time: 1.615 ms
Execution Time: 267.897 ms
(30 rows)Updated query and plan
explain analyze SELECT "label_links"."id"
FROM "labels"
INNER JOIN "label_links" ON "label_links"."target_type" = 'Issue' AND "label_links"."label_id" = "labels"."id"
INNER JOIN "issues" ON "issues"."id" = "label_links"."target_id"
INNER JOIN "namespaces" ON "namespaces"."id" = "labels"."group_id" AND "namespaces"."type" = 'Group'
WHERE "labels"."type" = 'GroupLabel' AND "issues"."project_id" = 26350347
ORDER BY "labels"."title" ASC;
QUERY PLAN
------------------------------------------------------------------------------------------
Sort (cost=1047.87..1047.87 rows=2 width=14) (actual time=0.122..0.123 rows=0 loops=1)
Sort Key: labels.title
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=2.13..1047.86 rows=2 width=14) (actual time=0.116..0.117 rows=0 loops=1)
-> Nested Loop (cost=1.57..1041.09 rows=8 width=18) (actual time=0.116..0.117 rows=0 loops=1)
-> Nested Loop (cost=1.13..907.88 rows=290 width=8) (actual time=0.116..0.116 rows=0 loops=1)
-> Index Only Scan using idx_issues_on_project_id_and_updated_at_and_id_and_state_id on issues (cost=0.56..10.31 rows=172 width=4) (actual time=0.017..0.036 rows=13 loops=1)
Index Cond: (project_id = 26350347)
Heap Fetches: 1
-> Index Scan using index_label_links_on_target_id_and_target_type on label_links (cost=0.57..5.19 rows=3 width=12) (actual time=0.006..0.006 rows=0 loops=13)
Index Cond: ((target_id = issues.id) AND ((target_type)::text = 'Issue'::text))
-> Index Scan using labels_pkey on labels (cost=0.43..0.46 rows=1 width=18) (never executed)
Index Cond: (id = label_links.label_id)
Filter: ((type)::text = 'GroupLabel'::text)
-> Index Only Scan using index_namespaces_on_type_and_id on namespaces (cost=0.56..0.85 rows=1 width=4) (never executed)
Index Cond: ((type = 'Group'::text) AND (id = labels.group_id))
Heap Fetches: 0
Planning Time: 1.520 ms
Execution Time: 0.169 ms
(19 rows)Another example
In the above example we get zero labels, so I decided to try it with a different project. This time for gitlab-com/gl-infra/infrastructure project. Similarly with warm cache, ~4.4s vs ~270ms
Original query and plan
Label.joins(:issues).where(issues: { project_id: 1304532 }, labels: { group_id: Group.find(1112072).self_and_ancestors }).pluck("label_links.id")
explain analyze SELECT "label_links"."id"
FROM "labels"
INNER JOIN "label_links" ON "label_links"."target_type" = 'Issue' AND "label_links"."label_id" = "labels"."id"
INNER JOIN "issues" ON "issues"."id" = "label_links"."target_id"
WHERE "issues"."project_id" = 1304532 AND "labels"."group_id" IN (
SELECT "namespaces"."id" FROM "namespaces"
WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" IN (6543, 1112072)
)
ORDER BY "labels"."title" ASC;
QUERY PLAN
--------------------------------------------------------------------------------------------------
Sort (cost=53.72..53.73 rows=1 width=14) (actual time=4428.627..4429.908 rows=19063 loops=1)
Sort Key: labels.title
Sort Method: quicksort Memory: 2064kB
-> Nested Loop (cost=2.25..53.71 rows=1 width=14) (actual time=14.767..4412.292 rows=19063 loops=1)
-> Nested Loop (cost=1.69..48.86 rows=7 width=18) (actual time=0.152..1880.507 rows=242229 loops=1)
-> Nested Loop (cost=1.12..37.03 rows=1 width=14) (actual time=0.086..25.292 rows=1780 loops=1)
-> Index Only Scan using index_namespaces_on_type_and_id on namespaces (cost=0.56..5.65 rows=1 width=4) (actual time=0.047..2.051 rows=2 loops=1)
Index Cond: ((type = 'Group'::text) AND (id = ANY ('{6543,1112072}'::integer[])))
Heap Fetches: 0
-> Index Scan using index_labels_on_group_id on labels (cost=0.56..31.15 rows=23 width=18) (actual time=0.046..11.285 rows=890 loops=2)
Index Cond: (group_id = namespaces.id)
-> Index Scan using index_label_links_on_label_id_and_target_type on label_links (cost=0.57..8.12 rows=371 width=12) (actual time=0.033..1.001 rows=136 loops=1780)
Index Cond: ((label_id = labels.id) AND ((target_type)::text = 'Issue'::text))
-> Index Scan using issues_pkey on issues (cost=0.56..0.69 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=242229)
Index Cond: (id = label_links.target_id)
Filter: (project_id = 1304532)
Rows Removed by Filter: 1
Planning Time: 2.304 ms
Execution Time: 4431.430 ms
(19 rows)Updated query and plan
GroupLabel.joins(:issues).joins(:group).where(issues: { project_id: 1304532 }).pluck("label_links.id")
explain analyze SELECT "label_links"."id"
FROM "labels"
INNER JOIN "label_links" ON "label_links"."target_type" = 'Issue' AND "label_links"."label_id" = "labels"."id"
INNER JOIN "issues" ON "issues"."id" = "label_links"."target_id"
INNER JOIN "namespaces" ON "namespaces"."id" = "labels"."group_id" AND "namespaces"."type" = 'Group'
WHERE "labels"."type" = 'GroupLabel' AND "issues"."project_id" = 1304532
ORDER BY "labels"."title" ASC
QUERY PLAN
----------------------------------------------------------------------------------------------------
Gather Merge (cost=51059.37..51069.49 rows=88 width=14) (actual time=229.496..267.445 rows=19063 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Sort (cost=50059.36..50059.58 rows=88 width=14) (actual time=222.247..223.361 rows=9532 loops=2)
Sort Key: labels.title
Sort Method: quicksort Memory: 1158kB
Worker 0: Sort Method: quicksort Memory: 714kB
-> Nested Loop (cost=2.13..50056.52 rows=88 width=14) (actual time=0.369..213.061 rows=9532 loops=2)
-> Nested Loop (cost=1.57..49722.45 rows=395 width=18) (actual time=0.316..155.765 rows=9532 loops=2)
-> Nested Loop (cost=1.13..43303.77 rows=13974 width=8) (actual time=0.091..99.053 rows=14714 loops=2)
-> Parallel Index Only Scan using idx_issues_on_project_id_and_updated_at_and_id_and_state_id on issues (cost=0.56..702.79 rows=8288 width=4) (actual time=0.049..6.977 rows=7232 loops=2)
Index Cond: (project_id = 1304532)
Heap Fetches: 204
-> Index Scan using index_label_links_on_target_id_and_target_type on label_links (cost=0.57..5.11 rows=3 width=12) (actual time=0.009..0.012 rows=2 loops=14463)
Index Cond: ((target_id = issues.id) AND ((target_type)::text = 'Issue'::text))
-> Index Scan using labels_pkey on labels (cost=0.43..0.46 rows=1 width=18) (actual time=0.003..0.003 rows=1 loops=29429)
Index Cond: (id = label_links.label_id)
Filter: ((type)::text = 'GroupLabel'::text)
Rows Removed by Filter: 0
-> Index Only Scan using index_namespaces_on_type_and_id on namespaces (cost=0.56..0.85 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=19063)
Index Cond: ((type = 'Group'::text) AND (id = labels.group_id))
Heap Fetches: 0
Planning Time: 1.586 ms
Execution Time: 268.971 ms
(24 rows)Merge Request Labels query
Original query and plan
Label.joins(:merge_requests).where(merge_requests: { merge_requests: 1304532 }, labels: { group_id: Group.find(1112072).self_and_ancestors }).pluck("label_links.id")
explain analyze SELECT "label_links"."id"
FROM "labels"
INNER JOIN "label_links" ON "label_links"."target_type" = 'MergeRequest' AND "label_links"."label_id" = "labels"."id"
INNER JOIN "merge_requests" ON "merge_requests"."id" = "label_links"."target_id"
WHERE "merge_requests"."target_project_id" = 1304532 AND "labels"."group_id" IN (
SELECT "namespaces"."id" FROM "namespaces"
WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" IN (6543, 1112072)
)
ORDER BY "labels"."title" ASC;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Sort (cost=41.16..41.17 rows=1 width=14) (actual time=384.175..384.180 rows=31 loops=1)
Sort Key: labels.title
Sort Method: quicksort Memory: 27kB
-> Nested Loop (cost=2.25..41.15 rows=1 width=14) (actual time=12.233..384.112 rows=31 loops=1)
-> Nested Loop (cost=1.69..39.95 rows=1 width=18) (actual time=0.108..93.255 rows=42572 loops=1)
-> Nested Loop (cost=1.12..37.03 rows=1 width=14) (actual time=0.057..3.540 rows=1780 loops=1)
-> Index Only Scan using index_namespaces_on_type_and_id on namespaces (cost=0.56..5.65 rows=1 width=4) (actual time=0.026..0.045 rows=2 loops=1)
Index Cond: ((type = 'Group'::text) AND (id = ANY ('{6543,1112072}'::integer[])))
Heap Fetches: 0
-> Index Scan using index_labels_on_group_id on labels (cost=0.56..31.15 rows=23 width=18) (actual time=0.020..1.560 rows=890 loops=2)
Index Cond: (group_id = namespaces.id)
-> Index Scan using index_label_links_on_label_id_and_target_type on label_links (cost=0.57..2.18 rows=74 width=12) (actual time=0.008..0.044 rows=24 loops=1780)
Index Cond: ((label_id = labels.id) AND ((target_type)::text = 'MergeRequest'::text))
-> Index Scan using merge_requests_pkey on merge_requests (cost=0.57..1.19 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=42572)
Index Cond: (id = label_links.target_id)
Filter: (target_project_id = 1304532)
Rows Removed by Filter: 1
Planning Time: 2.478 ms
Execution Time: 384.251 ms
(19 rows)Updated query and plan
GroupLabel.joins(:merge_requests).joins(:group).where(merge_requests: { project_id: 1304532 }).pluck("label_links.id")
explain analyze SELECT "label_links"."id"
FROM "labels"
INNER JOIN "label_links" ON "label_links"."target_type" = 'MergeRequest' AND "label_links"."label_id" = "labels"."id"
INNER JOIN "merge_requests" ON "merge_requests"."id" = "label_links"."target_id"
INNER JOIN "namespaces" ON "namespaces"."id" = "labels"."group_id" AND "namespaces"."type" = 'Group'
WHERE "labels"."type" = 'GroupLabel' AND "merge_requests"."target_project_id" = 1304532
ORDER BY "labels"."title" ASC;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Sort (cost=1256.77..1256.77 rows=1 width=14) (actual time=3.271..3.277 rows=31 loops=1)
Sort Key: labels.title
Sort Method: quicksort Memory: 27kB
-> Nested Loop (cost=2.13..1256.76 rows=1 width=14) (actual time=0.991..3.232 rows=31 loops=1)
-> Nested Loop (cost=1.57..1255.07 rows=2 width=18) (actual time=0.964..2.992 rows=31 loops=1)
-> Nested Loop (cost=1.14..1226.94 rows=59 width=8) (actual time=0.054..2.533 rows=56 loops=1)
-> Index Only Scan using index_on_merge_requests_for_latest_diffs on merge_requests (cost=0.57..10.98 rows=338 width=4) (actual time=0.031..0.270 rows=234 loops=1)
Index Cond: (target_project_id = 1304532)
Heap Fetches: 0
-> Index Scan using index_label_links_on_target_id_and_target_type on label_links (cost=0.57..3.59 rows=1 width=12) (actual time=0.009..0.009 rows=0 loops=234)
Index Cond: ((target_id = merge_requests.id) AND ((target_type)::text = 'MergeRequest'::text))
-> Index Scan using labels_pkey on labels (cost=0.43..0.48 rows=1 width=18) (actual time=0.008..0.008 rows=1 loops=56)
Index Cond: (id = label_links.label_id)
Filter: ((type)::text = 'GroupLabel'::text)
Rows Removed by Filter: 0
-> Index Only Scan using index_namespaces_on_type_and_id on namespaces (cost=0.56..0.85 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=31)
Index Cond: ((type = 'Group'::text) AND (id = labels.group_id))
Heap Fetches: 0
Planning Time: 2.123 ms
Execution Time: 3.346 ms
(20 rows)Screenshots or screen recordings
These are strongly recommended to assist reviewers and reduce the time to merge your change.
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