Skip to content

Update projects list to sort by the displayed timestamps

David Palubin requested to merge dpalubin/gitlab-ce:issue-27181 into master

What does this MR do?

This MR updates the projects list on the dashboard so that when sorting by Last Updated or Oldest Updated, the sort order matches with the project timestamps displayed on the page. Previously the sort order was dependent solely on last_activity_at, while the displayed timestamp was the most recent of last_activity_at, last_repository_updated_at, and updated_at.

New query:

gitlabhq_development=# explain analyze  SELECT "projects".* FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "project_authorizations"."user_id" = 1  ORDER BY (SELECT Max(date) FROM (VALUES (projects.last_activity_at), (projects.last_repository_updated_at), (projects.updated_at)) AS value(date)) DESC;
                                                                                       QUERY PLAN                                                                                       
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=4.61..4.63 rows=8 width=802) (actual time=0.505..0.510 rows=9 loops=1)
   Sort Key: ((SubPlan 1)) DESC
   Sort Method: quicksort  Memory: 29kB
   ->  Merge Join  (cost=2.06..4.49 rows=8 width=802) (actual time=0.182..0.411 rows=9 loops=1)
         Merge Cond: (projects.id = project_authorizations.project_id)
         ->  Index Scan using idx_projects_on_repository_storage_last_repository_updated_at on projects  (cost=0.14..5.44 rows=20 width=794) (actual time=0.075..0.190 rows=20 loops=1)
         ->  Sort  (cost=1.92..1.94 rows=8 width=4) (actual time=0.071..0.075 rows=9 loops=1)
               Sort Key: project_authorizations.project_id
               Sort Method: quicksort  Memory: 25kB
               ->  Seq Scan on project_authorizations  (cost=0.00..1.80 rows=8 width=4) (actual time=0.025..0.050 rows=9 loops=1)
                     Filter: (user_id = 1)
                     Rows Removed by Filter: 68
         SubPlan 1
           ->  Aggregate  (cost=0.05..0.06 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=9)
                 ->  Values Scan on "*VALUES*"  (cost=0.00..0.04 rows=3 width=8) (actual time=0.002..0.004 rows=3 loops=9)
 Planning time: 1.850 ms
 Execution time: 1.010 ms
(17 rows)

versus the old one:

gitlabhq_development=# explain analyze SELECT "projects".* FROM "projects" INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id" WHERE "project_authorizations"."user_id" = 1  ORDER BY "projects"."last_activity_at" DESC;
                                                                                       QUERY PLAN                                                                                       
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=4.17..4.19 rows=8 width=794) (actual time=0.281..0.284 rows=9 loops=1)
   Sort Key: projects.last_activity_at DESC
   Sort Method: quicksort  Memory: 29kB
   ->  Merge Join  (cost=2.06..4.05 rows=8 width=794) (actual time=0.093..0.209 rows=9 loops=1)
         Merge Cond: (projects.id = project_authorizations.project_id)
         ->  Index Scan using idx_projects_on_repository_storage_last_repository_updated_at on projects  (cost=0.14..5.44 rows=20 width=794) (actual time=0.021..0.105 rows=20 loops=1)
         ->  Sort  (cost=1.92..1.94 rows=8 width=4) (actual time=0.064..0.067 rows=9 loops=1)
               Sort Key: project_authorizations.project_id
               Sort Method: quicksort  Memory: 25kB
               ->  Seq Scan on project_authorizations  (cost=0.00..1.80 rows=8 width=4) (actual time=0.021..0.046 rows=9 loops=1)
                     Filter: (user_id = 1)
                     Rows Removed by Filter: 68
 Planning time: 1.603 ms
 Execution time: 0.525 ms
(14 rows)

Closes #27181 (moved)

Does this MR meet the acceptance criteria?

Conformity

Performance and testing

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

  • Label as security and @ mention @gitlab-com/gl-security/appsec
  • The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
  • Security reports checked/validated by a reviewer from the AppSec team
Edited by David Palubin

Merge request reports