Tune user_group_member_roles SQL query for members page

What does this MR do and why?

• Follows from the incident https://gitlab.com/gitlab-org/gitlab/-/issues/576075 where the SQL query using user_group_member_roles to get a list of members caused a spike.
• Instead of doing a LEFT OUTER JOIN do a UNION on shared_members (responsible for computing least access level) and shared_members_with_member_role (responsible for computing member role).
• The change is behind a feature flag.

Database

SQL Query with FF disabled

SELECT
  COUNT(*)
FROM
  (
    SELECT
      1 AS one
    FROM
      (
        SELECT
          DISTINCT ON (user_id, invite_email) member_union.id,
          COALESCE(
            project_authorizations.access_level,
            member_union.access_level
          ) access_level,
          member_union.source_id,
          member_union.source_type,
          member_union.user_id,
          member_union.notification_level,
          member_union.type,
          member_union.created_at,
          member_union.updated_at,
          member_union.created_by_id,
          member_union.invite_email,
          member_union.invite_token,
          member_union.invite_accepted_at,
          member_union.requested_at,
          member_union.expires_at,
          member_union.ldap,
          member_union.override,
          member_union.state,
          member_union.invite_email_success,
          member_union.member_namespace_id,
          member_union.member_role_id,
          member_union.expiry_notified_at,
          member_union.request_accepted_at
        FROM
          (
            (
              SELECT
                "members"."id",
                "members"."access_level",
                "members"."source_id",
                "members"."source_type",
                "members"."user_id",
                "members"."notification_level",
                "members"."type",
                "members"."created_at",
                "members"."updated_at",
                "members"."created_by_id",
                "members"."invite_email",
                "members"."invite_token",
                "members"."invite_accepted_at",
                "members"."requested_at",
                "members"."expires_at",
                "members"."ldap",
                "members"."override",
                "members"."state",
                "members"."invite_email_success",
                "members"."member_namespace_id",
                "members"."member_role_id",
                "members"."expiry_notified_at",
                "members"."request_accepted_at"
              FROM
                (
                  SELECT
                    DISTINCT ON (user_id, invite_email) *
                  FROM
                    (
                      (
                        SELECT
                          "members"."id",
                          "members"."access_level",
                          "members"."source_id",
                          "members"."source_type",
                          "members"."user_id",
                          "members"."notification_level",
                          "members"."type",
                          "members"."created_at",
                          "members"."updated_at",
                          "members"."created_by_id",
                          "members"."invite_email",
                          "members"."invite_token",
                          "members"."invite_accepted_at",
                          "members"."requested_at",
                          "members"."expires_at",
                          "members"."ldap",
                          "members"."override",
                          "members"."state",
                          "members"."invite_email_success",
                          "members"."member_namespace_id",
                          "members"."member_role_id",
                          "members"."expiry_notified_at",
                          "members"."request_accepted_at"
                        FROM
                          "members"
                        WHERE
                          "members"."type" = 'GroupMember'
                          AND "members"."source_type" = 'Namespace'
                          AND "members"."requested_at" IS NULL
                          AND "members"."source_id" IN (
                            SELECT
                              "namespaces"."id"
                            FROM
                              (
                                (
                                  SELECT
                                    "namespaces"."id",
                                    "namespaces"."name",
                                    "namespaces"."path",
                                    "namespaces"."owner_id",
                                    "namespaces"."created_at",
                                    "namespaces"."updated_at",
                                    "namespaces"."type",
                                    "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"."parent_id",
                                    "namespaces"."shared_runners_minutes_limit",
                                    "namespaces"."repository_size_limit",
                                    "namespaces"."require_two_factor_authentication",
                                    "namespaces"."two_factor_grace_period",
                                    "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"."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",
                                    "namespaces"."state"
                                  FROM
                                    "namespaces"
                                  WHERE
                                    "namespaces"."type" = 'Group'
                                    AND "namespaces"."id" = 81415389
                                )
                                UNION
                                (
                                  SELECT
                                    "namespaces"."id",
                                    "namespaces"."name",
                                    "namespaces"."path",
                                    "namespaces"."owner_id",
                                    "namespaces"."created_at",
                                    "namespaces"."updated_at",
                                    "namespaces"."type",
                                    "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"."parent_id",
                                    "namespaces"."shared_runners_minutes_limit",
                                    "namespaces"."repository_size_limit",
                                    "namespaces"."require_two_factor_authentication",
                                    "namespaces"."two_factor_grace_period",
                                    "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"."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",
                                    "namespaces"."state"
                                  FROM
                                    "namespaces"
                                  WHERE
                                    "namespaces"."type" = 'Group'
                                    AND "namespaces"."id" = 11968
                                )
                              ) namespaces
                            WHERE
                              "namespaces"."type" = 'Group'
                          )
                      )
                      UNION
                      (
                        SELECT
                          "members"."id",
                          LEAST(
                            "group_group_links"."group_access",
                            "members"."access_level"
                          ) AS access_level,
                          "members"."source_id",
                          "members"."source_type",
                          "members"."user_id",
                          "members"."notification_level",
                          "members"."type",
                          "members"."created_at",
                          "members"."updated_at",
                          "members"."created_by_id",
                          "members"."invite_email",
                          "members"."invite_token",
                          "members"."invite_accepted_at",
                          "members"."requested_at",
                          "members"."expires_at",
                          "members"."ldap",
                          "members"."override",
                          "members"."state",
                          "members"."invite_email_success",
                          "members"."member_namespace_id",
                          CASE
                            WHEN "members"."access_level" > "group_group_links"."group_access" THEN "group_group_links"."member_role_id"
                            WHEN "members"."access_level" < "group_group_links"."group_access" THEN "members"."member_role_id"
                            WHEN "group_group_links"."member_role_id" IS NULL THEN NULL
                            ELSE "members"."member_role_id"
                          END,
                          "members"."expiry_notified_at",
                          "members"."request_accepted_at"
                        FROM
                          "members"
                          JOIN group_group_links ON members.source_id = group_group_links.shared_with_group_id
                        WHERE
                          "members"."type" = 'GroupMember'
                          AND "members"."source_type" = 'Namespace'
                          AND "members"."requested_at" IS NULL
                          AND "members"."source_id" IN (
                            SELECT
                              "namespaces"."id"
                            FROM
                              "namespaces"
                              INNER JOIN "group_group_links" ON "group_group_links"."shared_with_group_id" = "namespaces"."id"
                            WHERE
                              "namespaces"."type" = 'Group'
                              AND "group_group_links"."shared_group_id" IN (
                                SELECT
                                  "namespaces"."id"
                                FROM
                                  "namespaces"
                                WHERE
                                  "namespaces"."type" = 'Group'
                                  AND "namespaces"."id" IN (4249178, 5810708, 7340026, 81415389)
                              )
                          )
                          AND "group_group_links"."shared_group_id" IN (
                            SELECT
                              "namespaces"."id"
                            FROM
                              "namespaces"
                            WHERE
                              "namespaces"."type" = 'Group'
                              AND "namespaces"."id" IN (4249178, 5810708, 7340026, 81415389)
                          )
                      )
                    ) members
                  WHERE
                    "members"."type" = 'GroupMember'
                    AND "members"."source_type" = 'Namespace'
                    AND (members.access_level > 5)
                  ORDER BY
                    user_id,
                    invite_email,
                    CASE
                      WHEN source_id = 81415389
                      and source_type = 'Namespace' THEN access_level + 1
                      ELSE access_level
                    END DESC,
                    member_role_id ASC,
                    expires_at DESC,
                    created_at ASC
                ) members
              WHERE
                "members"."type" = 'GroupMember'
                AND "members"."invite_token" IS NULL
                AND (members.access_level > 5)
            )
            UNION
            ALL (
              SELECT
                "members"."id",
                "members"."access_level",
                "members"."source_id",
                "members"."source_type",
                "members"."user_id",
                "members"."notification_level",
                "members"."type",
                "members"."created_at",
                "members"."updated_at",
                "members"."created_by_id",
                "members"."invite_email",
                "members"."invite_token",
                "members"."invite_accepted_at",
                "members"."requested_at",
                "members"."expires_at",
                "members"."ldap",
                "members"."override",
                "members"."state",
                "members"."invite_email_success",
                "members"."member_namespace_id",
                "members"."member_role_id",
                "members"."expiry_notified_at",
                "members"."request_accepted_at"
              FROM
                "members"
              WHERE
                "members"."type" = 'GroupMember'
                AND "members"."source_type" = 'Namespace'
                AND "members"."source_id" IN (
                  WITH "base_ancestors_cte" AS MATERIALIZED (
                    SELECT
                      "namespaces"."traversal_ids"
                    FROM
                      "namespaces"
                      INNER JOIN "project_group_links" ON "namespaces"."id" = "project_group_links"."group_id"
                    WHERE
                      "namespaces"."type" = 'Group'
                      AND "project_group_links"."project_id" = 54456184
                  )
                  SELECT
                    "id"
                  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 (members.access_level > 5)
            )
            UNION
            ALL (
              SELECT
                "members"."id",
                "members"."access_level",
                "members"."source_id",
                "members"."source_type",
                "members"."user_id",
                "members"."notification_level",
                "members"."type",
                "members"."created_at",
                "members"."updated_at",
                "members"."created_by_id",
                "members"."invite_email",
                "members"."invite_token",
                "members"."invite_accepted_at",
                "members"."requested_at",
                "members"."expires_at",
                "members"."ldap",
                "members"."override",
                "members"."state",
                "members"."invite_email_success",
                "members"."member_namespace_id",
                "members"."member_role_id",
                "members"."expiry_notified_at",
                "members"."request_accepted_at"
              FROM
                "members"
              WHERE
                "members"."type" = 'ProjectMember'
                AND "members"."member_namespace_id" = 81791485
                AND "members"."requested_at" IS NULL
            )
          ) AS member_union
          LEFT JOIN project_authorizations on project_authorizations.user_id = member_union.user_id
          AND project_authorizations.project_id = 54456184
        ORDER BY
          user_id,
          invite_email,
          CASE
            WHEN type = 'ProjectMember' THEN 1
            WHEN type = 'GroupMember' THEN 2
            ELSE 3
          END
      ) AS members
    WHERE
      "members"."invite_token" IS NULL
      AND "members"."requested_at" IS NULL
    LIMIT
      10001
  ) subquery_for_count

SQL Query with FF enabled

SELECT
  COUNT(*)
FROM
  (
    SELECT
      1 AS one
    FROM
      (
        SELECT
          DISTINCT ON (user_id, invite_email) member_union.id,
          COALESCE(
            project_authorizations.access_level,
            member_union.access_level
          ) access_level,
          member_union.source_id,
          member_union.source_type,
          member_union.user_id,
          member_union.notification_level,
          member_union.type,
          member_union.created_at,
          member_union.updated_at,
          member_union.created_by_id,
          member_union.invite_email,
          member_union.invite_token,
          member_union.invite_accepted_at,
          member_union.requested_at,
          member_union.expires_at,
          member_union.ldap,
          member_union.override,
          member_union.state,
          member_union.invite_email_success,
          member_union.member_namespace_id,
          member_union.member_role_id,
          member_union.expiry_notified_at,
          member_union.request_accepted_at
        FROM
          (
            (
              SELECT
                "members"."id",
                "members"."access_level",
                "members"."source_id",
                "members"."source_type",
                "members"."user_id",
                "members"."notification_level",
                "members"."type",
                "members"."created_at",
                "members"."updated_at",
                "members"."created_by_id",
                "members"."invite_email",
                "members"."invite_token",
                "members"."invite_accepted_at",
                "members"."requested_at",
                "members"."expires_at",
                "members"."ldap",
                "members"."override",
                "members"."state",
                "members"."invite_email_success",
                "members"."member_namespace_id",
                "members"."member_role_id",
                "members"."expiry_notified_at",
                "members"."request_accepted_at"
              FROM
                (
                  SELECT
                    DISTINCT ON (user_id, invite_email) *
                  FROM
                    (
                      (
                        SELECT
                          "members"."id",
                          "members"."access_level",
                          "members"."source_id",
                          "members"."source_type",
                          "members"."user_id",
                          "members"."notification_level",
                          "members"."type",
                          "members"."created_at",
                          "members"."updated_at",
                          "members"."created_by_id",
                          "members"."invite_email",
                          "members"."invite_token",
                          "members"."invite_accepted_at",
                          "members"."requested_at",
                          "members"."expires_at",
                          "members"."ldap",
                          "members"."override",
                          "members"."state",
                          "members"."invite_email_success",
                          "members"."member_namespace_id",
                          "members"."member_role_id",
                          "members"."expiry_notified_at",
                          "members"."request_accepted_at"
                        FROM
                          "members"
                        WHERE
                          "members"."type" = 'GroupMember'
                          AND "members"."source_type" = 'Namespace'
                          AND "members"."requested_at" IS NULL
                          AND "members"."source_id" IN (
                            SELECT
                              "namespaces"."id"
                            FROM
                              (
                                (
                                  SELECT
                                    "namespaces"."id",
                                    "namespaces"."name",
                                    "namespaces"."path",
                                    "namespaces"."owner_id",
                                    "namespaces"."created_at",
                                    "namespaces"."updated_at",
                                    "namespaces"."type",
                                    "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"."parent_id",
                                    "namespaces"."shared_runners_minutes_limit",
                                    "namespaces"."repository_size_limit",
                                    "namespaces"."require_two_factor_authentication",
                                    "namespaces"."two_factor_grace_period",
                                    "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"."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",
                                    "namespaces"."state"
                                  FROM
                                    "namespaces"
                                  WHERE
                                    "namespaces"."type" = 'Group'
                                    AND "namespaces"."id" = 81415389
                                )
                                UNION
                                (
                                  SELECT
                                    "namespaces"."id",
                                    "namespaces"."name",
                                    "namespaces"."path",
                                    "namespaces"."owner_id",
                                    "namespaces"."created_at",
                                    "namespaces"."updated_at",
                                    "namespaces"."type",
                                    "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"."parent_id",
                                    "namespaces"."shared_runners_minutes_limit",
                                    "namespaces"."repository_size_limit",
                                    "namespaces"."require_two_factor_authentication",
                                    "namespaces"."two_factor_grace_period",
                                    "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"."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",
                                    "namespaces"."state"
                                  FROM
                                    "namespaces"
                                  WHERE
                                    "namespaces"."type" = 'Group'
                                    AND "namespaces"."id" = 11968
                                )
                              ) namespaces
                            WHERE
                              "namespaces"."type" = 'Group'
                          )
                      )
                      UNION
                      (
                        SELECT
                          "members"."id",
                          LEAST(
                            "group_group_links"."group_access",
                            "members"."access_level"
                          ) AS access_level,
                          "members"."source_id",
                          "members"."source_type",
                          "members"."user_id",
                          "members"."notification_level",
                          "members"."type",
                          "members"."created_at",
                          "members"."updated_at",
                          "members"."created_by_id",
                          "members"."invite_email",
                          "members"."invite_token",
                          "members"."invite_accepted_at",
                          "members"."requested_at",
                          "members"."expires_at",
                          "members"."ldap",
                          "members"."override",
                          "members"."state",
                          "members"."invite_email_success",
                          "members"."member_namespace_id",
                          NULL AS member_role_id,
                          "members"."expiry_notified_at",
                          "members"."request_accepted_at"
                        FROM
                          "members"
                          JOIN group_group_links ON members.source_id = group_group_links.shared_with_group_id
                        WHERE
                          "members"."type" = 'GroupMember'
                          AND "members"."source_type" = 'Namespace'
                          AND "members"."requested_at" IS NULL
                          AND "members"."source_id" IN (
                            SELECT
                              "namespaces"."id"
                            FROM
                              "namespaces"
                              INNER JOIN "group_group_links" ON "group_group_links"."shared_with_group_id" = "namespaces"."id"
                            WHERE
                              "namespaces"."type" = 'Group'
                              AND "group_group_links"."shared_group_id" IN (
                                SELECT
                                  "namespaces"."id"
                                FROM
                                  "namespaces"
                                WHERE
                                  "namespaces"."type" = 'Group'
                                  AND "namespaces"."id" IN (4249178, 5810708, 7340026, 81415389)
                              )
                          )
                          AND "group_group_links"."shared_group_id" IN (
                            SELECT
                              "namespaces"."id"
                            FROM
                              "namespaces"
                            WHERE
                              "namespaces"."type" = 'Group'
                              AND "namespaces"."id" IN (4249178, 5810708, 7340026, 81415389)
                          )
                      )
                    ) members
                  WHERE
                    "members"."type" = 'GroupMember'
                    AND "members"."source_type" = 'Namespace'
                    AND (members.access_level > 5)
                  ORDER BY
                    user_id,
                    invite_email,
                    CASE
                      WHEN source_id = 81415389
                      and source_type = 'Namespace' THEN access_level + 1
                      ELSE access_level
                    END DESC,
                    member_role_id ASC,
                    expires_at DESC,
                    created_at ASC
                ) members
              WHERE
                "members"."type" = 'GroupMember'
                AND "members"."invite_token" IS NULL
                AND (members.access_level > 5)
            )
            UNION
            ALL (
              SELECT
                "members"."id",
                "members"."access_level",
                "members"."source_id",
                "members"."source_type",
                "members"."user_id",
                "members"."notification_level",
                "members"."type",
                "members"."created_at",
                "members"."updated_at",
                "members"."created_by_id",
                "members"."invite_email",
                "members"."invite_token",
                "members"."invite_accepted_at",
                "members"."requested_at",
                "members"."expires_at",
                "members"."ldap",
                "members"."override",
                "members"."state",
                "members"."invite_email_success",
                "members"."member_namespace_id",
                "members"."member_role_id",
                "members"."expiry_notified_at",
                "members"."request_accepted_at"
              FROM
                "members"
              WHERE
                "members"."type" = 'GroupMember'
                AND "members"."source_type" = 'Namespace'
                AND "members"."source_id" IN (
                  WITH "base_ancestors_cte" AS MATERIALIZED (
                    SELECT
                      "namespaces"."traversal_ids"
                    FROM
                      "namespaces"
                      INNER JOIN "project_group_links" ON "namespaces"."id" = "project_group_links"."group_id"
                    WHERE
                      "namespaces"."type" = 'Group'
                      AND "project_group_links"."project_id" = 54456184
                  )
                  SELECT
                    "id"
                  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 (members.access_level > 5)
            )
            UNION
            ALL (
              SELECT
                "members"."id",
                "members"."access_level",
                "members"."source_id",
                "members"."source_type",
                "members"."user_id",
                "members"."notification_level",
                "members"."type",
                "members"."created_at",
                "members"."updated_at",
                "members"."created_by_id",
                "members"."invite_email",
                "members"."invite_token",
                "members"."invite_accepted_at",
                "members"."requested_at",
                "members"."expires_at",
                "members"."ldap",
                "members"."override",
                "members"."state",
                "members"."invite_email_success",
                "members"."member_namespace_id",
                "members"."member_role_id",
                "members"."expiry_notified_at",
                "members"."request_accepted_at"
              FROM
                "members"
              WHERE
                "members"."type" = 'ProjectMember'
                AND "members"."member_namespace_id" = 81791485
                AND "members"."requested_at" IS NULL
            )
          ) AS member_union
          LEFT JOIN project_authorizations on project_authorizations.user_id = member_union.user_id
          AND project_authorizations.project_id = 54456184
        ORDER BY
          user_id,
          invite_email,
          CASE
            WHEN type = 'ProjectMember' THEN 1
            WHEN type = 'GroupMember' THEN 2
            ELSE 3
          END
      ) AS members
    WHERE
      "members"."invite_token" IS NULL
      AND "members"."requested_at" IS NULL
    LIMIT
      10001
  ) subquery_for_count

Query plans:

• Query that caused the incident
• Query with FF disabled
• Query with FF enabled

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #576075