Skip to content

Remove index on issues.relative_position

Sean McGivern requested to merge remove-index-on-issues-relative-position into master

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

Edited by Sean McGivern

Merge request reports