Missing an index

Origin (context)

My error tracker (that has a perfomance module) reported a slow DB query.

It looked like this:

SELECT 1 AS one FROM "users" WHERE LOWER("users"."email") = LOWER('[email protected]') AND "users"."account_id" = 1 LIMIT 1

But wait, I have an index for that (index_users_on_email_and_account_id). How come it is an expensive query?

Exploration journey

First things first, where is that query coming from?

After some digging in the logs I found that the query was generated by a validation:

  validates :email, uniqueness: { case_sensitive: false, scope: :account_id }

After doing some reasearch (aka: searching in the interwebs) I found that when using the LOWER function in the WHERE clause a regular index will not trigger an index scan.

Result

So, missing the index could be read as:

Although having not applied all of them, I found some possible solutions.

A cheap one: to normalize the email in a before_validation and remove the case_sensitive option from the validation.

Some expensive ones: