Skip to content

Avoid N+1 queries when checking for pending todos

Andy Schoenen requested to merge improve-todo-service-database-performance into master

🍳 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.

💾 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'

🔗 Execution plan

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

Availability and Testing

Edited by Andy Schoenen

Merge request reports