summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorStan Hu <stanhu@gmail.com>2019-07-23 14:54:04 -0700
committerStan Hu <stanhu@gmail.com>2019-07-30 08:45:37 -0700
commit5bb2f0c6fcd7bc195a18efa73420be7de0ce879b (patch)
tree5892a57832ad5326cdb582b4a9a8e36848559283
parent11f82c891ab82620982ef157d3a6783ee56a6997 (diff)
downloadgitlab-ce-5bb2f0c6fcd7bc195a18efa73420be7de0ce879b.tar.gz
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
-rw-r--r--changelogs/unreleased/sh-add-index-extern-uid.yml5
-rw-r--r--db/post_migrate/20190723105753_add_index_on_identities_lower_extern_uid_and_provider.rb19
-rw-r--r--db/schema.rb1
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