Update projects list to sort by the displayed timestamps
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
-
Changelog entry -
Documentation created/updated or follow-up review issue created -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Performance and testing
-
Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process. -
Tested in all supported browsers
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