Skip to content

Fix slow API response for internal projects sorted by reverse created_at

Patrick Bair requested to merge 197957-projects-api-improve-response-time-for-created_at-desc-order-and-visibility-internal into master

API requests for projects with visibility_level=10 is slow when sorting by created_at desc, due to creation of internal visibility issues being largely phased out. Months of project data has to be scanned over to find the X newest records. In addition, the internal projects are only about ~1% of total project data, so it's a good fit for a conditional index. The conditional index will also help in all API requests for internal projects, as it speeds up the underlying COUNT.

The main issue describes more details: #197957 (closed)

Optimization:

create index index_projects_api_created_at_id_for_vis10 on projects (created_at, id) where (visibility_level = 10 AND pending_delete = false);
-- The query has been executed. Duration: 1.569 min

 Schema |                  Name                      | Type  | Owner  |  Table   |  Size   | Description
--------+--------------------------------------------+-------+--------+----------+---------+-------------
 public | index_projects_api_created_at_id_for_vis10 | index | gitlab | projects | 3512 kB |

Query by created_at desc

SELECT "projects".*
FROM "projects"
WHERE (
  EXISTS (
    SELECT 1
    FROM "project_authorizations"
    WHERE "project_authorizations"."user_id" = 5297955
    AND (project_authorizations.project_id = projects.id))
  OR projects.visibility_level IN (10,20))
AND "projects"."visibility_level" = 10
AND "projects"."pending_delete" = false
ORDER BY "projects"."created_at" DESC, "projects"."id" DESC
LIMIT 20 OFFSET 0;
Before: https://explain.depesz.com/s/74Bq
After: https://explain.depesz.com/s/ifWk

Generic Count Query

SELECT COUNT(*)
FROM (
  SELECT 1 AS one
  FROM "projects"
  WHERE (
    EXISTS (
      SELECT 1
      FROM "project_authorizations"
      WHERE "project_authorizations"."user_id" = 5297955 AND (project_authorizations.project_id = projects.id))
    OR projects.visibility_level IN (10,20))
AND "projects"."visibility_level" = 10
AND "projects"."pending_delete" = false
LIMIT 10001) subquery_for_count;
Before: https://explain.depesz.com/s/i4Iu
After: https://explain.depesz.com/s/cLRR

Migration Output

rails db:migrate:up VERSION=20200325183636
== 20200325183636 AddApiIndexForInternalProjects: migrating ===================
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:projects, [:created_at, :id], {:where=>"visibility_level = 10 AND pending_delete = false", :name=>"index_projects_api_created_at_id_for_vis10", :algorithm=>:concurrently})
   -> 0.0115s
-- execute("SET statement_timeout TO 0")
   -> 0.0001s
-- add_index(:projects, [:created_at, :id], {:where=>"visibility_level = 10 AND pending_delete = false", :name=>"index_projects_api_created_at_id_for_vis10", :algorithm=>:concurrently})
   -> 0.0037s
-- execute("RESET ALL")
   -> 0.0001s
== 20200325183636 AddApiIndexForInternalProjects: migrated (0.0156s) ==========

rails db:migrate:down VERSION=20200325183636
== 20200325183636 AddApiIndexForInternalProjects: reverting ===================
-- transaction_open?()
   -> 0.0000s
-- indexes(:projects)
   -> 0.0114s
-- execute("SET statement_timeout TO 0")
   -> 0.0001s
-- remove_index(:projects, {:algorithm=>:concurrently, :name=>"index_projects_api_created_at_id_for_vis10"})
   -> 0.0027s
-- execute("RESET ALL")
   -> 0.0001s
== 20200325183636 AddApiIndexForInternalProjects: reverted (0.0147s) ==========

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Edited by 🤖 GitLab Bot 🤖

Merge request reports