Merge branch 'sh-add-index-extern-uid' into 'master'

Add partial index on identities table to speed up LDAP lookups

Closes #59630

See merge request gitlab-org/gitlab-ce!26710

3 files changed, 25 insertions, 0 deletions

diff --git a/changelogs/unreleased/sh-add-index-extern-uid.yml b/changelogs/unreleased/sh-add-index-extern-uid.yml
new file mode 100644
index 00000000000..531770237a8
--- /dev/null
+++ b/changelogs/unreleased/sh-add-index-extern-uid.yml
@@ -0,0 +1,5 @@
+---
+title: Add partial index on identities table to speed up LDAP lookups
+merge_request: 26710
+author:
+type: performance
diff --git a/db/post_migrate/20190723105753_add_index_on_identities_lower_extern_uid_and_provider.rb b/db/post_migrate/20190723105753_add_index_on_identities_lower_extern_uid_and_provider.rb
new file mode 100644
index 00000000000..36ecca4821f
--- /dev/null
+++ b/db/post_migrate/20190723105753_add_index_on_identities_lower_extern_uid_and_provider.rb
@@ -0,0 +1,19 @@
+# frozen_string_literal: true
+
+class AddIndexOnIdentitiesLowerExternUidAndProvider < ActiveRecord::Migration[5.2]
+  include Gitlab::Database::MigrationHelpers
+
+  DOWNTIME = false
+
+  disable_ddl_transaction!
+
+  INDEX_NAME = "index_on_identities_lower_extern_uid_and_provider"
+
+  def up
+    add_concurrent_index(:identities, 'lower(extern_uid), provider', name: INDEX_NAME)
+  end
+
+  def down
+    remove_concurrent_index_by_name(:identities, INDEX_NAME)
+  end
+end
diff --git a/db/schema.rb b/db/schema.rb
index f6b7e22fe09..6f5fc6c65eb 100644
--- a/db/schema.rb
+++ b/db/schema.rb
@@ -1580,6 +1580,7 @@ ActiveRecord::Schema.define(version: 2019_07_29_090456) do
     t.datetime "updated_at"
     t.integer "saml_provider_id"
     t.string "secondary_extern_uid"
+    t.index "lower((extern_uid)::text), provider", name: "index_on_identities_lower_extern_uid_and_provider"
     t.index ["saml_provider_id"], name: "index_identities_on_saml_provider_id", where: "(saml_provider_id IS NOT NULL)"
     t.index ["user_id"], name: "index_identities_on_user_id"
   end