Avoid N+1 queries when checking for pending todos
🍳 What does this MR do?
This avoids an N+1 query problem when checking for pending to-dos as part of updating a note. For this it was also necessary to change the PendingNotesFinder to be able to query to-dos for multiple users at once.
- Issue: #325690 (closed)
- Related to https://gitlab.com/gitlab-org/ecosystem-team/team-tasks/-/issues/67
💾 Database
Old query:
SELECT
todos.*
FROM
todos
WHERE
todos.user_id = 16943
AND (todos.state IN ('pending'))
AND todos.project_id = 278964
AND todos.target_id = 81662685
AND todos.target_type = 'Issue'
New query:
SELECT
todos.*
FROM
todos
WHERE
todos.user_id IN (16943, 2535118)
AND (todos.state IN ('pending'))
AND todos.project_id = 278964
AND todos.target_id = 81662685
AND todos.target_type = 'Issue'
Summary:
Time: 12.962 ms
- planning: 0.375 ms
- execution: 12.587 ms
- I/O read: 12.145 ms
- I/O write: N/A
Shared buffers:
- hits: 1 (~8.00 KiB) from the buffer pool
- reads: 8 (~64.00 KiB) from the OS file cache, including disk I/O
- dirtied: 1 (~8.00 KiB)
- writes: 0
🗒 Does this MR meet the acceptance criteria?
Conformity
-
📋 Does this MR need a changelog?-
I have included a changelog entry. -
I have not included a changelog entry because _____.
-
-
Documentation (if required) -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides - [-] Separation of EE specific content
Availability and Testing
-
Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process. - [-] Tested in all supported browsers
- [-] Informed Infrastructure department of a default or new setting change, if applicable per definition of done
Edited by Andy Schoenen