Add group_id argument to GraphQL Query.users schema
What does this MR do and why?
To support the edit branch rules feature the frontend team needs to be able to search for users within a group. We currently have an endpoint to search for users but this endpoint does not support filtering by a group.
https://docs.gitlab.com/ee/api/graphql/reference/#queryusers
We adding a new group_id argument to this endpoint
Screenshots or screen recordings
query TestQuery($groupId: ID!) {
users(groupId: $groupId) {
count
nodes {
username
}
}
}query plan
For filter users by direct or inherited group membership:
Query example
SELECT "users".* FROM "users" WHERE "users"."id" IN (WITH "group_hierarchy" AS MATERIALIZED (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{9970}') OR 1=0)), "descendant_projects" AS MATERIALIZED (SELECT "projects"."id" FROM "projects" WHERE "projects"."namespace_id" IN (SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{9970}')))) SELECT "members"."user_id" FROM ((SELECT "members"."user_id" FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."source_id" IN (SELECT "id" FROM "group_hierarchy") AND "members"."state" = 0 AND "members"."requested_at" IS NULL AND "members"."invite_token" IS NULL AND (members.access_level > 5))
UNION ALL
(SELECT "members"."user_id" FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."source_id" IN (SELECT "group_group_links"."shared_with_group_id" FROM "group_group_links" WHERE "group_group_links"."shared_group_id" IN (SELECT "id" FROM "group_hierarchy")) AND "members"."state" = 0 AND "members"."requested_at" IS NULL AND "members"."invite_token" IS NULL AND (members.access_level > 5))
UNION ALL
(SELECT "members"."user_id" FROM "members" WHERE "members"."type" = 'ProjectMember' AND "members"."source_type" = 'Project' AND "members"."source_id" IN (SELECT "id" FROM "descendant_projects") AND "members"."state" = 0 AND "members"."requested_at" IS NULL AND "members"."invite_token" IS NULL AND (members.access_level > 5))
UNION ALL
(SELECT "members"."user_id" FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."source_id" IN (SELECT "project_group_links"."group_id" FROM "project_group_links" WHERE "project_group_links"."project_id" IN (SELECT "id" FROM "descendant_projects")) AND "members"."state" = 0 AND "members"."requested_at" IS NULL AND "members"."invite_token" IS NULL AND (members.access_level > 5))) members) AND (LOWER("users"."username") IN (LOWER('gitlab'))) ORDER BY "users"."id" DESC Full execution plan
Merge Join (cost=54869.10..54870.12 rows=1 width=1452) (actual time=16390.780..16390.803 rows=0 loops=1)
Merge Cond: (users.id = members_1.user_id)
Buffers: shared hit=39058 read=9278 dirtied=457
I/O Timings: read=15918.480 write=0.000
-> Sort (cost=3.59..3.59 rows=1 width=1452) (actual time=10.923..10.925 rows=1 loops=1)
Sort Key: users.id DESC
Sort Method: quicksort Memory: 26kB
Buffers: shared read=5
I/O Timings: read=10.794 write=0.000
-> Index Scan using index_on_users_lower_username on public.users (cost=0.56..3.58 rows=1 width=1452) (actual time=10.891..10.895 rows=1 loops=1)
Index Cond: (lower((users.username)::text) = 'gitlab'::text)
Buffers: shared read=5
I/O Timings: read=10.794 write=0.000
-> Sort (cost=54865.51..54866.01 rows=200 width=4) (actual time=16379.400..16379.621 rows=3159 loops=1)
Sort Key: members_1.user_id DESC
Sort Method: quicksort Memory: 248kB
Buffers: shared hit=39058 read=9273 dirtied=457
I/O Timings: read=15907.686 write=0.000
-> HashAggregate (cost=54855.87..54857.87 rows=200 width=4) (actual time=16377.677..16378.317 rows=3225 loops=1)
Group Key: members_1.user_id
Buffers: shared hit=39058 read=9273 dirtied=457
I/O Timings: read=15907.686 write=0.000
-> Append (cost=3128.66..54173.33 rows=54603 width=4) (actual time=1254.161..16355.346 rows=14377 loops=1)
Buffers: shared hit=39058 read=9273 dirtied=457
I/O Timings: read=15907.686 write=0.000
CTE group_hierarchy
-> Bitmap Heap Scan on public.namespaces (cost=133.94..853.97 rows=460 width=4) (actual time=148.802..1240.937 rows=771 loops=1)
Buffers: shared hit=1 read=732 dirtied=44
I/O Timings: read=1224.529 write=0.000
-> Bitmap Index Scan using index_namespaces_on_traversal_ids_for_groups (cost=0.00..133.83 rows=460 width=0) (actual time=142.215..142.216 rows=771 loops=1)
Index Cond: (namespaces.traversal_ids @> '{9970}'::integer[])
Buffers: shared hit=1 read=86
I/O Timings: read=137.573 write=0.000
CTE descendant_projects
-> Nested Loop (cost=855.69..2263.77 rows=11291 width=4) (actual time=9.781..1709.378 rows=3523 loops=1)
Buffers: shared hit=6402 read=1119 dirtied=137
I/O Timings: read=1668.275 write=0.000
-> HashAggregate (cost=855.12..859.72 rows=460 width=28) (actual time=3.453..4.714 rows=771 loops=1)
Group Key: namespaces_1.traversal_ids[array_length(namespaces_1.traversal_ids, 1)]
Buffers: shared hit=733
I/O Timings: read=0.000 write=0.000
-> Bitmap Heap Scan on public.namespaces namespaces_1 (cost=133.94..853.97 rows=460 width=28) (actual time=1.695..3.068 rows=771 loops=1)
Buffers: shared hit=733
I/O Timings: read=0.000 write=0.000
-> Bitmap Index Scan using index_namespaces_on_traversal_ids_for_groups (cost=0.00..133.83 rows=460 width=0) (actual time=1.588..1.588 rows=771 loops=1)
Index Cond: (namespaces_1.traversal_ids @> '{9970}'::integer[])
Buffers: shared hit=87
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using index_projects_on_namespace_id_and_id on public.projects (cost=0.56..2.80 rows=25 width=8) (actual time=1.317..2.206 rows=5 loops=771)
Index Cond: (projects.namespace_id = (namespaces_1.traversal_ids)[array_length(namespaces_1.traversal_ids, 1)])
Heap Fetches: 345
Buffers: shared hit=5669 read=1119 dirtied=137
I/O Timings: read=1668.275 write=0.000
-> Nested Loop (cost=10.92..434.35 rows=150 width=4) (actual time=1254.158..4281.315 rows=5204 loops=1)
Buffers: shared hit=4971 read=2031 dirtied=155
I/O Timings: read=4182.201 write=0.000
-> HashAggregate (cost=10.35..12.35 rows=200 width=4) (actual time=1245.343..1246.719 rows=771 loops=1)
Group Key: group_hierarchy.id
Buffers: shared hit=1 read=732 dirtied=44
I/O Timings: read=1224.529 write=0.000
-> CTE Scan on group_hierarchy (cost=0.00..9.20 rows=460 width=4) (actual time=148.809..1243.068 rows=771 loops=1)
Buffers: shared hit=1 read=732 dirtied=44
I/O Timings: read=1224.529 write=0.000
-> Index Only Scan using index_members_on_source_state_type_access_level_and_user_id on public.members members_1 (cost=0.56..2.10 rows=1 width=8) (actual time=2.502..3.931 rows=7 loops=771)
Index Cond: ((members_1.source_id = group_hierarchy.id) AND (members_1.source_type = 'Namespace'::text) AND (members_1.state = 0) AND (members_1.type = 'GroupMember'::text) AND (members_1.access_level > 5))
Heap Fetches: 487
Buffers: shared hit=4970 read=1299 dirtied=111
I/O Timings: read=2957.671 write=0.000
-> Nested Loop (cost=380.25..2579.27 rows=717 width=4) (actual time=641.075..686.962 rows=367 loops=1)
Buffers: shared hit=2422 read=293 dirtied=7
I/O Timings: read=673.726 write=0.000
-> HashAggregate (cost=379.69..390.49 rows=1080 width=8) (actual time=640.978..641.015 rows=28 loops=1)
Group Key: group_group_links.shared_with_group_id
Buffers: shared hit=2063 read=265 dirtied=2
I/O Timings: read=628.894 write=0.000
-> Nested Loop (cost=10.77..376.99 rows=1080 width=8) (actual time=34.778..640.751 rows=53 loops=1)
Buffers: shared hit=2063 read=265 dirtied=2
I/O Timings: read=628.894 write=0.000
-> HashAggregate (cost=10.35..12.35 rows=200 width=4) (actual time=0.375..1.237 rows=771 loops=1)
Group Key: group_hierarchy_1.id
I/O Timings: read=0.000 write=0.000
-> CTE Scan on group_hierarchy group_hierarchy_1 (cost=0.00..9.20 rows=460 width=4) (actual time=0.002..0.102 rows=771 loops=1)
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using index_group_group_links_on_shared_group_and_shared_with_group on public.group_group_links (cost=0.42..1.80 rows=2 width=16) (actual time=0.824..0.828 rows=0 loops=771)
Index Cond: (group_group_links.shared_group_id = group_hierarchy_1.id)
Heap Fetches: 11
Buffers: shared hit=2063 read=265 dirtied=2
I/O Timings: read=628.894 write=0.000
-> Index Only Scan using index_members_on_source_state_type_access_level_and_user_id on public.members members_2 (cost=0.56..2.02 rows=1 width=8) (actual time=0.685..1.637 rows=13 loops=28)
Index Cond: ((members_2.source_id = group_group_links.shared_with_group_id) AND (members_2.source_type = 'Namespace'::text) AND (members_2.state = 0) AND (members_2.type = 'GroupMember'::text) AND (members_2.access_level > 5))
Heap Fetches: 36
Buffers: shared hit=359 read=28 dirtied=5
I/O Timings: read=44.832 write=0.000
-> Nested Loop (cost=254.61..700.55 rows=46050 width=4) (actual time=1729.770..8643.362 rows=3073 loops=1)
Buffers: shared hit=19041 read=5188 dirtied=239
I/O Timings: read=8371.457 write=0.000
-> HashAggregate (cost=254.05..256.05 rows=200 width=4) (actual time=1717.725..1724.969 rows=3523 loops=1)
Group Key: descendant_projects.id
Buffers: shared hit=6402 read=1119 dirtied=137
I/O Timings: read=1668.275 write=0.000
-> CTE Scan on descendant_projects (cost=0.00..225.82 rows=11291 width=4) (actual time=9.786..1712.825 rows=3523 loops=1)
Buffers: shared hit=6402 read=1119 dirtied=137
I/O Timings: read=1668.275 write=0.000
-> Index Only Scan using index_members_on_source_state_type_access_level_and_user_id on public.members members_3 (cost=0.56..2.18 rows=4 width=8) (actual time=1.826..1.959 rows=1 loops=3523)
Index Cond: ((members_3.source_id = descendant_projects.id) AND (members_3.source_type = 'Project'::text) AND (members_3.state = 0) AND (members_3.type = 'ProjectMember'::text) AND (members_3.access_level > 5))
Heap Fetches: 307
Buffers: shared hit=12639 read=4069 dirtied=102
I/O Timings: read=6703.182 write=0.000
-> Nested Loop (cost=1105.94..46522.38 rows=7686 width=4) (actual time=2528.229..2739.681 rows=5733 loops=1)
Buffers: shared hit=12624 read=1761 dirtied=56
I/O Timings: read=2680.302 write=0.000
-> HashAggregate (cost=1105.38..1339.50 rows=23412 width=4) (actual time=2526.725..2526.956 rows=89 loops=1)
Group Key: project_group_links.group_id
Buffers: shared hit=9298 read=1551 dirtied=1
I/O Timings: read=2478.350 write=0.000
-> Nested Loop (cost=254.47..1046.85 rows=23412 width=4) (actual time=83.320..2525.261 rows=282 loops=1)
Buffers: shared hit=9298 read=1551 dirtied=1
I/O Timings: read=2478.350 write=0.000
-> HashAggregate (cost=254.05..256.05 rows=200 width=4) (actual time=1.670..5.663 rows=3523 loops=1)
Group Key: descendant_projects_1.id
I/O Timings: read=0.000 write=0.000
-> CTE Scan on descendant_projects descendant_projects_1 (cost=0.00..225.82 rows=11291 width=4) (actual time=0.002..0.360 rows=3523 loops=1)
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_project_group_links_on_project_id on public.project_group_links (cost=0.42..3.93 rows=2 width=8) (actual time=0.671..0.714 rows=0 loops=3523)
Index Cond: (project_group_links.project_id = descendant_projects_1.id)
Buffers: shared hit=9298 read=1551 dirtied=1
I/O Timings: read=2478.350 write=0.000
-> Index Only Scan using index_members_on_source_state_type_access_level_and_user_id on public.members members_4 (cost=0.56..1.92 rows=1 width=8) (actual time=0.207..2.381 rows=64 loops=89)
Index Cond: ((members_4.source_id = project_group_links.group_id) AND (members_4.source_type = 'Namespace'::text) AND (members_4.state = 0) AND (members_4.type = 'GroupMember'::text) AND (members_4.access_level > 5))
Heap Fetches: 521
Buffers: shared hit=3326 read=210 dirtied=55
I/O Timings: read=201.952 write=0.000
Summary:
Time: 16.417 s
- planning: 24.226 ms
- execution: 16.393 s
- I/O read: 15.918 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 39058 (~305.10 MiB) from the buffer pool
- reads: 9278 (~72.50 MiB) from the OS file cache, including disk I/O
- dirtied: 457 (~3.60 MiB)
- writes: 0
It's a lot be check inherited memberships makes it complex to optimize more. Moreover do remember that this query is automatically limited by the resolver connection_type downstream. For more details see:
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.
Closes #428742 (closed)
Edited by Patrick Cyiza