Skip to content

Add indexes for user activity queries.

Adds two indexes that are needed for a user activity query (finding recent events).

As for timings, see https://gitlab.com/gitlab-org/gitlab-ce/issues/44446#note_64199401.

Indexes have been added manually in production (with different names though - pending cleanup: https://gitlab.com/gitlab-com/infrastructure/issues/3891). Notice the drop in timings after ~6.10pm:

Screenshot_from_2018-03-20_21-03-26

Migrations:

== 20180320182229 AddIndexesForUserActivityQueries: migrating =================
-- index_exists?(:events, [:author_id, :project_id])
   -> 0.0038s
-- transaction_open?()
   -> 0.0000s
-- execute("SET statement_timeout TO 0")
   -> 0.0003s
-- add_index(:events, [:author_id, :project_id], {:algorithm=>:concurrently})
   -> 0.0069s
-- index_exists?(:user_interacted_projects, :user_id)
   -> 0.0011s
-- transaction_open?()
   -> 0.0000s
-- execute("SET statement_timeout TO 0")
   -> 0.0002s
-- add_index(:user_interacted_projects, :user_id, {:algorithm=>:concurrently})
   -> 0.0044s
== 20180320182229 AddIndexesForUserActivityQueries: migrated (0.0171s) ========

== 20180320182229 AddIndexesForUserActivityQueries: reverting =================
-- index_exists?(:events, [:author_id, :project_id])
   -> 0.0037s
-- transaction_open?()
   -> 0.0000s
-- select_one("SELECT current_setting('server_version_num') AS v")
   -> 0.0004s
-- execute("SET statement_timeout TO 0")
   -> 0.0002s
-- remove_index(:events, {:algorithm=>:concurrently, :column=>[:author_id, :project_id]})
   -> 0.0040s
-- foreign_keys(:user_interacted_projects)
   -> 0.0029s
-- remove_foreign_key(:user_interacted_projects, :users)
   -> 0.0046s
-- index_exists?(:user_interacted_projects, :user_id)
   -> 0.0020s
-- transaction_open?()
   -> 0.0000s
-- select_one("SELECT current_setting('server_version_num') AS v")
   -> 0.0004s
-- execute("SET statement_timeout TO 0")
   -> 0.0003s
-- remove_index(:user_interacted_projects, {:algorithm=>:concurrently, :column=>:user_id})
   -> 0.0022s
-- foreign_keys(:user_interacted_projects)
   -> 0.0031s
-- transaction_open?()
   -> 0.0000s
-- execute("SET statement_timeout TO 0")
   -> 0.0002s
-- execute("ALTER TABLE user_interacted_projects\nADD CONSTRAINT fk_0894651f08\nFOREIGN KEY (user_id)\nREFERENCES users (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0025s
-- execute("ALTER TABLE user_interacted_projects VALIDATE CONSTRAINT fk_0894651f08;")
   -> 0.0027s
== 20180320182229 AddIndexesForUserActivityQueries: reverted (0.0299s) ========

General Checklist

Edited by Yorick Peterse

Merge request reports