Added trigram indexes for various searched columns

This allows the LIKE condition to use an index. Without a GIN + trigram
index LIKE queries using a wildcard at the start _won't_ use an index
and instead perform a sequence scan.

1 files changed, 50 insertions, 0 deletions

diff --git a/db/migrate/20160226114608_add_trigram_indexes_for_searching.rb b/db/migrate/20160226114608_add_trigram_indexes_for_searching.rb
new file mode 100644
index 00000000000..fca5ac01a08
--- /dev/null
+++ b/db/migrate/20160226114608_add_trigram_indexes_for_searching.rb
@@ -0,0 +1,50 @@
+class AddTrigramIndexesForSearching < ActiveRecord::Migration
+  disable_ddl_transaction!
+
+  def up
+    return unless Gitlab::Database.postgresql?
+
+    unless trigrams_enabled?
+      raise 'You must enable the pg_trgm extension as a PostgreSQL super user'
+    end
+
+    # trigram indexes are case-insensitive so we can just index the column
+    # instead of indexing lower(column)
+    to_index.each do |table, columns|
+      columns.each do |column|
+        execute "CREATE INDEX CONCURRENTLY index_#{table}_on_#{column}_trigram ON #{table} USING gin(#{column} gin_trgm_ops);"
+      end
+    end
+  end
+
+  def down
+    return unless Gitlab::Database.postgresql?
+
+    to_index.each do |table, columns|
+      columns.each do |column|
+        remove_index table, name: "index_#{table}_on_#{column}_trigram"
+      end
+    end
+  end
+
+  def trigrams_enabled?
+    res = execute("SELECT true AS enabled FROM pg_available_extensions WHERE name = 'pg_trgm' AND installed_version IS NOT NULL;")
+    row = res.first
+
+    row && row['enabled'] == 't' ? true : false
+  end
+
+  def to_index
+    {
+      ci_runners:     [:token, :description],
+      issues:         [:title, :description],
+      merge_requests: [:title, :description],
+      milestones:     [:title, :description],
+      namespaces:     [:name, :path],
+      notes:          [:note],
+      projects:       [:name, :path, :description],
+      snippets:       [:title, :file_name],
+      users:          [:username, :name, :email]
+    }
+  end
+end