Rails 5 Expression Indexes & Operator Classes support

Let's assume that in our health care application we have a page which shows all Patients. This page also has a filter and it allows us to filter patients by their name.

We could implement the filter as shown here.

Patient.where("lower(first_name) = ?", first_name.downcase)

There might be many users with the same name. In such cases, to speed up the search process, we can add an index. But, adding a regular index will not trigger an index scan since we are using an expression in the where clause i.e lower(name). In such cases, we can leverage expression indexes given by PostgreSQL.

Before Rails 5 adding an expression index is not straightforward since the migrate api does not support it. In order to add one we would need to ditch schema.rb and start using structure.sql. We would also need to add following migration.

def up
execute <<-SQL
CREATE INDEX patient_lower_name_idx ON patients (lower(name));
SQL
end

def down
execute <<-SQL
DROP INDEX patient_lower_name_idx;
SQL
end

Rails 5 adds support for expression indexes

Rails 5 provides ability to add an expression index using add_index method as follows:

def change
add_index :patients,
'lower(last_name)',
name: "index_patients_on_name_unique",
unique: true
end

And we also get to keep schema.rb.

Time goes on. everyone is happy with the search functionality until one day a new requirement comes along which is, in short, to have partial matches on patient names.

We modify our search as follows:

User.where("lower(name) like ?", "%#{name.downcase}%")

Since the query is different from before, PostgreSQL query planner will not take the already existing btree index into account and will revert to a sequential scan.

Quoting directly from Postgresql documents,

'The operator classes text_pattern_ops, varchar_pattern_ops, and bpchar_pattern_ops support B-tree indexes on the types text, varchar, and char respectively. The difference from the default operator classes is that the values are compared strictly character by character rather than according to the locale-specific collation rules. This makes these operator classes suitable for use by queries involving pattern matching expressions (LIKE or POSIX regular expressions) when the database does not use the standard "C" locale.'

We need to add an operator class to the previous index for the query planner to utilize the index that we created earlier.

Rails 5 adds support for specifying operator classes on expression indexes

In order to add an index with an operator class we could write our migration as shown below.

def change
remove_index :patients, name: :index_patients_on_name_unique
add_index :patients, 'lower(last_name) varchar_pattern_ops',
name: "index_patients_on_name_unique",
unique: true
end