From 17dd3e89d5a43761e85d7af30ad083db6ca5834b Mon Sep 17 00:00:00 2001 From: Yorick Peterse Date: Wed, 10 Aug 2016 12:29:06 +0200 Subject: Remove trigram indexes for "ci_runners" These indexes are only used when you search for runners in the admin interface. This operation is so rarely used that it does not make sense to slow down every update in order to update the GIN trigram indexes. Removing these indexes should speed up queries such as those used for updating the last contact time of CI runners. Locally the timings of this query were reduced from ~50 ms to ~25 ms: UPDATE ci_runners SET updated_at = now(), contacted_at = now(); --- CHANGELOG | 1 + ...60810102349_remove_ci_runner_trigram_indexes.rb | 27 ++++++++++++++++++++++ db/schema.rb | 4 +--- 3 files changed, 29 insertions(+), 3 deletions(-) create mode 100644 db/migrate/20160810102349_remove_ci_runner_trigram_indexes.rb diff --git a/CHANGELOG b/CHANGELOG index 42d32e53685..a317b69941b 100644 --- a/CHANGELOG +++ b/CHANGELOG @@ -36,6 +36,7 @@ v 8.11.0 (unreleased) - Retrieve rendered HTML from cache in one request - Fix renaming repository when name contains invalid chararacters under project settings - Upgrade Grape from 0.13.0 to 0.15.0. !4601 + - Trigram indexes for the "ci_runners" table have been removed to speed up UPDATE queries - Fix devise deprecation warnings. - Update version_sorter and use new interface for faster tag sorting - Optimize checking if a user has read access to a list of issues !5370 diff --git a/db/migrate/20160810102349_remove_ci_runner_trigram_indexes.rb b/db/migrate/20160810102349_remove_ci_runner_trigram_indexes.rb new file mode 100644 index 00000000000..0cfb637804b --- /dev/null +++ b/db/migrate/20160810102349_remove_ci_runner_trigram_indexes.rb @@ -0,0 +1,27 @@ +# See http://doc.gitlab.com/ce/development/migration_style_guide.html +# for more information on how to write migrations for GitLab. + +class RemoveCiRunnerTrigramIndexes < ActiveRecord::Migration + include Gitlab::Database::MigrationHelpers + + DOWNTIME = false + + # Disabled for the "down" method so the indexes can be re-created concurrently. + disable_ddl_transaction! + + def up + return unless Gitlab::Database.postgresql? + + transaction do + execute 'DROP INDEX IF EXISTS index_ci_runners_on_token_trigram;' + execute 'DROP INDEX IF EXISTS index_ci_runners_on_description_trigram;' + end + end + + def down + return unless Gitlab::Database.postgresql? + + execute 'CREATE INDEX CONCURRENTLY index_ci_runners_on_token_trigram ON ci_runners USING gin(token gin_trgm_ops);' + execute 'CREATE INDEX CONCURRENTLY index_ci_runners_on_description_trigram ON ci_runners USING gin(description gin_trgm_ops);' + end +end diff --git a/db/schema.rb b/db/schema.rb index 71980a6d51f..b795eeaa23c 100644 --- a/db/schema.rb +++ b/db/schema.rb @@ -11,7 +11,7 @@ # # It's strongly recommended that you check this file into your version control system. -ActiveRecord::Schema.define(version: 20160804150737) do +ActiveRecord::Schema.define(version: 20160810102349) do # These are extensions that must be enabled in order to support this database enable_extension "plpgsql" @@ -301,10 +301,8 @@ ActiveRecord::Schema.define(version: 20160804150737) do t.boolean "locked", default: false, null: false end - add_index "ci_runners", ["description"], name: "index_ci_runners_on_description_trigram", using: :gin, opclasses: {"description"=>"gin_trgm_ops"} add_index "ci_runners", ["locked"], name: "index_ci_runners_on_locked", using: :btree add_index "ci_runners", ["token"], name: "index_ci_runners_on_token", using: :btree - add_index "ci_runners", ["token"], name: "index_ci_runners_on_token_trigram", using: :gin, opclasses: {"token"=>"gin_trgm_ops"} create_table "ci_services", force: :cascade do |t| t.string "type" -- cgit v1.2.1