Fix package API N^2 query
⚙
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 inversions
is redundant. - Add preloading for
tags
,build_info
, andproject_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
- [-] 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
Related #219171 (closed)