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
