Fix timeouts loading /admin/projects page
These extra sort keys caused sequential scans on the namespaces and projects page. Removing them makes the page load on GitLab.com.
Before:
gitlabhq_production=# explain analyze select projects.id from projects LEFT OUTER JOIN "project_statistics" ON "project_statistics"."project_id" = "projects"."id" LEFT OUTER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id" WHERE "projects"."pending_delete" = 'f' AND "projects"."archived" = 'f' ORDER BY "projects"."last_activity_at" DESC, namespaces.path, projects.name ASC LIMIT 20 OFFSET 0;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=605613.84..605613.89 rows=20 width=35) (actual time=7086.366..7086.369 rows=20 loops=1)
-> Sort (cost=605613.84..615470.23 rows=3942557 width=35) (actual time=7086.365..7086.366 rows=20 loops=1)
Sort Key: projects.last_activity_at DESC, namespaces.path, projects.name
Sort Method: top-N heapsort Memory: 26kB
-> Hash Left Join (cost=160884.02..500703.81 rows=3942557 width=35) (actual time=1364.837..6427.062 rows=4094767 loops=1)
Hash Cond: (projects.namespace_id = namespaces.id)
-> Seq Scan on projects (cost=0.00..208745.24 rows=3942557 width=29) (actual time=0.053..2447.934 rows=4094767 loops=1)
Filter: ((NOT pending_delete) AND (NOT archived))
Rows Removed by Filter: 51035
-> Hash (cost=114236.12..114236.12 rows=2683512 width=14) (actual time=1362.665..1362.665 rows=2381793 loops=1)
Buckets: 524288 Batches: 16 Memory Usage: 11116kB
-> Seq Scan on namespaces (cost=0.00..114236.12 rows=2683512 width=14) (actual time=0.035..856.617 rows=2381793 loops=1)
Planning time: 3.748 ms
Execution time: 7087.477 ms
(14 rows)
After:
gitlabhq_production=# explain analyze select projects.id from projects LEFT OUTER JOIN "project_statistics" ON "project_statistics"."project_id" = "projects"."id" LEFT OUTER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id" ORDER BY "projects"."last_activity_at" DESC LIMIT 20 OFFSET 0;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.56..5.28 rows=20 width=12) (actual time=0.106..0.156 rows=20 loops=1)
-> Index Scan Backward using index_projects_on_last_activity_at on projects (cost=0.56..956178.59 rows=4046645 width=12) (actual time=0.105..0.153 rows=20 loops=1)
Planning time: 0.807 ms
Execution time: 0.172 ms
(4 rows)
Closes #44338 (closed)
Edited by Stan Hu