Skip to content

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:

Captura_de_pantalla_2019-02-18_a_la_s__15.44.44

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.

/cc @yguo @abrandl @cshobe

Edited by 🤖 GitLab Bot 🤖