Add index on namespaces lower(name) for UsersController#exists
Add a description of your merge request here. Merge requests without an adequate description will not be reviewed until one is added.
Fixes #41340 (closed)
Database Checklist
When adding migrations:
-
Updated db/schema.rb
-
Added a down
method so the migration can be reverted -
Added the output of the migration(s) to the MR body -
Added tests for the migration in spec/migrations
if necessary (e.g. when migrating data)
When adding or modifying queries to improve performance:
-
Included data that shows the performance improvement, preferably in the form of a benchmark -
Included the output of EXPLAIN (ANALYZE, BUFFERS)
of the relevant queries
When adding foreign keys to existing tables:
-
Included a migration to remove orphaned rows in the source table before adding the foreign key -
Removed any instances of dependent: ...
that may no longer be necessary
When adding tables:
-
Ordered columns based on the Ordering Table Columns guidelines -
Added foreign keys to any columns pointing to data in other tables -
Added indexes for fields that are used in statements such as WHERE, ORDER BY, GROUP BY, and JOINs
When removing columns, tables, indexes or other structures:
-
Removed these in a post-deployment migration -
Made sure the application no longer uses (or ignores) these structures
General Checklist
-
Changelog entry added, if necessary -
Documentation created/updated -
API support added -
Tests added for this feature/bug - Review
-
Has been reviewed by Backend -
Has been reviewed by Database
-
-
Conform by the merge request performance guides -
Conform by the style guides -
Squashed related commits together
$ bundle exec rake db:migrate
== 20171220191323 AddIndexOnNamespacesLowerName: migrating ====================
-- index_exists?(:namespaces, {:name=>:index_on_namespaces_lower_name})
-> 0.0095s
-- execute("CREATE INDEX CONCURRENTLY index_on_namespaces_lower_name ON namespaces (LOWER(name));")
-> 0.0165s
== 20171220191323 AddIndexOnNamespacesLowerName: migrated (0.0262s) ===========
$ bundle exec rake db:rollback
== 20171220191323 AddIndexOnNamespacesLowerName: reverting ====================
-- remove_index(:namespaces, {:name=>:index_on_namespaces_lower_name})
-> 0.0042s
== 20171220191323 AddIndexOnNamespacesLowerName: reverted (0.0044s) ===========
This should address this query:
gitlabhq_production=# explain SELECT "namespaces".* FROM "namespaces" WHERE (lower(path) = 'username' OR lower(name) = 'username') LIMIT 1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Limit (cost=0.00..12.36 rows=1 width=189)
-> Seq Scan on namespaces (cost=0.00..129220.82 rows=10456 width=189)
Filter: ((lower((path)::text) = 'username'::text) OR (lower((name)::text) = 'username'::text))
Edited by Yorick Peterse