Notes posted by douglasresende
RSS feed
1 thank
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)