Consider adding an index for project_statistics.storage_size
In our Admin Area > Projects view we offer the option to sort projects by storage size:
That redirects to the route /admin/projects?sort=storage_size_desc, which executes the following query (column list trimmed for brevity):
SELECT
"projects"."id" AS t0_r0,
"project_statistics"."id" AS t1_r0,
"routes"."id" AS t2_r0,
"namespaces"."id" AS t3_r0,
"users"."id" AS t4_r0,
"routes_namespaces"."id" AS t5_r0
FROM
"projects"
LEFT OUTER JOIN "project_statistics" ON "project_statistics"."project_id" = "projects"."id"
LEFT OUTER JOIN "routes" ON "routes"."source_id" = "projects"."id"
AND "routes"."source_type" = 'Project'
LEFT OUTER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
LEFT OUTER JOIN "users" ON "users"."id" = "namespaces"."owner_id"
LEFT OUTER JOIN "routes" "routes_namespaces" ON "routes_namespaces"."source_id" = "namespaces"."id"
AND "routes_namespaces"."source_type" = 'Namespace'
WHERE
"projects"."pending_delete" = 'f'
AND "projects"."archived" = 'f'
ORDER BY
project_statistics.storage_size DESC,
projects.id DESC
This query times out in staging and GitLab.com (an attempt at EXPLAIN ANALYZE also timed out https://ops.gitlab.net/gitlab-com/chatops/builds/255872). Perhaps an index for project_statistics.storage_size would help.
Edited by 🤖 GitLab Bot 🤖
