Skip to content

Fix project dashboard showing the wrong timestamps

Stan Hu requested to merge sh-dashboard-sort-fix into master

Use the max of last_activity_at and last_repository_updated_at column. The latter is updated only when a push happens, but the former is updated whenever any activity (e.g. issue creation) happens, but it is throttled every minute.

Query plan looks similar. Now:

gitlabhq_production=# explain analyze  SELECT "projects".* FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "project_authorizations"."user_id" = 64248  ORDER BY GREATEST(COALESCE(last_activity_at, '1970-01-01'), COALESCE(last_repository_updated_at, '1970-01-01'));
                                                                                                                   QUERY PLAN                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=433.04..433.28 rows=95 width=642) (actual time=6.464..6.519 rows=481 loops=1)
   Sort Key: (GREATEST(COALESCE(projects.last_activity_at, '1970-01-01 00:00:00+00'::timestamp with time zone), (COALESCE(projects.last_repository_updated_at, '1970-01-01 00:00:00'::timestamp without time zone))::timestamp with time zone))
   Sort Method: quicksort  Memory: 266kB
   ->  Nested Loop  (cost=0.99..429.92 rows=95 width=642) (actual time=0.041..5.439 rows=481 loops=1)
         ->  Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations  (cost=0.56..6.22 rows=95 width=4) (actual time=0.018..0.220 rows=481 loops=1)
               Index Cond: (user_id = 64248)
               Heap Fetches: 0
         ->  Index Scan using projects_pkey on projects  (cost=0.43..4.45 rows=1 width=634) (actual time=0.010..0.010 rows=1 loops=481)
               Index Cond: (id = project_authorizations.project_id)
 Planning time: 0.696 ms
 Execution time: 6.898 ms
(11 rows)

Before:

gitlabhq_production=# explain analyze SELECT "projects".* FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "project_authorizations"."user_id" = 64248  ORDER BY "projects"."last_activity_at" DESC;
                                                                                                 QUERY PLAN                                                                                                 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=432.81..433.04 rows=95 width=634) (actual time=5.069..5.132 rows=481 loops=1)
   Sort Key: projects.last_activity_at DESC
   Sort Method: quicksort  Memory: 261kB
   ->  Nested Loop  (cost=0.99..429.69 rows=95 width=634) (actual time=0.033..4.451 rows=481 loops=1)
         ->  Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations  (cost=0.56..6.22 rows=95 width=4) (actual time=0.018..0.139 rows=481 loops=1)
               Index Cond: (user_id = 64248)
               Heap Fetches: 0
         ->  Index Scan using projects_pkey on projects  (cost=0.43..4.45 rows=1 width=634) (actual time=0.008..0.009 rows=1 loops=481)
               Index Cond: (id = project_authorizations.project_id)
 Planning time: 0.587 ms
 Execution time: 5.228 ms
(11 rows)

Closes #27181 (moved)

Edited by Stan Hu

Merge request reports