Skip to content

Optimize Routable.find_by_full_path by avoiding joins

Summary

This is a follow-up to !45892 (merged), which introduces a generic Routable.find_by_full_path helper that can be called directly without having to know about the type of the Routable (either Project/Group/Namespace).

When called as Project.find_by_full_path / Group.find_by_full_path, the queries to the routes and redirect_routes tables are joined with the projects / namespaces tables:

When called as Routable.find_by_full_path the route records are fetched first without joins, and then the project or group is fetched with another direct query:

SELECT "routes".* FROM "routes" WHERE "routes"."path" = 'foo/bar' LIMIT 1;
SELECT "routes".* FROM "routes" WHERE (LOWER("routes"."path") = LOWER('foo/bar')) ORDER BY "routes"."id" ASC LIMIT 1;
SELECT "redirect_routes".* FROM "redirect_routes" WHERE (LOWER("redirect_routes"."path") = LOWER('foo/bar')) LIMIT 1;
SELECT "projects".* FROM "projects" WHERE "projects"."id" = 1 LIMIT 1;

These separate queries seem to perform much better, a quick benchark for direct lookups shows around a 30% speedup:

# Before
[27] pry> Benchmark.ms { 1000.times { Project.find_by_full_path('root/flight') } }
=> 2575.894467998296
[28] pry> Benchmark.ms { 1000.times { Project.find_by_full_path('root/flight') } }
=> 2385.2449629921466

# After
[34] pry> Benchmark.ms { 1000.times { Routable.find_by_full_path('root/flight', route_scope: Route.where(source_type: 'Project'), redirect_route_scope: RedirectRoute.where(source_type: 'Project')) } }
=> 1624.174872005824
[35] pry> Benchmark.ms { 1000.times { Routable.find_by_full_path('root/flight', route_scope: Route.where(source_type: 'Project'), redirect_route_scope: RedirectRoute.where(source_type: 'Project')) } }
=> 1639.3099919951055

Improvements

  • Verify how these queries behave with production data, too see if this performance optimization is worthwhile.
  • Avoid joins when possible, although we still need to support chained scopes like Project.where(foo: :bar).find_by_full_path(...).

Involved components

  • app/models/concerns/routable.rb
Edited by Markus Koller