Fix releases API N+1 in sorted_links usage (!60561) · Merge requests · GitLab.org / GitLab

Catalin Irimie requested to merge cat-releases-sortedlinks-np1 into master Apr 29, 2021

What does this MR do?

In !60189 (merged) we've introduced a sorted_links scope to prevent N+1s when exposing sorted links as links.sorted through the Release entity.

However, this seems to have an odd behavior that I didn't catch before: the preload doesn't seem to work with the scoped sorted_links.

For example, for a project with multiple release, where the last release has 5 links:

irb(main):013:0> r = Release.where(project_id: 8).includes(:links, sorted_links: { release: { project: [:p
roject_feature, :route, { namespace: :route }] } }).last
D, [2021-04-29T01:56:48.516034 #3645] DEBUG -- :   Release Load (5.2ms)  /*application:console*/ SELECT "releases".* FROM "releases" WHERE "releases"."project_id" = 8 ORDER BY "releases"."id" DESC LIMIT 1
D, [2021-04-29T01:56:48.518182 #3645] DEBUG -- :   Releases::Link Load (0.6ms)  /*application:console*/ SELECT "release_links".* FROM "release_links" WHERE "release_links"."release_id" = 1146
D, [2021-04-29T01:56:48.520124 #3645] DEBUG -- :   Releases::Link Load (0.4ms)  /*application:console*/ SELECT "release_links".* FROM "release_links" WHERE "release_links"."release_id" = 1146 ORDER BY "release_links"."created_at" DESC
D, [2021-04-29T01:56:48.521509 #3645] DEBUG -- :   Release Load (0.4ms)  /*application:console*/ SELECT "releases".* FROM "releases" WHERE "releases"."id" = 1146
D, [2021-04-29T01:56:48.523462 #3645] DEBUG -- :   Project Load (0.9ms)  /*application:console*/ SELECT "projects".* FROM "projects" WHERE "projects"."id" = 8
D, [2021-04-29T01:56:48.525821 #3645] DEBUG -- :   ProjectFeature Load (0.5ms)  /*application:console*/ SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 8
D, [2021-04-29T01:56:48.535616 #3645] DEBUG -- :   Route Load (7.5ms)  /*application:console*/ SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Project' AND "routes"."source_id" = 8
D, [2021-04-29T01:56:48.538735 #3645] DEBUG -- :   Namespace Load (1.6ms)  /*application:console*/ SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 1
D, [2021-04-29T01:56:48.541118 #3645] DEBUG -- :   Route Load (0.5ms)  /*application:console*/ SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Namespace' AND "routes"."source_id" = 1
=> #<Release id: 1146, tag: "v12.10.0.pre", description: [FILTERED], project_id: 8, created_at: "2021...
irb(main):014:0> r.sorted_links.map {|l| l.external?}
=> [true, true, true, true]

Compared with:

irb(main):015:0> r = Release.where(project_id: 8).includes(:links, :sorted_links, project: [:project_feature, :route, { namespace: :route }]).last
D, [2021-04-29T01:57:39.297262 #3645] DEBUG -- :   Release Load (0.8ms)  /*application:console*/ SELECT "releases".* FROM "releases" WHERE "releases"."project_id" = 8 ORDER BY "releases"."id" DESC LIMIT 1
D, [2021-04-29T01:57:39.298847 #3645] DEBUG -- :   Releases::Link Load (0.4ms)  /*application:console*/ SELECT "release_links".* FROM "release_links" WHERE "release_links"."release_id" = 1146
D, [2021-04-29T01:57:39.300357 #3645] DEBUG -- :   Releases::Link Load (0.4ms)  /*application:console*/ SELECT "release_links".* FROM "release_links" WHERE "release_links"."release_id" = 1146 ORDER BY "release_links"."created_at" DESC
D, [2021-04-29T01:57:39.301921 #3645] DEBUG -- :   Project Load (0.7ms)  /*application:console*/ SELECT "projects".* FROM "projects" WHERE "projects"."id" = 8
D, [2021-04-29T01:57:39.303662 #3645] DEBUG -- :   ProjectFeature Load (0.3ms)  /*application:console*/ SELECT "project_features".* FROM "project_features" WHERE "project_features"."project_id" = 8
D, [2021-04-29T01:57:39.305965 #3645] DEBUG -- :   Route Load (0.7ms)  /*application:console*/ SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Project' AND "routes"."source_id" = 8
D, [2021-04-29T01:57:39.307613 #3645] DEBUG -- :   Namespace Load (0.6ms)  /*application:console*/ SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 1
D, [2021-04-29T01:57:39.308957 #3645] DEBUG -- :   Route Load (0.3ms)  /*application:console*/ SELECT "routes".* FROM "routes" WHERE "routes"."source_type" = 'Namespace' AND "routes"."source_id" = 1
=> #<Release id: 1146, tag: "v12.10.0.pre", description: [FILTERED], project_id: 8, created_at: "2021...
irb(main):016:0> r.sorted_links.map {|l| l.external?}
D, [2021-04-29T01:57:44.065881 #3645] DEBUG -- :   Release Load (0.8ms)  /*application:console*/ SELECT "releases".* FROM "releases" WHERE "releases"."id" = 1146 LIMIT 1
D, [2021-04-29T01:57:44.067930 #3645] DEBUG -- :   Project Load (0.9ms)  /*application:console*/ SELECT "projects".* FROM "projects" WHERE "projects"."id" = 8 LIMIT 1
D, [2021-04-29T01:57:44.070188 #3645] DEBUG -- :   Namespace Load (0.6ms)  /*application:console*/ SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 1 LIMIT 1
D, [2021-04-29T01:57:44.071763 #3645] DEBUG -- :   Route Load (0.4ms)  /*application:console*/ SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 1 AND "routes"."source_type" = 'Namespace' LIMIT 1
D, [2021-04-29T01:57:44.073363 #3645] DEBUG -- :   Release Load (0.5ms)  /*application:console*/ SELECT "releases".* FROM "releases" WHERE "releases"."id" = 1146 LIMIT 1
D, [2021-04-29T01:57:44.075191 #3645] DEBUG -- :   Project Load (0.8ms)  /*application:console*/ SELECT "projects".* FROM "projects" WHERE "projects"."id" = 8 LIMIT 1
D, [2021-04-29T01:57:44.077306 #3645] DEBUG -- :   Namespace Load (0.6ms)  /*application:console*/ SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 1 LIMIT 1
D, [2021-04-29T01:57:44.078990 #3645] DEBUG -- :   Route Load (0.4ms)  /*application:console*/ SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 1 AND "routes"."source_type" = 'Namespace' LIMIT 1
D, [2021-04-29T01:57:44.080426 #3645] DEBUG -- :   Release Load (0.4ms)  /*application:console*/ SELECT "releases".* FROM "releases" WHERE "releases"."id" = 1146 LIMIT 1
D, [2021-04-29T01:57:44.082038 #3645] DEBUG -- :   Project Load (0.8ms)  /*application:console*/ SELECT "projects".* FROM "projects" WHERE "projects"."id" = 8 LIMIT 1
D, [2021-04-29T01:57:44.083918 #3645] DEBUG -- :   Namespace Load (0.5ms)  /*application:console*/ SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 1 LIMIT 1
D, [2021-04-29T01:57:44.085622 #3645] DEBUG -- :   Route Load (0.4ms)  /*application:console*/ SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 1 AND "routes"."source_type" = 'Namespace' LIMIT 1
D, [2021-04-29T01:57:44.087302 #3645] DEBUG -- :   Release Load (0.4ms)  /*application:console*/ SELECT "releases".* FROM "releases" WHERE "releases"."id" = 1146 LIMIT 1
D, [2021-04-29T01:57:44.092476 #3645] DEBUG -- :   Project Load (1.2ms)  /*application:console*/ SELECT "projects".* FROM "projects" WHERE "projects"."id" = 8 LIMIT 1
D, [2021-04-29T01:57:44.095436 #3645] DEBUG -- :   Namespace Load (0.7ms)  /*application:console*/ SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 1 LIMIT 1
D, [2021-04-29T01:57:44.097211 #3645] DEBUG -- :   Route Load (0.4ms)  /*application:console*/ SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 1 AND "routes"."source_type" = 'Namespace' LIMIT 1
=> [true, true, true, true]

The queries all look like:

D, [2021-04-29T00:45:49.357665 #25487] DEBUG -- :   CACHE Route Load (0.1ms)  SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 1 AND "routes"."source_type" = 'Namespace' LIMIT 1  [["source_id", 1], ["source_type", "Namespace"], ["LIMIT", 1]]
D, [2021-04-29T00:45:49.360002 #25487] DEBUG -- :   ↳ app/models/concerns/routable.rb:103:in `full_path'
D, [2021-04-29T00:45:49.360061 #25487] DEBUG -- :   ↳ app/models/namespace.rb:193:in `to_param'
D, [2021-04-29T00:45:49.360806 #25487] DEBUG -- :   ↳ config/routes.rb:333:in `block (3 levels) in <top (required)>'
D, [2021-04-29T00:45:49.361088 #25487] DEBUG -- :   ↳ lib/gitlab/url_builder.rb:40:in `build'
D, [2021-04-29T00:45:49.361143 #25487] DEBUG -- :   ↳ ee/lib/ee/gitlab/url_builder.rb:23:in `build'
D, [2021-04-29T00:45:49.361198 #25487] DEBUG -- :   ↳ app/models/concerns/has_repository.rb:114:in `web_url'
D, [2021-04-29T00:45:49.361253 #25487] DEBUG -- :   ↳ app/models/releases/link.rb:27:in `internal?'
D, [2021-04-29T00:45:49.361307 #25487] DEBUG -- :   ↳ app/models/releases/link.rb:31:in `external?'

Thankfully those are all cached queries, but this MR adds the same preload relationship as the below project namespace route, to properly preload Release::Link queries that check if a link is external.

Screenshots (strongly suggested)

Before (for only 5 links):

  "db_duration_s": 0.23696,
  "db_count": 46,
  "db_write_count": 0,
  "db_cached_count": 20,

After:

  "db_duration_s": 0.01644,
  "db_count": 35,
  "db_write_count": 0,
  "db_cached_count": 12,

Does this MR meet the acceptance criteria?

Conformity

📋 Does this MR need a changelog?
- I have included a changelog entry.
- I have not included a changelog entry because _____.
Documentation (if required)
Code review guidelines
Merge request performance guidelines
Style guides
Database guides
Separation of EE specific content

Availability and Testing

Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
Tested in all supported browsers
Informed Infrastructure department of a default or new setting change, if applicable per definition of done

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

Label as security and @ mention @gitlab-com/gl-security/appsec
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
Security reports checked/validated by a reviewer from the AppSec team

Edited Apr 29, 2021 by Catalin Irimie

Fix releases API N+1 in sorted_links usage