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

Performance Tip In Ruby

Today performance is what that comes first, both from the end-user perspective and from developers point of view. So in this blog, I’ll be covering some simple things which we developers should follow, in order to optimize performance of our ruby code.

Here are the some tips with there benchmarking result:

  • Always use string interpolation instead of string concatenation
  language = "ruby"
  Benchmark.bm do |x|
    x.report {p "testing string, "<< language}
    x.report {p "testing string, #{language}"}
  end

  [#<Benchmark::Tms:0x000000000b271388 @cstime=0.0, @real=6.565399962710217e-05, @cutime=0.0, @label="", @stime=0.0, @total=0.0, @utime=0.0>,
   #<Benchmark::Tms:0x000000000b270b40 @cstime=0.0, @real=4.6490000386256725e-05, @cutime=0.0, @label="", @stime=0.0, @total=0.0, @utime=0.0>]

From the above benchmarking, we can see that string concatenation operation is slower than the string    interpolation operation.

  • In ruby destructive operations are faster, we all know destructive operations are risk prone, as they modify the actual value, instead of modifying the copy. But if we have such use-cases where, we want to modify the actual object, than we should go for this destructive ones
  hash1 = {"color": "blue", height: 100}
  hash2 = {"width": 200}
  Benchmark.bm do |x|
    x.report {hash1.merge(hash2)}
    x.report {hash1.merge!(hash2)}
  end

  [#<Benchmark::Tms:0x000000000b090078 @cstime=0.0, @real=7.367998478002846e-06, @cutime=0.0, @label="", @stime=0.0, @total=0.0, @utime=0.0>,
   #<Benchmark::Tms:0x000000000b08f880 @cstime=0.0, @real=3.569999535102397e-06, @cutime=0.0, @label="", @stime=0.0, @total=0.0, @utime=0.0>]

As we can see from the above benchmarking, destructive operations are nearly two times faster than the normal operations.

  • Parallel Assignments are slower than the normal assignment
  Benchmark.bm do |x|
    x.report {var1,var2 = 1, 2}   #parallel assignment
    x.report {var1 = 1; var2 = 2} 
  end

  [#<Benchmark::Tms:0x000000000a2d59d8 @cstime=0.0, @real=8.679999154992402e-06, @cutime=0.0, @label="", @stime=0.0, @total=0.0, @utime=0.0>,
   #<Benchmark::Tms:0x000000000a2d5230 @cstime=0.0, @real=5.723002686863765e-06, @cutime=0.0, @label="", @stime=0.0, @total=0.0, @utime=0.0>]

Just look at the above benchmark output, it justifies that parallel assignments are slower.

  • Magic statement in Ruby:  frozen_string_literal

In ruby, we know that strings are mutable, what does that even mean?

In order to understand this, Let’s take an example:

  hash1 = {"width": 200}
  
  def get_width
    hash1["width"]
  end

In the above code, whenever get_width will be accessed, it will always allocate memory for the string “width”. so, in order to solve this, ruby introduced the concept of “freeze”, now calling the above code like this will solve the problem.

 
  def get_width
    hash2["width".freeze]
  end

But using freeze, everywhere with strings, will makes our code unclean and not DRY.
So, for this Ruby 2.3 introduced MAGIC COMMENT, termed as frozen_string_literal.

When we add this magic comment “frozen_string_literal” to any ruby file, all the strings within that file will be treated as immutable resulting in improving our code performance.

  # frozen_string_literal: true

  hash1 = {"width": 200}
  
  def get_width
    hash1["width"]
  end

For more information like this, stay tuned 🙂