Skip to content

Optimize query for keys of repository members

Matthias Käppler requested to merge github/fork/jirutka/query-perf into master

Created by: jirutka

There’re three methods in Authority module to retrieve SSH key identifiers of repository’s members, differs only in project_access role, but called all one by one from GitoliteConfig class. This generates three queries per project (e.g. when adding/removing SSH key, there’re often many projects that needs to update). Besides that these methods violates DRY principle.

We can simply query just pair of project_access and identifier (e.g. keys for all “roles” at once) and then get desired identifiers by the project_access. The query is generated three times as before, but second and third one are served from cache.

Before:

  Key Load (4.9ms)  SELECT "keys".* FROM "keys" INNER JOIN "users" ON "users"."id" = "keys"."user_id" INNER JOIN "users_projects" ON "users_projects"."user_id" = "users"."id" WHERE (users_projects.project_id = 14 AND users_projects.project_access = 20)
  Key Load (2.4ms)  SELECT "keys".* FROM "keys" WHERE "keys"."project_id" = 14
  Key Load (5.7ms)  SELECT "keys".* FROM "keys" INNER JOIN "users" ON "users"."id" = "keys"."user_id" INNER JOIN "users_projects" ON "users_projects"."user_id" = "users"."id" WHERE (users_projects.project_id = 14 AND users_projects.project_access = 30)
  Key Load (5.2ms)  SELECT "keys".* FROM "keys" INNER JOIN "users" ON "users"."id" = "keys"."user_id" INNER JOIN "users_projects" ON "users_projects"."user_id" = "users"."id" WHERE (users_projects.project_id = 14 AND users_projects.project_access = 40)
  ProtectedBranch Load (2.2ms)  SELECT "protected_branches".* FROM "protected_branches" WHERE "protected_branches"."project_id" = 14

After:

  UsersProject Load (3.1ms)  SELECT keys.identifier as key_identifier, project_access FROM "users_projects" INNER JOIN "users" ON "users"."id" = "users_projects"."user_id" INNER JOIN "keys" ON "keys"."user_id" = "users"."id" WHERE "users_projects"."project_id" = 14
   (1.8ms)  SELECT identifier FROM "keys" WHERE "keys"."project_id" = 14
  CACHE (0.0ms)  SELECT keys.identifier as key_identifier, project_access FROM "users_projects" INNER JOIN "users" ON "users"."id" = "users_projects"."user_id" INNER JOIN "keys" ON "keys"."user_id" = "users"."id" WHERE "users_projects"."project_id" = 14
  CACHE (0.0ms)  SELECT identifier FROM "keys" WHERE "keys"."project_id" = 14
  CACHE (0.0ms)  SELECT keys.identifier as key_identifier, project_access FROM "users_projects" INNER JOIN "users" ON "users"."id" = "users_projects"."user_id" INNER JOIN "keys" ON "keys"."user_id" = "users"."id" WHERE "users_projects"."project_id" = 14
  CACHE (0.1ms)  SELECT identifier FROM "keys" WHERE "keys"."project_id" = 14
  ProtectedBranch Load (1.4ms)  SELECT "protected_branches".* FROM "protected_branches" WHERE "protected_branches"."project_id" = 14

Note: I’ve run tests on my machine with MRI 1.9.3-p327 and all passed. BTW, does anyone know what’s wrong with 1.9.3-p362 that it’s segfaulting (Travis and the same on my machine)?

Merge request reports