Route lookup optimization database calls
In https://gitlab.com/gitlab-com/gl-infra/infrastructure/snippets/1875425 the following two SQL queries account for more than 13% of all queries executed on GitLab.com:
SELECT "routes".\* FROM "routes"
WHERE "routes"."source_id" = ?
AND "routes"."source_type" = ?
LIMIT ?
SELECT "projects".\* FROM "projects"
INNER JOIN "routes" ON "routes"."source_id"
= "projects"."id" AND "routes"."source_type"
= ? WHERE ((LOWER(routes.path)
= LOWER(?))) ORDER BY (CASE WHEN
routes.path = ? THEN ? ELSE ?
END) LIMIT ?
Considering how much time we spend in these queries, is there more that we could do to optimize them or cache their results?
cc @abrandl
Edited by Andrew Newdigate