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.

1
2Patient.where("lower(first_name) = ?", first_name.downcase)
3
Copy

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.

1
2def up
3execute <<-SQL
4CREATE INDEX patient_lower_name_idx ON patients (lower(name));
5SQL
6end
7
8def down
9execute <<-SQL
10DROP INDEX patient_lower_name_idx;
11SQL
12end
13
Copy

Rails 5 adds support for expression indexes

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

1
2def change
3add_index :patients,
4'lower(last_name)',
5name: "index_patients_on_name_unique",
6unique: true
7end
8
Copy

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:

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

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,

1
2'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.'
3
Copy

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.

1
2def change
3remove_index :patients, name: :index_patients_on_name_unique
4add_index :patients, 'lower(last_name) varchar_pattern_ops',
5name: "index_patients_on_name_unique",
6unique: true
7end
8
Copy