Resolve "should INDEX index_epics_on_lock_version ON epics be in init_structure.sql ?"
requested to merge 348872-should-index-index_epics_on_lock_version-on-epics-be-in-init_structure-sql into master
What does this MR do and why?
Removes the following indexes if they exist.
- index_issues_on_lock_version
- index_merge_requests_on_lock_version
- index_epics_on_lock_version
To get more context about why we are doing this. Please read this comment.
up:
== 20220106141756 RemoveIndexes: migrating ====================================
-- transaction_open?()
-> 0.0000s
-- indexes(:issues)
-> 0.0109s
-- execute("SET statement_timeout TO 0")
-> 0.0005s
-- remove_index(:issues, {:algorithm=>:concurrently, :name=>"index_issues_on_lock_version"})
-> 0.0036s
-- execute("RESET statement_timeout")
-> 0.0005s
-- transaction_open?()
-> 0.0000s
-- indexes(:merge_requests)
-> 0.0094s
-- remove_index(:merge_requests, {:algorithm=>:concurrently, :name=>"index_merge_requests_on_lock_version"})
-> 0.0023s
-- transaction_open?()
-> 0.0000s
-- indexes(:epics)
-> 0.0046s
-- remove_index(:epics, {:algorithm=>:concurrently, :name=>"index_epics_on_lock_version"})
-> 0.0021s
== 20220106141756 RemoveIndexes: migrated (0.0385s) ===========================
down:
== 20220106141756 RemoveIndexes: reverting ====================================
-- transaction_open?()
-> 0.0000s
-- index_exists?(:issues, :lock_version, {:where=>"lock_version IS NULL", :name=>"index_issues_on_lock_version", :algorithm=>:concurrently})
-> 0.0119s
-- execute("SET statement_timeout TO 0")
-> 0.0005s
-- add_index(:issues, :lock_version, {:where=>"lock_version IS NULL", :name=>"index_issues_on_lock_version", :algorithm=>:concurrently})
-> 0.0027s
-- execute("RESET statement_timeout")
-> 0.0004s
-- transaction_open?()
-> 0.0000s
-- index_exists?(:merge_requests, :lock_version, {:where=>"lock_version IS NULL", :name=>"index_merge_requests_on_lock_version", :algorithm=>:concurrently})
-> 0.0086s
-- add_index(:merge_requests, :lock_version, {:where=>"lock_version IS NULL", :name=>"index_merge_requests_on_lock_version", :algorithm=>:concurrently})
-> 0.0024s
-- transaction_open?()
-> 0.0000s
-- index_exists?(:epics, :lock_version, {:where=>"lock_version IS NULL", :name=>"index_epics_on_lock_version", :algorithm=>:concurrently})
-> 0.0045s
-- add_index(:epics, :lock_version, {:where=>"lock_version IS NULL", :name=>"index_epics_on_lock_version", :algorithm=>:concurrently})
-> 0.0020s
== 20220106141756 RemoveIndexes: reverted (0.0374s) ===========================
How to set up and validate locally
First part:
- Connect to PostgreSQL / DBlab (Production)
- Run
\d+ issues
,\d+ merge_requests
,\d+ epics
- The indexes should not be present
Second part:
cd gitlab-development-kit/gitlab
bundle exec rake db:drop db:create db:migrate
cd ..
gdk postgresql
- Run
\d+ issues
,\d+ merge_requests
,\d+ epics
- The indexes should be present
Third part:
cd gitlab-development-kit/gitlab
- git checkout
348872-should-index-index_epics_on_lock_version-on-epics-be-in-init_structure-sql
bundle exec rake db:drop db:create db:migrate
cd ..
gdk postgresql
- Run
\d+ issues
,\d+ merge_requests
,\d+ epics
- The indexes should not be present
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.
Related to #348872 (closed)
Edited by Diogo Frazão