Group project template tab can't be opened on Sass
Summary
https://gitlab.com/projects/new#create_from_template page is slow because it executes the following SQL query:
Query
SELECT COUNT(*)
FROM “projects”
WHERE “projects”.“namespace_id” IN
(WITH RECURSIVE “base_and_descendants” AS (
(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”.“emails_disabled”, “namespaces”.“max_pages_size”, “namespaces”.“max_artifacts_size”, “namespaces”.“mentions_disabled”, “namespaces”.“default_branch_protection”, “namespaces”.“unlock_membership_to_ldap”, “namespaces”.“max_personal_access_token_lifetime”, “namespaces”.“push_rule_id”, “namespaces”.“shared_runners_enabled”, “namespaces”.“allow_descendants_override_disabled_shared_runners”, “namespaces”.“traversal_ids”
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” = 1161495
AND “members”.“requested_at” IS NULL
AND (access_level >= 10)
AND (members.access_level >= 30))
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”.“emails_disabled”, “namespaces”.“max_pages_size”, “namespaces”.“max_artifacts_size”, “namespaces”.“mentions_disabled”, “namespaces”.“default_branch_protection”, “namespaces”.“unlock_membership_to_ldap”, “namespaces”.“max_personal_access_token_lifetime”, “namespaces”.“push_rule_id”, “namespaces”.“shared_runners_enabled”, “namespaces”.“allow_descendants_override_disabled_shared_runners”, “namespaces”.“traversal_ids”
FROM “namespaces”, “base_and_descendants”
WHERE “namespaces”.“TYPE” = ‘GROUP’
AND “namespaces”.“parent_id” = “base_and_descendants”.“id”)) SELECT DISTINCT “namespaces”.“id”
FROM “base_and_descendants” AS “namespaces”
INNER JOIN “projects” ON “projects”.“namespace_id” = “namespaces”.“id”
WHERE “namespaces”.“id” IN
(WITH “descendants_base_cte” AS MATERIALIZED
(WITH “base_ancestors_cte” AS MATERIALIZED
(SELECT “namespaces”.“traversal_ids”
FROM “namespaces”
WHERE “namespaces”.“TYPE” = ‘GROUP’
AND “namespaces”.“custom_project_templates_group_id” IS NOT NULL) SELECT “id”, “traversal_ids”
FROM “namespaces”
INNER JOIN
(SELECT DISTINCT unnest(“base_ancestors_cte”.“traversal_ids”)
FROM base_ancestors_cte) AS ancestors(ancestor_id) ON namespaces.id = ancestors.ancestor_id
WHERE “namespaces”.“TYPE” = ‘GROUP’
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’, ‘opensource’)
AND (gitlab_subscriptions.namespace_id = namespaces.id)))), “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 “custom_project_templates_group_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”
AND “namespaces”.“custom_project_templates_group_id” IS NOT NULL))
AND “projects”.“marked_for_deletion_at” IS NULL
AND “projects”.“pending_delete” = FALSE
Query plan: https://files.slack.com/files-pri/T02592416-F0491S26QCU/plan-text.txt
The Group 65
tab can't be opened because it executes multiple similar queries.
Steps to reproduce
- Enable Performance bar (
pb
) - Go to https://gitlab.com/projects/new#create_from_template
- Verify the executed queries
- One of them takes ~3s to execute
- Go to
Group 65
tab - Can't be opened
Example Project
What is the current bug behavior?
- Projects#new page is slower
- Group project templates tab can't be opened
What is the expected correct behavior?
- The slow query is faster or not executed
- Group project templates tab can be opened