How to create an index in LOWER ("users". "Username") in Rails (using postgres)

Question

I have a sequential scan in my UsersController#create action.

 SELECT ? AS one FROM "users" WHERE (LOWER("users"."username") = LOWER(?) AND "users"."id" != ?) LIMIT ? Explain plan 1 Query planLimit (cost=0.00..3.35 rows=1 width=0) 2 Query plan-> Seq Scan on users (cost=0.00..3.35 rows=1 width=0) 3 Query planFilter: ? 

I am quite positive, this follows from the following model check:

validates :username, uniqueness: { case_sensitive: false }

Should I create an index for this expression? And if so, what is the correct way to do this in Rails 4?

Answer 1

Yes, this check will make such a query and this query will perform a table scan.

You actually have a couple of problems:

• Validation is subject to race conditions because the logic is not in the database where it belongs. The database should be responsible for all data integrity issues, regardless of the usual Rails ideology.
• Your check activates table scans and no one likes table scans.

You can solve both of these problems with the same index. The first problem is solved using a unique index inside the database. The second problem is solved by indexing the result lower(username) , not username .

AFAIK Rails still doesn't understand indexes in expressions, so you need to do two things:

• Switch from schema.rb to structure.sql so that Rails doesn't forget about your index. In config/application.rb you want to set:

   config.active_record.schema_format = :sql 

  You will also need to start using db:structure:* rake tasks instead of db:schema:* tasks. After you switched to structure.sql , you can remove db/schema.rb , since it will no longer be updated or used; You will also want to start tracking db/structure.sql in version control.

• Create the index manually by writing the SQL bit during the migration process. This is easy:

   def up connection.execute(%q{ create index idx_users_lower_username on users(lower(username)) }) end def down connection.execute(%q{ drop index idx_users_lower_username }) end 

Of course, this will leave you with certain PostgreSQL stuff, but nothing to worry about, as ActiveRecord does not give you any useful portability anyway.