Fix package API N^2 query (!40770) · Merge requests · GitLab.org / GitLab

Steve Abrams requested to merge 219171-package-n2-bug into master Aug 28, 2020

What does this MR do? ⚙

When the packages list endpoint is requested, for each package, we fetch build info (pipeline info), and we also fetch a list of the other versions available for each package, and their respective build info as well. So if we have 5 different versions of the same package, for each one, we fetch the build info, then the 4 other packages and each of those package's build info.

This MR fixes this exponential problem by:

Preventing versions from being included in the index endpoint. It is not used by the front end and was never included in the API documentation. Plus, being an index endpoint, the information included in versions is redundant.
Add preloading for tags, build_info, and project_routes to prevent N+1 queries on the index and show API endpoints. Now the show endpoint will only make a set of queries for itself, and then a set of queries for all versions rather than a set of queries for each version (N+1)

Database / Testing / Screenshots

The queries run in the finders themselves are not really "improving" in performance, so I have not included a query plan here. However, by preloading the needed associations on each package (includes(...)), we prevent performing a series of additional queries when the API entities are being rendered.

To test this locally, I created 5 NPM packages with different versions: 1.0.0 - 5.0.0. I then tested each endpoint with curl and looked at the rails log to see the amount of SQL statements being executed:

curl -H "PRIVATE-TOKEN: <token>" http://localhost:3001/api/v4/projects/17/packages

This is the rails log output before adding the new scopes:

SELECT is called 62 times in this output.

The group endpoint has essentially the same output

Started GET "/api/v4/projects/17/packages" for 127.0.0.1 at 2020-08-31 14:21:59 -0600
  Project Load (0.7ms)  SELECT "projects".* FROM "projects" WHERE "projects"."pending_delete" = $1 AND "projects"."id" = $2 LIMIT $3  [["pending_delete", false], ["id", 17], ["LIMIT", 1]]
  ↳ lib/api/helpers.rb:113:in `find_project'
  PersonalAccessToken Load (0.3ms)  SELECT "personal_access_tokens".* FROM "personal_access_tokens" WHERE "personal_access_tokens"."token_digest" = $1 LIMIT $2  [["token_digest", "bVzg/FPu9df4QlpZ4u4qP4QmbZDvUf5TbzZH540kvQ8="], ["LIMIT", 1]]
  ↳ app/models/concerns/token_authenticatable_strategies/digest.rb:8:in `find_token_authenticatable'
  License Load (0.2ms)  SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT $1  [["LIMIT", 100]]
  ↳ ee/app/models/license.rb:276:in `load_license'
  User Load (0.4ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
  ↳ lib/gitlab/auth/auth_finders.rb:113:in `find_user_from_access_token'
  Group Load (0.5ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = $1 AND "namespaces"."id" = $2 AND "namespaces"."type" = $3 LIMIT $4  [["type", "Group"], ["id", 1], ["type", "Group"], ["LIMIT", 1]]
  ↳ ee/app/policies/ee/project_policy.rb:329:in `block (2 levels) in '
  ProjectFeature Load (0.3ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = $1 LIMIT $2  [["project_id", 17], ["LIMIT", 1]]
  ↳ app/policies/project_policy.rb:654:in `feature_available?'
   (0.4ms)  SELECT COUNT(*) FROM "packages_packages" WHERE "packages_packages"."project_id" = $1 AND ("packages_packages"."package_type" != $2 OR "packages_packages"."name" != $3) AND "packages_packages"."version" IS NOT NULL  [["project_id", 17], ["package_type", 4], ["name", "NuGet.Temporary.Package"]]
  ↳ lib/gitlab/pagination/offset_pagination.rb:53:in `add_pagination_headers'
  Packages::Package Load (0.4ms)  SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = $1 AND ("packages_packages"."package_type" != $2 OR "packages_packages"."name" != $3) AND "packages_packages"."version" IS NOT NULL ORDER BY created_at ASC LIMIT $4 OFFSET $5  [["project_id", 17], ["package_type", 4], ["name", "NuGet.Temporary.Package"], ["LIMIT", 20], ["OFFSET", 0]]
  ↳ lib/api/project_packages.rb:38:in `block (2 levels) in '
  Packages::BuildInfo Load (0.3ms)  SELECT "packages_build_infos".* FROM "packages_build_infos" WHERE "packages_build_infos"."package_id" IN ($1, $2, $3, $4, $5)  [["package_id", 16], ["package_id", 17], ["package_id", 18], ["package_id", 19], ["package_id", 20]]
  ↳ lib/api/project_packages.rb:38:in `block (2 levels) in '
  Namespace Load (0.4ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
  ↳ config/application.rb:330:in `block (4 levels) in '
  Route Load (0.2ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = $1 AND "routes"."source_type" = $2 LIMIT $3  [["source_id", 1], ["source_type", "Namespace"], ["LIMIT", 1]]
  ↳ app/models/concerns/routable.rb:74:in `full_path'
  Packages::Package Load (0.3ms)  SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = $1 AND "packages_packages"."name" = $2 AND "packages_packages"."version" != $3 AND "packages_packages"."package_type" = $4 ORDER BY "packages_packages"."version" ASC  [["project_id", 17], ["name", "@root/npm-practice"], ["version", "1.0.0"], ["package_type", 2]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  Packages::BuildInfo Load (0.2ms)  SELECT "packages_build_infos".* FROM "packages_build_infos" WHERE "packages_build_infos"."package_id" = $1 LIMIT $2  [["package_id", 17], ["LIMIT", 1]]
  ↳ lib/api/entities/package_version.rb:11:in `block in '
  Packages::BuildInfo Load (0.2ms)  SELECT "packages_build_infos".* FROM "packages_build_infos" WHERE "packages_build_infos"."package_id" = $1 LIMIT $2  [["package_id", 18], ["LIMIT", 1]]
  ↳ lib/api/entities/package_version.rb:11:in `block in '
  Packages::BuildInfo Load (0.2ms)  SELECT "packages_build_infos".* FROM "packages_build_infos" WHERE "packages_build_infos"."package_id" = $1 LIMIT $2  [["package_id", 19], ["LIMIT", 1]]
  ↳ lib/api/entities/package_version.rb:11:in `block in '
  Packages::BuildInfo Load (0.2ms)  SELECT "packages_build_infos".* FROM "packages_build_infos" WHERE "packages_build_infos"."package_id" = $1 LIMIT $2  [["package_id", 20], ["LIMIT", 1]]
  ↳ lib/api/entities/package_version.rb:11:in `block in '
  Packages::Package Load (0.2ms)  SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = $1 AND "packages_packages"."name" = $2 AND "packages_packages"."version" != $3 AND "packages_packages"."package_type" = $4 ORDER BY "packages_packages"."version" ASC  [["project_id", 17], ["name", "@root/npm-practice"], ["version", "2.0.0"], ["package_type", 2]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  Packages::BuildInfo Load (0.2ms)  SELECT "packages_build_infos".* FROM "packages_build_infos" WHERE "packages_build_infos"."package_id" = $1 LIMIT $2  [["package_id", 16], ["LIMIT", 1]]
  ↳ lib/api/entities/package_version.rb:11:in `block in '
  CACHE Packages::BuildInfo Load (0.1ms)  SELECT "packages_build_infos".* FROM "packages_build_infos" WHERE "packages_build_infos"."package_id" = $1 LIMIT $2  [["package_id", 18], ["LIMIT", 1]]
  ↳ lib/api/entities/package_version.rb:11:in `block in '
  CACHE Packages::BuildInfo Load (0.1ms)  SELECT "packages_build_infos".* FROM "packages_build_infos" WHERE "packages_build_infos"."package_id" = $1 LIMIT $2  [["package_id", 19], ["LIMIT", 1]]
  ↳ lib/api/entities/package_version.rb:11:in `block in '
  CACHE Packages::BuildInfo Load (0.1ms)  SELECT "packages_build_infos".* FROM "packages_build_infos" WHERE "packages_build_infos"."package_id" = $1 LIMIT $2  [["package_id", 20], ["LIMIT", 1]]
  ↳ lib/api/entities/package_version.rb:11:in `block in '
  Packages::Package Load (0.3ms)  SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = $1 AND "packages_packages"."name" = $2 AND "packages_packages"."version" != $3 AND "packages_packages"."package_type" = $4 ORDER BY "packages_packages"."version" ASC  [["project_id", 17], ["name", "@root/npm-practice"], ["version", "3.0.0"], ["package_type", 2]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  CACHE Packages::BuildInfo Load (0.1ms)  SELECT "packages_build_infos".* FROM "packages_build_infos" WHERE "packages_build_infos"."package_id" = $1 LIMIT $2  [["package_id", 16], ["LIMIT", 1]]
  ↳ lib/api/entities/package_version.rb:11:in `block in '
  CACHE Packages::BuildInfo Load (0.1ms)  SELECT "packages_build_infos".* FROM "packages_build_infos" WHERE "packages_build_infos"."package_id" = $1 LIMIT $2  [["package_id", 17], ["LIMIT", 1]]
  ↳ lib/api/entities/package_version.rb:11:in `block in '
  CACHE Packages::BuildInfo Load (0.1ms)  SELECT "packages_build_infos".* FROM "packages_build_infos" WHERE "packages_build_infos"."package_id" = $1 LIMIT $2  [["package_id", 19], ["LIMIT", 1]]
  ↳ lib/api/entities/package_version.rb:11:in `block in '
  CACHE Packages::BuildInfo Load (0.1ms)  SELECT "packages_build_infos".* FROM "packages_build_infos" WHERE "packages_build_infos"."package_id" = $1 LIMIT $2  [["package_id", 20], ["LIMIT", 1]]
  ↳ lib/api/entities/package_version.rb:11:in `block in '
  Packages::Package Load (0.2ms)  SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = $1 AND "packages_packages"."name" = $2 AND "packages_packages"."version" != $3 AND "packages_packages"."package_type" = $4 ORDER BY "packages_packages"."version" ASC  [["project_id", 17], ["name", "@root/npm-practice"], ["version", "4.0.0"], ["package_type", 2]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  CACHE Packages::BuildInfo Load (0.1ms)  SELECT "packages_build_infos".* FROM "packages_build_infos" WHERE "packages_build_infos"."package_id" = $1 LIMIT $2  [["package_id", 16], ["LIMIT", 1]]
  ↳ lib/api/entities/package_version.rb:11:in `block in '
  CACHE Packages::BuildInfo Load (0.1ms)  SELECT "packages_build_infos".* FROM "packages_build_infos" WHERE "packages_build_infos"."package_id" = $1 LIMIT $2  [["package_id", 17], ["LIMIT", 1]]
  ↳ lib/api/entities/package_version.rb:11:in `block in '
  CACHE Packages::BuildInfo Load (0.1ms)  SELECT "packages_build_infos".* FROM "packages_build_infos" WHERE "packages_build_infos"."package_id" = $1 LIMIT $2  [["package_id", 18], ["LIMIT", 1]]
  ↳ lib/api/entities/package_version.rb:11:in `block in '
  CACHE Packages::BuildInfo Load (0.1ms)  SELECT "packages_build_infos".* FROM "packages_build_infos" WHERE "packages_build_infos"."package_id" = $1 LIMIT $2  [["package_id", 20], ["LIMIT", 1]]
  ↳ lib/api/entities/package_version.rb:11:in `block in '
  Packages::Package Load (0.2ms)  SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = $1 AND "packages_packages"."name" = $2 AND "packages_packages"."version" != $3 AND "packages_packages"."package_type" = $4 ORDER BY "packages_packages"."version" ASC  [["project_id", 17], ["name", "@root/npm-practice"], ["version", "5.0.0"], ["package_type", 2]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  CACHE Packages::BuildInfo Load (0.1ms)  SELECT "packages_build_infos".* FROM "packages_build_infos" WHERE "packages_build_infos"."package_id" = $1 LIMIT $2  [["package_id", 16], ["LIMIT", 1]]
  ↳ lib/api/entities/package_version.rb:11:in `block in '
  CACHE Packages::BuildInfo Load (0.1ms)  SELECT "packages_build_infos".* FROM "packages_build_infos" WHERE "packages_build_infos"."package_id" = $1 LIMIT $2  [["package_id", 17], ["LIMIT", 1]]
  ↳ lib/api/entities/package_version.rb:11:in `block in '
  CACHE Packages::BuildInfo Load (0.1ms)  SELECT "packages_build_infos".* FROM "packages_build_infos" WHERE "packages_build_infos"."package_id" = $1 LIMIT $2  [["package_id", 18], ["LIMIT", 1]]
  ↳ lib/api/entities/package_version.rb:11:in `block in '
  CACHE Packages::BuildInfo Load (0.1ms)  SELECT "packages_build_infos".* FROM "packages_build_infos" WHERE "packages_build_infos"."package_id" = $1 LIMIT $2  [["package_id", 19], ["LIMIT", 1]]
  ↳ lib/api/entities/package_version.rb:11:in `block in '
  Packages::Tag Load (0.3ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 16]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  Packages::Tag Load (0.2ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 17]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  Packages::Tag Load (0.2ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 18]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  Packages::Tag Load (0.2ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 19]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  Packages::Tag Load (0.2ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 20]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  CACHE Packages::Tag Load (0.1ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 17]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  CACHE Packages::Tag Load (0.1ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 16]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  CACHE Packages::Tag Load (0.1ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 18]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  CACHE Packages::Tag Load (0.1ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 19]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  CACHE Packages::Tag Load (0.1ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 20]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  CACHE Packages::Tag Load (0.1ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 18]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  CACHE Packages::Tag Load (0.1ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 16]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  CACHE Packages::Tag Load (0.1ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 17]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  CACHE Packages::Tag Load (0.1ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 19]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  CACHE Packages::Tag Load (0.1ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 20]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  CACHE Packages::Tag Load (0.1ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 19]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  CACHE Packages::Tag Load (0.0ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 16]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  CACHE Packages::Tag Load (0.1ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 17]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  CACHE Packages::Tag Load (0.1ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 18]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  CACHE Packages::Tag Load (0.1ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 20]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  CACHE Packages::Tag Load (0.0ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 20]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  CACHE Packages::Tag Load (0.0ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 16]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  CACHE Packages::Tag Load (0.1ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 17]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  CACHE Packages::Tag Load (0.1ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 18]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  CACHE Packages::Tag Load (0.0ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 19]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  Route Load (0.3ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = $1 AND "routes"."source_type" = $2 LIMIT $3  [["source_id", 17], ["source_type", "Project"], ["LIMIT", 1]]
  ↳ app/models/concerns/routable.rb:74:in `full_path'

This is the rails log output after adding the new scopes:

SELECT is called 13 times in this output.

The group endpoint has essentially the same improvement.

Started GET "/api/v4/projects/17/packages" for 127.0.0.1 at 2020-09-01 14:28:18 -0600
  Project Load (0.8ms)  SELECT "projects".* FROM "projects" WHERE "projects"."pending_delete" = $1 AND "projects"."id" = $2 LIMIT $3  [["pending_delete", false], ["id", 17], ["LIMIT", 1]]
  ↳ lib/api/helpers.rb:113:in `find_project'
  PersonalAccessToken Load (0.3ms)  SELECT "personal_access_tokens".* FROM "personal_access_tokens" WHERE "personal_access_tokens"."token_digest" = $1 LIMIT $2  [["token_digest", "bVzg/FPu9df4QlpZ4u4qP4QmbZDvUf5TbzZH540kvQ8="], ["LIMIT", 1]]
  ↳ app/models/concerns/token_authenticatable_strategies/digest.rb:8:in `find_token_authenticatable'
  License Load (0.2ms)  SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT $1  [["LIMIT", 100]]
  ↳ ee/app/models/license.rb:275:in `load_license'
  User Load (0.5ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
  ↳ lib/gitlab/auth/auth_finders.rb:113:in `find_user_from_access_token'
  Group Load (0.4ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = $1 AND "namespaces"."id" = $2 AND "namespaces"."type" = $3 LIMIT $4  [["type", "Group"], ["id", 1], ["type", "Group"], ["LIMIT", 1]]
  ↳ ee/app/policies/ee/project_policy.rb:295:in `block (2 levels) in '
  ProjectFeature Load (0.3ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = $1 LIMIT $2  [["project_id", 17], ["LIMIT", 1]]
  ↳ app/policies/project_policy.rb:654:in `feature_available?'
   (0.4ms)  SELECT COUNT(*) FROM "packages_packages" WHERE "packages_packages"."project_id" = $1 AND ("packages_packages"."package_type" != $2 OR "packages_packages"."name" != $3) AND "packages_packages"."version" IS NOT NULL  [["project_id", 17], ["package_type", 4], ["name", "NuGet.Temporary.Package"]]
  ↳ lib/gitlab/pagination/offset_pagination.rb:53:in `add_pagination_headers'
  Packages::Package Load (0.4ms)  SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = $1 AND ("packages_packages"."package_type" != $2 OR "packages_packages"."name" != $3) AND "packages_packages"."version" IS NOT NULL ORDER BY created_at ASC LIMIT $4 OFFSET $5  [["project_id", 17], ["package_type", 4], ["name", "NuGet.Temporary.Package"], ["LIMIT", 20], ["OFFSET", 0]]
  ↳ lib/api/project_packages.rb:38:in `block (2 levels) in '
  Packages::BuildInfo Load (0.3ms)  SELECT "packages_build_infos".* FROM "packages_build_infos" WHERE "packages_build_infos"."package_id" IN ($1, $2, $3, $4, $5)  [["package_id", 16], ["package_id", 17], ["package_id", 18], ["package_id", 19], ["package_id", 20]]
  ↳ lib/api/project_packages.rb:38:in `block (2 levels) in '
  Namespace Load (0.4ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = $1  [["id", 1]]
  ↳ lib/api/project_packages.rb:38:in `block (2 levels) in '
  Route Load (0.3ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = $1 AND "routes"."source_id" = $2  [["source_type", "Namespace"], ["source_id", 1]]
  ↳ lib/api/project_packages.rb:38:in `block (2 levels) in '
  Packages::Tag Load (0.3ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" IN ($1, $2, $3, $4, $5)  [["package_id", 16], ["package_id", 17], ["package_id", 18], ["package_id", 19], ["package_id", 20]]
  ↳ lib/api/project_packages.rb:38:in `block (2 levels) in '
  Route Load (0.3ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = $1 AND "routes"."source_type" = $2 LIMIT $3  [["source_id", 17], ["source_type", "Project"], ["LIMIT", 1]]
  ↳ app/models/concerns/routable.rb:74:in `full_path'

curl -H "PRIVATE-TOKEN: <token>" http://localhost:3001/api/v4/projects/17/packages/16

This is the rails log output before adding the new scopes:

SELECT is called 21 times in this output.

Started GET "/api/v4/projects/17/packages/16" for 127.0.0.1 at 2020-09-01 14:39:23 -0600
  Project Load (0.7ms)  SELECT "projects".* FROM "projects" WHERE "projects"."pending_delete" = $1 AND "projects"."id" = $2 LIMIT $3  [["pending_delete", false], ["id", 17], ["LIMIT", 1]]
  ↳ lib/api/helpers.rb:113:in `find_project'
  PersonalAccessToken Load (0.3ms)  SELECT "personal_access_tokens".* FROM "personal_access_tokens" WHERE "personal_access_tokens"."token_digest" = $1 LIMIT $2  [["token_digest", "bVzg/FPu9df4QlpZ4u4qP4QmbZDvUf5TbzZH540kvQ8="], ["LIMIT", 1]]
  ↳ app/models/concerns/token_authenticatable_strategies/digest.rb:8:in `find_token_authenticatable'
  License Load (0.2ms)  SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT $1  [["LIMIT", 100]]
  ↳ ee/app/models/license.rb:275:in `load_license'
  User Load (0.4ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
  ↳ lib/gitlab/auth/auth_finders.rb:113:in `find_user_from_access_token'
  Group Load (0.4ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = $1 AND "namespaces"."id" = $2 AND "namespaces"."type" = $3 LIMIT $4  [["type", "Group"], ["id", 1], ["type", "Group"], ["LIMIT", 1]]
  ↳ ee/app/policies/ee/project_policy.rb:295:in `block (2 levels) in '
  ProjectFeature Load (0.4ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = $1 LIMIT $2  [["project_id", 17], ["LIMIT", 1]]
  ↳ app/policies/project_policy.rb:654:in `feature_available?'
  Packages::Package Load (0.4ms)  SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = $1 AND ("packages_packages"."package_type" != $2 OR "packages_packages"."name" != $3) AND "packages_packages"."id" = $4 LIMIT $5  [["project_id", 17], ["package_type", 4], ["name", "NuGet.Temporary.Package"], ["id", 16], ["LIMIT", 1]]
  ↳ app/finders/packages/package_finder.rb:13:in `execute'
  Packages::BuildInfo Load (0.3ms)  SELECT "packages_build_infos".* FROM "packages_build_infos" WHERE "packages_build_infos"."package_id" = $1 LIMIT $2  [["package_id", 16], ["LIMIT", 1]]
  ↳ lib/api/entities/package.rb:29:in `block in '
  Namespace Load (0.3ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
  ↳ config/application.rb:324:in `block (4 levels) in '
  Route Load (0.2ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = $1 AND "routes"."source_type" = $2 LIMIT $3  [["source_id", 1], ["source_type", "Namespace"], ["LIMIT", 1]]
  ↳ app/models/concerns/routable.rb:74:in `full_path'
  Packages::Package Load (0.4ms)  SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = $1 AND "packages_packages"."name" = $2 AND "packages_packages"."version" != $3 AND "packages_packages"."package_type" = $4 ORDER BY "packages_packages"."version" ASC  [["project_id", 17], ["name", "@root/npm-practice"], ["version", "1.0.0"], ["package_type", 2]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  Packages::BuildInfo Load (0.2ms)  SELECT "packages_build_infos".* FROM "packages_build_infos" WHERE "packages_build_infos"."package_id" = $1 LIMIT $2  [["package_id", 17], ["LIMIT", 1]]
  ↳ lib/api/entities/package_version.rb:11:in `block in '
  Packages::BuildInfo Load (0.3ms)  SELECT "packages_build_infos".* FROM "packages_build_infos" WHERE "packages_build_infos"."package_id" = $1 LIMIT $2  [["package_id", 18], ["LIMIT", 1]]
  ↳ lib/api/entities/package_version.rb:11:in `block in '
  Packages::BuildInfo Load (0.2ms)  SELECT "packages_build_infos".* FROM "packages_build_infos" WHERE "packages_build_infos"."package_id" = $1 LIMIT $2  [["package_id", 19], ["LIMIT", 1]]
  ↳ lib/api/entities/package_version.rb:11:in `block in '
  Packages::BuildInfo Load (0.2ms)  SELECT "packages_build_infos".* FROM "packages_build_infos" WHERE "packages_build_infos"."package_id" = $1 LIMIT $2  [["package_id", 20], ["LIMIT", 1]]
  ↳ lib/api/entities/package_version.rb:11:in `block in '
  Packages::Tag Load (0.3ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 16]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  Packages::Tag Load (0.2ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 17]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  Packages::Tag Load (0.2ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 18]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  Packages::Tag Load (0.2ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 19]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  Packages::Tag Load (0.2ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 20]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  Route Load (0.4ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = $1 AND "routes"."source_type" = $2 LIMIT $3  [["source_id", 17], ["source_type", "Project"], ["LIMIT", 1]]
  ↳ app/models/concerns/routable.rb:74:in `full_path'

This is the rails log output after adding the new scopes:

SELECT is called 15 times in this output.

Started GET "/api/v4/projects/17/packages/16" for 127.0.0.1 at 2020-09-01 14:35:56 -0600
  Project Load (0.7ms)  SELECT "projects".* FROM "projects" WHERE "projects"."pending_delete" = $1 AND "projects"."id" = $2 LIMIT $3  [["pending_delete", false], ["id", 17], ["LIMIT", 1]]
  ↳ lib/api/helpers.rb:113:in `find_project'
  PersonalAccessToken Load (0.8ms)  SELECT "personal_access_tokens".* FROM "personal_access_tokens" WHERE "personal_access_tokens"."token_digest" = $1 LIMIT $2  [["token_digest", "bVzg/FPu9df4QlpZ4u4qP4QmbZDvUf5TbzZH540kvQ8="], ["LIMIT", 1]]
  ↳ app/models/concerns/token_authenticatable_strategies/digest.rb:8:in `find_token_authenticatable'
  License Load (0.2ms)  SELECT "licenses".* FROM "licenses" ORDER BY "licenses"."id" DESC LIMIT $1  [["LIMIT", 100]]
  ↳ ee/app/models/license.rb:275:in `load_license'
  User Load (0.6ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
  ↳ lib/gitlab/auth/auth_finders.rb:113:in `find_user_from_access_token'
  Group Load (0.5ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = $1 AND "namespaces"."id" = $2 AND "namespaces"."type" = $3 LIMIT $4  [["type", "Group"], ["id", 1], ["type", "Group"], ["LIMIT", 1]]
  ↳ ee/app/policies/ee/project_policy.rb:295:in `block (2 levels) in '
  ProjectFeature Load (0.3ms)  SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = $1 LIMIT $2  [["project_id", 17], ["LIMIT", 1]]
  ↳ app/policies/project_policy.rb:654:in `feature_available?'
  Packages::Package Load (0.3ms)  SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = $1 AND ("packages_packages"."package_type" != $2 OR "packages_packages"."name" != $3) AND "packages_packages"."id" = $4 LIMIT $5  [["project_id", 17], ["package_type", 4], ["name", "NuGet.Temporary.Package"], ["id", 16], ["LIMIT", 1]]
  ↳ app/finders/packages/package_finder.rb:16:in `execute'
  Packages::BuildInfo Load (0.2ms)  SELECT "packages_build_infos".* FROM "packages_build_infos" WHERE "packages_build_infos"."package_id" = $1  [["package_id", 16]]
  ↳ app/finders/packages/package_finder.rb:16:in `execute'
  Namespace Load (0.4ms)  SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = $1  [["id", 1]]
  ↳ app/finders/packages/package_finder.rb:16:in `execute'
  Route Load (0.3ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = $1 AND "routes"."source_id" = $2  [["source_type", "Namespace"], ["source_id", 1]]
  ↳ app/finders/packages/package_finder.rb:16:in `execute'
  Packages::Tag Load (0.2ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" = $1  [["package_id", 16]]
  ↳ app/finders/packages/package_finder.rb:16:in `execute'
  Packages::Package Load (0.5ms)  SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = $1 AND "packages_packages"."name" = $2 AND "packages_packages"."version" != $3 AND "packages_packages"."package_type" = $4 ORDER BY "packages_packages"."version" ASC  [["project_id", 17], ["name", "@root/npm-practice"], ["version", "1.0.0"], ["package_type", 2]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  Packages::BuildInfo Load (0.3ms)  SELECT "packages_build_infos".* FROM "packages_build_infos" WHERE "packages_build_infos"."package_id" IN ($1, $2, $3, $4)  [["package_id", 17], ["package_id", 18], ["package_id", 19], ["package_id", 20]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  Packages::Tag Load (0.3ms)  SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" IN ($1, $2, $3, $4)  [["package_id", 17], ["package_id", 18], ["package_id", 19], ["package_id", 20]]
  ↳ lib/gitlab/json.rb:124:in `dump'
  Route Load (0.2ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = $1 AND "routes"."source_type" = $2 LIMIT $3  [["source_id", 17], ["source_type", "Project"], ["LIMIT", 1]]
  ↳ app/models/concerns/routable.rb:74:in `full_path'

Does this MR meet the acceptance criteria?

Conformity

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

Related #219171 (closed)

Edited Sep 01, 2020 by Steve Abrams

Fix package API N^2 query