Skip to content

Re-organise "issues" indexes for faster ordering

yorickpeterse-staging requested to merge reorganise-issues-indexes-for-sorting into master

This MR adds a variety of composite indexes to the "issues" table to reduce time spent getting issue lists. See the following resources for more information:

Database Checklist

When adding migrations:

  • Updated db/schema.rb
  • Added a down method so the migration can be reverted
  • Added the output of the migration(s) to the MR body
  • Added the execution time of the migration(s) to the MR body
  • Made sure the migration won't interfere with a running GitLab cluster, for example by disabling transactions for long running migrations

When adding indexes:

  • Described the need for these indexes in the MR body
  • Made sure existing indexes can not be reused instead
    • We are removing some existing indexes (that are no longer necessary) to balance things out

When removing columns, tables, indexes or other structures:

  • Removed these in a post-deployment migration
    • The indexes are removed in a regular migration since we first add the new indexes, thus this is safe to do

Migration Output

==  ReorganiseIssuesIndexesForFasterSorting: migrating ========================
-- index_exists?(:issues, [:project_id, :created_at, :id, :state])
   -> 0.0154s
-- transaction_open?()
   -> 0.0000s
-- execute("SET statement_timeout TO 0")
   -> 0.0011s
-- add_index(:issues, [:project_id, :created_at, :id, :state], {:algorithm=>:concurrently})
   -> 33.6328s
-- index_exists?(:issues, [:project_id, :due_date, :id, :state])
   -> 0.0120s
-- transaction_open?()
   -> 0.0000s
-- execute("SET statement_timeout TO 0")
   -> 0.0010s
-- add_index(:issues, [:project_id, :due_date, :id, :state], {:algorithm=>:concurrently})
   -> 16.6955s
-- index_exists?(:issues, [:project_id, :updated_at, :id, :state])
   -> 0.0121s
-- transaction_open?()
   -> 0.0000s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- add_index(:issues, [:project_id, :updated_at, :id, :state], {:algorithm=>:concurrently})
   -> 16.7783s
-- index_exists?(:issues, :project_id)
   -> 0.0130s
-- index_exists?(:issues, :created_at)
   -> 0.0121s
-- transaction_open?()
   -> 0.0000s
-- select_one("SELECT current_setting('server_version_num') AS v")
   -> 0.0016s
-- execute("SET statement_timeout TO 0")
   -> 0.0007s
-- remove_index(:issues, {:algorithm=>:concurrently, :column=>:created_at})
   -> 0.0360s
-- index_exists?(:issues, :due_date)
   -> 0.0144s
-- transaction_open?()
   -> 0.0000s
-- select_one("SELECT current_setting('server_version_num') AS v")
   -> 0.0007s
-- execute("SET statement_timeout TO 0")
   -> 0.0006s
-- remove_index(:issues, {:algorithm=>:concurrently, :column=>:due_date})
   -> 0.0234s
-- index_exists?(:issues, :updated_at)
   -> 0.0118s
==  ReorganiseIssuesIndexesForFasterSorting: migrated (67.2663s) ==============

Migration Timings

Migration Time Environment
ReorganiseIssuesIndexesForFasterSorting 67.26 sec staging

Performance Impact

I used the following simple query to demonstrate the impact:

SELECT "issues".* 
FROM "issues" 
WHERE "issues"."deleted_at" IS NULL 
AND "issues"."project_id" = 13083 
AND "issues"."state" = 'opened'  
ORDER BY "issues"."created_at" DESC, "issues"."id" DESC

The query plan before this migration:

                                                                      QUERY PLAN                                                                       
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=50710.24..50730.65 rows=8166 width=1291) (actual time=62.352..65.565 rows=7914 loops=1)
   Sort Key: created_at DESC, id DESC
   Sort Method: quicksort  Memory: 13784kB
   ->  Index Scan using index_issues_on_project_id on issues  (cost=0.43..50179.63 rows=8166 width=1291) (actual time=0.067..45.894 rows=7914 loops=1)
         Index Cond: (project_id = 13083)
         Filter: ((deleted_at IS NULL) AND ((state)::text = 'opened'::text))
         Rows Removed by Filter: 19251
 Planning time: 0.243 ms
 Execution time: 67.294 ms

And the plan after the index changes are applied:

                                                                                        QUERY PLAN                                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan Backward using index_issues_on_project_id_and_created_at_and_id_and_state on issues  (cost=0.56..16401.37 rows=8166 width=1291) (actual time=0.034..20.491 rows=7914 loops=1)
   Index Cond: ((project_id = 13083) AND ((state)::text = 'opened'::text))
   Filter: (deleted_at IS NULL)
   Rows Removed by Filter: 1522
 Planning time: 0.203 ms
 Execution time: 22.048 ms

General Checklist

Edited by yorickpeterse-staging

Merge request reports