Expression Indexes and Operator Classes in PostgreSQL

In this blog we are going to explore expression indexes and Operator Classes in PostgreSQL and its support in Rails. But before diving deep into it, we should be aware about terminology like Indexes.

So, moving on what is Expression Indexes ??

In order to understand that, let’s take a scenario, suppose there is a movies booking application and on one page we want to filter movies on the basis of movie name. So, what would be the query for the same ??

It will be following in:

PostgreSQL:

  SELECT * FROM movies WHERE lower(name) = 'name';

 

Rails:

  Movie.where("lower(name) = ?", name.downcase)

 

Okay, so everything looks perfect, but what if the number of movies increases?

In that case this query will take time. So, in order to optimise it we need to do indexing for this column name.  But in this case normal indexing will not work because here we are using expression lower(name) in the where clause. 

For such cases, PostgreSQL helps us by perform Indexing On Expressions

PostgreSQL(supported above  or equal to 9.4 versions) :

  CREATE UNIQUE INDEX index_movies_on_name_unique ON movies (lower(name));

 

Rails(supported Rails 5) :

  add_index :movies, "lower(name)",
            name: "index_movies_on_name_unique",
            unique: true

So, our problem is solved now right ?

Yes, regarding the above scenario, expression indexing solves our issue.

But what if on that page, we need to perform partial search on the basis of movie’s name ?

In this case, our query will look like this:

Rails:

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

Here, in PostgreSQL, the indexing which we did earlier using expression indexing will not be used, instead it will go for sequential searching.

Therefore, for this we need to remove expression indexing and add operator class to the previous index, so that during searching it will use this one instead of doing sequential search

PostgreSQL(supported above  or equal to 9.4 versions) :

 CREATE UNIQUE INDEX index_movies_on_name_unique ON movies (lower(name) varchar_pattern_ops);
Rails(supported Rails 5) :
 
 def change
  remove_index :movies, name: :index_movies_on_name_unique
  add_index :movies,  'lower(name) varchar_pattern_ops',
                        name: "index_movies_on_name_unique",
                        unique: true
 end 

Hope you liked it, for more such information stay tuned …. 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s