Fix project dashboard showing the wrong timestamps
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