Skip to content

Add index snippets on project_id and title

João Alexandre Cunha requested to merge add-index-snippets-project-id-and-title into master

What does this MR do and why?

This MR will resolve #347065 (closed). This index purpose is to support the working being done in !73757 (merged).

The index estimations were assessed in this comment. Excerpt:

I've tested creating the b-tree index on (project_id, title) and it takes just 1s (internal). The index size was also only 22MB (internal). So adding the index is also not a big maintenance burden.

With the b-tree on (project_id, title) the query indeed gets much faster (~4ms), since now there's only 1 row to be filtered out by the truncated created_at.

On the below db-test bot it took 17s to create the index, which is still ok.

DB Migrations

Up

λ  gitlab git:(add-index-snippets-project-id-and-title) ✗ be rake db:migrate
== 20211204010826 AddIndexSnippetsOnProjectIdAndTitle: migrating ==============
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:snippets, [:project_id, :title], {:name=>"index_snippets_on_project_id_and_title", :algorithm=>:concurrently})
   -> 0.0318s
-- execute("SET statement_timeout TO 0")
   -> 0.0037s
-- add_index(:snippets, [:project_id, :title], {:name=>"index_snippets_on_project_id_and_title", :algorithm=>:concurrently})
   -> 0.0150s
-- execute("RESET statement_timeout")
   -> 0.0010s
== 20211204010826 AddIndexSnippetsOnProjectIdAndTitle: migrated (0.0612s) =====

Down

λ  gitlab git:(add-index-snippets-project-id-and-title) be rake db:rollback VERSION="20211204010826"
== 20211204010826 AddIndexSnippetsOnProjectIdAndTitle: reverting ==============
-- transaction_open?()
   -> 0.0000s
-- indexes(:snippets)
   -> 0.0084s
-- execute("SET statement_timeout TO 0")
   -> 0.0007s
-- remove_index(:snippets, {:algorithm=>:concurrently, :name=>"index_snippets_on_project_id_and_title"})
   -> 0.0055s
-- execute("RESET statement_timeout")
   -> 0.0007s
== 20211204010826 AddIndexSnippetsOnProjectIdAndTitle: reverted (0.0200s) =====

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by João Alexandre Cunha

Merge request reports