Resolve "Improve performance of Search API (Advanced): users scope"

What does this MR do?

Issue #215711 (closed)

In researching this issue, I saw a few things that result in duplicate calls to the database.

• In search_service.rb the redact_unathorized_results method calls .to_a on the results, redacts data, and chains the id_not_in method onto the results_collection even if nothing was redacted
• In offset_pagination.rb the paginate_with_limit_optimization method always chains page and per methods onto the relation. The Search API sends in pre-paginated data to this method which results in a un-needed database call when adding pagination headers.

This MR attempts to address both areas:

• only call id_not_in on results_collection data when results were redacted
• check if the data was already paginated within before applying page and per methods

Note: the users scope never goes to ElasticSearch

Query Updates

GET "/api/v4/projects/63/search?scope=users&search=ro"

Before

Started GET "/api/v4/projects/63/search?scope=users&search=ro" for 127.0.0.1 at 2020-06-10 13:41:03 -0400
Gitlab::Metrics::Samplers::DatabaseSampler: A copy of EE::Gitlab::Metrics::Samplers::DatabaseSampler has been removed from the module tree but is still active!, stopping
Creating scope :of_projects. Overwriting existing method MergeRequest.of_projects.
Creating scope :join_project. Overwriting existing method MergeRequest.join_project.
Creating scope :references_project. Overwriting existing method MergeRequest.references_project.
Creating scope :system. Overwriting existing method Note.system.
Creating scope :group_view_details. Overwriting existing method User.group_view_details.
Creating scope :without_statuses. Overwriting existing method CommitStatus.without_statuses.
Creating scope :opened. Overwriting existing method Epic.opened.
Creating scope :closed. Overwriting existing method Epic.closed.
An enum element in Ci::Runner uses the prefix 'not_'. This will cause a conflict with auto generated negative scopes.
Creating scope :with_files_stored_remotely. Overwriting existing method Ci::JobArtifact.with_files_stored_remotely.
Creating scope :order_created_desc. Overwriting existing method Packages::Package.order_created_desc.
Creating scope :order_name_desc. Overwriting existing method Packages::Package.order_name_desc.
   (0.3ms)  SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::float as lag
  ↳ app/models/concerns/cacheable_attributes.rb:19:in `current_without_cache'
  ApplicationSetting Load (2.1ms)  SELECT "application_settings".* FROM "application_settings" ORDER BY "application_settings"."id" DESC LIMIT 1
  ↳ app/models/concerns/cacheable_attributes.rb:19:in `current_without_cache'
  PersonalAccessToken Load (1.3ms)  SELECT "personal_access_tokens".* FROM "personal_access_tokens" WHERE "personal_access_tokens"."token_digest" = 'R27ZoLo1xAaezTWG/7z4uMnF9M8yUPN1lDKLEh0cQq4=' LIMIT 1
  ↳ app/models/concerns/token_authenticatable_strategies/digest.rb:8:in `find_token_authenticatable'
  User Load (2.8ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1
  ↳ lib/gitlab/auth/auth_finders.rb:102:in `find_user_from_access_token'
  License Load (0.5ms)  SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT 100
  ↳ ee/app/models/license.rb:264:in `load_license'
  Project Load (3.4ms)  SELECT "projects".* FROM "projects" WHERE "projects"."pending_delete" = FALSE AND "projects"."id" = 63 LIMIT 1
  ↳ lib/api/helpers.rb:109:in `find_project'
  Group Load (1.2ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 54 AND "namespaces"."type" = 'Group' LIMIT 1
  ↳ ee/app/policies/ee/project_policy.rb:344:in `block (2 levels) in <module:ProjectPolicy>'
  IpRestriction Load (0.4ms)  SELECT "ip_restrictions".* FROM "ip_restrictions" WHERE "ip_restrictions"."group_id" = 54
  ↳ ee/lib/gitlab/ip_restriction/enforcer.rb:31:in `allows_address?'
  Project Load (0.7ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 63 LIMIT 1
  ↳ app/services/search_service.rb:22:in `project'
  Route Load (0.7ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 63 AND "routes"."source_type" = 'Project' LIMIT 1
  ↳ app/models/concerns/routable.rb:77:in `full_path'
  User Load (3.2ms)  SELECT "users".* FROM "users" WHERE ((LOWER("users"."name") = 'ro' OR LOWER("users"."username") = 'ro') OR "users"."email" = 'ro') AND "users"."id" IN (SELECT "users"."id" FROM "users" INNER JOIN "project_authorizations" ON "users"."id" = "project_authorizations"."user_id" WHERE "project_authorizations"."project_id" = 63) ORDER BY CASE
  WHEN users.name = 'ro' THEN 0
  WHEN users.username = 'ro' THEN 1
  WHEN users.email = 'ro' THEN 2
  ELSE 3
END
, "users"."name" ASC LIMIT 20 OFFSET 0
  ↳ app/services/search_service.rb:85:in `redact_unauthorized_results'
   (1.3ms)  SELECT COUNT(*) FROM "users" WHERE ((LOWER("users"."name") = 'ro' OR LOWER("users"."username") = 'ro') OR "users"."email" = 'ro') AND "users"."id" IN (SELECT "users"."id" FROM "users" INNER JOIN "project_authorizations" ON "users"."id" = "project_authorizations"."user_id" WHERE "project_authorizations"."project_id" = 63) AND 1=1
  ↳ lib/gitlab/pagination/offset_pagination.rb:47:in `add_pagination_headers'
  User Load (1.6ms)  SELECT "users".* FROM "users" WHERE ((LOWER("users"."name") = 'ro' OR LOWER("users"."username") = 'ro') OR "users"."email" = 'ro') AND "users"."id" IN (SELECT "users"."id" FROM "users" INNER JOIN "project_authorizations" ON "users"."id" = "project_authorizations"."user_id" WHERE "project_authorizations"."project_id" = 63) AND 1=1 ORDER BY CASE
  WHEN users.name = 'ro' THEN 0
  WHEN users.username = 'ro' THEN 1
  WHEN users.email = 'ro' THEN 2
  ELSE 3
END
, "users"."name" ASC LIMIT 20 OFFSET 0
  ↳ lib/api/search.rb:128:in `block (2 levels) in <class:Search>'
  Route Load (0.4ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 63 AND "routes"."source_type" = 'Project' LIMIT 1
  ↳ app/models/concerns/routable.rb:77:in `full_path'

After

Started GET "/api/v4/projects/63/search?scope=users&search=ro" for 127.0.0.1 at 2020-06-10 13:43:43 -0400
Creating scope :of_projects. Overwriting existing method MergeRequest.of_projects.
Creating scope :join_project. Overwriting existing method MergeRequest.join_project.
Creating scope :references_project. Overwriting existing method MergeRequest.references_project.
Creating scope :system. Overwriting existing method Note.system.
Creating scope :group_view_details. Overwriting existing method User.group_view_details.
Creating scope :without_statuses. Overwriting existing method CommitStatus.without_statuses.
Creating scope :opened. Overwriting existing method Epic.opened.
Creating scope :closed. Overwriting existing method Epic.closed.
An enum element in Ci::Runner uses the prefix 'not_'. This will cause a conflict with auto generated negative scopes.
Creating scope :with_files_stored_remotely. Overwriting existing method Ci::JobArtifact.with_files_stored_remotely.
Creating scope :order_created_desc. Overwriting existing method Packages::Package.order_created_desc.
Creating scope :order_name_desc. Overwriting existing method Packages::Package.order_name_desc.
   (0.3ms)  SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::float as lag
  ↳ app/models/concerns/cacheable_attributes.rb:19:in `current_without_cache'
  ApplicationSetting Load (1.6ms)  SELECT "application_settings".* FROM "application_settings" ORDER BY "application_settings"."id" DESC LIMIT 1
  ↳ app/models/concerns/cacheable_attributes.rb:19:in `current_without_cache'
  PersonalAccessToken Load (0.3ms)  SELECT "personal_access_tokens".* FROM "personal_access_tokens" WHERE "personal_access_tokens"."token_digest" = 'R27ZoLo1xAaezTWG/7z4uMnF9M8yUPN1lDKLEh0cQq4=' LIMIT 1
  ↳ app/models/concerns/token_authenticatable_strategies/digest.rb:8:in `find_token_authenticatable'
  User Load (0.6ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1
  ↳ lib/gitlab/auth/auth_finders.rb:102:in `find_user_from_access_token'
  License Load (0.2ms)  SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT 100
  ↳ ee/app/models/license.rb:264:in `load_license'
  Project Load (2.9ms)  SELECT "projects".* FROM "projects" WHERE "projects"."pending_delete" = FALSE AND "projects"."id" = 63 LIMIT 1
  ↳ lib/api/helpers.rb:109:in `find_project'
  Group Load (1.3ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 54 AND "namespaces"."type" = 'Group' LIMIT 1
  ↳ ee/app/policies/ee/project_policy.rb:344:in `block (2 levels) in <module:ProjectPolicy>'
  IpRestriction Load (0.4ms)  SELECT "ip_restrictions".* FROM "ip_restrictions" WHERE "ip_restrictions"."group_id" = 54
  ↳ ee/lib/gitlab/ip_restriction/enforcer.rb:31:in `allows_address?'
  Project Load (0.6ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 63 LIMIT 1
  ↳ app/services/search_service.rb:22:in `project'
  Route Load (0.6ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 63 AND "routes"."source_type" = 'Project' LIMIT 1
  ↳ app/models/concerns/routable.rb:77:in `full_path'
  User Load (1.4ms)  SELECT "users".* FROM "users" WHERE ((LOWER("users"."name") = 'ro' OR LOWER("users"."username") = 'ro') OR "users"."email" = 'ro') AND "users"."id" IN (SELECT "users"."id" FROM "users" INNER JOIN "project_authorizations" ON "users"."id" = "project_authorizations"."user_id" WHERE "project_authorizations"."project_id" = 63) ORDER BY CASE
  WHEN users.name = 'ro' THEN 0
  WHEN users.username = 'ro' THEN 1
  WHEN users.email = 'ro' THEN 2
  ELSE 3
END
, "users"."name" ASC LIMIT 20 OFFSET 0
  ↳ app/services/search_service.rb:85:in `reject'
  Route Load (0.3ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 63 AND "routes"."source_type" = 'Project' LIMIT 1
  ↳ app/models/concerns/routable.rb:77:in `full_path'

GET "/api/v4/groups/54/search?scope=users&search=ro"

Before

Started GET "/api/v4/groups/54/search?scope=users&search=ro" for 127.0.0.1 at 2020-06-10 13:41:44 -0400
  PersonalAccessToken Load (0.3ms)  SELECT "personal_access_tokens".* FROM "personal_access_tokens" WHERE "personal_access_tokens"."token_digest" = 'R27ZoLo1xAaezTWG/7z4uMnF9M8yUPN1lDKLEh0cQq4=' LIMIT 1
  ↳ app/models/concerns/token_authenticatable_strategies/digest.rb:8:in `find_token_authenticatable'
  User Load (0.5ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1
  ↳ lib/gitlab/auth/auth_finders.rb:102:in `find_user_from_access_token'
  License Load (0.2ms)  SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT 100
  ↳ ee/app/models/license.rb:264:in `load_license'
  Group Load (0.4ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 54 LIMIT 1
  ↳ lib/api/helpers.rb:129:in `find_group'
  IpRestriction Load (0.3ms)  SELECT "ip_restrictions".* FROM "ip_restrictions" WHERE "ip_restrictions"."group_id" = 54
  ↳ ee/lib/gitlab/ip_restriction/enforcer.rb:31:in `allows_address?'
  Group Load (0.5ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 54 LIMIT 1
  ↳ app/services/search_service.rb:36:in `group'
  Namespace Exists? (5.2ms)  WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" IN (SELECT "elasticsearch_indexed_namespaces"."namespace_id" FROM "elasticsearch_indexed_namespaces"))
UNION
(SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT 1 AS one FROM "base_and_descendants" AS "namespaces" WHERE "namespaces"."id" = 54 LIMIT 1
  ↳ ee/app/models/ee/application_setting.rb:154:in `elasticsearch_indexes_namespace?'
  Route Load (0.4ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 54 AND "routes"."source_type" = 'Namespace' LIMIT 1
  ↳ app/models/concerns/routable.rb:77:in `full_path'
   (3.5ms)  SELECT "projects"."id" FROM "projects" INNER JOIN routes rs ON rs.source_id = projects.id AND rs.source_type = 'Project' WHERE (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0,10,20)) AND "projects"."archived" = FALSE AND (rs.path LIKE 'qa-perf-test-land/%') ORDER BY "projects"."id" DESC
  ↳ ee/app/services/ee/search/group_service.rb:15:in `elastic_projects'
  CACHE Namespace Exists? (0.1ms)  WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" IN (SELECT "elasticsearch_indexed_namespaces"."namespace_id" FROM "elasticsearch_indexed_namespaces"))
UNION
(SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT 1 AS one FROM "base_and_descendants" AS "namespaces" WHERE "namespaces"."id" = 54 LIMIT 1
  ↳ ee/app/models/ee/application_setting.rb:154:in `elasticsearch_indexes_namespace?'
Creating scope :order_name_asc. Overwriting existing method Member.order_name_asc.
Creating scope :order_name_desc. Overwriting existing method Member.order_name_desc.
  User Load (15.3ms)  SELECT "users".* FROM "users" WHERE ((LOWER("users"."name") = 'ro' OR LOWER("users"."username") = 'ro') OR "users"."email" = 'ro') 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" = '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" = 'Group' AND "namespaces"."id" = 54)
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" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 54)
UNION
(SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."type" = 'Group' AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces"."id" 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')))
UNION
(SELECT "users".* FROM "users" INNER JOIN "members" ON "members"."source_type" = 'Project' AND "members"."requested_at" IS NULL AND "members"."user_id" = "users"."id" AND "members"."type" = 'ProjectMember' INNER JOIN "projects" ON "projects"."id" = "members"."source_id" INNER JOIN "namespaces" ON "namespaces"."type" = 'Group' AND "namespaces"."id" = "projects"."namespace_id" AND "namespaces"."type" = 'Group' WHERE "namespaces"."id" IN (WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 54)
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"))) users) AND "users"."id" IN (SELECT members.user_id FROM "namespaces" INNER JOIN "members" ON "members"."source_type" = 'Namespace' AND "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL AND "members"."source_id" = "namespaces"."id" AND "members"."type" = 'GroupMember' INNER JOIN "users" ON "users"."id" = "members"."user_id" WHERE "namespaces"."type" = 'Group' ORDER BY "namespaces"."id" DESC) ORDER BY CASE
  WHEN users.name = 'ro' THEN 0
  WHEN users.username = 'ro' THEN 1
  WHEN users.email = 'ro' THEN 2
  ELSE 3
END
, "users"."name" ASC LIMIT 20 OFFSET 0
  ↳ app/services/search_service.rb:85:in `redact_unauthorized_results'
   (10.5ms)  SELECT COUNT(*) FROM "users" WHERE ((LOWER("users"."name") = 'ro' OR LOWER("users"."username") = 'ro') OR "users"."email" = 'ro') 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" = '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" = 'Group' AND "namespaces"."id" = 54)
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" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 54)
UNION
(SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."type" = 'Group' AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces"."id" 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')))
UNION
(SELECT "users".* FROM "users" INNER JOIN "members" ON "members"."source_type" = 'Project' AND "members"."requested_at" IS NULL AND "members"."user_id" = "users"."id" AND "members"."type" = 'ProjectMember' INNER JOIN "projects" ON "projects"."id" = "members"."source_id" INNER JOIN "namespaces" ON "namespaces"."type" = 'Group' AND "namespaces"."id" = "projects"."namespace_id" AND "namespaces"."type" = 'Group' WHERE "namespaces"."id" IN (WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 54)
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"))) users) AND "users"."id" IN (SELECT members.user_id FROM "namespaces" INNER JOIN "members" ON "members"."source_type" = 'Namespace' AND "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL AND "members"."source_id" = "namespaces"."id" AND "members"."type" = 'GroupMember' INNER JOIN "users" ON "users"."id" = "members"."user_id" WHERE "namespaces"."type" = 'Group' ORDER BY "namespaces"."id" DESC) AND 1=1
  ↳ lib/gitlab/pagination/offset_pagination.rb:47:in `add_pagination_headers'
  User Load (11.6ms)  SELECT "users".* FROM "users" WHERE ((LOWER("users"."name") = 'ro' OR LOWER("users"."username") = 'ro') OR "users"."email" = 'ro') 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" = '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" = 'Group' AND "namespaces"."id" = 54)
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" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 54)
UNION
(SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."type" = 'Group' AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces"."id" 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')))
UNION
(SELECT "users".* FROM "users" INNER JOIN "members" ON "members"."source_type" = 'Project' AND "members"."requested_at" IS NULL AND "members"."user_id" = "users"."id" AND "members"."type" = 'ProjectMember' INNER JOIN "projects" ON "projects"."id" = "members"."source_id" INNER JOIN "namespaces" ON "namespaces"."type" = 'Group' AND "namespaces"."id" = "projects"."namespace_id" AND "namespaces"."type" = 'Group' WHERE "namespaces"."id" IN (WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 54)
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"))) users) AND "users"."id" IN (SELECT members.user_id FROM "namespaces" INNER JOIN "members" ON "members"."source_type" = 'Namespace' AND "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL AND "members"."source_id" = "namespaces"."id" AND "members"."type" = 'GroupMember' INNER JOIN "users" ON "users"."id" = "members"."user_id" WHERE "namespaces"."type" = 'Group' ORDER BY "namespaces"."id" DESC) AND 1=1 ORDER BY CASE
  WHEN users.name = 'ro' THEN 0
  WHEN users.username = 'ro' THEN 1
  WHEN users.email = 'ro' THEN 2
  ELSE 3
END
, "users"."name" ASC LIMIT 20 OFFSET 0
  ↳ lib/api/search.rb:107:in `block (2 levels) in <class:Search>'
  Route Load (0.4ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 54 AND "routes"."source_type" = 'Namespace' LIMIT 1
  ↳ app/models/concerns/routable.rb:77:in `full_path'

After

Started GET "/api/v4/groups/54/search?scope=users&search=ro" for 127.0.0.1 at 2020-06-10 13:43:02 -0400
Creating scope :of_projects. Overwriting existing method MergeRequest.of_projects.
Creating scope :join_project. Overwriting existing method MergeRequest.join_project.
Creating scope :references_project. Overwriting existing method MergeRequest.references_project.
Creating scope :system. Overwriting existing method Note.system.
Creating scope :group_view_details. Overwriting existing method User.group_view_details.
Creating scope :without_statuses. Overwriting existing method CommitStatus.without_statuses.
Creating scope :opened. Overwriting existing method Epic.opened.
Creating scope :closed. Overwriting existing method Epic.closed.
An enum element in Ci::Runner uses the prefix 'not_'. This will cause a conflict with auto generated negative scopes.
Creating scope :with_files_stored_remotely. Overwriting existing method Ci::JobArtifact.with_files_stored_remotely.
Creating scope :order_created_desc. Overwriting existing method Packages::Package.order_created_desc.
Creating scope :order_name_desc. Overwriting existing method Packages::Package.order_name_desc.
   (0.5ms)  SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::float as lag
  ↳ app/models/concerns/cacheable_attributes.rb:19:in `current_without_cache'
  ApplicationSetting Load (2.2ms)  SELECT "application_settings".* FROM "application_settings" ORDER BY "application_settings"."id" DESC LIMIT 1
  ↳ app/models/concerns/cacheable_attributes.rb:19:in `current_without_cache'
  PersonalAccessToken Load (1.0ms)  SELECT "personal_access_tokens".* FROM "personal_access_tokens" WHERE "personal_access_tokens"."token_digest" = 'R27ZoLo1xAaezTWG/7z4uMnF9M8yUPN1lDKLEh0cQq4=' LIMIT 1
  ↳ app/models/concerns/token_authenticatable_strategies/digest.rb:8:in `find_token_authenticatable'
  User Load (2.5ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1
  ↳ lib/gitlab/auth/auth_finders.rb:102:in `find_user_from_access_token'
  License Load (0.3ms)  SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT 100
  ↳ ee/app/models/license.rb:264:in `load_license'
  Group Load (1.8ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 54 LIMIT 1
  ↳ lib/api/helpers.rb:129:in `find_group'
  IpRestriction Load (0.4ms)  SELECT "ip_restrictions".* FROM "ip_restrictions" WHERE "ip_restrictions"."group_id" = 54
  ↳ ee/lib/gitlab/ip_restriction/enforcer.rb:31:in `allows_address?'
  Group Load (0.5ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 54 LIMIT 1
  ↳ app/services/search_service.rb:36:in `group'
  Namespace Exists? (2.5ms)  WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" IN (SELECT "elasticsearch_indexed_namespaces"."namespace_id" FROM "elasticsearch_indexed_namespaces"))
UNION
(SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT 1 AS one FROM "base_and_descendants" AS "namespaces" WHERE "namespaces"."id" = 54 LIMIT 1
  ↳ ee/app/models/ee/application_setting.rb:154:in `elasticsearch_indexes_namespace?'
  Route Load (0.8ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 54 AND "routes"."source_type" = 'Namespace' LIMIT 1
  ↳ app/models/concerns/routable.rb:77:in `full_path'
   (5.7ms)  SELECT "projects"."id" FROM "projects" INNER JOIN routes rs ON rs.source_id = projects.id AND rs.source_type = 'Project' WHERE (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (0,10,20)) AND "projects"."archived" = FALSE AND (rs.path LIKE 'qa-perf-test-land/%') ORDER BY "projects"."id" DESC
  ↳ ee/app/services/ee/search/group_service.rb:15:in `elastic_projects'
  CACHE Namespace Exists? (0.1ms)  WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" IN (SELECT "elasticsearch_indexed_namespaces"."namespace_id" FROM "elasticsearch_indexed_namespaces"))
UNION
(SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT 1 AS one FROM "base_and_descendants" AS "namespaces" WHERE "namespaces"."id" = 54 LIMIT 1
  ↳ ee/app/models/ee/application_setting.rb:154:in `elasticsearch_indexes_namespace?'
Creating scope :order_name_asc. Overwriting existing method Member.order_name_asc.
Creating scope :order_name_desc. Overwriting existing method Member.order_name_desc.
  User Load (11.8ms)  SELECT "users".* FROM "users" WHERE ((LOWER("users"."name") = 'ro' OR LOWER("users"."username") = 'ro') OR "users"."email" = 'ro') 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" = '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" = 'Group' AND "namespaces"."id" = 54)
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" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 54)
UNION
(SELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."type" = 'Group' AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "namespaces"."id" 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')))
UNION
(SELECT "users".* FROM "users" INNER JOIN "members" ON "members"."source_type" = 'Project' AND "members"."requested_at" IS NULL AND "members"."user_id" = "users"."id" AND "members"."type" = 'ProjectMember' INNER JOIN "projects" ON "projects"."id" = "members"."source_id" INNER JOIN "namespaces" ON "namespaces"."type" = 'Group' AND "namespaces"."id" = "projects"."namespace_id" AND "namespaces"."type" = 'Group' WHERE "namespaces"."id" IN (WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 54)
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"))) users) AND "users"."id" IN (SELECT members.user_id FROM "namespaces" INNER JOIN "members" ON "members"."source_type" = 'Namespace' AND "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL AND "members"."source_id" = "namespaces"."id" AND "members"."type" = 'GroupMember' INNER JOIN "users" ON "users"."id" = "members"."user_id" WHERE "namespaces"."type" = 'Group' ORDER BY "namespaces"."id" DESC) ORDER BY CASE
  WHEN users.name = 'ro' THEN 0
  WHEN users.username = 'ro' THEN 1
  WHEN users.email = 'ro' THEN 2
  ELSE 3
END
, "users"."name" ASC LIMIT 20 OFFSET 0
  ↳ app/services/search_service.rb:85:in `reject'
  GeoNode Exists? (0.2ms)  SELECT 1 AS one FROM "geo_nodes" LIMIT 1
  ↳ ee/lib/gitlab/geo.rb:36:in `block in enabled?'
  Route Load (0.4ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 54 AND "routes"."source_type" = 'Namespace' LIMIT 1
  ↳ app/models/concerns/routable.rb:77:in `full_path'

GET "/api/v4/search?scope=users&search=roo&per_page=1"

Before

Started GET "/api/v4/search?scope=users&search=roo&per_page=1" for 127.0.0.1 at 2020-06-10 13:40:09 -0400
Gitlab::Metrics::Samplers::DatabaseSampler: A copy of EE::Gitlab::Metrics::Samplers::DatabaseSampler has been removed from the module tree but is still active!, stopping
Creating scope :of_projects. Overwriting existing method MergeRequest.of_projects.
Creating scope :join_project. Overwriting existing method MergeRequest.join_project.
Creating scope :references_project. Overwriting existing method MergeRequest.references_project.
Creating scope :system. Overwriting existing method Note.system.
Creating scope :group_view_details. Overwriting existing method User.group_view_details.
Creating scope :without_statuses. Overwriting existing method CommitStatus.without_statuses.
Creating scope :opened. Overwriting existing method Epic.opened.
Creating scope :closed. Overwriting existing method Epic.closed.
An enum element in Ci::Runner uses the prefix 'not_'. This will cause a conflict with auto generated negative scopes.
Creating scope :with_files_stored_remotely. Overwriting existing method Ci::JobArtifact.with_files_stored_remotely.
Creating scope :order_created_desc. Overwriting existing method Packages::Package.order_created_desc.
Creating scope :order_name_desc. Overwriting existing method Packages::Package.order_name_desc.
   (0.3ms)  SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::float as lag
  ↳ app/models/concerns/cacheable_attributes.rb:19:in `current_without_cache'
  ApplicationSetting Load (2.2ms)  SELECT "application_settings".* FROM "application_settings" ORDER BY "application_settings"."id" DESC LIMIT 1
  ↳ app/models/concerns/cacheable_attributes.rb:19:in `current_without_cache'
  PersonalAccessToken Load (0.9ms)  SELECT "personal_access_tokens".* FROM "personal_access_tokens" WHERE "personal_access_tokens"."token_digest" = 'R27ZoLo1xAaezTWG/7z4uMnF9M8yUPN1lDKLEh0cQq4=' LIMIT 1
  ↳ app/models/concerns/token_authenticatable_strategies/digest.rb:8:in `find_token_authenticatable'
  User Load (2.5ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1
  ↳ lib/gitlab/auth/auth_finders.rb:102:in `find_user_from_access_token'
  License Load (0.5ms)  SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT 100
  ↳ ee/app/models/license.rb:264:in `load_license'
  User Load (3.4ms)  SELECT "users".* FROM "users" WHERE (("users"."name" ILIKE '%roo%' OR "users"."username" ILIKE '%roo%') OR "users"."email" = 'roo') ORDER BY CASE
  WHEN users.name = 'roo' THEN 0
  WHEN users.username = 'roo' THEN 1
  WHEN users.email = 'roo' THEN 2
  ELSE 3
END
, "users"."name" ASC LIMIT 2 OFFSET 0
  ↳ app/services/search_service.rb:85:in `redact_unauthorized_results'
  User Load (0.9ms)  SELECT "users".* FROM "users" WHERE (("users"."name" ILIKE '%roo%' OR "users"."username" ILIKE '%roo%') OR "users"."email" = 'roo') AND 1=1 ORDER BY CASE
  WHEN users.name = 'roo' THEN 0
  WHEN users.username = 'roo' THEN 1
  WHEN users.email = 'roo' THEN 2
  ELSE 3
END
, "users"."name" ASC LIMIT 2 OFFSET 0
  ↳ lib/gitlab/pagination/offset_pagination.rb:47:in `add_pagination_headers'
  GeoNode Exists? (0.7ms)  SELECT 1 AS one FROM "geo_nodes" LIMIT 1
  ↳ ee/lib/gitlab/geo.rb:36:in `block in enabled?'

After

Started GET "/api/v4/search?scope=users&search=roo&per_page=1" for 127.0.0.1 at 2020-06-10 13:31:37 -0400
   (0.8ms)  SELECT "schema_migrations"."version" FROM "schema_migrations" ORDER BY "schema_migrations"."version" ASC
  ↳ lib/gitlab/middleware/basic_health_check.rb:25:in `call'
  PersonalAccessToken Load (0.9ms)  SELECT "personal_access_tokens".* FROM "personal_access_tokens" WHERE "personal_access_tokens"."token_digest" = 'R27ZoLo1xAaezTWG/7z4uMnF9M8yUPN1lDKLEh0cQq4=' LIMIT 1
  ↳ app/models/concerns/token_authenticatable_strategies/digest.rb:8:in `find_token_authenticatable'
  User Load (2.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1
  ↳ lib/gitlab/auth/auth_finders.rb:102:in `find_user_from_access_token'
  License Load (0.4ms)  SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT 100
  ↳ ee/app/models/license.rb:264:in `load_license'
  User Load (1.9ms)  SELECT "users".* FROM "users" WHERE (("users"."name" ILIKE '%roo%' OR "users"."username" ILIKE '%roo%') OR "users"."email" = 'roo') ORDER BY CASE
  WHEN users.name = 'roo' THEN 0
  WHEN users.username = 'roo' THEN 1
  WHEN users.email = 'roo' THEN 2
  ELSE 3
END
, "users"."name" ASC LIMIT 2 OFFSET 0
  ↳ app/services/search_service.rb:85:in `reject'

Does this MR meet the acceptance criteria?

Conformity

• Changelog entry
• [-] Documentation (if required)
• Code review guidelines
• [-] Merge request performance guidelines
• Style guides
• [-] Database guides
• [-] Separation of EE specific content

Availability and Testing

• Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
• [-] Tested in all supported browsers
• [-] Informed Infrastructure department of a default or new setting change, if applicable per definition of done

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

• [-] Label as security and @ mention @gitlab-com/gl-security/appsec
• [-] The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
• [-] Security reports checked/validated by a reviewer from the AppSec team