Refactoring and improvements for ee/lib/ee/api/members.rb
What does this MR do?
This MR introduces some refactoring and code improvements, following some work done recently.
The list of introduced improvements can be found in the related issue.
DB queries and plans
- loading
group.billed_user_ids: 4 queries, related to these lines of codes:
# ee/app/models/ee/group.rb:310
strong_memoize(:non_gold_billed_user_ids) do
(billed_group_members.distinct.pluck(:user_id) +
billed_project_members.distinct.pluck(:user_id) +
billed_shared_group_members.distinct.pluck(:user_id) +
billed_invited_group_to_project_members.distinct.pluck(:user_id)).to_set
end1st query:
SELECT DISTINCT
"members"."user_id"
FROM
"members"
LEFT OUTER JOIN "users" ON "members"."user_id" = "users"."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"."source_id" IN ( WITH RECURSIVE "base_and_descendants" AS ((
SELECT
"namespaces".*
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" = 21)
UNION (
SELECT
"namespaces".*
FROM
"namespaces",
"base_and_descendants"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"))
SELECT
"namespaces"."id"
FROM
"base_and_descendants" AS "namespaces") QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Sort (cost=146.63..148.65 rows=808 width=138) (actual time=55.009..55.012 rows=71 loops=1)
Sort Key: n.nspname, p.proname, (pg_get_function_arguments(p.oid))
Sort Method: quicksort Memory: 43kB
-> Hash Join (cost=1.14..107.61 rows=808 width=138) (actual time=42.495..54.854 rows=71 loops=1)
Hash Cond: (p.pronamespace = n.oid)
-> Seq Scan on pg_proc p (cost=0.00..89.30 rows=808 width=78) (actual time=0.052..53.465 rows=2402 loops=1)
Filter: pg_function_is_visible(oid)
-> Hash (cost=1.09..1.09 rows=4 width=68) (actual time=0.011..0.011 rows=4 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on pg_namespace n (cost=0.00..1.09 rows=4 width=68) (actual time=0.005..0.007 rows=4 loops=1)
Filter: ((nspname <> 'pg_catalog'::name) AND (nspname <> 'information_schema'::name))link to depesz.com: https://explain.depesz.com/s/KE7D
2nd query:
SELECT DISTINCT
"members"."user_id"
FROM
"members"
LEFT OUTER JOIN "users" ON "members"."user_id" = "users"."id"
WHERE
"members"."type" = 'ProjectMember'
AND "members"."source_type" = 'Project'
AND "users"."state" = 'active'
AND "members"."requested_at" IS NULL
AND "members"."invite_token" IS NULL
AND (members.access_level > 5)
AND ("users"."user_type" IS NULL
OR "users"."user_type" != 6)
AND "members"."source_id" IN (
SELECT
"projects"."id"
FROM
"projects"
INNER JOIN "namespaces" ON "namespaces"."type" = 'Group'
AND "namespaces"."id" = "projects"."namespace_id"
AND "namespaces"."type" = 'Group'
WHERE
"projects"."namespace_id" IN ( WITH RECURSIVE "base_and_descendants" AS ((
SELECT
"namespaces".*
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" = 21)
UNION (
SELECT
"namespaces".*
FROM
"namespaces",
"base_and_descendants"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"))
SELECT
id
FROM
"base_and_descendants" AS "namespaces")) QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Sort (cost=146.63..148.65 rows=808 width=138) (actual time=55.009..55.012 rows=71 loops=1)
Sort Key: n.nspname, p.proname, (pg_get_function_arguments(p.oid))
Sort Method: quicksort Memory: 43kB
-> Hash Join (cost=1.14..107.61 rows=808 width=138) (actual time=42.495..54.854 rows=71 loops=1)
Hash Cond: (p.pronamespace = n.oid)
-> Seq Scan on pg_proc p (cost=0.00..89.30 rows=808 width=78) (actual time=0.052..53.465 rows=2402 loops=1)
Filter: pg_function_is_visible(oid)
-> Hash (cost=1.09..1.09 rows=4 width=68) (actual time=0.011..0.011 rows=4 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on pg_namespace n (cost=0.00..1.09 rows=4 width=68) (actual time=0.005..0.007 rows=4 loops=1)
Filter: ((nspname <> 'pg_catalog'::name) AND (nspname <> 'information_schema'::name))link to depesz.com: https://explain.depesz.com/s/aIJc
3rd query:
SELECT DISTINCT
"members"."user_id"
FROM
"members"
LEFT OUTER JOIN "users" ON "members"."user_id" = "users"."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"."source_id" IN ( WITH RECURSIVE "base_and_ancestors" AS ((
SELECT
"namespaces".*
FROM
"namespaces"
INNER JOIN "group_group_links" ON "group_group_links"."shared_with_group_id" = "namespaces"."id"
WHERE
"namespaces"."type" = 'Group'
AND "group_group_links"."shared_group_id" IN ( WITH RECURSIVE "base_and_descendants" AS ((
SELECT
"namespaces".*
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" = 21)
UNION (
SELECT
"namespaces".*
FROM
"namespaces",
"base_and_descendants"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."parent_id" = "base_and_descendants"."id"))
SELECT
"namespaces"."id"
FROM
"base_and_descendants" AS "namespaces"))
UNION (
SELECT
"namespaces".*
FROM
"namespaces",
"base_and_ancestors"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" = "base_and_ancestors"."parent_id"))
SELECT
"namespaces"."id"
FROM
"base_and_ancestors" AS "namespaces") QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Sort (cost=146.63..148.65 rows=808 width=138) (actual time=55.009..55.012 rows=71 loops=1)
Sort Key: n.nspname, p.proname, (pg_get_function_arguments(p.oid))
Sort Method: quicksort Memory: 43kB
-> Hash Join (cost=1.14..107.61 rows=808 width=138) (actual time=42.495..54.854 rows=71 loops=1)
Hash Cond: (p.pronamespace = n.oid)
-> Seq Scan on pg_proc p (cost=0.00..89.30 rows=808 width=78) (actual time=0.052..53.465 rows=2402 loops=1)
Filter: pg_function_is_visible(oid)
-> Hash (cost=1.09..1.09 rows=4 width=68) (actual time=0.011..0.011 rows=4 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on pg_namespace n (cost=0.00..1.09 rows=4 width=68) (actual time=0.005..0.007 rows=4 loops=1)
Filter: ((nspname <> 'pg_catalog'::name) AND (nspname <> 'information_schema'::name))link to depesz.com: https://explain.depesz.com/s/9Q3Q
4th query:
SELECT DISTINCT
"members"."user_id"
FROM
"members"
LEFT OUTER JOIN "users" ON "members"."user_id" = "users"."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"."source_id" IN ( WITH RECURSIVE "base_and_ancestors" AS ((
SELECT
"namespaces".*
FROM
"namespaces"
INNER JOIN "project_group_links" ON "project_group_links"."group_id" = "namespaces"."id"
WHERE
"namespaces"."type" = 'Group'
AND "project_group_links"."project_id" IN (
SELECT
"projects"."id"
FROM
"projects"
INNER JOIN routes rs ON rs.source_id = projects.id
AND rs.source_type = 'Project'
WHERE (rs.path LIKE 'gitlab-org/%')))
UNION (
SELECT
"namespaces".*
FROM
"namespaces",
"base_and_ancestors"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."id" = "base_and_ancestors"."parent_id"))
SELECT
"namespaces"."id"
FROM
"base_and_ancestors" AS "namespaces") QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Sort (cost=146.63..148.65 rows=808 width=138) (actual time=55.009..55.012 rows=71 loops=1)
Sort Key: n.nspname, p.proname, (pg_get_function_arguments(p.oid))
Sort Method: quicksort Memory: 43kB
-> Hash Join (cost=1.14..107.61 rows=808 width=138) (actual time=42.495..54.854 rows=71 loops=1)
Hash Cond: (p.pronamespace = n.oid)
-> Seq Scan on pg_proc p (cost=0.00..89.30 rows=808 width=78) (actual time=0.052..53.465 rows=2402 loops=1)
Filter: pg_function_is_visible(oid)
-> Hash (cost=1.09..1.09 rows=4 width=68) (actual time=0.011..0.011 rows=4 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on pg_namespace n (cost=0.00..1.09 rows=4 width=68) (actual time=0.005..0.007 rows=4 loops=1)
Filter: ((nspname <> 'pg_catalog'::name) AND (nspname <> 'information_schema'::name))link to depesz.com: https://explain.depesz.com/s/4yql
- after
group.billed_user_idswas loaded:
-
main query without a search term:
::User.id_in(group_billed_user_ids).sort_by_attribute('name_asc')SELECT "users".* FROM "users" WHERE "users"."id" IN (1, 5, 11, 18, 20, 3, 6, 7, 10, 14, 15, 17) ORDER BY LOWER("users"."name") ASCQUERY PLAN --------------------------------------------------------------------------------------------------------------------------- Sort (cost=146.63..148.65 rows=808 width=138) (actual time=55.009..55.012 rows=71 loops=1) Sort Key: n.nspname, p.proname, (pg_get_function_arguments(p.oid)) Sort Method: quicksort Memory: 43kB -> Hash Join (cost=1.14..107.61 rows=808 width=138) (actual time=42.495..54.854 rows=71 loops=1) Hash Cond: (p.pronamespace = n.oid) -> Seq Scan on pg_proc p (cost=0.00..89.30 rows=808 width=78) (actual time=0.052..53.465 rows=2402 loops=1) Filter: pg_function_is_visible(oid) -> Hash (cost=1.09..1.09 rows=4 width=68) (actual time=0.011..0.011 rows=4 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB -> Seq Scan on pg_namespace n (cost=0.00..1.09 rows=4 width=68) (actual time=0.005..0.007 rows=4 loops=1) Filter: ((nspname <> 'pg_catalog'::name) AND (nspname <> 'information_schema'::name))link to depesz.com: https://explain.depesz.com/s/4xRH
-
main query with a search term:
::User.id_in(group_billed_user_ids).search('Deana').sort_by_attribute('name_asc')SELECT "users".* FROM "users" WHERE "users"."id" IN (1, 5, 11, 18, 20, 3, 6, 7, 10, 14, 15, 17) AND ((("users"."name" ILIKE '%deana%' OR "users"."username" ILIKE '%deana%') OR "users"."email" = 'deana') OR "users"."id" = ( SELECT "emails"."user_id" FROM "emails" WHERE "emails"."email" = 'deana' LIMIT 1)) ORDER BY LOWER("users"."name") ASCQUERY PLAN --------------------------------------------------------------------------------------------------------------------------- Sort (cost=146.63..148.65 rows=808 width=138) (actual time=55.009..55.012 rows=71 loops=1) Sort Key: n.nspname, p.proname, (pg_get_function_arguments(p.oid)) Sort Method: quicksort Memory: 43kB -> Hash Join (cost=1.14..107.61 rows=808 width=138) (actual time=42.495..54.854 rows=71 loops=1) Hash Cond: (p.pronamespace = n.oid) -> Seq Scan on pg_proc p (cost=0.00..89.30 rows=808 width=78) (actual time=0.052..53.465 rows=2402 loops=1) Filter: pg_function_is_visible(oid) -> Hash (cost=1.09..1.09 rows=4 width=68) (actual time=0.011..0.011 rows=4 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB -> Seq Scan on pg_namespace n (cost=0.00..1.09 rows=4 width=68) (actual time=0.005..0.007 rows=4 loops=1) Filter: ((nspname <> 'pg_catalog'::name) AND (nspname <> 'information_schema'::name))link to depesz.com: https://explain.depesz.com/s/Rovl
Conformity
-
Changelog entry -
Documentation (if required) -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Availability and Testing
-
Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process. -
Tested in all supported browsers -
Informed Infrastructure department of a default or new setting change, if applicable per definition of done
Security
If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:
-
Label as security and @ mention @gitlab-com/gl-security/appsec -
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods -
Security reports checked/validated by a reviewer from the AppSec team
Closes #291940 (closed)
Edited by Mayra Cabrera