Skip to content

Fix timeouts loading /admin/projects page

Stan Hu requested to merge sh-optimize-admin-projects-page into master

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

Merge request reports