Skip to content

Bulk update user todos count cache

Andy Schoenenrequested to merge
bulk-update-user-todos-count-cache into master

🍳 What does this MR do?

Avoids N+1 queries when updating todo count cache for multiple users. TodoService updates the cache count for example when users get mentioned in a Note.

💾 Database

Previous query plans

Bulk todos count query

Updating the count will produce the following query when executed for 100 users:

SELECT
    COUNT(*) AS count_all,
    "todos"."user_id" AS todos_user_id,
    "todos"."state" AS todos_state
FROM
    "todos"
WHERE
    "todos"."state" IN ('done', 'pending')
    AND "todos"."user_id" IN (
        SELECT
            "users"."id"
        FROM
            "users"
        LIMIT 100)
GROUP BY
    "todos"."user_id",
    "todos"."state"

This seems to be quite slow:

Update: I've limited the amount of users to 10:

Update 2: I've added an index ON todos USING btree (user_id) WHERE ((state)::text = ANY ('{done,pending}'::text[])) to improve query performance.

Update 3 by @toupeira: I've changed the index to be on (user_id, state) instead, and changed the query to stop filtering on state (since we want all states anyway):

Update 4 by @toupeira: Reverted the index changes, and switched to a UNION query using the existing partial indexes:

🗒 Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Edited by Toon Claes

Merge request reports