Remove a n+1 situation in the NPM presenter
⚔ Context
The ~"npm Registry" has a metadata endpoint that is used by $ npm
to get information about a given package. Basically, $ npm
will issue a request like Hey, give me all the information about package foobar
and the package registry will reply: "Certainly, here is everything I known about package foobar".
The response is a json structure. Among other things it contains:
-
All the existing versions
- For each version, we will list the dependencies and the tarball file.
- All the existing tags
To have a better view of what is happening, here is the (simplified) models schema:
graph TB
pkg["Packages::Package"] -- has_many --> pkg_file["Packages::PackageFile"]
pkg -- has_many --> tag["Packages::Tag"]
pkg -- has_many --> lnk["Packages::DependencyLink"]
lnk -- belongs_to --> dep["Packages::Dependency"]
The npm package presenter receives a set (better said an AR relationship) of Packages::Package
and has to walk through those models to build the structure (basically a hash) that will then get transformed in a json response.
On top of that, the npm API is offered at two levels:
- Instance level
- Project level
Which means that the metadata endpoint is implemented twice but the logic is the same and the use the same presenter class as it should be.
The current implementation generates the json structure on the fly each time a request is received.
Guess what happens when the requested package name is linked with 4000+ versions? Yes,
Our analysis detected an n+1 situation when pulling the dependencies on each version. We opened #338483 (closed) to eager load them which is what this MR tries to do.
While removing the n+1 situation, we noticed two other things that were impactful on the ~performance. Given that those were small and easy fixes, we took the liberty to add those changes here.
Given the amount of changes that this MR brings and the blast radius of a metadata endpoint returning wrong responses (the npm Registry is one of the most used registry and several $ npm
commands depend on the accurate result of the metadata endpoint), the changes of this MR are gated behind a feature flag. The rollout issue is: #338603 (closed).
🔬 What does this MR do?
- Update the npm packages finder:
- To not preload the package files. This is gated behind the feature flag
- The finder is used in cases where the package files are not needed = no need to preload them
- Instead, let users of the finder results (such as the package presenter) to preload the package files when needed.
- Update the npm package presenter:
- Changes behind the feature flag:
- Loop on
packages
using a batch approach (instead of.each
) - For each batch, eager load the models that are needed (such as the package files and the dependencies)
- Don't select dependency links. Simply return all of them. We don't have a situation where a npm package has a dependency link that is not needed in the metadata endpoint.
- Loop on
- Changes not behind the feature flag (applied in all cases):
- Switch a
#map
on packages to#pluck
as#map
could load and instantiate all active records to just get the version attributes.#pluck
is much better geared to do that without creating any active record object.
- Switch a
- Changes behind the feature flag:
- Update the related specs
🖼 Screenshots or Screencasts (strongly suggested)
Let's say that we have a package with 11 versions (each version has several dependencies) and 5 tags. Tag latest
doesnt' exists in the database. The backend will need to compute it.
We're going to access the metadata endpoint (at the instance level) and check the SQL queries triggered.
master
On SQL queries
User Load (1.2ms) SELECT "users".* FROM "users" WHERE "users"."id" = 1 ORDER BY "users"."id" ASC LIMIT 1
↳ ee/lib/ee/api/helpers.rb:143:in `find_user_from_warden'
Namespace Load (0.6ms) SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."parent_id" IS NULL AND (lower(path) = 'root') LIMIT 1
↳ app/models/namespace.rb:135:in `by_path'
Packages::Package Load (1.6ms) SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."namespace_id" = 1) AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."namespace_id" = 1) AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version") ORDER BY "packages_packages"."id" DESC LIMIT 1
↳ app/finders/packages/npm/package_finder.rb:6:in `last'
Packages::PackageFile Load (0.4ms) SELECT "packages_package_files".* FROM "packages_package_files" WHERE "packages_package_files"."package_id" = 261
↳ app/finders/packages/npm/package_finder.rb:6:in `last'
Project Load (1.3ms) SELECT "projects".* FROM "projects" WHERE "projects"."pending_delete" = FALSE AND "projects"."id" = 40 LIMIT 1
↳ lib/api/helpers.rb:122:in `find_project'
Packages::Package Exists? (0.8ms) SELECT 1 AS one FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version") LIMIT 1
↳ lib/api/concerns/packages/npm_endpoints.rb:117:in `block (2 levels) in <module:NpmEndpoints>'
CACHE Project Load (0.0ms) SELECT "projects".* FROM "projects" WHERE "projects"."pending_delete" = FALSE AND "projects"."id" = 40 LIMIT 1
↳ lib/api/helpers.rb:122:in `find_project'
Group Load (0.7ms) SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 1 LIMIT 1
↳ ee/app/policies/ee/project_policy.rb:319:in `block (2 levels) in <module:ProjectPolicy>'
CACHE Packages::Package Exists? (0.0ms) SELECT 1 AS one FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version") LIMIT 1
↳ lib/api/concerns/packages/npm_endpoints.rb:122:in `block (3 levels) in <module:NpmEndpoints>'
Packages::Package Load (0.9ms) SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version")
↳ app/presenters/packages/npm/package_presenter.rb:20:in `versions'
Packages::PackageFile Load (0.7ms) SELECT "packages_package_files".* FROM "packages_package_files" WHERE "packages_package_files"."package_id" IN (251, 252, 253, 254, 255, 256, 257, 258, 259, 260, 261)
↳ app/presenters/packages/npm/package_presenter.rb:20:in `versions'
Packages::DependencyLink Load (0.5ms) SELECT "packages_dependency_links".* FROM "packages_dependency_links" WHERE "packages_dependency_links"."package_id" = 251 AND "packages_dependency_links"."dependency_type" IN (1, 2, 3, 4) ORDER BY "packages_dependency_links"."id" ASC LIMIT 1000
↳ app/presenters/packages/npm/package_presenter.rb:66:in `build_package_dependencies'
Packages::Dependency Load (0.5ms) SELECT "packages_dependencies".* FROM "packages_dependencies" WHERE "packages_dependencies"."id" IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
↳ app/presenters/packages/npm/package_presenter.rb:66:in `build_package_dependencies'
Packages::DependencyLink Load (0.5ms) SELECT "packages_dependency_links".* FROM "packages_dependency_links" WHERE "packages_dependency_links"."package_id" = 252 AND "packages_dependency_links"."dependency_type" IN (1, 2, 3, 4) ORDER BY "packages_dependency_links"."id" ASC LIMIT 1000
↳ app/presenters/packages/npm/package_presenter.rb:66:in `build_package_dependencies'
Packages::Dependency Load (0.4ms) SELECT "packages_dependencies".* FROM "packages_dependencies" WHERE "packages_dependencies"."id" IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
↳ app/presenters/packages/npm/package_presenter.rb:66:in `build_package_dependencies'
Packages::DependencyLink Load (0.5ms) SELECT "packages_dependency_links".* FROM "packages_dependency_links" WHERE "packages_dependency_links"."package_id" = 253 AND "packages_dependency_links"."dependency_type" IN (1, 2, 3, 4) ORDER BY "packages_dependency_links"."id" ASC LIMIT 1000
↳ app/presenters/packages/npm/package_presenter.rb:66:in `build_package_dependencies'
Packages::Dependency Load (0.5ms) SELECT "packages_dependencies".* FROM "packages_dependencies" WHERE "packages_dependencies"."id" IN (2, 3, 4, 5, 6, 7, 9, 10, 11, 12, 13, 15, 16, 17, 18, 20, 21, 22, 23, 24, 25, 26, 27, 28, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
↳ app/presenters/packages/npm/package_presenter.rb:66:in `build_package_dependencies'
Packages::DependencyLink Load (0.5ms) SELECT "packages_dependency_links".* FROM "packages_dependency_links" WHERE "packages_dependency_links"."package_id" = 254 AND "packages_dependency_links"."dependency_type" IN (1, 2, 3, 4) ORDER BY "packages_dependency_links"."id" ASC LIMIT 1000
↳ app/presenters/packages/npm/package_presenter.rb:66:in `build_package_dependencies'
Packages::Dependency Load (0.4ms) SELECT "packages_dependencies".* FROM "packages_dependencies" WHERE "packages_dependencies"."id" IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
↳ app/presenters/packages/npm/package_presenter.rb:66:in `build_package_dependencies'
Packages::DependencyLink Load (0.6ms) SELECT "packages_dependency_links".* FROM "packages_dependency_links" WHERE "packages_dependency_links"."package_id" = 255 AND "packages_dependency_links"."dependency_type" IN (1, 2, 3, 4) ORDER BY "packages_dependency_links"."id" ASC LIMIT 1000
↳ app/presenters/packages/npm/package_presenter.rb:66:in `build_package_dependencies'
Packages::Dependency Load (0.7ms) SELECT "packages_dependencies".* FROM "packages_dependencies" WHERE "packages_dependencies"."id" IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
↳ app/presenters/packages/npm/package_presenter.rb:66:in `build_package_dependencies'
Packages::DependencyLink Load (0.7ms) SELECT "packages_dependency_links".* FROM "packages_dependency_links" WHERE "packages_dependency_links"."package_id" = 256 AND "packages_dependency_links"."dependency_type" IN (1, 2, 3, 4) ORDER BY "packages_dependency_links"."id" ASC LIMIT 1000
↳ app/presenters/packages/npm/package_presenter.rb:66:in `build_package_dependencies'
Packages::Dependency Load (0.7ms) SELECT "packages_dependencies".* FROM "packages_dependencies" WHERE "packages_dependencies"."id" IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
↳ app/presenters/packages/npm/package_presenter.rb:66:in `build_package_dependencies'
Packages::DependencyLink Load (0.6ms) SELECT "packages_dependency_links".* FROM "packages_dependency_links" WHERE "packages_dependency_links"."package_id" = 257 AND "packages_dependency_links"."dependency_type" IN (1, 2, 3, 4) ORDER BY "packages_dependency_links"."id" ASC LIMIT 1000
↳ app/presenters/packages/npm/package_presenter.rb:66:in `build_package_dependencies'
Packages::Dependency Load (0.4ms) SELECT "packages_dependencies".* FROM "packages_dependencies" WHERE "packages_dependencies"."id" IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
↳ app/presenters/packages/npm/package_presenter.rb:66:in `build_package_dependencies'
Packages::DependencyLink Load (0.5ms) SELECT "packages_dependency_links".* FROM "packages_dependency_links" WHERE "packages_dependency_links"."package_id" = 258 AND "packages_dependency_links"."dependency_type" IN (1, 2, 3, 4) ORDER BY "packages_dependency_links"."id" ASC LIMIT 1000
↳ app/presenters/packages/npm/package_presenter.rb:66:in `build_package_dependencies'
Packages::Dependency Load (0.6ms) SELECT "packages_dependencies".* FROM "packages_dependencies" WHERE "packages_dependencies"."id" IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
↳ app/presenters/packages/npm/package_presenter.rb:66:in `build_package_dependencies'
Packages::DependencyLink Load (0.4ms) SELECT "packages_dependency_links".* FROM "packages_dependency_links" WHERE "packages_dependency_links"."package_id" = 259 AND "packages_dependency_links"."dependency_type" IN (1, 2, 3, 4) ORDER BY "packages_dependency_links"."id" ASC LIMIT 1000
↳ app/presenters/packages/npm/package_presenter.rb:66:in `build_package_dependencies'
Packages::Dependency Load (0.4ms) SELECT "packages_dependencies".* FROM "packages_dependencies" WHERE "packages_dependencies"."id" IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
↳ app/presenters/packages/npm/package_presenter.rb:66:in `build_package_dependencies'
Packages::DependencyLink Load (0.6ms) SELECT "packages_dependency_links".* FROM "packages_dependency_links" WHERE "packages_dependency_links"."package_id" = 260 AND "packages_dependency_links"."dependency_type" IN (1, 2, 3, 4) ORDER BY "packages_dependency_links"."id" ASC LIMIT 1000
↳ app/presenters/packages/npm/package_presenter.rb:66:in `build_package_dependencies'
Packages::Dependency Load (0.5ms) SELECT "packages_dependencies".* FROM "packages_dependencies" WHERE "packages_dependencies"."id" IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
↳ app/presenters/packages/npm/package_presenter.rb:66:in `build_package_dependencies'
Packages::DependencyLink Load (0.4ms) SELECT "packages_dependency_links".* FROM "packages_dependency_links" WHERE "packages_dependency_links"."package_id" = 261 AND "packages_dependency_links"."dependency_type" IN (1, 2, 3, 4) ORDER BY "packages_dependency_links"."id" ASC LIMIT 1000
↳ app/presenters/packages/npm/package_presenter.rb:66:in `build_package_dependencies'
Packages::Dependency Load (0.4ms) SELECT "packages_dependencies".* FROM "packages_dependencies" WHERE "packages_dependencies"."id" IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
↳ app/presenters/packages/npm/package_presenter.rb:66:in `build_package_dependencies'
Packages::Tag Load (0.9ms) SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" IN (SELECT "packages_packages"."id" FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version")) ORDER BY "packages_tags"."updated_at" DESC LIMIT 200
↳ app/presenters/packages/npm/package_presenter.rb:38:in `to_h'
Packages::Package Load (0.3ms) SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."id" IN (258, 252, 254, 253)
↳ app/presenters/packages/npm/package_presenter.rb:38:in `to_h'
Route Load (0.4ms) SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 40 AND "routes"."source_type" = 'Project' LIMIT 1
↳ app/models/concerns/routable.rb:118:in `full_path'
- 36 SQL queries
-
packages_package_files
accessed twice -
packages_dependency_links
accessed 11 times (<- that's the n+1 issue) -
packages_dependencies
accessed 11 times
feature flag disabled
This MR withSQL queries
User Load (0.6ms) SELECT "users".* FROM "users" WHERE "users"."id" = 1 ORDER BY "users"."id" ASC LIMIT 1
↳ ee/lib/ee/api/helpers.rb:143:in `find_user_from_warden'
Namespace Load (0.9ms) SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."parent_id" IS NULL AND (lower(path) = 'root') LIMIT 1
↳ app/models/namespace.rb:135:in `by_path'
Packages::Package Load (1.7ms) SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."namespace_id" = 1) AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."namespace_id" = 1) AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version") ORDER BY "packages_packages"."id" DESC LIMIT 1
↳ app/finders/packages/npm/package_finder.rb:6:in `last'
Packages::PackageFile Load (0.3ms) SELECT "packages_package_files".* FROM "packages_package_files" WHERE "packages_package_files"."package_id" = 261
↳ app/finders/packages/npm/package_finder.rb:6:in `last'
Project Load (0.7ms) SELECT "projects".* FROM "projects" WHERE "projects"."pending_delete" = FALSE AND "projects"."id" = 40 LIMIT 1
↳ lib/api/helpers.rb:122:in `find_project'
Packages::Package Exists? (0.7ms) SELECT 1 AS one FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version") LIMIT 1
↳ lib/api/concerns/packages/npm_endpoints.rb:117:in `block (2 levels) in <module:NpmEndpoints>'
CACHE Project Load (0.0ms) SELECT "projects".* FROM "projects" WHERE "projects"."pending_delete" = FALSE AND "projects"."id" = 40 LIMIT 1
↳ lib/api/helpers.rb:122:in `find_project'
Group Load (0.5ms) SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 1 LIMIT 1
↳ ee/app/policies/ee/project_policy.rb:319:in `block (2 levels) in <module:ProjectPolicy>'
CACHE Packages::Package Exists? (0.0ms) SELECT 1 AS one FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version") LIMIT 1
↳ lib/api/concerns/packages/npm_endpoints.rb:122:in `block (3 levels) in <module:NpmEndpoints>'
Packages::Package Load (0.7ms) SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version")
↳ app/presenters/packages/npm/package_presenter.rb:32:in `legacy_versions'
Packages::PackageFile Load (0.4ms) SELECT "packages_package_files".* FROM "packages_package_files" WHERE "packages_package_files"."package_id" IN (251, 252, 253, 254, 255, 256, 257, 258, 259, 260, 261)
↳ app/presenters/packages/npm/package_presenter.rb:32:in `legacy_versions'
Packages::DependencyLink Load (0.5ms) SELECT "packages_dependency_links".* FROM "packages_dependency_links" WHERE "packages_dependency_links"."package_id" = 251 AND "packages_dependency_links"."dependency_type" IN (1, 2, 3, 4) ORDER BY "packages_dependency_links"."id" ASC LIMIT 1000
↳ app/presenters/packages/npm/package_presenter.rb:97:in `build_package_dependencies'
Packages::Dependency Load (0.4ms) SELECT "packages_dependencies".* FROM "packages_dependencies" WHERE "packages_dependencies"."id" IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
↳ app/presenters/packages/npm/package_presenter.rb:97:in `build_package_dependencies'
Packages::DependencyLink Load (0.4ms) SELECT "packages_dependency_links".* FROM "packages_dependency_links" WHERE "packages_dependency_links"."package_id" = 252 AND "packages_dependency_links"."dependency_type" IN (1, 2, 3, 4) ORDER BY "packages_dependency_links"."id" ASC LIMIT 1000
↳ app/presenters/packages/npm/package_presenter.rb:97:in `build_package_dependencies'
Packages::Dependency Load (0.4ms) SELECT "packages_dependencies".* FROM "packages_dependencies" WHERE "packages_dependencies"."id" IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
↳ app/presenters/packages/npm/package_presenter.rb:97:in `build_package_dependencies'
Packages::DependencyLink Load (0.4ms) SELECT "packages_dependency_links".* FROM "packages_dependency_links" WHERE "packages_dependency_links"."package_id" = 253 AND "packages_dependency_links"."dependency_type" IN (1, 2, 3, 4) ORDER BY "packages_dependency_links"."id" ASC LIMIT 1000
↳ app/presenters/packages/npm/package_presenter.rb:97:in `build_package_dependencies'
Packages::Dependency Load (0.4ms) SELECT "packages_dependencies".* FROM "packages_dependencies" WHERE "packages_dependencies"."id" IN (2, 3, 4, 5, 6, 7, 9, 10, 11, 12, 13, 15, 16, 17, 18, 20, 21, 22, 23, 24, 25, 26, 27, 28, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
↳ app/presenters/packages/npm/package_presenter.rb:97:in `build_package_dependencies'
Packages::DependencyLink Load (0.4ms) SELECT "packages_dependency_links".* FROM "packages_dependency_links" WHERE "packages_dependency_links"."package_id" = 254 AND "packages_dependency_links"."dependency_type" IN (1, 2, 3, 4) ORDER BY "packages_dependency_links"."id" ASC LIMIT 1000
↳ app/presenters/packages/npm/package_presenter.rb:97:in `build_package_dependencies'
Packages::Dependency Load (0.4ms) SELECT "packages_dependencies".* FROM "packages_dependencies" WHERE "packages_dependencies"."id" IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
↳ app/presenters/packages/npm/package_presenter.rb:97:in `build_package_dependencies'
Packages::DependencyLink Load (0.5ms) SELECT "packages_dependency_links".* FROM "packages_dependency_links" WHERE "packages_dependency_links"."package_id" = 255 AND "packages_dependency_links"."dependency_type" IN (1, 2, 3, 4) ORDER BY "packages_dependency_links"."id" ASC LIMIT 1000
↳ app/presenters/packages/npm/package_presenter.rb:97:in `build_package_dependencies'
Packages::Dependency Load (0.4ms) SELECT "packages_dependencies".* FROM "packages_dependencies" WHERE "packages_dependencies"."id" IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
↳ app/presenters/packages/npm/package_presenter.rb:97:in `build_package_dependencies'
Packages::DependencyLink Load (0.5ms) SELECT "packages_dependency_links".* FROM "packages_dependency_links" WHERE "packages_dependency_links"."package_id" = 256 AND "packages_dependency_links"."dependency_type" IN (1, 2, 3, 4) ORDER BY "packages_dependency_links"."id" ASC LIMIT 1000
↳ app/presenters/packages/npm/package_presenter.rb:97:in `build_package_dependencies'
Packages::Dependency Load (0.4ms) SELECT "packages_dependencies".* FROM "packages_dependencies" WHERE "packages_dependencies"."id" IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
↳ app/presenters/packages/npm/package_presenter.rb:97:in `build_package_dependencies'
Packages::DependencyLink Load (0.4ms) SELECT "packages_dependency_links".* FROM "packages_dependency_links" WHERE "packages_dependency_links"."package_id" = 257 AND "packages_dependency_links"."dependency_type" IN (1, 2, 3, 4) ORDER BY "packages_dependency_links"."id" ASC LIMIT 1000
↳ app/presenters/packages/npm/package_presenter.rb:97:in `build_package_dependencies'
Packages::Dependency Load (0.4ms) SELECT "packages_dependencies".* FROM "packages_dependencies" WHERE "packages_dependencies"."id" IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
↳ app/presenters/packages/npm/package_presenter.rb:97:in `build_package_dependencies'
Packages::DependencyLink Load (0.4ms) SELECT "packages_dependency_links".* FROM "packages_dependency_links" WHERE "packages_dependency_links"."package_id" = 258 AND "packages_dependency_links"."dependency_type" IN (1, 2, 3, 4) ORDER BY "packages_dependency_links"."id" ASC LIMIT 1000
↳ app/presenters/packages/npm/package_presenter.rb:97:in `build_package_dependencies'
Packages::Dependency Load (0.4ms) SELECT "packages_dependencies".* FROM "packages_dependencies" WHERE "packages_dependencies"."id" IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
↳ app/presenters/packages/npm/package_presenter.rb:97:in `build_package_dependencies'
Packages::DependencyLink Load (0.4ms) SELECT "packages_dependency_links".* FROM "packages_dependency_links" WHERE "packages_dependency_links"."package_id" = 259 AND "packages_dependency_links"."dependency_type" IN (1, 2, 3, 4) ORDER BY "packages_dependency_links"."id" ASC LIMIT 1000
↳ app/presenters/packages/npm/package_presenter.rb:97:in `build_package_dependencies'
Packages::Dependency Load (0.4ms) SELECT "packages_dependencies".* FROM "packages_dependencies" WHERE "packages_dependencies"."id" IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
↳ app/presenters/packages/npm/package_presenter.rb:97:in `build_package_dependencies'
Packages::DependencyLink Load (0.4ms) SELECT "packages_dependency_links".* FROM "packages_dependency_links" WHERE "packages_dependency_links"."package_id" = 260 AND "packages_dependency_links"."dependency_type" IN (1, 2, 3, 4) ORDER BY "packages_dependency_links"."id" ASC LIMIT 1000
↳ app/presenters/packages/npm/package_presenter.rb:97:in `build_package_dependencies'
Packages::Dependency Load (0.4ms) SELECT "packages_dependencies".* FROM "packages_dependencies" WHERE "packages_dependencies"."id" IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
↳ app/presenters/packages/npm/package_presenter.rb:97:in `build_package_dependencies'
Packages::DependencyLink Load (0.4ms) SELECT "packages_dependency_links".* FROM "packages_dependency_links" WHERE "packages_dependency_links"."package_id" = 261 AND "packages_dependency_links"."dependency_type" IN (1, 2, 3, 4) ORDER BY "packages_dependency_links"."id" ASC LIMIT 1000
↳ app/presenters/packages/npm/package_presenter.rb:97:in `build_package_dependencies'
Packages::Dependency Load (0.4ms) SELECT "packages_dependencies".* FROM "packages_dependencies" WHERE "packages_dependencies"."id" IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
↳ app/presenters/packages/npm/package_presenter.rb:97:in `build_package_dependencies'
Packages::Tag Load (1.0ms) SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" IN (SELECT "packages_packages"."id" FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version")) ORDER BY "packages_tags"."updated_at" DESC LIMIT 200
↳ app/presenters/packages/npm/package_presenter.rb:62:in `to_h'
Packages::Package Load (0.4ms) SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."id" IN (258, 252, 254, 253)
↳ app/presenters/packages/npm/package_presenter.rb:62:in `to_h'
(0.7ms) SELECT "packages_packages"."version" FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version")
↳ app/models/packages/package.rb:198:in `pluck_versions'
Route Load (0.3ms) SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 40 AND "routes"."source_type" = 'Project' LIMIT 1
↳ app/models/concerns/routable.rb:118:in `full_path'
- 37 SQL queries
-
packages_package_files
accessed twice -
packages_dependency_links
accessed 11 times (<- that's the n+1 issue) -
packages_dependencies
accessed 11 times - This is almost the same result as
master
- The main difference is our
#map
->#pluck
change which triggers an additional query - I think that this is still acceptable
- The main difference is our
feature flag enabled
This MR withSQL queries
User Load (0.6ms) SELECT "users".* FROM "users" WHERE "users"."id" = 1 ORDER BY "users"."id" ASC LIMIT 1
↳ ee/lib/ee/api/helpers.rb:143:in `find_user_from_warden'
Namespace Load (0.5ms) SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."parent_id" IS NULL AND (lower(path) = 'root') LIMIT 1
↳ app/models/namespace.rb:135:in `by_path'
Packages::Package Load (1.6ms) SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."namespace_id" = 1) AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."namespace_id" = 1) AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version") ORDER BY "packages_packages"."id" DESC LIMIT 1
↳ app/finders/packages/npm/package_finder.rb:6:in `last'
Project Load (0.6ms) SELECT "projects".* FROM "projects" WHERE "projects"."pending_delete" = FALSE AND "projects"."id" = 40 LIMIT 1
↳ lib/api/helpers.rb:122:in `find_project'
Packages::Package Exists? (1.3ms) SELECT 1 AS one FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version") LIMIT 1
↳ lib/api/concerns/packages/npm_endpoints.rb:117:in `block (2 levels) in <module:NpmEndpoints>'
CACHE Project Load (0.0ms) SELECT "projects".* FROM "projects" WHERE "projects"."pending_delete" = FALSE AND "projects"."id" = 40 LIMIT 1
↳ lib/api/helpers.rb:122:in `find_project'
Group Load (0.5ms) SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 1 LIMIT 1
↳ ee/app/policies/ee/project_policy.rb:319:in `block (2 levels) in <module:ProjectPolicy>'
CACHE Packages::Package Exists? (0.0ms) SELECT 1 AS one FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version") LIMIT 1
↳ lib/api/concerns/packages/npm_endpoints.rb:122:in `block (3 levels) in <module:NpmEndpoints>'
Packages::Package Load (0.7ms) SELECT "packages_packages"."id" FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version") ORDER BY "packages_packages"."id" ASC LIMIT 1
↳ app/models/concerns/each_batch.rb:61:in `each_batch'
Packages::Package Load (0.7ms) SELECT "packages_packages"."id" FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version") AND "packages_packages"."id" >= 251 ORDER BY "packages_packages"."id" ASC LIMIT 1 OFFSET 1000
↳ app/models/concerns/each_batch.rb:80:in `block in each_batch'
Packages::Package Load (0.6ms) SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version") AND "packages_packages"."id" >= 251
↳ app/presenters/packages/npm/package_presenter.rb:49:in `block in new_versions'
Packages::PackageFile Load (0.4ms) SELECT "packages_package_files".* FROM "packages_package_files" WHERE "packages_package_files"."package_id" IN (251, 252, 253, 254, 255, 256, 257, 258, 259, 260, 261)
↳ app/presenters/packages/npm/package_presenter.rb:49:in `block in new_versions'
Packages::DependencyLink Load (0.4ms) SELECT "packages_dependency_links".* FROM "packages_dependency_links" WHERE "packages_dependency_links"."package_id" IN (251, 252, 253, 254, 255, 256, 257, 258, 259, 260, 261)
↳ app/presenters/packages/npm/package_presenter.rb:49:in `block in new_versions'
Packages::Dependency Load (0.5ms) SELECT "packages_dependencies".* FROM "packages_dependencies" WHERE "packages_dependencies"."id" IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
↳ app/presenters/packages/npm/package_presenter.rb:49:in `block in new_versions'
Packages::Tag Load (0.9ms) SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" IN (SELECT "packages_packages"."id" FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version")) ORDER BY "packages_tags"."updated_at" DESC LIMIT 200
↳ app/presenters/packages/npm/package_presenter.rb:62:in `to_h'
Packages::Package Load (0.3ms) SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."id" IN (258, 252, 254, 253)
↳ app/presenters/packages/npm/package_presenter.rb:62:in `to_h'
(0.6ms) SELECT "packages_packages"."version" FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version")
↳ app/models/packages/package.rb:198:in `pluck_versions'
Route Load (0.3ms) SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 40 AND "routes"."source_type" = 'Project' LIMIT 1
↳ app/models/concerns/routable.rb:118:in `full_path'
- 18 SQL queries (50% less queries for these conditions)
-
packages_package_files
is accessed only once✅ -
packages_dependency_links
is accessed only once✅ -
packages_dependencies
is accessed only once✅
feature flag enabled + batch loops
This MR withWe set the batch size to 5 so that our 11 versions will trigger 3 "loops":
SQL queries
User Load (6.0ms) SELECT "users".* FROM "users" WHERE "users"."id" = 1 ORDER BY "users"."id" ASC LIMIT 1
↳ ee/lib/ee/api/helpers.rb:143:in `find_user_from_warden'
Namespace Load (3.2ms) SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."parent_id" IS NULL AND (lower(path) = 'root') LIMIT 1
↳ app/models/namespace.rb:135:in `by_path'
Packages::Package Load (7.9ms) SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."namespace_id" = 1) AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."namespace_id" = 1) AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version") ORDER BY "packages_packages"."id" DESC LIMIT 1
↳ app/finders/packages/npm/package_finder.rb:6:in `last'
Project Load (0.7ms) SELECT "projects".* FROM "projects" WHERE "projects"."pending_delete" = FALSE AND "projects"."id" = 40 LIMIT 1
↳ lib/api/helpers.rb:122:in `find_project'
Packages::Package Exists? (0.6ms) SELECT 1 AS one FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version") LIMIT 1
↳ lib/api/concerns/packages/npm_endpoints.rb:117:in `block (2 levels) in <module:NpmEndpoints>'
CACHE Project Load (0.0ms) SELECT "projects".* FROM "projects" WHERE "projects"."pending_delete" = FALSE AND "projects"."id" = 40 LIMIT 1
↳ lib/api/helpers.rb:122:in `find_project'
Group Load (0.5ms) SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 1 LIMIT 1
↳ ee/app/policies/ee/project_policy.rb:319:in `block (2 levels) in <module:ProjectPolicy>'
CACHE Packages::Package Exists? (0.0ms) SELECT 1 AS one FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version") LIMIT 1
↳ lib/api/concerns/packages/npm_endpoints.rb:122:in `block (3 levels) in <module:NpmEndpoints>'
GeoNode Exists? (0.8ms) SELECT 1 AS one FROM "geo_nodes" LIMIT 1
↳ ee/lib/gitlab/geo.rb:49:in `block in enabled?'
Packages::Package Load (0.7ms) SELECT "packages_packages"."id" FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version") ORDER BY "packages_packages"."id" ASC LIMIT 1
↳ app/models/concerns/each_batch.rb:61:in `each_batch'
Packages::Package Load (0.6ms) SELECT "packages_packages"."id" FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version") AND "packages_packages"."id" >= 251 ORDER BY "packages_packages"."id" ASC LIMIT 1 OFFSET 5
↳ app/models/concerns/each_batch.rb:80:in `block in each_batch'
Packages::Package Load (0.7ms) SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version") AND "packages_packages"."id" >= 251 AND "packages_packages"."id" < 256
↳ app/presenters/packages/npm/package_presenter.rb:49:in `block in new_versions'
Packages::PackageFile Load (1.5ms) SELECT "packages_package_files".* FROM "packages_package_files" WHERE "packages_package_files"."package_id" IN (251, 252, 253, 254, 255)
↳ app/presenters/packages/npm/package_presenter.rb:49:in `block in new_versions'
Packages::DependencyLink Load (0.9ms) SELECT "packages_dependency_links".* FROM "packages_dependency_links" WHERE "packages_dependency_links"."package_id" IN (251, 252, 253, 254, 255)
↳ app/presenters/packages/npm/package_presenter.rb:49:in `block in new_versions'
Packages::Dependency Load (0.9ms) SELECT "packages_dependencies".* FROM "packages_dependencies" WHERE "packages_dependencies"."id" IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
↳ app/presenters/packages/npm/package_presenter.rb:49:in `block in new_versions'
Packages::Package Load (0.8ms) SELECT "packages_packages"."id" FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version") AND "packages_packages"."id" >= 256 ORDER BY "packages_packages"."id" ASC LIMIT 1 OFFSET 5
↳ app/models/concerns/each_batch.rb:80:in `block in each_batch'
Packages::Package Load (0.7ms) SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version") AND "packages_packages"."id" >= 256 AND "packages_packages"."id" < 261
↳ app/presenters/packages/npm/package_presenter.rb:49:in `block in new_versions'
Packages::PackageFile Load (0.3ms) SELECT "packages_package_files".* FROM "packages_package_files" WHERE "packages_package_files"."package_id" IN (256, 257, 258, 259, 260)
↳ app/presenters/packages/npm/package_presenter.rb:49:in `block in new_versions'
Packages::DependencyLink Load (0.3ms) SELECT "packages_dependency_links".* FROM "packages_dependency_links" WHERE "packages_dependency_links"."package_id" IN (256, 257, 258, 259, 260)
↳ app/presenters/packages/npm/package_presenter.rb:49:in `block in new_versions'
CACHE Packages::Dependency Load (0.1ms) SELECT "packages_dependencies".* FROM "packages_dependencies" WHERE "packages_dependencies"."id" IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
↳ app/presenters/packages/npm/package_presenter.rb:49:in `block in new_versions'
Packages::Package Load (0.6ms) SELECT "packages_packages"."id" FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version") AND "packages_packages"."id" >= 261 ORDER BY "packages_packages"."id" ASC LIMIT 1 OFFSET 5
↳ app/models/concerns/each_batch.rb:80:in `block in each_batch'
Packages::Package Load (0.6ms) SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version") AND "packages_packages"."id" >= 261
↳ app/presenters/packages/npm/package_presenter.rb:49:in `block in new_versions'
Packages::PackageFile Load (0.5ms) SELECT "packages_package_files".* FROM "packages_package_files" WHERE "packages_package_files"."package_id" = 261
↳ app/presenters/packages/npm/package_presenter.rb:49:in `block in new_versions'
Packages::DependencyLink Load (0.4ms) SELECT "packages_dependency_links".* FROM "packages_dependency_links" WHERE "packages_dependency_links"."package_id" = 261
↳ app/presenters/packages/npm/package_presenter.rb:49:in `block in new_versions'
CACHE Packages::Dependency Load (0.1ms) SELECT "packages_dependencies".* FROM "packages_dependencies" WHERE "packages_dependencies"."id" IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
↳ app/presenters/packages/npm/package_presenter.rb:49:in `block in new_versions'
Packages::Tag Load (1.6ms) SELECT "packages_tags".* FROM "packages_tags" WHERE "packages_tags"."package_id" IN (SELECT "packages_packages"."id" FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version")) ORDER BY "packages_tags"."updated_at" DESC LIMIT 200
↳ app/presenters/packages/npm/package_presenter.rb:62:in `to_h'
Packages::Package Load (0.3ms) SELECT "packages_packages".* FROM "packages_packages" WHERE "packages_packages"."id" IN (258, 252, 254, 253)
↳ app/presenters/packages/npm/package_presenter.rb:62:in `to_h'
(0.7ms) SELECT "packages_packages"."version" FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 AND "packages_packages"."id" IN (SELECT MAX(id) AS id FROM "packages_packages" WHERE "packages_packages"."project_id" = 40 AND "packages_packages"."package_type" = 2 AND "packages_packages"."name" = '@root/mermaid' AND "packages_packages"."status" = 0 GROUP BY "packages_packages"."version")
↳ app/models/packages/package.rb:198:in `pluck_versions'
Route Load (1.4ms) SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 40 AND "routes"."source_type" = 'Project' LIMIT 1
↳ app/models/concerns/routable.rb:118:in `full_path'
- 29 SQL queries (still less queries than on
master
) -
packages_package_files
accessed three times -
packages_dependency_links
accessed three times -
packages_dependencies
access three times - The models with the details (files + dependencies) are accessed for each loop which is what we expect.
- At least, we don't load all the details for all packages at once. It's only for a "slice" of it.
🛠 How to setup and validate locally (strongly suggested)
You're going to need:
- A user. Let's assume that it's
root
. - A personal access token with the
api_scope
- A GitLab installation
- Create a new project with whatever visibility
We're going to fork an npm package and push it to our GitLab package registry.
- Fork the
mermaidjs
project:$ git clone https://github.com/mermaid-js/mermaid.git
- We use mermaid js because it has several dependencies
- Any other npm package can work too.
-
cd
into it - Open
package.json
:- Update the name to
@root/mermaid
. Basically@<user namespace path>/<package name>
- If you're using
mermaid
, remove all thescripts
entries. This will speed up the publish action.
- Update the name to
- Create root
.npmrc
file with:@root:registry=http://<gitlab installation>/api/v4/projects/<project_id>/packages/npm/ //<gitlab installation>/api/v4/projects/<project_id>/packages/npm/:_authToken=<PAT token>
- Let's push a package
$ npm publish
- You should see something like
+ @root/mermaid@8.11.4
- You should see something like
- Push more versions.
- Simply bump the version in
package.json
and$ npm publish
- Simply bump the version in
- Let's create a tag:
$ npm dist-tag add @root/mermaid@<existing version> <tag_name>
- Lastly, don't forget that changes are behind a feature flag:
Feature.enable(:npm_presenter_queries_tuning)
Now, you're ready to get the metadata endpoint. You can access it in the browser if you're already logged in. Browse: http://<gitlab installation>/api/v4/packages/npm/@root%2Fmermaid
.
Watch log/development.log
for the queries fired.
📐 Does this MR meet the acceptance criteria?
Conformity
-
I have included changelog trailers, or none are needed. (Does this MR need a changelog?) - [-] I have added/updated documentation, or it's not needed. (Is documentation required?)
- [-] I have properly separated EE content from FOSS, or this MR is FOSS only. (Where should EE code go?)
-
I have added information for database reviewers in the MR description, or it's not needed. (Does this MR have database related changes?) -
I have self-reviewed this MR per code review guidelines. -
This MR does not harm performance, or I have asked a reviewer to help assess the performance impact. (Merge request performance guidelines) -
I have followed the style guides. -
This change is backwards compatible across updates, or this does not apply.
Availability and Testing
-
I have added/updated tests following the Testing Guide, or it's not needed. (Consider all test levels. See the Test Planning Process.) - [-] I have tested this MR in all supported browsers, or it's not needed.
- [-] I have informed the Infrastructure department of a default or new setting change per definition of done, or it's not needed.
Security
Does this MR contain changes to processing or storing of credentials or tokens, authorization and authentication methods or other items described in the security review guidelines? If not, then delete this Security section.
- [-] 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
💾 Database Review
Up
== 20210817130415 AddProjectIdNameVersionIdToNpmPackages: migrating ===========
-- transaction_open?()
-> 0.0000s
-- index_exists?(:packages_packages, [:project_id, :name, :version, :id], {:where=>"package_type = 2 AND status = 0", :name=>"idx_installable_npm_pkgs_on_project_id_name_version_id", :algorithm=>:concurrently})
-> 0.0109s
-- execute("SET statement_timeout TO 0")
-> 0.0008s
-- add_index(:packages_packages, [:project_id, :name, :version, :id], {:where=>"package_type = 2 AND status = 0", :name=>"idx_installable_npm_pkgs_on_project_id_name_version_id", :algorithm=>:concurrently})
-> 0.0063s
-- execute("RESET statement_timeout")
-> 0.0008s
== 20210817130415 AddProjectIdNameVersionIdToNpmPackages: migrated (0.0222s) ==
Down
== 20210817130415 AddProjectIdNameVersionIdToNpmPackages: reverting ===========
-- transaction_open?()
-> 0.0000s
-- index_exists?(:packages_packages, [:project_id, :name, :version, :id], {:where=>"package_type = 2 AND status = 0", :name=>"idx_installable_npm_pkgs_on_project_id_name_version_id", :algorithm=>:concurrently})
-> 0.0081s
-- execute("SET statement_timeout TO 0")
-> 0.0008s
-- remove_index(:packages_packages, {:where=>"package_type = 2 AND status = 0", :name=>"idx_installable_npm_pkgs_on_project_id_name_version_id", :algorithm=>:concurrently, :column=>[:project_id, :name, :version, :id]})
-> 0.0125s
-- execute("RESET statement_timeout")
-> 0.0007s
== 20210817130415 AddProjectIdNameVersionIdToNpmPackages: reverted (0.0248s) ==
Explain plans
All explains plans have been created using those commands:
reset
exec CREATE INDEX idx_installable_npm_pkgs_on_project_id_name_version_id ON packages_packages USING btree (project_id, name, version, id) WHERE ((package_type = 2) AND (status = 0));
exec VACUUM ANALYZE packages_packages
explain <query>
Here are the updated queries and explain plans:
@ database reviewers, other queries are eagerly loading rows using their primary keys or foreign keys. I didn't include explain plans for those. Let me know if you see any query that needs an analysis.
🔮 Follow ups
Note that this MR is the first action to improve response times in those metadata endpoints. We have other more heavier solutions such as generating the metadata response in a background job and store this in object storage so that the endpoints will simply point to the file when requested.
See Solutions
in #243780 (comment 649798873)