Replace Group ancestors scope with linear version
-
Review changes -
-
Download -
Patches
-
Plain diff
What does this MR do and why?
In this MR, we're switching the behavior of the method Group#ids_with_disabled_email to use the linear version. The new behavior is behind the linear_group_ancestor_scopes feature flag.
Database changes
In these examples, we're passing 6k groups to the query to test it with heavy data.
The old query was:
SELECT "id"
FROM
(SELECT *
FROM namespaces) AS namespaces_with_emails_disabled
WHERE "namespaces_with_emails_disabled"."id" IN
(SELECT "namespaces"."id"
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" = 1614863
AND "members"."requested_at" IS NULL
AND (access_level >= 10))
AND (EXISTS
(WITH RECURSIVE "base_and_ancestors" AS (
(SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND (id = namespaces_with_emails_disabled.id))
UNION
(SELECT "namespaces".*
FROM "namespaces",
"base_and_ancestors"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = "base_and_ancestors"."parent_id")) SELECT 1
FROM "base_and_ancestors" AS "namespaces"
WHERE "namespaces"."emails_disabled" = TRUE
LIMIT 1))This is the query plan and the execution times:
Time: 208.663 ms
- planning: 1.798 ms
- execution: 206.865 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 90505 (~707.10 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0The new query is:
SELECT "id"
FROM
(SELECT *
FROM namespaces) AS namespaces_with_emails_disabled
WHERE "namespaces_with_emails_disabled"."id" IN
(SELECT "namespaces"."id"
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" = 1614863
AND "members"."requested_at" IS NULL
AND (access_level >= 10))
AND (EXISTS
(SELECT 1
FROM
(SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."id" IN
(SELECT unnest(traversal_ids)
FROM "namespaces"
WHERE (id = namespaces_with_emails_disabled.id))) namespaces
WHERE "namespaces"."emails_disabled" = TRUE
LIMIT 1))This is the query plan and the execution times:
Time: 117.777 ms
- planning: 1.419 ms
- execution: 116.358 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 115075 (~899.00 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0How to set up and validate locally
Example below:
-
Enable the feature flag for linear scopes
Feature.enable(:use_traversal_ids) -
Enable the feature flag for linear ancestors scopes
Feature.enable(:use_traversal_ids_for_ancestor_scopes) -
Enable the feature flag for
Ci::Runnerlinear ancestors scopesFeature.enable(:linear_group_ancestor_scopes) -
Execute the following in the Rails console with the proper id depending on the scope testing:
Group.ids_with_disabled_email(User.first.groups)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 #339194 (closed)
Merge request reports
- latest versionbd908d321 commit,
- version 102e7ea3b61 commit,
- version 9687665881 commit,
- version 801118f732 commits,
- version 7df5235a22 commits,
- version 60994733b2 commits,
- version 5f5277bee2 commits,
- version 4a46febf11 commit,
- version 3d92dd8b91 commit,
- version 228bdbd051 commit,
- version 1e71265751 commit,
- Side-by-side
- Inline