Skip to content

Add missing expression indexes

Andreas Brandl requested to merge ab/add-missing-expression-indexes into master

What does this MR do?

This adds expression indexes through a migration which are missing in db/structure.sql.

At the time those indexes were introduced, we were using db/schema.rb. These expression indexes were not added to said file, so any new installations don't benefit from those indexes.

We discovered this by comparing GitLab.com (a long running installation) against the development schema.

We expect any installation to benefit from those indexes, so we add them in a regular migration.

For more background, please refer to #280826 (closed) and #271165 (closed).

Migration

== 20201111152859 AddMissingExpressionIndexes: migrating ======================
-- index_name_exists?(:namespaces, :index_on_namespaces_lower_name)
   -> 0.0039s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:namespaces, "LOWER(name)", {:name=>:index_on_namespaces_lower_name, :algorithm=>:concurrently})
   -> 0.0054s
-- execute("SET statement_timeout TO 0")
   -> 0.0001s
-- add_index(:namespaces, "LOWER(name)", {:name=>:index_on_namespaces_lower_name, :algorithm=>:concurrently})
   -> 0.0191s
-- execute("RESET ALL")
   -> 0.0009s
-- index_name_exists?(:namespaces, :index_on_namespaces_lower_path)
   -> 0.0024s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:namespaces, "LOWER(path)", {:name=>:index_on_namespaces_lower_path, :algorithm=>:concurrently})
   -> 0.0314s
-- add_index(:namespaces, "LOWER(path)", {:name=>:index_on_namespaces_lower_path, :algorithm=>:concurrently})
   -> 0.0154s
-- index_name_exists?(:projects, :index_on_projects_lower_path)
   -> 0.0010s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:projects, "LOWER(path)", {:name=>:index_on_projects_lower_path, :algorithm=>:concurrently})
   -> 0.0265s
-- add_index(:projects, "LOWER(path)", {:name=>:index_on_projects_lower_path, :algorithm=>:concurrently})
   -> 0.0184s
-- index_name_exists?(:routes, :index_on_routes_lower_path)
   -> 0.0010s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:routes, "LOWER(path)", {:name=>:index_on_routes_lower_path, :algorithm=>:concurrently})
   -> 0.0036s
-- add_index(:routes, "LOWER(path)", {:name=>:index_on_routes_lower_path, :algorithm=>:concurrently})
   -> 0.0243s
-- index_name_exists?(:users, :index_on_users_lower_username)
   -> 0.0009s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:users, "LOWER(username)", {:name=>:index_on_users_lower_username, :algorithm=>:concurrently})
   -> 0.0129s
-- add_index(:users, "LOWER(username)", {:name=>:index_on_users_lower_username, :algorithm=>:concurrently})
   -> 0.0131s
-- index_name_exists?(:users, :index_on_users_lower_email)
   -> 0.0010s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:users, "LOWER(email)", {:name=>:index_on_users_lower_email, :algorithm=>:concurrently})
   -> 0.0131s
-- add_index(:users, "LOWER(email)", {:name=>:index_on_users_lower_email, :algorithm=>:concurrently})
   -> 0.0182s
-- execute("ANALYZE namespaces")
   -> 0.0014s
-- execute("ANALYZE projects")
   -> 0.0018s
-- execute("ANALYZE routes")
   -> 0.0005s
-- execute("ANALYZE users")
   -> 0.0011s
== 20201111152859 AddMissingExpressionIndexes: migrated (0.2218s) =============

Does this MR meet the acceptance criteria?

Conformity

Edited by Andreas Brandl

Merge request reports