Skip to content

Add duplicated gin trigram index on notes note

Marc Shaw requested to merge add_duplicate_notes_note_trigram_index into master

What does this MR do?

Identified in another issue: #218410 (comment 565624409) We see that inserting a note can be very timely. It seems like recreating the index fixes the issue.

Proposed by @abrandl here #218410 (comment 571485758) we should create an identical index, then drop the old one. This MR is creating the duplicate index.

== 20210511051718 CreateIndexOnNotesNote: migrating ===========================
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:notes, :note, {:name=>"index_notes_on_note_gin_trigram", :using=>:gin, :opclass=>:gin_trgm_ops, :algorithm=>:concurrently})
   -> 0.0124s
-- execute("SET statement_timeout TO 0")
   -> 0.0010s
-- add_index(:notes, :note, {:name=>"index_notes_on_note_gin_trigram", :using=>:gin, :opclass=>:gin_trgm_ops, :algorithm=>:concurrently})
   -> 5.6516s
-- execute("RESET ALL")
   -> 0.0007s
-- transaction_open?()
   -> 0.0000s
-- indexes(:notes)
   -> 0.0049s
-- remove_index(:notes, {:algorithm=>:concurrently, :name=>"index_notes_on_note_trigram"})
   -> 0.0102s
== 20210511051718 CreateIndexOnNotesNote: migrated (5.6834s) ==================
➜  gitlab git:(add_duplicate_notes_note_trigram_index) ✗ bundle exec rake db:rollback STEP=2
== 20210511051718 CreateIndexOnNotesNote: reverting ===========================
-- transaction_open?()
   -> 0.0000s
-- indexes(:notes)
   -> 0.0103s
-- execute("SET statement_timeout TO 0")
   -> 0.0010s
-- remove_index(:notes, {:algorithm=>:concurrently, :name=>"index_notes_on_note_gin_trigram"})
   -> 0.0304s
-- execute("RESET ALL")
   -> 0.0010s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:notes, :note, {:name=>"index_notes_on_note_trigram", :using=>:gin, :opclass=>:gin_trgm_ops, :algorithm=>:concurrently})
   -> 0.0057s
-- add_index(:notes, :note, {:name=>"index_notes_on_note_trigram", :using=>:gin, :opclass=>:gin_trgm_ops, :algorithm=>:concurrently})
   -> 6.3945s
== 20210511051718 CreateIndexOnNotesNote: reverted (6.4456s) ==================

Screenshots (strongly suggested)

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

Does this MR contain changes to processing or storing of credentials or tokens, authorization and authentication methods or other items described in the security review guidelines? If not, then delete this Security section.

  • Label as security and @ mention @gitlab-com/gl-security/appsec
  • The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
  • Security reports checked/validated by a reviewer from the AppSec team
Edited by Marc Shaw

Merge request reports