Add sort argument to container repos graphql
🔬 What does this MR do?
Adds a sort argument to the container_repositories graphql type.
Allows sorting by: created_at, updated_at, name
📷 Screenshots (strongly suggested)
🐘 Database
Base query
Full queries and explains using a project with >4100 container_repositories
gitlabhq_production=> explain (analyze, buffers) SELECT * FROM container_repositories WHERE project_id = 991785 ORDER BY created_at ASC LIMIT 100;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2866.68..2866.93 rows=100 width=59) (actual time=9.463..9.478 rows=100 loops=1)
Buffers: shared hit=3924
-> Sort (cost=2866.68..2876.82 rows=4055 width=59) (actual time=9.462..9.467 rows=100 loops=1)
Sort Key: created_at
Sort Method: top-N heapsort Memory: 53kB
Buffers: shared hit=3924
-> Index Scan using index_container_repositories_on_project_id on container_repositories (cost=0.43..2711.70 rows=4055 width=59) (actual time=0.031..7.982 rows=4127 loops=1)
Index Cond: (project_id = 991785)
Buffers: shared hit=3924
Planning Time: 0.113 ms
Execution Time: 9.515 ms
(11 rows)
gitlabhq_production=> explain (analyze, buffers) SELECT * FROM container_repositories WHERE project_id = 991785 ORDER BY created_at DESC LIMIT 100;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2866.68..2866.93 rows=100 width=59) (actual time=10.611..10.636 rows=100 loops=1)
Buffers: shared hit=3924
-> Sort (cost=2866.68..2876.82 rows=4055 width=59) (actual time=10.610..10.622 rows=100 loops=1)
Sort Key: created_at DESC
Sort Method: top-N heapsort Memory: 39kB
Buffers: shared hit=3924
-> Index Scan using index_container_repositories_on_project_id on container_repositories (cost=0.43..2711.70 rows=4055 width=59) (actual time=0.027..9.304 rows=4127 loops=1)
Index Cond: (project_id = 991785)
Buffers: shared hit=3924
Planning Time: 0.153 ms
Execution Time: 10.668 ms
(11 rows)
gitlabhq_production=> explain (analyze, buffers) SELECT * FROM container_repositories WHERE project_id = 991785 ORDER BY updated_at ASC LIMIT 100;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2866.68..2866.93 rows=100 width=59) (actual time=9.462..9.477 rows=100 loops=1)
Buffers: shared hit=3924
-> Sort (cost=2866.68..2876.82 rows=4055 width=59) (actual time=9.461..9.466 rows=100 loops=1)
Sort Key: updated_at
Sort Method: top-N heapsort Memory: 53kB
Buffers: shared hit=3924
-> Index Scan using index_container_repositories_on_project_id on container_repositories (cost=0.43..2711.70 rows=4055 width=59) (actual time=0.034..7.967 rows=4127 loops=1)
Index Cond: (project_id = 991785)
Buffers: shared hit=3924
Planning Time: 0.137 ms
Execution Time: 9.506 ms
(11 rows)
gitlabhq_production=> explain (analyze, buffers) SELECT * FROM container_repositories WHERE project_id = 991785 ORDER BY updated_at DESC LIMIT 100;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2866.68..2866.93 rows=100 width=59) (actual time=11.657..11.680 rows=100 loops=1)
Buffers: shared hit=3924
-> Sort (cost=2866.68..2876.82 rows=4055 width=59) (actual time=11.655..11.667 rows=100 loops=1)
Sort Key: updated_at DESC
Sort Method: top-N heapsort Memory: 39kB
Buffers: shared hit=3924
-> Index Scan using index_container_repositories_on_project_id on container_repositories (cost=0.43..2711.70 rows=4055 width=59) (actual time=0.038..10.056 rows=4127 loops=1)
Index Cond: (project_id = 991785)
Buffers: shared hit=3924
Planning Time: 0.178 ms
Execution Time: 11.717 ms
(11 rows)
gitlabhq_production=> explain (analyze, buffers) SELECT * FROM container_repositories WHERE project_id = 991785 ORDER BY name ASC LIMIT 100;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.43..95.47 rows=100 width=59) (actual time=1.010..2.288 rows=100 loops=1)
Buffers: shared hit=102 read=2
I/O Timings: read=1.863
-> Index Scan using index_container_repositories_on_project_id_and_name on container_repositories (cost=0.43..3854.29 rows=4055 width=59) (actual time=1.008..2.247 rows=100 loops=1)
Index Cond: (project_id = 991785)
Buffers: shared hit=102 read=2
I/O Timings: read=1.863
Planning Time: 0.138 ms
Execution Time: 2.315 ms
(9 rows)
gitlabhq_production=> explain (analyze, buffers) SELECT * FROM container_repositories WHERE project_id = 991785 ORDER BY name DESC LIMIT 100;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.43..95.47 rows=100 width=59) (actual time=0.112..0.357 rows=100 loops=1)
Buffers: shared hit=94 read=1
I/O Timings: read=0.039
-> Index Scan Backward using index_container_repositories_on_project_id_and_name on container_repositories (cost=0.43..3854.29 rows=4055 width=59) (actual time=0.112..0.346 rows=100 loops=1)
Index Cond: (project_id = 991785)
Buffers: shared hit=94 read=1
I/O Timings: read=0.039
Planning Time: 0.140 ms
Execution Time: 0.378 ms
(9 rows)
SELECT "container_repositories".*
FROM "container_repositories"
WHERE "container_repositories"."project_id" = 278964
ORDER BY created_at DESC
LIMIT 100All plans end up using index_container_repositories_on_project_id and then sorting the results from there:
☑ Does this MR meet the acceptance criteria?
Conformity
-
Changelog entry -
Documentation (if required) -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides - [-] Separation of EE specific content
Availability 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
- [-] Informed Infrastructure department of a default or new setting change, if applicable per definition of done
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
Related to #290302 (closed)
Edited by Steve Abrams