Prevent timeouts in group autocomplete query
What does this MR do and why?
Remove unnecessary SQL clause to prevent query plan flip when providing a search term
Related to gitlab-org/release/tasks#12039 (closed), https://gitlab.com/gitlab-org/gitlab/-/issues/474559
Queries
In production, this query works fine so I generated these query plans from staging.
Before
Query plan
Limit (cost=106763.38..106763.41 rows=10 width=2984) (actual time=47551.041..47551.061 rows=10 loops=1)
-> Sort (cost=106763.38..106763.86 rows=193 width=2984) (actual time=47551.039..47551.058 rows=10 loops=1)
Sort Key: (CASE WHEN ((replace((routes.name)::text, ' '::text, ''::text) ~~* 'gitlab%'::text) OR ((routes.path)::text ~~* 'gitlab%'::text)) THEN 1 ELSE 2 END), routes.path
Sort Method: top-N heapsort Memory: 33kB
-> Nested Loop (cost=66046.52..106759.21 rows=193 width=2984) (actual time=47540.911..47549.678 rows=1181 loops=1)
-> HashAggregate (cost=66046.08..66178.74 rows=13266 width=2959) (actual time=47540.872..47541.713 rows=1375 loops=1)
Group Key: namespaces.id, namespaces.name, namespaces.path, namespaces.owner_id, namespaces.created_at, namespaces.updated_at, namespaces.type, namespaces.description, namespaces.avatar, namespaces.membership_lock, namespaces.share_with_group_lock, namespaces.visibility_level, namespaces.request_access_enabled, namespaces.ldap_sync_status, namespaces.ldap_sync_error, namespaces.ldap_sync_last_update_at, namespaces.ldap_sync_last_successful_update_at, namespaces.ldap_sync_last_sync_at, namespaces.lfs_enabled, namespaces.description_html, namespaces.parent_id, namespaces.shared_runners_minutes_limit, namespaces.repository_size_limit, namespaces.require_two_factor_authentication, namespaces.two_factor_grace_period, namespaces.cached_markdown_version, namespaces.project_creation_level, namespaces.runners_token, namespaces.file_template_project_id, namespaces.saml_discovery_token, namespaces.runners_token_encrypted, namespaces.custom_project_templates_group_id, namespaces.auto_devops_enabled, namespaces.extra_shared_runners_minutes_limit, namespaces.last_ci_minutes_notification_at, namespaces.last_ci_minutes_usage_notification_level, namespaces.subgroup_creation_level, namespaces.max_pages_size, namespaces.max_artifacts_size, namespaces.marked_for_deletion_at, namespaces.marked_for_deletion_by_user_id, namespaces.mentions_disabled, namespaces.default_branch_protection, namespaces.max_personal_access_token_lifetime, namespaces.push_rule_id, namespaces.shared_runners_enabled, namespaces.allow_descendants_override_disabled_shared_runners, namespaces.traversal_ids, namespaces.organization_id
Batches: 1 Memory Usage: 1479kB
-> Append (cost=63949.15..64421.00 rows=13266 width=2959) (actual time=47538.648..47539.471 rows=1375 loops=1)
-> HashAggregate (cost=63406.25..63538.90 rows=13265 width=2959) (actual time=47538.647..47539.349 rows=1375 loops=1)
Group Key: namespaces.id, namespaces.name, namespaces.path, namespaces.owner_id, namespaces.created_at, namespaces.updated_at, namespaces.type, namespaces.description, namespaces.avatar, namespaces.membership_lock, namespaces.share_with_group_lock, namespaces.visibility_level, namespaces.request_access_enabled, namespaces.ldap_sync_status, namespaces.ldap_sync_error, namespaces.ldap_sync_last_update_at, namespaces.ldap_sync_last_successful_update_at, namespaces.ldap_sync_last_sync_at, namespaces.lfs_enabled, namespaces.description_html, namespaces.parent_id, namespaces.shared_runners_minutes_limit, namespaces.repository_size_limit, namespaces.require_two_factor_authentication, namespaces.two_factor_grace_period, namespaces.cached_markdown_version, namespaces.project_creation_level, namespaces.runners_token, namespaces.file_template_project_id, namespaces.saml_discovery_token, namespaces.runners_token_encrypted, namespaces.custom_project_templates_group_id, namespaces.auto_devops_enabled, namespaces.extra_shared_runners_minutes_limit, namespaces.last_ci_minutes_notification_at, namespaces.last_ci_minutes_usage_notification_level, namespaces.subgroup_creation_level, namespaces.max_pages_size, namespaces.max_artifacts_size, namespaces.marked_for_deletion_at, namespaces.marked_for_deletion_by_user_id, namespaces.mentions_disabled, namespaces.default_branch_protection, namespaces.max_personal_access_token_lifetime, namespaces.push_rule_id, namespaces.shared_runners_enabled, namespaces.allow_descendants_override_disabled_shared_runners, namespaces.traversal_ids, namespaces.organization_id
Batches: 1 Memory Usage: 1479kB
CTE direct_groups
-> Nested Loop (cost=0.86..542.91 rows=3 width=379) (actual time=0.038..6.714 rows=1331 loops=1)
-> Index Scan using idx_members_on_user_and_source_and_source_type_and_member_role on members members_1 (cost=0.43..380.64 rows=47 width=4) (actual time=0.025..2.865 rows=1331 loops=1)
Index Cond: ((user_id = 1614863) AND ((source_type)::text = 'Namespace'::text))
Filter: ((requested_at IS NULL) AND (access_level >= 10) AND ((type)::text = 'GroupMember'::text))
-> Index Scan using namespaces_pkey on namespaces namespaces_5 (cost=0.43..3.45 rows=1 width=379) (actual time=0.003..0.003 rows=1 loops=1331)
Index Cond: (id = members_1.source_id)
Filter: ((type)::text = 'Group'::text)
-> Append (cost=45297.70..61781.28 rows=13265 width=2959) (actual time=47522.963..47535.661 rows=2332 loops=1)
-> HashAggregate (cost=45297.70..45429.07 rows=13137 width=379) (actual time=47522.962..47523.792 rows=1369 loops=1)
Group Key: namespaces.id, namespaces.name, namespaces.path, namespaces.owner_id, namespaces.created_at, namespaces.updated_at, namespaces.type, namespaces.description, namespaces.avatar, namespaces.membership_lock, namespaces.share_with_group_lock, namespaces.visibility_level, namespaces.request_access_enabled, namespaces.ldap_sync_status, namespaces.ldap_sync_error, namespaces.ldap_sync_last_update_at, namespaces.ldap_sync_last_successful_update_at, namespaces.ldap_sync_last_sync_at, namespaces.lfs_enabled, namespaces.description_html, namespaces.parent_id, namespaces.shared_runners_minutes_limit, namespaces.repository_size_limit, namespaces.require_two_factor_authentication, namespaces.two_factor_grace_period, namespaces.cached_markdown_version, namespaces.project_creation_level, namespaces.runners_token, namespaces.file_template_project_id, namespaces.saml_discovery_token, namespaces.runners_token_encrypted, namespaces.custom_project_templates_group_id, namespaces.auto_devops_enabled, namespaces.extra_shared_runners_minutes_limit, namespaces.last_ci_minutes_notification_at, namespaces.last_ci_minutes_usage_notification_level, namespaces.subgroup_creation_level, namespaces.max_pages_size, namespaces.max_artifacts_size, namespaces.marked_for_deletion_at, namespaces.marked_for_deletion_by_user_id, namespaces.mentions_disabled, namespaces.default_branch_protection, namespaces.max_personal_access_token_lifetime, namespaces.push_rule_id, namespaces.shared_runners_enabled, namespaces.allow_descendants_override_disabled_shared_runners, namespaces.traversal_ids, namespaces.organization_id
Batches: 1 Memory Usage: 1479kB
CTE descendants_base_cte
-> CTE Scan on direct_groups namespaces_6 (cost=0.00..0.07 rows=1 width=36) (actual time=0.044..8.107 rows=1331 loops=1)
Filter: ((type)::text = 'Group'::text)
-> Nested Loop (cost=0.42..43688.35 rows=13137 width=379) (actual time=22.845..47518.088 rows=1369 loops=1)
Join Filter: ((d1.traversal_ids <= namespaces.traversal_ids) AND (next_traversal_ids_sibling(d1.traversal_ids) > namespaces.traversal_ids))
Rows Removed by Join Filter: 59042771
-> Nested Loop Anti Join (cost=0.00..0.07 rows=1 width=32) (actual time=8.674..96.123 rows=180 loops=1)
Join Filter: ((d2.id <> d1.id) AND (d2.id = ANY (d1.traversal_ids)))
Rows Removed by Join Filter: 460684
-> CTE Scan on descendants_base_cte d1 (cost=0.00..0.02 rows=1 width=36) (actual time=0.045..0.538 rows=1331 loops=1)
-> CTE Scan on descendants_base_cte d2 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.033 rows=347 loops=1331)
-> Index Scan using index_namespaces_on_organization_id_for_groups on namespaces (cost=0.42..12357.60 rows=118229 width=379) (actual time=0.012..197.853 rows=328023 loops=180)
-> Nested Loop (cost=9821.46..16146.61 rows=127 width=379) (actual time=8.258..11.257 rows=961 loops=1)
-> HashAggregate (cost=9821.02..9877.58 rows=5656 width=4) (actual time=8.237..8.402 rows=967 loops=1)
Group Key: projects.namespace_id
Batches: 1 Memory Usage: 273kB
-> Nested Loop (cost=0.86..9806.88 rows=5656 width=4) (actual time=0.043..7.448 rows=5075 loops=1)
-> Index Only Scan using project_authorizations_pkey on project_authorizations (cost=0.43..194.32 rows=5656 width=4) (actual time=0.025..0.939 rows=5075 loops=1)
Index Cond: (user_id = 1614863)
Heap Fetches: 397
-> Index Only Scan using index_projects_on_id_and_namespace_id on projects (cost=0.43..1.70 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=5075)
Index Cond: (id = project_authorizations.project_id)
Heap Fetches: 621
-> Index Scan using namespaces_pkey on namespaces namespaces_1 (cost=0.43..1.11 rows=1 width=379) (actual time=0.003..0.003 rows=1 loops=967)
Index Cond: (id = projects.namespace_id)
Filter: (((type)::text <> 'Project'::text) AND ((type)::text = 'Group'::text))
Rows Removed by Filter: 0
-> Nested Loop (cost=0.51..6.63 rows=1 width=379) (actual time=0.433..0.453 rows=2 loops=1)
-> Hash Semi Join (cost=0.08..3.18 rows=1 width=8) (actual time=0.406..0.418 rows=2 loops=1)
Hash Cond: (group_group_links.shared_with_group_id = namespaces_3.id)
-> Seq Scan on group_group_links (cost=0.00..2.86 rows=86 width=16) (actual time=0.010..0.019 rows=85 loops=1)
-> Hash (cost=0.07..0.07 rows=1 width=4) (actual time=0.368..0.370 rows=1331 loops=1)
Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 63kB
-> CTE Scan on direct_groups namespaces_3 (cost=0.00..0.07 rows=1 width=4) (actual time=0.003..0.209 rows=1331 loops=1)
Filter: ((type)::text = 'Group'::text)
-> Index Scan using namespaces_pkey on namespaces namespaces_2 (cost=0.43..3.46 rows=1 width=379) (actual time=0.014..0.014 rows=1 loops=2)
Index Cond: (id = group_group_links.shared_group_id)
Filter: (((type)::text <> 'Project'::text) AND ((type)::text = 'Group'::text))
-> Nested Loop Semi Join (cost=1.43..7.56 rows=1 width=379) (actual time=0.048..0.050 rows=0 loops=1)
-> Nested Loop (cost=0.86..6.62 rows=1 width=383) (actual time=0.047..0.048 rows=0 loops=1)
-> Index Scan using index_members_on_access_level on members (cost=0.43..3.17 rows=1 width=4) (actual time=0.047..0.047 rows=0 loops=1)
Index Cond: (access_level = 5)
Filter: (((type)::text = 'GroupMember'::text) AND ((source_type)::text = 'Namespace'::text) AND (user_id = 1614863))
Rows Removed by Filter: 11
-> Index Scan using namespaces_pkey on namespaces namespaces_4 (cost=0.43..3.46 rows=1 width=379) (never executed)
Index Cond: (id = members.source_id)
Filter: (((type)::text <> 'Project'::text) AND ((type)::text = 'Group'::text))
-> Nested Loop (cost=0.56..0.92 rows=1 width=4) (never executed)
-> Index Scan using index_gitlab_subscriptions_on_namespace_id on gitlab_subscriptions (cost=0.43..0.75 rows=1 width=8) (never executed)
Index Cond: (namespace_id = namespaces_4.id)
-> Index Scan using plans_pkey on plans (cost=0.14..0.16 rows=1 width=4) (never executed)
Index Cond: (id = gitlab_subscriptions.hosted_plan_id)
Filter: ((name)::text = ANY ('{silver,premium,premium_trial,gold,ultimate,ultimate_trial,ultimate_trial_paid_customer,opensource}'::text[]))
-> Index Scan using index_routes_on_source_type_and_source_id on routes (cost=0.43..3.05 rows=1 width=48) (actual time=0.005..0.005 rows=1 loops=1375)
Index Cond: (((source_type)::text = 'Namespace'::text) AND (source_id = namespaces.id))
Filter: ((replace((name)::text, ' '::text, ''::text) ~~* '%gitlab%'::text) OR ((path)::text ~~* '%gitlab%'::text))
Rows Removed by Filter: 0
Planning Time: 4.530 ms
Execution Time: 47551.652 msSQL
SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."lfs_enabled", "namespaces"."description_html", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."marked_for_deletion_at", "namespaces"."marked_for_deletion_by_user_id", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids", "namespaces"."organization_id" FROM ((WITH "direct_groups" AS MATERIALIZED (SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."lfs_enabled", "namespaces"."description_html", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."marked_for_deletion_at", "namespaces"."marked_for_deletion_by_user_id", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids", "namespaces"."organization_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)) SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."lfs_enabled", "namespaces"."description_html", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."marked_for_deletion_at", "namespaces"."marked_for_deletion_by_user_id", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids", "namespaces"."organization_id" FROM ((WITH "descendants_base_cte" AS MATERIALIZED (SELECT "id", "traversal_ids" FROM "direct_groups" "namespaces" WHERE "namespaces"."type" = 'Group'), "superset" AS (SELECT d1.traversal_ids
FROM descendants_base_cte d1
WHERE NOT EXISTS (
SELECT 1
FROM descendants_base_cte d2
WHERE d2.id = ANY(d1.traversal_ids)
AND d2.id <> d1.id
)
) SELECT DISTINCT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."lfs_enabled", "namespaces"."description_html", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."marked_for_deletion_at", "namespaces"."marked_for_deletion_by_user_id", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids", "namespaces"."organization_id" FROM "superset", "namespaces" WHERE "namespaces"."type" = 'Group' AND next_traversal_ids_sibling("superset"."traversal_ids") > "namespaces"."traversal_ids" AND "superset"."traversal_ids" <= "namespaces"."traversal_ids")
UNION
(SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."lfs_enabled", "namespaces"."description_html", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."marked_for_deletion_at", "namespaces"."marked_for_deletion_by_user_id", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids", "namespaces"."organization_id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" IN (SELECT "projects"."namespace_id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "project_authorizations"."user_id" = 1614863))
UNION
(SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."lfs_enabled", "namespaces"."description_html", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."marked_for_deletion_at", "namespaces"."marked_for_deletion_by_user_id", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids", "namespaces"."organization_id" 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')
UNION
(SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."lfs_enabled", "namespaces"."description_html", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."marked_for_deletion_at", "namespaces"."marked_for_deletion_by_user_id", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids", "namespaces"."organization_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"."access_level" = 5 AND (EXISTS (SELECT 1 FROM "plans" INNER JOIN "gitlab_subscriptions" ON "gitlab_subscriptions"."hosted_plan_id" = "plans"."id" WHERE "plans"."name" IN ('silver', 'premium', 'premium_trial', 'gold', 'ultimate', 'ultimate_trial', 'ultimate_trial_paid_customer', 'opensource') AND (gitlab_subscriptions.namespace_id = namespaces.id))))) namespaces INNER JOIN "routes" ON "routes"."source_type" = 'Namespace' AND "routes"."source_id" = "namespaces"."id" WHERE "namespaces"."type" = 'Group' AND "namespaces"."type" != 'Project' AND (REPLACE(routes.name, ' ', '') ILIKE '%gitlab%' OR routes.path ILIKE '%gitlab%') ORDER BY CASE WHEN REPLACE(routes.name, ' ', '') ILIKE 'gitlab%' OR routes.path ILIKE 'gitlab%' THEN 1 ELSE 2 END, routes.path LIMIT 10After
Query plan
Limit (cost=237869.27..237869.29 rows=10 width=2984) (actual time=151.493..151.514 rows=10 loops=1)
-> Sort (cost=237869.27..237870.61 rows=536 width=2984) (actual time=151.491..151.511 rows=10 loops=1)
Sort Key: (CASE WHEN (starts_with(replace((routes.name)::text, ' '::text, ''::text), 'gitlab'::text) OR starts_with((routes.path)::text, 'gitlab'::text)) THEN 1 ELSE 2 END), routes.path
Sort Method: top-N heapsort Memory: 29kB
-> Nested Loop (cost=138124.50..237857.68 rows=536 width=2984) (actual time=138.393..150.149 rows=1244 loops=1)
-> Unique (cost=138124.07..142736.69 rows=36901 width=2959) (actual time=138.347..139.201 rows=1438 loops=1)
-> Sort (cost=138124.07..138216.32 rows=36901 width=2959) (actual time=138.346..138.502 rows=1438 loops=1)
Sort Key: namespaces.id, namespaces.name, namespaces.path, namespaces.owner_id, namespaces.created_at, namespaces.updated_at, namespaces.type, namespaces.description, namespaces.avatar, namespaces.membership_lock, namespaces.share_with_group_lock, namespaces.visibility_level, namespaces.request_access_enabled, namespaces.ldap_sync_status, namespaces.ldap_sync_error, namespaces.ldap_sync_last_update_at, namespaces.ldap_sync_last_successful_update_at, namespaces.ldap_sync_last_sync_at, namespaces.lfs_enabled, namespaces.description_html, namespaces.parent_id, namespaces.shared_runners_minutes_limit, namespaces.repository_size_limit, namespaces.require_two_factor_authentication, namespaces.two_factor_grace_period, namespaces.cached_markdown_version, namespaces.project_creation_level, namespaces.runners_token, namespaces.file_template_project_id, namespaces.saml_discovery_token, namespaces.runners_token_encrypted, namespaces.custom_project_templates_group_id, namespaces.auto_devops_enabled, namespaces.extra_shared_runners_minutes_limit, namespaces.last_ci_minutes_notification_at, namespaces.last_ci_minutes_usage_notification_level, namespaces.subgroup_creation_level, namespaces.max_pages_size, namespaces.max_artifacts_size, namespaces.marked_for_deletion_at, namespaces.marked_for_deletion_by_user_id, namespaces.mentions_disabled, namespaces.default_branch_protection, namespaces.max_personal_access_token_lifetime, namespaces.push_rule_id, namespaces.shared_runners_enabled, namespaces.allow_descendants_override_disabled_shared_runners, namespaces.traversal_ids, namespaces.organization_id
Sort Method: quicksort Memory: 727kB
-> Append (cost=99538.00..105080.38 rows=36901 width=2959) (actual time=135.813..137.872 rows=1438 loops=1)
-> Unique (cost=98995.10..103607.60 rows=36900 width=2959) (actual time=135.812..137.665 rows=1438 loops=1)
CTE direct_groups
-> Nested Loop (cost=0.86..542.91 rows=3 width=378) (actual time=0.069..19.276 rows=1394 loops=1)
-> Index Scan using idx_members_on_user_and_source_and_source_type_and_member_role on members members_1 (cost=0.43..380.64 rows=47 width=4) (actual time=0.035..10.831 rows=1394 loops=1)
Index Cond: ((user_id = 1614863) AND ((source_type)::text = 'Namespace'::text))
Filter: ((requested_at IS NULL) AND (access_level >= 10) AND ((type)::text = 'GroupMember'::text))
-> Index Scan using namespaces_pkey on namespaces namespaces_5 (cost=0.43..3.45 rows=1 width=378) (actual time=0.006..0.006 rows=1 loops=1394)
Index Cond: (id = members_1.source_id)
Filter: ((type)::text = 'Group'::text)
-> Sort (cost=98995.10..99087.35 rows=36900 width=2959) (actual time=135.809..136.054 rows=2453 loops=1)
Sort Key: namespaces.id, namespaces.name, namespaces.path, namespaces.owner_id, namespaces.created_at, namespaces.updated_at, namespaces.type, namespaces.description, namespaces.avatar, namespaces.membership_lock, namespaces.share_with_group_lock, namespaces.visibility_level, namespaces.request_access_enabled, namespaces.ldap_sync_status, namespaces.ldap_sync_error, namespaces.ldap_sync_last_update_at, namespaces.ldap_sync_last_successful_update_at, namespaces.ldap_sync_last_sync_at, namespaces.lfs_enabled, namespaces.description_html, namespaces.parent_id, namespaces.shared_runners_minutes_limit, namespaces.repository_size_limit, namespaces.require_two_factor_authentication, namespaces.two_factor_grace_period, namespaces.cached_markdown_version, namespaces.project_creation_level, namespaces.runners_token, namespaces.file_template_project_id, namespaces.saml_discovery_token, namespaces.runners_token_encrypted, namespaces.custom_project_templates_group_id, namespaces.auto_devops_enabled, namespaces.extra_shared_runners_minutes_limit, namespaces.last_ci_minutes_notification_at, namespaces.last_ci_minutes_usage_notification_level, namespaces.subgroup_creation_level, namespaces.max_pages_size, namespaces.max_artifacts_size, namespaces.marked_for_deletion_at, namespaces.marked_for_deletion_by_user_id, namespaces.mentions_disabled, namespaces.default_branch_protection, namespaces.max_personal_access_token_lifetime, namespaces.push_rule_id, namespaces.shared_runners_enabled, namespaces.allow_descendants_override_disabled_shared_runners, namespaces.traversal_ids, namespaces.organization_id
Sort Method: quicksort Memory: 1273kB
-> Append (cost=49448.18..65951.49 rows=36900 width=2959) (actual time=113.717..130.689 rows=2453 loops=1)
-> HashAggregate (cost=49448.18..49813.73 rows=36555 width=378) (actual time=113.715..114.757 rows=1432 loops=1)
Group Key: namespaces.id, namespaces.name, namespaces.path, namespaces.owner_id, namespaces.created_at, namespaces.updated_at, namespaces.type, namespaces.description, namespaces.avatar, namespaces.membership_lock, namespaces.share_with_group_lock, namespaces.visibility_level, namespaces.request_access_enabled, namespaces.ldap_sync_status, namespaces.ldap_sync_error, namespaces.ldap_sync_last_update_at, namespaces.ldap_sync_last_successful_update_at, namespaces.ldap_sync_last_sync_at, namespaces.lfs_enabled, namespaces.description_html, namespaces.parent_id, namespaces.shared_runners_minutes_limit, namespaces.repository_size_limit, namespaces.require_two_factor_authentication, namespaces.two_factor_grace_period, namespaces.cached_markdown_version, namespaces.project_creation_level, namespaces.runners_token, namespaces.file_template_project_id, namespaces.saml_discovery_token, namespaces.runners_token_encrypted, namespaces.custom_project_templates_group_id, namespaces.auto_devops_enabled, namespaces.extra_shared_runners_minutes_limit, namespaces.last_ci_minutes_notification_at, namespaces.last_ci_minutes_usage_notification_level, namespaces.subgroup_creation_level, namespaces.max_pages_size, namespaces.max_artifacts_size, namespaces.marked_for_deletion_at, namespaces.marked_for_deletion_by_user_id, namespaces.mentions_disabled, namespaces.default_branch_protection, namespaces.max_personal_access_token_lifetime, namespaces.push_rule_id, namespaces.shared_runners_enabled, namespaces.allow_descendants_override_disabled_shared_runners, namespaces.traversal_ids, namespaces.organization_id
Batches: 1 Memory Usage: 2631kB
CTE descendants_base_cte
-> CTE Scan on direct_groups namespaces_6 (cost=0.00..0.07 rows=1 width=36) (actual time=0.073..20.854 rows=1394 loops=1)
Filter: ((type)::text = 'Group'::text)
-> Nested Loop (cost=0.67..44970.12 rows=36555 width=378) (actual time=21.923..111.707 rows=1432 loops=1)
-> Nested Loop Anti Join (cost=0.00..0.07 rows=1 width=32) (actual time=21.568..106.769 rows=180 loops=1)
Join Filter: ((d2.id <> d1.id) AND (d2.id = ANY (d1.traversal_ids)))
Rows Removed by Join Filter: 484750
-> CTE Scan on descendants_base_cte d1 (cost=0.00..0.02 rows=1 width=36) (actual time=0.075..0.259 rows=1394 loops=1)
-> CTE Scan on descendants_base_cte d2 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.035 rows=349 loops=1394)
-> Index Scan using index_namespaces_on_traversal_ids_for_groups_btree on namespaces (cost=0.67..44604.51 rows=36555 width=378) (actual time=0.008..0.022 rows=8 loops=180)
Index Cond: ((traversal_ids < next_traversal_ids_sibling(d1.traversal_ids)) AND (traversal_ids >= d1.traversal_ids))
-> Nested Loop (cost=9430.44..15577.63 rows=344 width=378) (actual time=11.847..15.329 rows=1019 loops=1)
-> HashAggregate (cost=9430.01..9485.03 rows=5502 width=4) (actual time=11.825..12.037 rows=1025 loops=1)
Group Key: projects.namespace_id
Batches: 1 Memory Usage: 273kB
-> Nested Loop (cost=0.86..9416.25 rows=5502 width=4) (actual time=0.064..10.970 rows=5297 loops=1)
-> Index Only Scan using project_authorizations_pkey on project_authorizations (cost=0.43..177.11 rows=5502 width=4) (actual time=0.040..2.044 rows=5297 loops=1)
Index Cond: (user_id = 1614863)
Heap Fetches: 501
-> Index Only Scan using index_projects_on_id_and_namespace_id on projects (cost=0.43..1.68 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=5297)
Index Cond: (id = project_authorizations.project_id)
Heap Fetches: 523
-> Index Scan using namespaces_pkey on namespaces namespaces_1 (cost=0.43..1.11 rows=1 width=378) (actual time=0.003..0.003 rows=1 loops=1025)
Index Cond: (id = projects.namespace_id)
Filter: ((type)::text = 'Group'::text)
Rows Removed by Filter: 0
-> Nested Loop (cost=0.51..6.63 rows=1 width=378) (actual time=0.420..0.439 rows=2 loops=1)
-> Hash Semi Join (cost=0.08..3.18 rows=1 width=8) (actual time=0.370..0.383 rows=2 loops=1)
Hash Cond: (group_group_links.shared_with_group_id = namespaces_3.id)
-> Seq Scan on group_group_links (cost=0.00..2.86 rows=86 width=16) (actual time=0.010..0.020 rows=85 loops=1)
-> Hash (cost=0.07..0.07 rows=1 width=4) (actual time=0.340..0.341 rows=1394 loops=1)
Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 66kB
-> CTE Scan on direct_groups namespaces_3 (cost=0.00..0.07 rows=1 width=4) (actual time=0.003..0.202 rows=1394 loops=1)
Filter: ((type)::text = 'Group'::text)
-> Index Scan using namespaces_pkey on namespaces namespaces_2 (cost=0.43..3.45 rows=1 width=378) (actual time=0.025..0.025 rows=1 loops=2)
Index Cond: (id = group_group_links.shared_group_id)
Filter: ((type)::text = 'Group'::text)
-> Nested Loop Semi Join (cost=1.43..7.36 rows=1 width=378) (actual time=0.128..0.130 rows=0 loops=1)
-> Nested Loop (cost=0.86..6.62 rows=1 width=382) (actual time=0.127..0.129 rows=0 loops=1)
-> Index Scan using index_members_on_access_level on members (cost=0.43..3.17 rows=1 width=4) (actual time=0.127..0.127 rows=0 loops=1)
Index Cond: (access_level = 5)
Filter: (((type)::text = 'GroupMember'::text) AND ((source_type)::text = 'Namespace'::text) AND (user_id = 1614863))
Rows Removed by Filter: 11
-> Index Scan using namespaces_pkey on namespaces namespaces_4 (cost=0.43..3.45 rows=1 width=378) (never executed)
Index Cond: (id = members.source_id)
Filter: ((type)::text = 'Group'::text)
-> Nested Loop (cost=0.56..0.72 rows=1 width=4) (never executed)
-> Index Scan using index_gitlab_subscriptions_on_namespace_id on gitlab_subscriptions (cost=0.43..0.56 rows=1 width=8) (never executed)
Index Cond: (namespace_id = namespaces_4.id)
-> Index Scan using plans_pkey on plans (cost=0.14..0.16 rows=1 width=4) (never executed)
Index Cond: (id = gitlab_subscriptions.hosted_plan_id)
Filter: ((name)::text = ANY ('{silver,premium,premium_trial,gold,ultimate,ultimate_trial,ultimate_trial_paid_customer,opensource}'::text[]))
-> Index Scan using index_routes_on_source_type_and_source_id on routes (cost=0.43..2.57 rows=1 width=48) (actual time=0.007..0.007 rows=1 loops=1438)
Index Cond: (((source_type)::text = 'Namespace'::text) AND (source_id = namespaces.id))
Filter: ((replace((name)::text, ' '::text, ''::text) ~~* '%gitlab%'::text) OR ((path)::text ~~* '%gitlab%'::text))
Rows Removed by Filter: 0
Planning Time: 12.373 ms
Execution Time: 154.093 msSQL
SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."lfs_enabled", "namespaces"."description_html", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."marked_for_deletion_at", "namespaces"."marked_for_deletion_by_user_id", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids", "namespaces"."organization_id" FROM ((WITH "direct_groups" AS MATERIALIZED (SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."lfs_enabled", "namespaces"."description_html", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."marked_for_deletion_at", "namespaces"."marked_for_deletion_by_user_id", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids", "namespaces"."organization_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)) SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."lfs_enabled", "namespaces"."description_html", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."marked_for_deletion_at", "namespaces"."marked_for_deletion_by_user_id", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids", "namespaces"."organization_id" FROM ((WITH "descendants_base_cte" AS MATERIALIZED (SELECT "id", "traversal_ids" FROM "direct_groups" "namespaces" WHERE "namespaces"."type" = 'Group'), "superset" AS (SELECT d1.traversal_ids
FROM descendants_base_cte d1
WHERE NOT EXISTS (
SELECT 1
FROM descendants_base_cte d2
WHERE d2.id = ANY(d1.traversal_ids)
AND d2.id <> d1.id
)
) SELECT DISTINCT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."lfs_enabled", "namespaces"."description_html", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."marked_for_deletion_at", "namespaces"."marked_for_deletion_by_user_id", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids", "namespaces"."organization_id" FROM "superset", "namespaces" WHERE "namespaces"."type" = 'Group' AND next_traversal_ids_sibling("superset"."traversal_ids") > "namespaces"."traversal_ids" AND "superset"."traversal_ids" <= "namespaces"."traversal_ids")
UNION
(SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."lfs_enabled", "namespaces"."description_html", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."marked_for_deletion_at", "namespaces"."marked_for_deletion_by_user_id", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids", "namespaces"."organization_id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" IN (SELECT "projects"."namespace_id" FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "project_authorizations"."user_id" = 1614863))
UNION
(SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."lfs_enabled", "namespaces"."description_html", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."marked_for_deletion_at", "namespaces"."marked_for_deletion_by_user_id", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids", "namespaces"."organization_id" 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')
UNION
(SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."lfs_enabled", "namespaces"."description_html", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."marked_for_deletion_at", "namespaces"."marked_for_deletion_by_user_id", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids", "namespaces"."organization_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"."access_level" = 5 AND (EXISTS (SELECT 1 FROM "plans" INNER JOIN "gitlab_subscriptions" ON "gitlab_subscriptions"."hosted_plan_id" = "plans"."id" WHERE "plans"."name" IN ('silver', 'premium', 'premium_trial', 'gold', 'ultimate', 'ultimate_trial', 'ultimate_trial_paid_customer', 'opensource') AND (gitlab_subscriptions.namespace_id = namespaces.id))))) namespaces INNER JOIN "routes" ON "routes"."source_type" = 'Namespace' AND "routes"."source_id" = "namespaces"."id" WHERE "namespaces"."type" = 'Group' AND (REPLACE(routes.name, ' ', '') ILIKE '%gitlab%' OR routes.path ILIKE '%gitlab%') ORDER BY CASE WHEN starts_with(REPLACE(routes.name, ' ', ''), 'gitlab') OR starts_with(routes.path, 'gitlab') THEN 1 ELSE 2 END, routes.path LIMIT 10MR 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.
Edited by Heinrich Lee Yu