Counting (or retrieving) todos uses a redundant AND that slows the query down
When counting the number of todos (and probably also when retrieving them) we generate a query along the lines of the following:
SELECT COUNT(*)
FROM todos
INNER JOIN projects ON projects.id = todos.project_id
WHERE todos.user_id = 1
AND todos.state IN ('pending')
...
AND projects.id IN (
SELECT todos.project_id
FROM todos
WHERE todos.user_id = 1
AND todos.state IN ('pending')
);
The problem here is the following clause:
AND projects.id IN (
SELECT todos.project_id
FROM todos
WHERE todos.user_id = 1
AND todos.state IN ('pending')
);
This clause is completely useless because we already have all the pending todos we want at this point. As a result we waste time limiting the todos to the exact same list of projects. In other words, this clause will never reduce the number of rows as far as I can tell.
We need to rewrite this query so it's along the lines of the following:
SELECT COUNT(*)
FROM todos
INNER JOIN projects ON projects.id = todos.project_id
WHERE todos.user_id = 1
AND todos.state IN ('pending')
AND (
EXISTS (
SELECT 1
FROM project_authorizations
WHERE project_authorizations.user_id = 1
AND project_authorizations.project_id = projects.id
)
OR projects.visibility_level IN (...)
);
This will produce the exact same data at a fraction of the cost.