Skip to content

Speed up Group#user_ids_for_project_authorizations

What does this MR do?

Speeds up a group method that is involved in granting access to groups and, so, project creation.

Are there points in the code the reviewer needs to double check?

SQL before this change (~400ms):

SELECT "users"."id"
FROM   "users"
WHERE  "users"."id" IN (SELECT "members"."user_id"
                        FROM   "members"
                        WHERE  "members"."type" IN ( 'GroupMember' )
                               AND "members"."source_type" = $1
                               AND ( "members"."user_id" IS NULL
                                     AND "members"."invite_token" IS NOT NULL
                                      OR "users"."state" = 'active' )
                               AND "members"."requested_at" IS NULL
                               AND "members"."source_id" = 13991
                               AND ( "members"."user_id" IS NOT NULL )
                        ORDER  BY "members"."id" DESC)
ORDER  BY "users"."id" DESC  
 Index Scan Backward using users_pkey on users  (cost=0.29..86121.59 rows=5036 width=4)
   Filter: (SubPlan 1)
   SubPlan 1
     ->  Sort  (cost=8.32..8.32 rows=1 width=8)
           Sort Key: members.id DESC
           ->  Index Scan using index_members_on_source_id_and_source_type on members  (cost=0.28..8.31 rows=1 width=8)
                 Index Cond: ((source_id = 13991) AND ((source_type)::text = 'Namespace'::text))
                 Filter: ((requested_at IS NULL) AND (user_id IS NOT NULL) AND (((user_id IS NULL) AND (invite_token IS NOT NULL)) OR ((users.state)::text = 'active'::text)) AND ((type)::text = 'GroupMember'::text))
(8 rows)

After (~1.2ms):

SELECT "members"."user_id"
FROM   "members"
       LEFT OUTER JOIN "users"
                    ON "members"."user_id" = "users"."id"
WHERE  "members"."type" IN ( 'GroupMember' )
       AND "members"."source_type" = $1
       AND "users"."state" = $2
       AND "members"."requested_at" IS NULL
       AND "members"."source_id" = $3  
 Nested Loop  (cost=0.57..16.62 rows=1 width=4)
   ->  Index Scan using index_members_on_source_id_and_source_type on members  (cost=0.28..8.30 rows=1 width=4)
         Index Cond: ((source_id = 13991) AND ((source_type)::text = 'Namespace'::text))
         Filter: ((requested_at IS NULL) AND ((type)::text = 'GroupMember'::text))
   ->  Index Scan using users_pkey on users  (cost=0.29..8.30 rows=1 width=4)
         Index Cond: (id = members.user_id)
         Filter: ((state)::text = 'active'::text)
(7 rows)

Why was this MR needed?

Speed up project creation.

Screenshots (if relevant)

Does this MR meet the acceptance criteria?

What are the relevant issue numbers?

Related to #34533 (closed)

Closes #36182 (closed)

Edited by Nick Thomas

Merge request reports