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:
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
-
Changelog entry added, if necessary - Review
-
Has been reviewed by Backend -
Has been reviewed by Database
-
-
Conform by the merge request performance guides -
Conform by the style guides -
Squashed related commits together
Edited by Yorick Peterse