Return all visible groups for the `Organization.groups` GraphQL query
What does this MR do and why?
As we want to display all visible groups on the organization groups
dashboard on /-/organizations/<organization path>/groups_and_projects.
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.
Query plans
Before
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/26883/commands/83730
After
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/26883/commands/83731
Query plan with non-default organization
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=28217.75..28217.76 rows=3 width=2984) (actual time=1939.575..1939.805 rows=20 loops=1)
-> Sort (cost=28217.75..28217.76 rows=3 width=2984) (actual time=1939.544..1939.758 rows=20 loops=1)
Sort Key: (lower((namespaces.name)::text)), namespaces.id DESC
Sort Method: top-N heapsort Memory: 40kB
-> Subquery Scan on namespaces (cost=28217.66..28217.73 rows=3 width=2984) (actual time=1935.609..1938.232 rows=842 loops=1)
-> HashAggregate (cost=28217.66..28217.69 rows=3 width=2952) (actual time=1935.539..1937.228 rows=842 loops=1)
Group Key: namespaces_1.id, namespaces_1.name, namespaces_1.path, namespaces_1.owner_id, namespaces_1.created_at, namespaces_1.updated_at, namespaces_1.type, namespaces_1.description, namespaces_1.avatar, namespaces_1.membership_lock, namespaces_1.share_with_group_lock, namespaces_1.visibility_level, namespaces_1.request_access_enabled, namespaces_1.ldap_sync_status, namespaces_1.ldap_sync_error, namespaces_1.ldap_sync_last_update_at, namespaces_1.ldap_sync_last_successful_update_at, namespaces_1.ldap_sync_last_sync_at, namespaces_1.description_html, namespaces_1.lfs_enabled, namespaces_1.parent_id, namespaces_1.shared_runners_minutes_limit, namespaces_1.repository_size_limit, namespaces_1.require_two_factor_authentication, namespaces_1.two_factor_grace_period, namespaces_1.cached_markdown_version, namespaces_1.project_creation_level, namespaces_1.runners_token, namespaces_1.file_template_project_id, namespaces_1.saml_discovery_token, namespaces_1.runners_token_encrypted, namespaces_1.custom_project_templates_group_id, namespaces_1.auto_devops_enabled, namespaces_1.extra_shared_runners_minutes_limit, namespaces_1.last_ci_minutes_notification_at, namespaces_1.last_ci_minutes_usage_notification_level, namespaces_1.subgroup_creation_level, namespaces_1.emails_disabled, namespaces_1.max_pages_size, namespaces_1.max_artifacts_size, namespaces_1.mentions_disabled, namespaces_1.default_branch_protection, namespaces_1.max_personal_access_token_lifetime, namespaces_1.push_rule_id, namespaces_1.shared_runners_enabled, namespaces_1.allow_descendants_override_disabled_shared_runners, namespaces_1.traversal_ids, namespaces_1.organization_id
Batches: 1 Memory Usage: 639kB
-> Append (cost=16.03..28217.30 rows=3 width=2952) (actual time=21.560..1927.852 rows=2025 loops=1)
-> Unique (cost=16.03..16.15 rows=1 width=375) (actual time=21.558..22.377 rows=842 loops=1)
CTE descendants_base_cte
-> Nested Loop (cost=1.13..13.74 rows=1 width=32) (actual time=0.751..4.238 rows=18 loops=1)
-> Index Scan using idx_members_on_user_and_source_and_source_type_and_member_role on members (cost=0.56..10.15 rows=1 width=4) (actual time=0.655..2.876 rows=18 loops=1)
Index Cond: ((user_id = 10327656) AND ((source_type)::text = 'Namespace'::text))
Filter: ((requested_at IS NULL) AND (access_level >= 10) AND ((type)::text = 'GroupMember'::text))
-> Index Scan using index_namespaces_on_type_and_id on namespaces namespaces_4 (cost=0.56..3.58 rows=1 width=32) (actual time=0.073..0.073 rows=1 loops=18)
Index Cond: (((type)::text = 'Group'::text) AND (id = members.source_id))
-> Sort (cost=2.29..2.29 rows=1 width=375) (actual time=21.556..21.764 rows=842 loops=1)
Sort Key: namespaces_1.id, namespaces_1.name, namespaces_1.path, namespaces_1.owner_id, namespaces_1.created_at, namespaces_1.updated_at, namespaces_1.description, namespaces_1.avatar, namespaces_1.membership_lock, namespaces_1.share_with_group_lock, namespaces_1.visibility_level, namespaces_1.request_access_enabled, namespaces_1.ldap_sync_status, namespaces_1.ldap_sync_error, namespaces_1.ldap_sync_last_update_at, namespaces_1.ldap_sync_last_successful_update_at, namespaces_1.ldap_sync_last_sync_at, namespaces_1.description_html, namespaces_1.lfs_enabled, namespaces_1.parent_id, namespaces_1.shared_runners_minutes_limit, namespaces_1.repository_size_limit, namespaces_1.require_two_factor_authentication, namespaces_1.two_factor_grace_period, namespaces_1.cached_markdown_version, namespaces_1.project_creation_level, namespaces_1.runners_token, namespaces_1.file_template_project_id, namespaces_1.saml_discovery_token, namespaces_1.runners_token_encrypted, namespaces_1.custom_project_templates_group_id, namespaces_1.auto_devops_enabled, namespaces_1.extra_shared_runners_minutes_limit, namespaces_1.last_ci_minutes_notification_at, namespaces_1.last_ci_minutes_usage_notification_level, namespaces_1.subgroup_creation_level, namespaces_1.emails_disabled, namespaces_1.max_pages_size, namespaces_1.max_artifacts_size, namespaces_1.mentions_disabled, namespaces_1.default_branch_protection, namespaces_1.max_personal_access_token_lifetime, namespaces_1.push_rule_id, namespaces_1.shared_runners_enabled, namespaces_1.allow_descendants_override_disabled_shared_runners, namespaces_1.traversal_ids
Sort Method: quicksort Memory: 361kB
-> Nested Loop (cost=0.43..2.28 rows=1 width=375) (actual time=11.511..19.858 rows=842 loops=1)
Join Filter: ((d1.traversal_ids <= namespaces_1.traversal_ids) AND (next_traversal_ids_sibling(d1.traversal_ids) > namespaces_1.traversal_ids))
Rows Removed by Join Filter: 5052
-> Nested Loop Anti Join (cost=0.00..0.07 rows=1 width=32) (actual time=4.295..4.410 rows=7 loops=1)
Join Filter: ((d2.id <> d1.id) AND (d2.id = ANY (d1.traversal_ids)))
Rows Removed by Join Filter: 186
-> CTE Scan on descendants_base_cte d1 (cost=0.00..0.02 rows=1 width=36) (actual time=0.753..0.772 rows=18 loops=1)
-> CTE Scan on descendants_base_cte d2 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.198 rows=11 loops=18)
-> Index Scan using index_namespaces_on_organization_id_for_groups on namespaces namespaces_1 (cost=0.43..1.95 rows=1 width=375) (actual time=0.024..1.245 rows=842 loops=7)
Index Cond: (organization_id = 5000)
-> Hash Join (cost=28197.90..28199.16 rows=1 width=375) (actual time=1901.661..1903.769 rows=640 loops=1)
Hash Cond: ((unnest(base_ancestors_cte.traversal_ids)) = namespaces_2.id)
CTE base_ancestors_cte
-> Subquery Scan on namespaces_7 (cost=28195.33..28195.43 rows=5 width=32) (actual time=1882.971..1889.544 rows=2990 loops=1)
-> HashAggregate (cost=28195.33..28195.38 rows=5 width=2952) (actual time=1882.969..1889.109 rows=2990 loops=1)
Group Key: namespaces_8.id, namespaces_8.name, namespaces_8.path, namespaces_8.owner_id, namespaces_8.created_at, namespaces_8.updated_at, namespaces_8.type, namespaces_8.description, namespaces_8.avatar, namespaces_8.members
hip_lock, namespaces_8.share_with_group_lock, namespaces_8.visibility_level, namespaces_8.request_access_enabled, namespaces_8.ldap_sync_status, namespaces_8.ldap_sync_error, namespaces_8.ldap_sync_last_update_at, namespaces_8.ldap_sync_last_successful_update_at, namespac
es_8.ldap_sync_last_sync_at, namespaces_8.description_html, namespaces_8.lfs_enabled, namespaces_8.parent_id, namespaces_8.shared_runners_minutes_limit, namespaces_8.repository_size_limit, namespaces_8.require_two_factor_authentication, namespaces_8.two_factor_grace_perio
d, namespaces_8.cached_markdown_version, namespaces_8.project_creation_level, namespaces_8.runners_token, namespaces_8.file_template_project_id, namespaces_8.saml_discovery_token, namespaces_8.runners_token_encrypted, namespaces_8.custom_project_templates_group_id, namesp
aces_8.auto_devops_enabled, namespaces_8.extra_shared_runners_minutes_limit, namespaces_8.last_ci_minutes_notification_at, namespaces_8.last_ci_minutes_usage_notification_level, namespaces_8.subgroup_creation_level, namespaces_8.emails_disabled, namespaces_8.max_pages_siz
e, namespaces_8.max_artifacts_size, namespaces_8.mentions_disabled, namespaces_8.default_branch_protection, namespaces_8.max_personal_access_token_lifetime, namespaces_8.push_rule_id, namespaces_8.shared_runners_enabled, namespaces_8.allow_descendants_override_disabled_sh
ared_runners, namespaces_8.traversal_ids, namespaces_8.organization_id
Batches: 1 Memory Usage: 2247kB
-> Append (cost=28180.83..28194.73 rows=5 width=2952) (actual time=1867.296..1873.605 rows=2990 loops=1)
-> HashAggregate (cost=56.63..56.67 rows=4 width=2952) (actual time=1867.294..1873.008 rows=2990 loops=1)
Group Key: namespaces_8.id, namespaces_8.name, namespaces_8.path, namespaces_8.owner_id, namespaces_8.created_at, namespaces_8.updated_at, namespaces_8.type, namespaces_8.description, namespaces_8.avatar, namespac
es_8.membership_lock, namespaces_8.share_with_group_lock, namespaces_8.visibility_level, namespaces_8.request_access_enabled, namespaces_8.ldap_sync_status, namespaces_8.ldap_sync_error, namespaces_8.ldap_sync_last_update_at, namespaces_8.ldap_sync_last_successful_update_
at, namespaces_8.ldap_sync_last_sync_at, namespaces_8.description_html, namespaces_8.lfs_enabled, namespaces_8.parent_id, namespaces_8.shared_runners_minutes_limit, namespaces_8.repository_size_limit, namespaces_8.require_two_factor_authentication, namespaces_8.two_factor
_grace_period, namespaces_8.cached_markdown_version, namespaces_8.project_creation_level, namespaces_8.runners_token, namespaces_8.file_template_project_id, namespaces_8.saml_discovery_token, namespaces_8.runners_token_encrypted, namespaces_8.custom_project_templates_grou
p_id, namespaces_8.auto_devops_enabled, namespaces_8.extra_shared_runners_minutes_limit, namespaces_8.last_ci_minutes_notification_at, namespaces_8.last_ci_minutes_usage_notification_level, namespaces_8.subgroup_creation_level, namespaces_8.emails_disabled, namespaces_8.m
ax_pages_size, namespaces_8.max_artifacts_size, namespaces_8.mentions_disabled, namespaces_8.default_branch_protection, namespaces_8.max_personal_access_token_lifetime, namespaces_8.push_rule_id, namespaces_8.shared_runners_enabled, namespaces_8.allow_descendants_override
_disabled_shared_runners, namespaces_8.traversal_ids, namespaces_8.organization_id
Batches: 1 Memory Usage: 2247kB
CTE direct_groups
-> HashAggregate (cost=28114.07..28119.14 rows=507 width=2952) (actual time=1811.300..1817.549 rows=2970 loops=1)
Group Key: namespaces_5.id, namespaces_5.name, namespaces_5.path, namespaces_5.owner_id, namespaces_5.created_at, namespaces_5.updated_at, namespaces_5.type, namespaces_5.description, namespaces_5.avatar,
namespaces_5.membership_lock, namespaces_5.share_with_group_lock, namespaces_5.visibility_level, namespaces_5.request_access_enabled, namespaces_5.ldap_sync_status, namespaces_5.ldap_sync_error, namespaces_5.ldap_sync_last_update_at, namespaces_5.ldap_sync_last_successful
_update_at, namespaces_5.ldap_sync_last_sync_at, namespaces_5.description_html, namespaces_5.lfs_enabled, namespaces_5.parent_id, namespaces_5.shared_runners_minutes_limit, namespaces_5.repository_size_limit, namespaces_5.require_two_factor_authentication, namespaces_5.tw
o_factor_grace_period, namespaces_5.cached_markdown_version, namespaces_5.project_creation_level, namespaces_5.runners_token, namespaces_5.file_template_project_id, namespaces_5.saml_discovery_token, namespaces_5.runners_token_encrypted, namespaces_5.custom_project_templa
tes_group_id, namespaces_5.auto_devops_enabled, namespaces_5.extra_shared_runners_minutes_limit, namespaces_5.last_ci_minutes_notification_at, namespaces_5.last_ci_minutes_usage_notification_level, namespaces_5.subgroup_creation_level, namespaces_5.emails_disabled, namesp
aces_5.max_pages_size, namespaces_5.max_artifacts_size, namespaces_5.mentions_disabled, namespaces_5.default_branch_protection, namespaces_5.max_personal_access_token_lifetime, namespaces_5.push_rule_id, namespaces_5.shared_runners_enabled, namespaces_5.allow_descendants_
override_disabled_shared_runners, namespaces_5.traversal_ids, namespaces_5.organization_id
Batches: 1 Memory Usage: 2215kB
-> Append (cost=1.13..28053.23 rows=507 width=2952) (actual time=0.090..1799.942 rows=2979 loops=1)
-> Nested Loop (cost=1.13..13.74 rows=1 width=375) (actual time=0.089..0.282 rows=18 loops=1)
-> Index Scan using idx_members_on_user_and_source_and_source_type_and_member_role on members members_1 (cost=0.56..10.15 rows=1 width=4) (actual time=0.040..0.071 rows=18 loops=1)
Index Cond: ((user_id = 10327656) AND ((source_type)::text = 'Namespace'::text))
Filter: ((requested_at IS NULL) AND (access_level >= 10) AND ((type)::text = 'GroupMember'::text))
-> Index Scan using index_namespaces_on_type_and_id on namespaces namespaces_5 (cost=0.56..3.58 rows=1 width=375) (actual time=0.011..0.011 rows=1 loops=18)
Index Cond: (((type)::text = 'Group'::text) AND (id = members_1.source_id))
-> Nested Loop (cost=18973.14..28031.88 rows=506 width=375) (actual time=1660.419..1799.174 rows=2961 loops=1)
-> HashAggregate (cost=18972.58..19024.94 rows=5236 width=4) (actual time=1660.204..1661.761 rows=3034 loops=1)
Group Key: projects.namespace_id
Batches: 1 Memory Usage: 465kB
-> Nested Loop (cost=1.14..18959.49 rows=5236 width=4) (actual time=5.277..1640.067 rows=24647 loops=1)
-> Index Only Scan using project_authorizations_pkey on project_authorizations (cost=0.57..380.02 rows=5236 width=4) (actual time=4.185..226.989 rows=24647 loops=1)
Index Cond: (user_id = 10327656)
Heap Fetches: 1308
-> Index Scan using projects_pkey on projects (cost=0.56..3.55 rows=1 width=8) (actual time=0.057..0.057 rows=1 loops=24647)
Index Cond: (id = project_authorizations.project_id)
-> Index Scan using namespaces_pkey on namespaces namespaces_6 (cost=0.56..1.72 rows=1 width=375) (actual time=0.044..0.044 rows=1 loops=3034)
Index Cond: (id = projects.namespace_id)
Filter: ((type)::text = 'Group'::text)
Rows Removed by Filter: 0
-> Append (cost=0.00..56.15 rows=4 width=2952) (actual time=1811.353..1853.072 rows=3078 loops=1)
-> CTE Scan on direct_groups namespaces_8 (cost=0.00..11.41 rows=3 width=2952) (actual time=1811.352..1825.878 rows=2970 loops=1)
Filter: ((type)::text = 'Group'::text)
-> Nested Loop (cost=12.40..44.68 rows=1 width=375) (actual time=8.115..26.788 rows=108 loops=1)
-> Nested Loop (cost=11.83..18.95 rows=8 width=8) (actual time=7.904..24.389 rows=108 loops=1)
-> HashAggregate (cost=11.41..11.44 rows=3 width=4) (actual time=3.140..4.323 rows=2970 loops=1)
Group Key: namespaces_10.id
Batches: 1 Memory Usage: 393kB
-> CTE Scan on direct_groups namespaces_10 (cost=0.00..11.41 rows=3 width=4) (actual time=0.027..1.378 rows=2970 loops=1)
Filter: ((type)::text = 'Group'::text)
-> Index Only Scan using index_group_group_links_on_shared_with_group_and_shared_group on group_group_links (cost=0.42..2.47 rows=3 width=16) (actual time=0.006..0.006 rows=0 loops=2970)
Index Cond: (shared_with_group_id = namespaces_10.id)
Heap Fetches: 16
-> Index Scan using namespaces_pkey on namespaces namespaces_9 (cost=0.56..3.22 rows=1 width=375) (actual time=0.020..0.020 rows=1 loops=108)
Index Cond: (id = group_group_links.shared_group_id)
Filter: ((type)::text = 'Group'::text)
-> Nested Loop (cost=1.13..13.74 rows=1 width=375) (actual time=0.241..0.256 rows=0 loops=1)
-> Index Scan using idx_members_on_user_and_source_and_source_type_and_member_role on members members_2 (cost=0.56..10.15 rows=1 width=4) (actual time=0.239..0.239 rows=0 loops=1)
Index Cond: ((user_id = 10327656) AND ((source_type)::text = 'Namespace'::text))
Filter: (((type)::text = 'GroupMember'::text) AND (access_level = 5))
Rows Removed by Filter: 18
-> Index Scan using index_namespaces_on_type_and_id on namespaces namespaces_11 (cost=0.56..3.58 rows=1 width=375) (never executed)
Index Cond: (((type)::text = 'Group'::text) AND (id = members_2.source_id))
-> HashAggregate (cost=0.51..1.14 rows=50 width=4) (actual time=1898.502..1899.336 rows=3191 loops=1)
Group Key: unnest(base_ancestors_cte.traversal_ids)
Batches: 1 Memory Usage: 393kB
-> ProjectSet (cost=0.00..0.39 rows=50 width=4) (actual time=1883.075..1894.162 rows=11628 loops=1)
-> CTE Scan on base_ancestors_cte (cost=0.00..0.10 rows=5 width=32) (actual time=1883.000..1891.304 rows=2990 loops=1)
-> Hash (cost=1.95..1.95 rows=1 width=375) (actual time=2.865..2.866 rows=842 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 254kB
-> Index Scan using index_namespaces_on_organization_id_for_groups on namespaces namespaces_2 (cost=0.43..1.95 rows=1 width=375) (actual time=0.086..1.590 rows=842 loops=1)
Index Cond: (organization_id = 5000)
-> Index Scan using index_namespaces_on_organization_id_for_groups on namespaces namespaces_3 (cost=0.43..1.95 rows=1 width=375) (actual time=0.095..1.452 rows=543 loops=1)
Index Cond: (organization_id = 5000)
Filter: (visibility_level = ANY ('{10,20}'::integer[]))
Rows Removed by Filter: 299
Planning Time: 224.462 ms
Execution Time: 1943.589 msScreenshots or screen recordings
| Before | After |
|---|---|
 |
 |
How to set up and validate locally
Follow these steps steps-to-reproduce
Related to #444218 (closed)
Edited by Abdul Wadood