Use MembersFinder ancestors linear scopes
What does this MR do and why?
In this MR, we're switching the behavior of the method MembersFinder#project_invited_groups to use the linear version. The new behavior is behind the linear_members_finder_ancestor_scopes feature flag.
Database queries
The queries have been tested using a group with 21 parents.
The original query was:
SELECT "members".*
FROM
(SELECT DISTINCT ON (user_id,
invite_email) member_union.id,
COALESCE(project_authorizations.access_level, member_union.access_level) access_level,
member_union.source_id,
member_union.source_type,
member_union.user_id,
member_union.notification_level,
member_union.type,
member_union.created_at,
member_union.updated_at,
member_union.created_by_id,
member_union.invite_email,
member_union.invite_token,
member_union.invite_accepted_at,
member_union.requested_at,
member_union.expires_at,
member_union.ldap,
member_union.override,
member_union.state,
member_union.invite_email_success
FROM (
(SELECT "members".*
FROM
(SELECT DISTINCT ON (user_id,
invite_email) *
FROM "members"
WHERE "members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL
AND "members"."source_id" IN
(SELECT "namespaces"."id"
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 22)
AND (members.access_level > 5)
ORDER BY user_id,
invite_email,
access_level DESC, expires_at DESC, created_at ASC) members
WHERE "members"."type" = 'GroupMember'
AND "members"."invite_token" IS NULL
AND (members.access_level > 5))
UNION ALL
(SELECT "members".*
FROM "members"
WHERE "members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."source_id" IN
(WITH RECURSIVE "base_and_ancestors" AS (
(SELECT "namespaces".*
FROM "namespaces"
INNER JOIN "project_group_links" ON "namespaces"."id" = "project_group_links"."group_id"
WHERE "namespaces"."type" = 'Group'
AND "project_group_links"."project_id" = 1)
UNION
(SELECT "namespaces".*
FROM "namespaces",
"base_and_ancestors"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = "base_and_ancestors"."parent_id")) SELECT "id"
FROM "base_and_ancestors" AS "namespaces"
WHERE ("namespaces"."visibility_level" IN (0,
10,
20)
OR EXISTS
(SELECT 1
FROM
(SELECT "namespaces".*
FROM (
(WITH "direct_groups" AS MATERIALIZED
(SELECT "namespaces".*
FROM (
(SELECT "namespaces".*
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" = 1
AND "members"."requested_at" IS NULL
AND (access_level >= 10))
UNION
(SELECT "namespaces".*
FROM "projects"
INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
WHERE "project_authorizations"."user_id" = 1)) namespaces
WHERE "namespaces"."type" = 'Group') SELECT "namespaces".*
FROM (
(SELECT "namespaces".*
FROM "direct_groups" "namespaces"
WHERE "namespaces"."type" = 'Group')
UNION
(SELECT "namespaces".*
FROM "namespaces"
INNER JOIN "group_group_links" ON "group_group_links"."shared_group_id" = "namespaces"."id"
WHERE "namespaces"."type" = 'Group'
AND "group_group_links"."shared_with_group_id" IN
(SELECT "namespaces"."id"
FROM "direct_groups" "namespaces"
WHERE "namespaces"."type" = 'Group'))) namespaces
WHERE "namespaces"."type" = 'Group')) namespaces
WHERE "namespaces"."type" = 'Group') authorized
WHERE authorized."id" = "namespaces"."id")))
AND (members.access_level > 5))) AS member_union
LEFT JOIN users ON users.id = member_union.user_id
LEFT JOIN project_authorizations ON project_authorizations.user_id = users.id
AND project_authorizations.project_id = 1
ORDER BY user_id,
invite_email,
CASE
WHEN TYPE = 'ProjectMember' THEN 1
WHEN TYPE = 'GroupMember' THEN 2
ELSE 3
END) AS membersThis is the query plan and the times are:
Time: 16.557 ms
- planning: 14.567 ms
- execution: 1.990 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 47 (~376.00 KiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0The new query is:
SELECT "members".*
FROM
(SELECT DISTINCT ON (user_id,
invite_email) member_union.id,
COALESCE(project_authorizations.access_level, member_union.access_level) access_level,
member_union.source_id,
member_union.source_type,
member_union.user_id,
member_union.notification_level,
member_union.type,
member_union.created_at,
member_union.updated_at,
member_union.created_by_id,
member_union.invite_email,
member_union.invite_token,
member_union.invite_accepted_at,
member_union.requested_at,
member_union.expires_at,
member_union.ldap,
member_union.override,
member_union.state,
member_union.invite_email_success
FROM (
(SELECT "members".*
FROM
(SELECT DISTINCT ON (user_id,
invite_email) *
FROM "members"
WHERE "members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL
AND "members"."source_id" IN
(SELECT "namespaces"."id"
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 22)
AND (members.access_level > 5)
ORDER BY user_id,
invite_email,
access_level DESC, expires_at DESC, created_at ASC) members
WHERE "members"."type" = 'GroupMember'
AND "members"."invite_token" IS NULL
AND (members.access_level > 5))
UNION ALL
(SELECT "members".*
FROM "members"
WHERE "members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."source_id" IN
(SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id
FROM
(SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."id" IN
(SELECT unnest(traversal_ids)
FROM "namespaces"
INNER JOIN "project_group_links" ON "namespaces"."id" = "project_group_links"."group_id"
WHERE "project_group_links"."project_id" = 1)) namespaces
WHERE ("namespaces"."visibility_level" IN (0,
10,
20)
OR EXISTS
(SELECT 1
FROM
(SELECT "namespaces".*
FROM (
(WITH "direct_groups" AS MATERIALIZED
(SELECT "namespaces".*
FROM (
(SELECT "namespaces".*
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" = 1
AND "members"."requested_at" IS NULL
AND (access_level >= 10))
UNION
(SELECT "namespaces".*
FROM "projects"
INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
WHERE "project_authorizations"."user_id" = 1)) namespaces
WHERE "namespaces"."type" = 'Group') SELECT "namespaces".*
FROM (
(SELECT "namespaces".*
FROM "direct_groups" "namespaces"
WHERE "namespaces"."type" = 'Group')
UNION
(SELECT "namespaces".*
FROM "namespaces"
INNER JOIN "group_group_links" ON "group_group_links"."shared_group_id" = "namespaces"."id"
WHERE "namespaces"."type" = 'Group'
AND "group_group_links"."shared_with_group_id" IN
(SELECT "namespaces"."id"
FROM "direct_groups" "namespaces"
WHERE "namespaces"."type" = 'Group'))) namespaces
WHERE "namespaces"."type" = 'Group')) namespaces
WHERE "namespaces"."type" = 'Group') authorized
WHERE authorized."id" = "namespaces"."id")))
AND (members.access_level > 5))) AS member_union
LEFT JOIN users ON users.id = member_union.user_id
LEFT JOIN project_authorizations ON project_authorizations.user_id = users.id
AND project_authorizations.project_id = 1
ORDER BY user_id,
invite_email,
CASE
WHEN TYPE = 'ProjectMember' THEN 1
WHEN TYPE = 'GroupMember' THEN 2
ELSE 3
END) AS membersThis is the query plan and the times are:
Time: 13.389 ms
- planning: 12.031 ms
- execution: 1.358 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 47 (~376.00 KiB) 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
-
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
MembersFinderlinear ancestors scopesFeature.enable(:linear_members_finder_ancestor_scopes) -
Execute the following in the Rails console with the proper id depending on the scope testing:
MembersFinder.new(Project.first, User.first).execute(include_relations: [:invited_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 #339227 (closed)
Edited by Francisco Javier López (ex-Gitlab)