User search filtered to group times out

Summary

Searching for a user filtered to a group sometimes (but not always) results in a 500 error.

User search always goes through DB queries, even with Elasticsearch enabled.

Steps to reproduce

1. Go to https://gitlab.com/search
2. Enter and submit a search term, e.g. gitlab
3. Click on the Users tab
4. Add a group filter by selecting one in the dropdown, e.g. gitlab-org

Example Project

https://gitlab.com/search?group_id=9970&scope=users&search=gitlab

What is the current bug behavior?

A 500 error is returned.

What is the expected correct behavior?

The search results should be displayed.

Relevant logs and/or screenshots

Running locally in development, the generated SQL query looks like this:

SELECT COUNT(*)
FROM
  (SELECT 1 AS one
   FROM "users"
   WHERE (("users"."name" ILIKE '%gitlab%'
           OR "users"."username" ILIKE '%gitlab%')
          OR "users"."email" = 'gitlab')
     AND "users"."id" IN
       (SELECT "users"."id"
        FROM
          (SELECT "users".*
           FROM "users"
           WHERE "users"."id" IN
               (SELECT "members"."user_id"
                FROM "members"
                LEFT OUTER JOIN "users" ON "members"."user_id" = "users"."id"
                WHERE "members"."type" IN ('GroupMember')
                  AND "members"."source_type" = 'Namespace'
                  AND "users"."state" = 'active'
                  AND "members"."requested_at" IS NULL
                  AND "members"."source_id" IN
                    (WITH RECURSIVE "base_and_ancestors" AS
                       (SELECT "namespaces".*
                        FROM "namespaces"
                        WHERE "namespaces"."type" IN ('Group')
                          AND "namespaces"."id" = 2
                        UNION SELECT "namespaces".*
                        FROM "namespaces",
                             "base_and_ancestors"
                        WHERE "namespaces"."type" IN ('Group')
                          AND "namespaces"."id" = "base_and_ancestors"."parent_id"),
                                    "base_and_descendants" AS
                       (SELECT "namespaces".*
                        FROM "namespaces"
                        WHERE "namespaces"."type" IN ('Group')
                          AND "namespaces"."id" = 2
                        UNION SELECT "namespaces".*
                        FROM "namespaces",
                             "base_and_descendants"
                        WHERE "namespaces"."type" IN ('Group')
                          AND "namespaces"."parent_id" = "base_and_descendants"."id") SELECT "namespaces"."id"
                     FROM
                       (SELECT "namespaces".*
                        FROM "base_and_ancestors" AS "namespaces"
                        WHERE "namespaces"."type" IN ('Group')
                        UNION SELECT "namespaces".*
                        FROM "base_and_descendants" AS "namespaces"
                        WHERE "namespaces"."type" IN ('Group')) namespaces
                     WHERE "namespaces"."type" IN ('Group')))
           UNION SELECT "users".*
           FROM "users"
           INNER JOIN "members" ON "members"."user_id" = "users"."id"
           AND "members"."type" IN ('ProjectMember')
           AND "members"."source_type" = 'Project'
           AND "members"."requested_at" IS NULL
           INNER JOIN "projects" ON "projects"."id" = "members"."source_id"
           INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
           AND "namespaces"."type" IN ('Group')
           AND "namespaces"."type" = 'Group'
           WHERE "namespaces"."id" IN
               (WITH RECURSIVE "base_and_descendants" AS
                  (SELECT "namespaces".*
                   FROM "namespaces"
                   WHERE "namespaces"."type" IN ('Group')
                     AND "namespaces"."id" = 2
                   UNION SELECT "namespaces".*
                   FROM "namespaces",
                        "base_and_descendants"
                   WHERE "namespaces"."type" IN ('Group')
                     AND "namespaces"."parent_id" = "base_and_descendants"."id") SELECT "id"
                FROM "base_and_descendants" AS "namespaces")) users)
     AND "users"."id" IN
       (SELECT members.user_id
        FROM "namespaces"
        INNER JOIN "members" ON "members"."source_id" = "namespaces"."id"
        AND "members"."type" IN ('GroupMember')
        AND "members"."source_type" = 'Namespace'
        AND "members"."source_type" = 'Namespace'
        AND "members"."requested_at" IS NULL
        INNER JOIN "users" ON "users"."id" = "members"."user_id"
        WHERE "namespaces"."type" IN ('Group')
        ORDER BY "namespaces"."id" DESC)
   LIMIT 1001) subquery_for_count;

Output of checks

This bug happens on GitLab.com

Possible fixes

The query seems to check for descendant groups through multiple subqueries, not sure if this is really necessary or duplicating work.

The query seems to check for ancestor groups as well, which doesn't seem necessary for this use-case.