Add partial index on identities table to speed up LDAP lookups

For customers with thousands of entries in the table, lookups for an
LDAP identity dominated the PostgreSQL statistics because of sequential
scans. We can speed this up by adding a partial index with the
extern_uid and provider.

Closes https://gitlab.com/gitlab-org/gitlab-ce/issues/59630

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