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

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

Relevant logs and/or screenshots

Screenshot_2022-11-01_at_15.11.17

Possible fixes