Remove index on issues.relative_position
What does this MR do?
This index is only on a single column, relative_position
. However,
when we query issues by anything other than ID, we pretty much always do
that with a filter on project_id
. (The exception is an admin user
viewing a dashboard, which is a rare case, and there aren't
instance-level boards anyway.)
We already have an index that's perfect for filtering by project_id
and ordering by relative_position
, because it's on these columns:
(project_id, relative_position, state_id, id DESC)
But sometimes the planner won't use it because of this index, which can lead to poor query performance.
See #254249 (comment 418198007) for more details.
This index is used, but I don't know how to tell if it's used well. In the last week, this index has had almost 50,000 scans, while idx_issues_on_project_id_and_rel_position_and_state_id_and_id
has had around 130,000 scans: #254249 (comment 421925447)
We tested this in production by disabling the index for a period of time: gitlab-com/gl-infra/production#2795 (closed)
Migration up:
== 20201001101136 RemoveIndexOnIssuesRelativePosition: migrating ==============
-- transaction_open?()
-> 0.0000s
-- indexes(:issues)
-> 0.0087s
-- execute("SET statement_timeout TO 0")
-> 0.0001s
-- remove_index(:issues, {:algorithm=>:concurrently, :name=>"index_issues_on_relative_position"})
-> 0.0048s
-- execute("RESET ALL")
-> 0.0002s
== 20201001101136 RemoveIndexOnIssuesRelativePosition: migrated (0.0142s) =====
On #database-lab, this took 5 seconds: https://gitlab.slack.com/archives/CLJMDRD8C/p1601548223263800
Migration down:
== 20201001101136 RemoveIndexOnIssuesRelativePosition: reverting ==============
-- transaction_open?()
-> 0.0000s
-- index_exists?(:issues, :relative_position, {:name=>"index_issues_on_relative_position", :algorithm=>:concurrently})
-> 0.0080s
-- execute("SET statement_timeout TO 0")
-> 0.0001s
-- add_index(:issues, :relative_position, {:name=>"index_issues_on_relative_position", :algorithm=>:concurrently})
-> 0.0077s
-- execute("RESET ALL")
-> 0.0002s
== 20201001101136 RemoveIndexOnIssuesRelativePosition: reverted (0.0166s) =====
On #database-lab, this took 20 mins: https://gitlab.slack.com/archives/CLJMDRD8C/p1601548404264700