Add index to projects on marked_for_deletion_at field is null
What does this MR do and why?
Add index to projects
table on marked_for_deletion_at
column where marked_for_deletion_at
is NULL. We have a the scope not_aimed_for_deletion
, however, this is not optimised and uses a sequential scan. We will be using this scope at several places in the follow-up MR !75742 (merged) and therefore need to create an index to optimise the query.
Database
Query Plan
SELECT id FROM projects WHERE marked_for_deletion_at IS NULL limit 10000
Limit (cost=0.44..236.49 rows=10000 width=4) (actual time=0.106..5.779 rows=10000 loops=1)
Buffers: shared hit=6390
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using index_projects_not_aimed_for_deletion on public.projects (cost=0.44..524181.76 rows=22205709 width=4) (actual time=0.103..4.205 rows=10000 loops=1)
Heap Fetches: 375
Buffers: shared hit=6390
I/O Timings: read=0.000 write=0.000
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7507/commands/26712
Migrations
Up
== 20211203091642 AddIndexToProjectsOnMarkedForDeletionAt: migrating ==========
-- transaction_open?()
-> 0.0000s
-- index_exists?(:projects, :id, {:where=>"marked_for_deletion_at IS NULL", :name=>"index_projects_not_aimed_for_deletion", :algorithm=>:concurrently})
-> 0.0158s
-- execute("SET statement_timeout TO 0")
-> 0.0005s
-- add_index(:projects, :id, {:where=>"marked_for_deletion_at IS NULL", :name=>"index_projects_not_aimed_for_deletion", :algorithm=>:concurrently})
-> 0.0092s
-- execute("RESET statement_timeout")
-> 0.0006s
== 20211203091642 AddIndexToProjectsOnMarkedForDeletionAt: migrated (0.0338s) =
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7507/commands/26711
Down
== 20211203091642 AddIndexToProjectsOnMarkedForDeletionAt: reverting ==========
-- transaction_open?()
-> 0.0000s
-- index_exists?(:projects, :id, {:name=>"index_projects_not_aimed_for_deletion", :algorithm=>:concurrently})
-> 0.0161s
-- execute("SET statement_timeout TO 0")
-> 0.0005s
-- remove_index(:projects, {:name=>"index_projects_not_aimed_for_deletion", :algorithm=>:concurrently, :column=>:id})
-> 0.0185s
-- execute("RESET statement_timeout")
-> 0.0008s
== 20211203091642 AddIndexToProjectsOnMarkedForDeletionAt: reverted (0.0447s) =
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7507/commands/26713
Screenshots or screen recordings
These are strongly recommended to assist reviewers and reduce the time to merge your change.
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Relates to #344013 (closed)