Projects API: Improve API response time for archived=true
Since the archived projects only make up ~2% of the total data in the projects
table, API requests on that condition are slow due to filtering a large number of rows. Due to rare use of the archived=true
condition in API requests, for now we will avoid adding a large number of specialized indexes.
The second more generic index could be applied for both authenticated and unauthenticated cases, but I added a more specific to further speed up public project lookups. The overall index sizes between both are quite small, so it should contribute minimal overhead.
Main issue describes more details: #197955 (closed)
Optimization for unauthenticated user:
CREATE INDEX index_projects_api_created_at_id_for_archived_vis20 ON projects (created_at, id) WHERE (archived = true AND visibility_level = 20)
-- The query has been executed. Duration: 1.764 min
Schema | Name | Type | Owner | Table | Size | Description
--------+-----------------------------------------------------+-------+--------+----------+---------+-------------
public | index_projects_api_created_at_id_for_archived_vis20 | index | gitlab | projects | 1192 kB |
Query
SELECT COUNT(*)
FROM (
SELECT 1 AS one
FROM "projects"
WHERE "projects"."visibility_level" = 20
AND "projects"."archived" = true
AND "projects"."pending_delete" = false LIMIT 10001) subquery_for_count;
https://explain.depesz.com/s/VYVz
Before:https://explain.depesz.com/s/cxRQ
After:Optimization for authenticated user:
CREATE INDEX index_projects_api_created_at_id_for_archived ON projects (created_at, id) WHERE (archived = true)
-- The query has been executed. Duration: 1.637 min
Schema | Name | Type | Owner | Table | Size | Description
--------+----------------------------------------------------------+-------+--------+----------+---------+-------------
public | index_projects_api_created_at_id_for_archived | index | gitlab | projects | 7376 kB |
Query 2
SELECT COUNT(*)
FROM (
SELECT 1 AS one
FROM "projects"
WHERE (EXISTS (
SELECT 1
FROM "project_authorizations"
WHERE "project_authorizations"."user_id" = 1
AND (project_authorizations.project_id = projects.id))
OR projects.visibility_level IN (10,20))
AND "projects"."archived" = true
AND "projects"."pending_delete" = false LIMIT 10001) subquery_for_count;
https://explain.depesz.com/s/vWUc
Before:https://explain.depesz.com/s/WJ74
After:Migration Output
rake db:migrate:up VERSION=20200323134519
== 20200323134519 AddApiIndexesForArchivedProjects: migrating =================
-- transaction_open?()
-> 0.0000s
-- index_exists?(:projects, [:created_at, :id], {:where=>"archived = true AND visibility_level = 20", :name=>"index_projects_api_created_at_id_for_archived_vis20", :algorithm=>:concurrently})
-> 0.0114s
-- execute("SET statement_timeout TO 0")
-> 0.0001s
-- add_index(:projects, [:created_at, :id], {:where=>"archived = true AND visibility_level = 20", :name=>"index_projects_api_created_at_id_for_archived_vis20", :algorithm=>:concurrently})
-> 0.0028s
-- execute("RESET ALL")
-> 0.0001s
-- transaction_open?()
-> 0.0000s
-- index_exists?(:projects, [:created_at, :id], {:where=>"archived = true", :name=>"index_projects_api_created_at_id_for_archived", :algorithm=>:concurrently})
-> 0.0085s
-- execute("SET statement_timeout TO 0")
-> 0.0001s
-- add_index(:projects, [:created_at, :id], {:where=>"archived = true", :name=>"index_projects_api_created_at_id_for_archived", :algorithm=>:concurrently})
-> 0.0034s
-- execute("RESET ALL")
-> 0.0002s
== 20200323134519 AddApiIndexesForArchivedProjects: migrated (0.0269s) ========
rake db:migrate:down VERSION=20200323134519
== 20200323134519 AddApiIndexesForArchivedProjects: reverting =================
-- transaction_open?()
-> 0.0000s
-- indexes(:projects)
-> 0.0118s
-- execute("SET statement_timeout TO 0")
-> 0.0001s
-- remove_index(:projects, {:algorithm=>:concurrently, :name=>"index_projects_api_created_at_id_for_archived"})
-> 0.0017s
-- execute("RESET ALL")
-> 0.0001s
-- transaction_open?()
-> 0.0000s
-- indexes(:projects)
-> 0.0101s
-- execute("SET statement_timeout TO 0")
-> 0.0001s
-- remove_index(:projects, {:algorithm=>:concurrently, :name=>"index_projects_api_created_at_id_for_archived_vis20"})
-> 0.0016s
-- execute("RESET ALL")
-> 0.0001s
== 20200323134519 AddApiIndexesForArchivedProjects: reverted (0.0260s) ========
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. -
Informed Infrastructure department of a default or new setting change, if applicable per definition of done
Edited by 🤖 GitLab Bot 🤖