Optimize query in Groups::AcceptingProjectCreationsFinder class
When we render the 'new projects' page (/projects/new
), we retrieve a list of groups in which the current user is allowed to create a project. This list is coming from Groups::AcceptingProjectCreationsFinder
class.
In this MR, we found that the query could be optimized: one of the indexes we use can be improved.
Some findings from the query plan:
- The problematic part is
Rows Removed by Filter: 37
(this is for user_id 1), there are multiple sub queries - The query is consulting
members
table and filtering on: user_id, source_type, access_level, type, requested_at - It is using the indexes
index_members_on_user_id_and_access_level_requested_at_is_null
andidx_members_on_user_and_source_and_source_type_and_member_role
but it still results in too many rows
I tried adding an index on user_id, access_level, source_type, type
but this did not help for the subqueries that use members.access_level IN (30,40,50)
Definition of done
- Optimize the query so we do not see
Rows Removed by Filter: 37
anymore (or larger number) - Investigate if we have any redundant indexes that could be replaced by a better index
Edited by Rutger Wessels