3 files changed, 20 insertions, 0 deletions
diff --git a/db/migrate/20230605043258_add_unconfirmed_created_at_index_to_users.rb b/db/migrate/20230605043258_add_unconfirmed_created_at_index_to_users.rb
new file mode 100644
index 00000000000..65bd7a1266b
--- /dev/null
+++ b/db/migrate/20230605043258_add_unconfirmed_created_at_index_to_users.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+class AddUnconfirmedCreatedAtIndexToUsers < Gitlab::Database::Migration[2.1]
+  disable_ddl_transaction!
+
+  INDEX_NAME = 'index_users_on_unconfirmed_and_created_at_for_active_humans'
+
+  def up
+    add_concurrent_index :users, [:created_at, :id],
+      name: INDEX_NAME,
+      where: "confirmed_at IS NULL AND state = 'active' AND user_type IN (0)"
+  end
+
+  def down
+    remove_concurrent_index_by_name :users, INDEX_NAME
+  end
+end
diff --git a/db/schema_migrations/20230605043258 b/db/schema_migrations/20230605043258
new file mode 100644
index 00000000000..54c14183c51
--- /dev/null
+++ b/db/schema_migrations/20230605043258
@@ -0,0 +1 @@
+c2c3012773050935afbcaaf1ca223e94d8fed23b079e00a95d423f4d3d53cf60
+\ No newline at end of file
diff --git a/db/structure.sql b/db/structure.sql
index f78fa973d49..ae2d55eb4b1 100644
--- a/db/structure.sql
+++ b/db/structure.sql
@@ -33194,6 +33194,8 @@ CREATE INDEX index_users_on_state_and_user_type ON users USING btree (state, use
 
 CREATE UNIQUE INDEX index_users_on_static_object_token ON users USING btree (static_object_token);
 
+CREATE INDEX index_users_on_unconfirmed_and_created_at_for_active_humans ON users USING btree (created_at, id) WHERE ((confirmed_at IS NULL) AND ((state)::text = 'active'::text) AND (user_type = 0));
+
 CREATE INDEX index_users_on_unconfirmed_email ON users USING btree (unconfirmed_email) WHERE (unconfirmed_email IS NOT NULL);
 
 CREATE UNIQUE INDEX index_users_on_unlock_token ON users USING btree (unlock_token);