Refactor queries in transfer label service
What does this MR do and why?
We need to batch queries that look for relevant labels to update based on associated issues and merge requests by using the intermediate table label_links
DB review
Queries before
- (Problem) Query to pluck all
label_linksassociated with group labels and issues that belong to the project https://console.postgres.ai/gitlab/gitlab-production-main/sessions/50370/commands/149502 (using a lot of shared buffers, that's why it only took 6 seconds. The first run took over 20 seconds) - The same for merge requests, not a problem https://postgres.ai/console/gitlab/gitlab-production-main/sessions/50370/commands/149503 (still need to fix as might be a problem for other projects)
- (Problem) Query to iterate over group labels associated to issues and merge requests in the project https://postgres.ai/console/gitlab/gitlab-production-main/sessions/50372/commands/149507 (also loading the entire set even if using find_each)
new queries
- Select distinct label_ids in batches
- find lower bound https://console.postgres.ai/gitlab/gitlab-production-main/sessions/50372/commands/149709
- find upper bound https://console.postgres.ai/gitlab/gitlab-production-main/sessions/50372/commands/149710
- Fetch batch https://console.postgres.ai/gitlab/gitlab-production-main/sessions/50372/commands/149713
- Find lower boundary for merge requests https://console.postgres.ai/gitlab/gitlab-production-main/sessions/50372/commands/149574
- Filter out project labels https://console.postgres.ai/gitlab/gitlab-production-main/sessions/50372/commands/149562
find_eachin batches of 500 https://console.postgres.ai/gitlab/gitlab-production-main/sessions/50372/commands/149596find_eachsecond batch https://console.postgres.ai/gitlab/gitlab-production-main/sessions/50372/commands/149597- update a 500 row batch for issue label links https://console.postgres.ai/gitlab/gitlab-production-main/sessions/50372/commands/149714
Related to #595408 (closed)
https://gitlab.com/gitlab-com/request-for-help/-/work_items/4321#note_3191460267
Edited by Mario Celi