Better performance with Index type `varchar_pattern_ops` operator_class in Rails

Rails has provision to provide order of indexing on a column for better performance of like queries. The varchar_pattern_ops improves the performance of like queries by 4 times i.e 4x.

For example, lets have this like query on name column (that has sequential index.)

Select * from users where name like 'John%'

We might have added a regular Rails index in migration for this name column as

add_index :users, :name - This will generate a sequential index.

The same can be made 4 times faster using btree index by adding xxx_pattern_ops options

add_index :users, :name, order: {name: :varchar_pattern_ops}

Or if you want to write it in migration while creating a new model / table then it can be done as follows: (this is an example from one of my project, so I am not using users and name column example described above. This is just for the sake of understanding.)

    class CreateFilters < ActiveRecord::Migration
      def change
        create_table :filters do |t|
          t.string :name
          t.index :name, order: { operator_class: 'varchar_pattern_ops',
            case_sensitive: false }    
          t.timestamps null: false
        end
      end
    end

This will create index on name column in PostgreSQL as follows

CREATE INDEX index_filters_on_name
  ON filters
  USING btree
  (name COLLATE pg_catalog."default");

The concept of varchar_pattern_ops option in Indexes is given in Rails API doc v.4.0.2 here along with its performance benefits. Refer this screenshot.

As per PostgreSQL documentation here 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