Notes posted by douglasresende

Adding index with other operator classes (PostgreSQL)

To perform on search by LIKE:

SQL Query:

SELECT users.* FROM users WHERE name LIKE 'Doug%';

Explain:

# Without index
Seq Scan on users  (cost=0.00..82183.32 rows=98524 width=418)
  Filter: ((name)::text ~~ 'Doug%'::text)

Adding index with operator class ‘varchar_pattern_ops’

add_index :users, :name, order: {name: :varchar_pattern_ops}
execute 'ANALYZE users;'

New Explain:

# With index
Bitmap Heap Scan on users  (cost=2444.46..56020.97 rows=98524 width=418)
  Filter: ((name)::text ~~ 'Doug%'::text)
  ->  Bitmap Index Scan on index_users_on_name  (cost=0.00..2419.83 rows=75940 width=0)
        Index Cond: ((name)::text ~>=~ 'Doug'::text)