Optimize `standardRoles.membersCount` query
What does this MR do and why?
The Roles and Permissions page is failing to load for large group hierarchies due to a poorly performing SQL query. The analysis of the poorly performing query can be found here.
This MR attempts to optimize this query to work efficiently for large group hierarchies with a lot of members. The group id used in the examples below have 5052258 members records.
I tried optimizing this query using a several different options like an OR
query, UNION ALL
query, Recursive CTE, Window Function, and I found that executing two separate queries (1 for group memberships + 1 for project memberships) and merging the results in memory seemed to provide the best combination of speed and low memory.
Before:
SELECT
"members"."access_level",
COUNT(*) AS members_count
FROM
(
(
SELECT
"members"."id",
"members"."access_level",
"members"."source_id",
"members"."source_type",
"members"."user_id",
"members"."notification_level",
"members"."type",
"members"."created_at",
"members"."updated_at",
"members"."created_by_id",
"members"."invite_email",
"members"."invite_token",
"members"."invite_accepted_at",
"members"."requested_at",
"members"."expires_at",
"members"."ldap",
"members"."override",
"members"."invite_email_success",
"members"."state",
"members"."member_namespace_id",
"members"."member_role_id",
"members"."expiry_notified_at",
"members"."request_accepted_at",
"members"."last_activity_on"
FROM
"members"
WHERE
"members"."source_type" = 'Namespace'
AND "members"."source_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (traversal_ids @> ('{70960922}'))
)
)
UNION
(
SELECT
"members"."id",
"members"."access_level",
"members"."source_id",
"members"."source_type",
"members"."user_id",
"members"."notification_level",
"members"."type",
"members"."created_at",
"members"."updated_at",
"members"."created_by_id",
"members"."invite_email",
"members"."invite_token",
"members"."invite_accepted_at",
"members"."requested_at",
"members"."expires_at",
"members"."ldap",
"members"."override",
"members"."invite_email_success",
"members"."state",
"members"."member_namespace_id",
"members"."member_role_id",
"members"."expiry_notified_at",
"members"."request_accepted_at",
"members"."last_activity_on"
FROM
"members"
WHERE
"members"."source_type" = 'Project'
AND "members"."source_id" IN (
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 @> ('{70960922}'))
)
)
)
) members
WHERE
"members"."member_role_id" IS NULL
GROUP BY
"members"."access_level";
Time: 3.023 min
- planning: 6.193 ms
- execution: 3.023 min
- I/O read: 2.560 min
- I/O write: 1.673 s
Shared buffers:
- hits: 986002 (~7.50 GiB) from the buffer pool
- reads: 1298012 (~9.90 GiB) from the OS file cache, including disk I/O
- dirtied: 136392 (~1.00 GiB)
- writes: 65579 (~512.30 MiB)
Temp buffers:
- reads: 128412 (~1003.20 MiB)
- writes: 187185 (~1.40 GiB)
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32671/commands/100775
After: (using two queries with results merged in memory)
WITH "namespace_ids" AS (
SELECT traversal_ids [array_length(traversal_ids, 1)] AS id
FROM "namespaces"
WHERE (traversal_ids @> ('{70960922}'))
)
SELECT
COUNT(*) AS "count_all",
"members"."access_level" AS "members_access_level"
FROM "members"
INNER JOIN "namespace_ids" ON "members"."source_id" = "namespace_ids"."id" AND "members"."source_type" = 'Namespace'
WHERE "members"."member_role_id" IS NULL
GROUP BY "members"."access_level"
Time: 340.397 ms
- planning: 45.190 ms
- execution: 295.207 ms
- I/O read: 703.703 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 52503 (~410.20 MiB) from the buffer pool
- reads: 19104 (~149.30 MiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32916/commands/101482
WITH "namespace_ids" AS (
SELECT traversal_ids [array_length(traversal_ids, 1)] AS id
FROM "namespaces"
WHERE (traversal_ids @> ('{70960922}'))
),
"project_ids" AS (
SELECT "projects"."id"
FROM "projects"
INNER JOIN "namespace_ids" ON "projects"."namespace_id" = "namespace_ids"."id"
WHERE "projects"."marked_for_deletion_at" IS NULL
AND "projects"."pending_delete" = FALSE
)
SELECT COUNT(*) AS "count_all", "members"."access_level" AS "members_access_level"
FROM "members"
INNER JOIN "project_ids" ON "members"."source_id" = "project_ids"."id" AND "members"."source_type" = 'Project'
WHERE "members"."member_role_id" IS NULL
GROUP BY "members"."access_level"
Time: 12.586 s
- planning: 106.385 ms
- execution: 12.480 s
- I/O read: 31.014 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 1296360 (~9.90 GiB) from the buffer pool
- reads: 1437312 (~11.00 GiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/32916/commands/101483
MR acceptance checklist
Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Screenshots or screen recordings
Before | After |
---|---|