Improve /project API performance
What does this MR do?
It makes the /projects
API endpoint faster.
The detailed comparison of the SQL queries are in the comment below.
The time the queries take are measured in gitlab-com/infrastructure#1891. TL;DR:
API query parameter | Old | New |
---|---|---|
visibility=private |
timeout (60+ seconds) | 29696.743 ms |
membership=true |
1830.465 ms |
2.015 ms |
Are there points in the code the reviewer needs to double check?
Anything isn't broken in ProjectsFinder
.
Why was this MR needed?
/v4/projects?visibility=private
times out. As reported the SELECT projects.*
query is the longest running query for this endpoint, and this MR optimizes that.
Todo
-
Write tests for changes in Project
model -
Write tests for changes in ProjectFinder
-
Print out the affected SQL queries -
Run EXPLAIN ANALYZE
to compare results- Before refactoring: https://explain.depesz.com/s/oGvX
- After refactoring: https://explain.depesz.com/s/Ubom
-
Check if performance for membership=true
query also improved (gitlab-org/gitlab-ce#31937) -
Test timings on staging environment
Does this MR meet the acceptance criteria?
-
Changelog entry added, if necessary -
Documentation created/updated -
API support added - Tests
-
Added for this feature/bug -
All builds are passing
-
-
Conform by the merge request performance guides -
Conform by the style guides -
Branch has no merge conflicts with master
(if it does - rebase it please) -
Squashed related commits together
What are the relevant issue numbers?
Closes gitlab-org/gitlab-ce#31855 and gitlab-org/gitlab-ce#31937. Might make improvements to gitlab-org/gitlab-ce#24261 too.
Edited by Toon Claes