The B-tree type indexes can also index this kind of query:
select * from table where field like 'aaaa%',
To do this, we need to create the index using the OPCLASS varchar_pattern_ops:
pgbench=# create table test_like(id serial,field_text text); CREATE TABLE pgbench=# insert into test_like(field_text) values('orange'),('apple'),('tomato'),('potato'); INSERT 0 4 pgbench=# explain select * from test_like where field_text like 'toma%'; QUERY PLAN -------------------------------------------------------------- Seq Scan on test_like (cost=10000000000.00..10000000025.88 rows=6 width=36) Filter: (field_text ~~ 'toma%'::text) (2 rows) pgbench=# create index test_like_idx on test_like (field_text varchar_pattern_ops); ...
