Call to `/-/children.json` are very slow (about one minute)

Summary

When filtering groups (even small ones), the call to /-/children.json takes forever.

Steps to reproduce

On a CE gitlab instance (latest version), we have many groups, but even in smallest ones, like:

A
- B
  - project1
  - project2 if I search for project in the A group page, the call to /-/children.json?filter=project takes dozen of seconds.

What is the current bug behavior?

call to https://ourgit/A/-/children.json?filter=web takes ~ a minute to answer

What is the expected correct behavior?

it should answer within few seconds at most

Relevant logs and/or screenshots

Activating performance bar gives some insight. Looks like the WITH RECURSIVE "base_and_descendants" ... requests from Groups::ChildrenController#index each takes 10+ seconds. Full request is :

/*application:web,correlation_id:01F64K33CVNQ9CZ79EBF7XHDYM,endpoint_id:Groups::ChildrenController#index*/ WITH RECURSIVE "base_and_ancestors" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" IN (WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 3730) 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" WHERE "namespaces"."id" NOT IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 3730) AND ("namespaces"."visibility_level" IN (0, 10, 20) OR EXISTS (SELECT 1 FROM (WITH RECURSIVE "base_and_ancestors" AS ((SELECT "namespaces".* FROM ((WITH "direct_groups" AS MATERIALIZED (SELECT "namespaces".* FROM ((SELECT "namespaces".* 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" = 2 AND "members"."requested_at" IS NULL AND (access_level >= 10)) UNION (SELECT namespaces.* FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id" WHERE "project_authorizations"."user_id" = 2)) namespaces WHERE "namespaces"."type" = 'Group') SELECT "namespaces".* FROM ((SELECT "namespaces".* FROM "direct_groups" "namespaces" WHERE "namespaces"."type" = 'Group') UNION (SELECT "namespaces".* 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')) namespaces WHERE "namespaces"."type" = 'Group') UNION (SELECT "namespaces".* FROM "namespaces", "base_and_ancestors" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = "base_and_ancestors"."parent_id")), "base_and_descendants" AS ((SELECT "namespaces".* 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" = 2 AND "members"."requested_at" IS NULL AND (access_level >= 10)) UNION (SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."type" = 'Group' AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces".* FROM ((SELECT "namespaces".* FROM "base_and_ancestors" AS "namespaces" WHERE "namespaces"."type" = 'Group') UNION (SELECT "namespaces".* FROM "base_and_descendants" AS "namespaces" WHERE "namespaces"."type" = 'Group')) namespaces WHERE "namespaces"."type" = 'Group' ORDER BY "namespaces"."id" DESC) authorized WHERE authorized."id" = "namespaces"."id")) AND ("namespaces"."path" ILIKE 'A' OR "namespaces"."name" ILIKE 'A') ORDER BY LOWER("namespaces"."name") ASC)) UNION (SELECT "namespaces".* FROM "namespaces", "base_and_ancestors" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = "base_and_ancestors"."parent_id" AND "base_and_ancestors"."parent_id" != 3730)) SELECT namespaces.*, (SELECT COUNT(*) AS preloaded_project_count FROM "projects" WHERE "projects"."namespace_id" = "namespaces"."id" AND "projects"."archived" != TRUE) AS preloaded_project_count, (SELECT COUNT(*) AS preloaded_member_count FROM "members" WHERE "members"."source_type" = 'Namespace' AND "members"."source_id" = "namespaces"."id" AND "members"."requested_at" IS NULL AND "members"."access_level" > 5) AS preloaded_member_count, (SELECT COUNT(*) AS preloaded_subgroup_count FROM "namespaces" "children" WHERE "children"."parent_id" = "namespaces"."id") AS preloaded_subgroup_count FROM "base_and_ancestors" AS "namespaces"

Results of GitLab environment info

Expand for output related to GitLab environment info


System information
System:	Ubuntu 18.04
Proxy:	        no
Current User:	git
Using RVM:	no
Ruby Version:	2.7.2p137
Gem Version:	3.1.4
Bundler Version:2.1.4
Rake Version:	13.0.3
Redis Version:	6.0.12
Git Version:	2.31.1
Sidekiq Version:5.2.9
Go Version:	unknown

GitLab information
Version:	13.11.3-ee
Revision:	7fde0affe23
Directory:	/opt/gitlab/embedded/service/gitlab-rails
DB Adapter:	PostgreSQL
DB Version:	12.6
URL:	        https://xxx
HTTP Clone URL:https://xxx/some-group/some-project.git
SSH Clone URL:	git@xxx:some-group/some-project.git
Elasticsearch:	no
Geo:	        no
Using LDAP:	yes
Using Omniauth:yes
Omniauth Providers: saml

GitLab Shell
Version:	13.17.0
Repository storage paths:
- default: 	/var/opt/gitlab/git-data/repositories
GitLab Shell path:	        /opt/gitlab/embedded/service/gitlab-shell
Git:	        /opt/gitlab/embedded/bin/git