Skip to content

Projects API: Improve API response time for archived=true

Patrick Bair requested to merge 197955-projects-api-improve-api-response-time-for-archived-true into master

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;

Before: https://explain.depesz.com/s/VYVz

After: https://explain.depesz.com/s/cxRQ

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;

Before: https://explain.depesz.com/s/vWUc

After: https://explain.depesz.com/s/WJ74

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

Availability and Testing

Edited by 🤖 GitLab Bot 🤖

Merge request reports