Skip to content

Optimize participants list

Adam Hegyi requested to merge optimize-autocomplete-sources-controller into master

What does this MR do?

Noticed that when I mention someone, the user list loading is quite slow.

Query

Old query:

SELECT Count(*)          AS count_all,
       members.source_id AS members_source_id
FROM   members
       INNER JOIN users
               ON users.id = members.user_id
WHERE  members.type = 'GroupMember'
       AND members.source_type = 'Namespace'
       AND members.source_id IN (SELECT namespaces.id
                                 FROM   ((SELECT namespaces.*
                                          FROM   namespaces
                                                 INNER JOIN members
                                                         ON namespaces.id =
                                                            members.source_id
                                          WHERE  members.type = 'GroupMember'
                                                 AND members.source_type =
                                                     'Namespace'
                                                 AND namespaces.type = 'Group'
                                                 AND members.user_id = 4156052
                                                 AND members.requested_at IS
                                                     NULL)
                                         UNION
                                         (SELECT namespaces.*
                                          FROM   projects
                                INNER JOIN project_authorizations
                                        ON projects.id =
           project_authorizations.project_id
           INNER JOIN namespaces
           ON namespaces.id = projects.namespace_id
           WHERE  project_authorizations.user_id = 4156052)) namespaces
           WHERE  namespaces.type = 'Group')
       AND members.requested_at IS NULL
GROUP  BY members.source_id  

We join users for no reason. Not sure why, maybe we didn't always had the CASCADE FK in place on the members table.

Plan - Note: sometimes I get much worse timings, usually between 80ms-400ms.

New query:

SELECT Count(*)          AS count_all,
       members.source_id AS members_source_id
FROM   members
WHERE  members.type = 'GroupMember'
       AND members.source_type = 'Namespace'
       AND members.source_id IN (SELECT namespaces.id
                                 FROM   ((SELECT namespaces.*
                                          FROM   namespaces
                                                 INNER JOIN members
                                                         ON namespaces.id =
                                                            members.source_id
                                          WHERE  members.type = 'GroupMember'
                                                 AND members.source_type =
                                                     'Namespace'
                                                 AND namespaces.type = 'Group'
                                                 AND members.user_id = 4156052
                                                 AND members.requested_at IS
                                                     NULL)
                                         UNION
                                         (SELECT namespaces.*
                                          FROM   projects
                                INNER JOIN project_authorizations
                                        ON projects.id =
           project_authorizations.project_id
           INNER JOIN namespaces
           ON namespaces.id = projects.namespace_id
           WHERE  project_authorizations.user_id = 4156052)) namespaces
           WHERE  namespaces.type = 'Group')
       AND members.requested_at IS NULL
GROUP  BY members.source_id  

Plan

Screenshots

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

  • Label as security and @ mention @gitlab-com/gl-security/appsec
  • The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
  • Security reports checked/validated by a reviewer from the AppSec team
Edited by Adam Hegyi

Merge request reports

Loading