Original from Paul Ramsey

Hi all, because English is not my native language, that’s why I will write as little as possible =)
here is the case, when we write a query with like and get Seq Scan (full table scan), instead index scan this material can be usefull for you

create table tt as   select s, md5(random()::text) from generate_Series(1,990000) s;
 create index on tt(md5);
 show LC_COLLATE;
 lc_collate
-------------
 en_US.UTF-8

 explain analyze select * from tt where md5 like 'a6b90b58a652b8e1bd01bbe2%';
                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Seq Scan on tt  (cost=0.00..20625.00 rows=4950 width=36) (actual time=132.559..132.559 rows=0 loops=1)
   Filter: (md5 ~~ 'a6b90b58a652b8e1bd01bbe2%'::text)
   Rows Removed by Filter: 990000
 Planning time: 0.203 ms
 Execution time: 132.583 ms

if we create same index but with text_pattern_ops we can get good improvement:

 create index on tt(md5 text_pattern_ops);

 explain analyze select * from tt where md5 like 'a6b90b58a652b8e1bd01bbe2%';
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Index Scan using tt_md5_idx1 on tt  (cost=0.42..8.45 rows=99 width=37) (actual time=0.022..0.022 rows=0 loops=1)
   Index Cond: ((md5 >= 'a6b90b58a652b8e1bd01bbe2'::text) AND (md5 < 'a6b90b58a652b8e1bd01bbe3'::text))
   Filter: (md5 ~~ 'a6b90b58a652b8e1bd01bbe2%'::text)
 Planning time: 0.403 ms
 Execution time: 0.043 ms