Add trigram indexes on email columns for self-managed instances
-
Please check this box if this contribution uses AI-generated content (including content generated by GitLab Duo features) as outlined in the GitLab DCO & CLA
What does this MR do and why?
This MR adds trigram indexes on email columns for self-managed instances which are needed for partial email search feature, see - !147204 (comment 1895356618)
Migrations
bin/rails db:migrate
Result
main: == [advisory_lock_connection] object_id: 124500, pg_backend_pid: 28926 main: == 20240507161859 AddTrigramIndexOnPublicEmailForUsers: migrating ============= main: -- transaction_open?(nil) main: -> 0.0000s main: -- view_exists?(:postgres_partitions) main: -> 0.0405s main: -- index_exists?(:users, :public_email, {:name=>"index_users_on_public_email_trigram", :using=>:gin, :opclass=>{:public_email=>:gin_trgm_ops}, :algorithm=>:concurrently}) main: -> 0.0234s main: -- execute("SET statement_timeout TO 0") main: -> 0.0005s main: -- add_index(:users, :public_email, {:name=>"index_users_on_public_email_trigram", :using=>:gin, :opclass=>{:public_email=>:gin_trgm_ops}, :algorithm=>:concurrently}) main: -> 0.0437s main: -- execute("RESET statement_timeout") main: -> 0.0012s main: == 20240507161859 AddTrigramIndexOnPublicEmailForUsers: migrated (0.1911s) ====main: == [advisory_lock_connection] object_id: 124500, pg_backend_pid: 28926 main: == [advisory_lock_connection] object_id: 124740, pg_backend_pid: 28928 main: == 20240507162033 AddTrigramIndexOnEmailForUsers: migrating =================== main: -- transaction_open?(nil) main: -> 0.0000s main: -- view_exists?(:postgres_partitions) main: -> 0.0014s main: -- index_exists?(:users, :email, {:name=>"index_users_on_email_trigram", :using=>:gin, :opclass=>{:email=>:gin_trgm_ops}, :algorithm=>:concurrently}) main: -> 0.0399s main: -- execute("SET statement_timeout TO 0") main: -> 0.0007s main: -- add_index(:users, :email, {:name=>"index_users_on_email_trigram", :using=>:gin, :opclass=>{:email=>:gin_trgm_ops}, :algorithm=>:concurrently}) main: -> 0.0097s main: -- execute("RESET statement_timeout") main: -> 0.0007s main: == 20240507162033 AddTrigramIndexOnEmailForUsers: migrated (0.0767s) ==========
main: == [advisory_lock_connection] object_id: 124740, pg_backend_pid: 28928 main: == [advisory_lock_connection] object_id: 124840, pg_backend_pid: 28930 main: == 20240507162310 AddTrigramIndexOnEmailForEmails: migrating ================== main: -- transaction_open?(nil) main: -> 0.0000s main: -- view_exists?(:postgres_partitions) main: -> 0.0012s main: -- index_exists?(:emails, :email, {:name=>"index_emails_on_email_trigram", :using=>:gin, :opclass=>{:email=>:gin_trgm_ops}, :algorithm=>:concurrently}) main: -> 0.0115s main: -- execute("SET statement_timeout TO 0") main: -> 0.0024s main: -- add_index(:emails, :email, {:name=>"index_emails_on_email_trigram", :using=>:gin, :opclass=>{:email=>:gin_trgm_ops}, :algorithm=>:concurrently}) main: -> 0.0071s main: -- execute("RESET statement_timeout") main: -> 0.0006s main: == 20240507162310 AddTrigramIndexOnEmailForEmails: migrated (0.0567s) =========
main: == [advisory_lock_connection] object_id: 124840, pg_backend_pid: 28930
bin/rails db:rollback:main
Result
main: == [advisory_lock_connection] object_id: 124160, pg_backend_pid: 28467 main: == 20240507162310 AddTrigramIndexOnEmailForEmails: reverting ================== main: -- transaction_open?(nil) main: -> 0.0000s main: -- view_exists?(:postgres_partitions) main: -> 0.0345s main: -- indexes(:emails) main: -> 0.0080s main: -- execute("SET statement_timeout TO 0") main: -> 0.0003s main: -- remove_index(:emails, {:algorithm=>:concurrently, :name=>"index_emails_on_email_trigram"}) main: -> 0.0050s main: -- execute("RESET statement_timeout") main: -> 0.0004s main: == 20240507162310 AddTrigramIndexOnEmailForEmails: reverted (0.0730s) =========main: == 20240507162033 AddTrigramIndexOnEmailForUsers: reverting =================== main: -- transaction_open?(nil) main: -> 0.0000s main: -- view_exists?(:postgres_partitions) main: -> 0.0006s main: -- indexes(:users) main: -> 0.0141s main: -- remove_index(:users, {:algorithm=>:concurrently, :name=>"index_users_on_email_trigram"}) main: -> 0.0019s main: == 20240507162033 AddTrigramIndexOnEmailForUsers: reverted (0.0272s) ==========
main: == 20240507161859 AddTrigramIndexOnPublicEmailForUsers: reverting ============= main: -- transaction_open?(nil) main: -> 0.0000s main: -- view_exists?(:postgres_partitions) main: -> 0.0007s main: -- indexes(:users) main: -> 0.0112s main: -- remove_index(:users, {:algorithm=>:concurrently, :name=>"index_users_on_public_email_trigram"}) main: -> 0.0019s main: == 20240507161859 AddTrigramIndexOnPublicEmailForUsers: reverted (0.0234s) ====
main: == [advisory_lock_connection] object_id: 124160, pg_backend_pid: 28467