Remove a n+1 situation in the NPM presenter (!68275) · Merge requests · GitLab.org / GitLab

David Fernandez requested to merge 338483-npm-presenter-eager-load into master Aug 16, 2021

⚔ 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.
- 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.
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.

On `master`

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

This MR with feature flag disabled

SQL 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

This MR with feature flag enabled

SQL 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 ✅

This MR with feature flag enabled + batch loops

We 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:
1. Update the name to @root/mermaid. Basically@<user namespace path>/<package name>
2. If you're using mermaid, remove all the scripts entries. This will speed up the publish action.

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
Push more versions.
- Simply bump the version in package.json and $ npm publish
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:

Batch loop queries

@ 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)

Edited Aug 20, 2021 by David Fernandez

Remove a n+1 situation in the NPM presenter

⚔ Context

🔬 What does this MR do?

🖼 Screenshots or Screencasts (strongly suggested)

On master

This MR with feature flag disabled

This MR with feature flag enabled

This MR with feature flag enabled + batch loops

🛠 How to setup and validate locally (strongly suggested)

📐 Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

💾 Database Review

Up

Down

Explain plans

🔮 Follow ups

Merge request reports

On `master`