Improve performance of Search API (Advanced): users scope

Summary

In &3166 (closed) a performance issue was raised around the response time of the Advanced Search API. This issue deals specifically with the users scope, at the Global, Group, and Project levels. Below are results of a performance test that was run on the 10k reference architecture:

API - Global Search
    ✓ { endpoint:users }............: avg=8298.67ms  min=31.19ms  med=10539.07ms max=12639.17ms p(90)=11797.77ms p(95)=11981.41ms

API - Group Search
    ✓ { endpoint:users }............: avg=11049.06ms min=573.63ms  med=10853.37ms max=23765.19ms p(90)=17645.64ms p(95)=19981.77ms

API - Project Search
    ✓ { endpoint:users }............: avg=3262.30ms  min=198.66ms  med=3525.94ms  max=7031.63ms  p(90)=4491.66ms  p(95)=4967.65ms

Improvements

As seen here, this scope does not query Elasticsearch. Rather, the database is used for search, via the UsersFinder.

Global

Queries

Started GET "/api/v4/search?scope=users&search=reported"
  PersonalAccessToken Load (0.3ms)  SELECT "personal_access_tokens".* FROM "personal_access_tokens" WHERE "personal_access_tokens"."token_digest" = '' 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:103:in `find_user_from_access_token'
  License Load (0.2ms)  SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT 1
  ↳ ee/app/models/license.rb:268:in `load_license'
  User Load (0.9ms)  SELECT "users".* FROM "users" WHERE (("users"."name" ILIKE '%reported%' OR "users"."username" ILIKE '%reported%') OR "users"."email" = 'reported') ORDER BY CASE
  WHEN users.name = 'reported' THEN 0
  WHEN users.username = 'reported' THEN 1
  WHEN users.email = 'reported' THEN 2
  ELSE 3
END
, "users"."name" ASC LIMIT 20 OFFSET 0
  ↳ app/services/search_service.rb:75:in `redact_unauthorized_results'
   (0.5ms)  SELECT COUNT(*) FROM "users" WHERE (("users"."name" ILIKE '%reported%' OR "users"."username" ILIKE '%reported%') OR "users"."email" = 'reported') AND 1=1
  ↳ lib/gitlab/pagination/offset_pagination.rb:47:in `add_pagination_headers'
  User Load (0.8ms)  SELECT "users".* FROM "users" WHERE (("users"."name" ILIKE '%reported%' OR "users"."username" ILIKE '%reported%') OR "users"."email" = 'reported') AND 1=1 ORDER BY CASE
  WHEN users.name = 'reported' THEN 0
  WHEN users.username = 'reported' THEN 1
  WHEN users.email = 'reported' THEN 2
  ELSE 3
END
, "users"."name" ASC LIMIT 20 OFFSET 0
  ↳ lib/api/search.rb:75:in `block (2 levels) in <class:Search>'

It looks like the same query for Users is being run twice. The only difference between the queries is that the second one has AND 1=1.

Group

Queries

Started GET "/api/v4/groups/28/search?scope=users&search=root"
  ApplicationSetting Load (0.8ms)  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.2ms)  SELECT "personal_access_tokens".* FROM "personal_access_tokens" WHERE "personal_access_tokens"."token_digest" = '' 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:103:in `find_user_from_access_token'
   (0.2ms)  SELECT "features"."key" FROM "features"
  ↳ lib/feature.rb:15:in `feature_names'
  License Load (0.2ms)  SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT 1
  ↳ ee/app/models/license.rb:268:in `load_license'
  Group Load (0.4ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 28 LIMIT 1
  ↳ lib/api/helpers.rb:128:in `find_group'
  IpRestriction Load (0.2ms)  SELECT "ip_restrictions".* FROM "ip_restrictions" WHERE "ip_restrictions"."group_id" = 28
  ↳ ee/lib/gitlab/ip_restriction/enforcer.rb:31:in `allows_address?'
  Group Load (0.4ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 28 LIMIT 1
  ↳ app/services/search_service.rb:34:in `group'
  Route Load (0.3ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 28 AND "routes"."source_type" = 'Namespace' LIMIT 1
  ↳ app/models/concerns/routable.rb:77:in `full_path'
   (0.9ms)  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 'h5bp/%') ORDER BY "projects"."id" DESC
  ↳ ee/app/services/ee/search/group_service.rb:15:in `elastic_projects'
  User Load (10.5ms)  SELECT "users".* FROM "users" WHERE (("users"."name" ILIKE '%root%' OR "users"."username" ILIKE '%root%') OR "users"."email" = 'root') 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" = 28)
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" = 28)
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"."user_id" = "users"."id" AND "members"."type" = 'ProjectMember' 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" = 'Group' WHERE "namespaces"."id" IN (WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 28)
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_id" = "namespaces"."id" AND "members"."source_type" = 'Namespace' AND "members"."type" = 'GroupMember' AND "members"."requested_at" IS NULL INNER JOIN "users" ON "users"."id" = "members"."user_id" WHERE "namespaces"."type" = 'Group' ORDER BY "namespaces"."id" DESC) ORDER BY CASE
  WHEN users.name = 'root' THEN 0
  WHEN users.username = 'root' THEN 1
  WHEN users.email = 'root' THEN 2
  ELSE 3
END
, "users"."name" ASC LIMIT 20 OFFSET 0
  ↳ app/services/search_service.rb:75:in `redact_unauthorized_results'
   (8.7ms)  SELECT COUNT(*) FROM "users" WHERE (("users"."name" ILIKE '%root%' OR "users"."username" ILIKE '%root%') OR "users"."email" = 'root') 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" = 28)
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" = 28)
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"."user_id" = "users"."id" AND "members"."type" = 'ProjectMember' 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" = 'Group' WHERE "namespaces"."id" IN (WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 28)
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_id" = "namespaces"."id" AND "members"."source_type" = 'Namespace' AND "members"."type" = 'GroupMember' AND "members"."requested_at" IS NULL 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 (9.6ms)  SELECT "users".* FROM "users" WHERE (("users"."name" ILIKE '%root%' OR "users"."username" ILIKE '%root%') OR "users"."email" = 'root') 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" = 28)
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" = 28)
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"."user_id" = "users"."id" AND "members"."type" = 'ProjectMember' 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" = 'Group' WHERE "namespaces"."id" IN (WITH RECURSIVE "base_and_descendants" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 28)
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_id" = "namespaces"."id" AND "members"."source_type" = 'Namespace' AND "members"."type" = 'GroupMember' AND "members"."requested_at" IS NULL 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 = 'root' THEN 0
  WHEN users.username = 'root' THEN 1
  WHEN users.email = 'root' THEN 2
  ELSE 3
END
, "users"."name" ASC LIMIT 20 OFFSET 0
  ↳ lib/api/search.rb:96:in `block (2 levels) in <class:Search>'

The query to find Users seems quite complex, and does take longer relative to the Global query. Again, it looks like the same query is being performed twice.

Project

Queries

Started GET "/api/v4/projects/1/search?scope=users&search=root"
   (0.4ms)  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.5ms)  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.8ms)  SELECT "personal_access_tokens".* FROM "personal_access_tokens" WHERE "personal_access_tokens"."token_digest" = '' 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:103:in `find_user_from_access_token'
   (0.4ms)  SELECT "features"."key" FROM "features"
  ↳ lib/feature.rb:15:in `feature_names'
  License Load (0.4ms)  SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT 1
  ↳ ee/app/models/license.rb:268:in `load_license'
  Project Load (3.1ms)  SELECT "projects".* FROM "projects" WHERE "projects"."pending_delete" = FALSE AND "projects"."id" = 1 LIMIT 1
  ↳ lib/api/helpers.rb:108:in `find_project'
  Group Load (1.4ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 22 AND "namespaces"."type" = 'Group' LIMIT 1
  ↳ ee/app/policies/ee/project_policy.rb:308:in `block (2 levels) in <module:ProjectPolicy>'
  IpRestriction Load (0.4ms)  SELECT "ip_restrictions".* FROM "ip_restrictions" WHERE "ip_restrictions"."group_id" = 22
  ↳ ee/lib/gitlab/ip_restriction/enforcer.rb:31:in `allows_address?'
  Project Load (0.6ms)  SELECT "projects".* FROM "projects" WHERE "projects"."id" = 1 LIMIT 1
  ↳ app/services/search_service.rb:20:in `project'
  Route Load (0.6ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 1 AND "routes"."source_type" = 'Project' LIMIT 1
  ↳ app/models/concerns/routable.rb:77:in `full_path'
  User Load (2.7ms)  SELECT "users".* FROM "users" WHERE (("users"."name" ILIKE '%root%' OR "users"."username" ILIKE '%root%') OR "users"."email" = 'root') 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" = 1) ORDER BY CASE
  WHEN users.name = 'root' THEN 0
  WHEN users.username = 'root' THEN 1
  WHEN users.email = 'root' THEN 2
  ELSE 3
END
, "users"."name" ASC LIMIT 20 OFFSET 0
  ↳ app/services/search_service.rb:75:in `redact_unauthorized_results'
   (1.2ms)  SELECT COUNT(*) FROM "users" WHERE (("users"."name" ILIKE '%root%' OR "users"."username" ILIKE '%root%') OR "users"."email" = 'root') 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" = 1) AND 1=1
  ↳ lib/gitlab/pagination/offset_pagination.rb:47:in `add_pagination_headers'
  User Load (1.4ms)  SELECT "users".* FROM "users" WHERE (("users"."name" ILIKE '%root%' OR "users"."username" ILIKE '%root%') OR "users"."email" = 'root') 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" = 1) AND 1=1 ORDER BY CASE
  WHEN users.name = 'root' THEN 0
  WHEN users.username = 'root' THEN 1
  WHEN users.email = 'root' THEN 2
  ELSE 3
END
, "users"."name" ASC LIMIT 20 OFFSET 0
  ↳ lib/api/search.rb:117:in `block (2 levels) in <class:Search>'

Same issue with duplicate query.