Optimize DB Query Count on GraphQL Group Connection Authorization
What does this MR do and why?
Related to #26732 (comment 709635169)
GraphQL node type authorization can be a bit heavy on the DB as it authorizes each element with all the policy rules. For a list of groups this might be a bit too much for the DB, so we need to optimize the node authorization process by preloading some information in batch to each of the nodes.
Kibana Logs for GraphQL Query Making These DB Calls
Visualization - https://log.gprd.gitlab.net/goto/bcbb10ebddea883ae307ab2f2ed020a0
Example Query (with FF paginatable_namespace_drop_down_for_project_creation enabled)
{
currentUser {
groups(permissionScope: CREATE_PROJECTS) {
nodes {
fullPath
path
name
webUrl
}
}
}
}Implemented enhancements:
Batch load root_ancestor
Database Plans
https://console.postgres.ai/shared/12c096c8-7f53-46dc-8270-a400acfc07e9
RAW SQL
SELECT namespaces.*,
root_query.id AS source_id
FROM "namespaces"
inner join (SELECT id,
traversal_ids[1] AS root_id
FROM "namespaces"
WHERE "namespaces"."id" IN ( 1986712, 2564205, 2917345,
2930755,
3753760, 3758886, 4408371,
4654006,
5223290, 5324448, 5419560,
5594517,
5595721, 5821789, 6097904,
6097910,
6143638, 6215292, 6883398,
6883788,
6884525, 6922551, 6943469,
6970315,
6997001, 7055722, 7273911,
7383458,
7484409, 7741629, 7889310,
7889311,
7912617, 8203083, 8245543,
8247644,
8306194, 8340888, 8374902,
8375111,
8376764, 8376775, 8376778,
8376781,
8376785, 8376790, 8376794,
8376797,
8376800, 8376808, 8413846,
8467042,
8496639, 8496641, 8684971,
9183086,
9279587, 9428691, 9494491,
9721411,
9930623, 10065358, 10127741,
10187265,
10349459, 10369362, 10633276,
10847505
,
10929210, 10943435, 10943440,
10943443
,
10991043, 11208347, 11269473,
11294029
,
11336947, 11345530, 11500744,
11553555
,
11563130, 11663343, 11975325,
12128712
,
12270422, 12398683, 12438815,
12488514
,
12491278, 12560957, 12701569,
12799249
,
12861633, 12923347, 13078431,
13127444
,
13342885, 13463136, 13610804,
13738791
)
AND "namespaces"."type" = 'Group') AS root_query
ON root_query.root_id = namespaces.idUse traversal_ids to preload max_member_access for a user on a group, including it's ancestors (previously only direct memberships to the group were preloaded).
Database Plans
https://console.postgres.ai/shared/15f546e5-fb6b-48ec-8e6c-ceb6dbf9cafa (duplicate AND (members.access_level > 5) removed from query)
RAW SQL
SELECT Max("members"."access_level") AS maximum_access_level,
"hierarchy"."id" AS hierarchy_id
FROM "members"
LEFT OUTER JOIN "users"
ON "members"."user_id" = "users"."id"
INNER JOIN (SELECT id,
Unnest(traversal_ids) AS traversal_id
FROM "namespaces"
WHERE "namespaces"."id" IN ( 10943443, 10943435, 7889310,
10943440,
12438815, 9721411, 10127741,
8376808,
10991043, 8376800, 8376797,
2917345,
8467042, 2930755, 8376794,
5594517,
8376778, 8376764, 8376781,
8376785,
8375111, 8376775, 3753760,
8376790,
13127444, 10633276, 5324448,
7055722,
9494491, 8306194, 12398683,
1986712,
8496641, 4408371, 11345530,
6215292,
6884525, 9279587, 11294029,
5419560,
13610804, 13078431, 6883788,
10369362,
7484409, 12128712, 9183086,
6922551,
3758886, 7912617, 13738791,
7273911,
5821789, 10929210, 8340888,
6097910,
8247644, 7889311, 12491278,
12799249,
5595721, 2564205, 7741629,
6883398,
11208347, 12701569, 11500744,
11269473
,
11553555, 8413846, 6997001,
7383458,
8496639, 5223290, 6097904,
8374902,
11336947, 10187265, 9930623,
10349459,
12861633, 12923347, 6970315,
12488514,
12560957, 11663343, 9428691,
4654006,
11563130, 11975325, 10065358,
10847505
,
8684971, 6143638, 12270422,
13342885,
8203083, 8245543, 6943469,
13463136 ))
AS
hierarchy
ON members.source_id = hierarchy.traversal_id
WHERE "members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "users"."state" = 'active'
AND "members"."requested_at" IS NULL
AND "members"."invite_token" IS NULL
AND ( members.access_level > 5 )
AND "members"."user_id" = 8110537
GROUP BY "hierarchy"."id" How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #343815 (closed)
Edited by Krasimir Angelov