How to create index on LOWER("users"."username") in Rails (using postgres)

Question

I have a sequential scan occurring 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 fairly positive this arises from the following model validation:

validates :username, uniqueness: { case_sensitive: false }

Should I be creating an index against this express? And if so, what's the proper way to do this in Rails 4?

Answer 1

Yes, that validation would do that sort of query and that sort of query is going to do a table scan.

You actually have a couple problems here:

• The validation is subject to race conditions because the logic isn't in the database where it belongs. The database should be responsible for all data integrity issues regardless of the usual Rails ideology.
• Your validation triggers table scans and no one likes table scans.

You can solve both of those problems with one index. The first problem is solved by using a unique index inside the database. The second problem is solved by indexing the result of lower(username) rather than username.

AFAIK Rails still doesn't understand indexes on expressions so you'll have to do two things:

1. Switch from schema.rb to structure.sql to keep Rails from forgetting about your index. In your config/application.rb you'll want to set:

   config.active_record.schema_format = :sql
   

   You'll also have to start using the db:structure:* rake tasks instead of the db:schema:* tasks. Once you've switched to structure.sql, you can delete db/schema.rb since it won't be updated or used anymore; you'll also want to start tracking db/structure.sql in revision control.

2. Create the index by hand by writing a bit of SQL in a migration. 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 PostgreSQL-specific things but that's nothing to worry about since ActiveRecord doesn't give you any useful portability anyway.