This blog is part of our Rails 5 series.
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.
There might be many
users with the same name.
In such cases,
to speed up the search process,
we can add an index.
adding a regular index
will not trigger an index scan
we are using an expression
in the where clause i.e
In such cases,
we can leverage
expression indexes given by PostgreSQL.
Before Rails 5
adding an expression index
is not straightforward
the migrate api does not support it.
In order to
we would need to ditch
schema.rb and start using
We would also need to add following migration.
Rails 5 adds support for expression indexes
Rails 5 provides ability to add an expression index using add_index method as follows:
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:
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,
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.