【PostgreSQL】 前缀模糊查询级优化

使用B-Tree来加速优化前匹配模糊查询

构造数据

新建一张商品表，插入一千万条数据。

create table goods(id int, name varchar); insert into goods select generate_series(1,10000000),md5(random()::varchar);

未建立索引时查询"123%"的商品名字，执行计划显示耗时大约为575ms：

explain (analyze,verbose,timing,costs,buffers) select * from goods where name like '123%' ; ======================================== Gather (cost=1000.00..136516.59 rows=1000 width=37) (actual time=1.390..572.857 rows=2364 loops=1) Output: id, name Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=83334 -> Parallel Seq Scan on public.goods (cost=0.00..135416.59 rows=417 width=37) (actual time=0.750..528.116 rows=788 loops=3) Output: id, name Filter: ((goods.name)::text ~~ '123%'::text) Rows Removed by Filter: 3332545 Buffers: shared hit=83334 Worker 0: actual time=1.032..511.776 rows=676 loops=1 Buffers: shared hit=24201 Worker 1: actual time=0.145..511.737 rows=755 loops=1 Buffers: shared hit=26101 Planning time: 0.065 ms Execution time: 573.157 ms 优化1，建立索引，lc_collate方式（B-Tree）

lc_collate (string)
报告文本数据排序使用的区域。详见第 23.1 节。该值是在数据库被创建时确定的。

lc_collate (string)
报告文本数据排序使用的区域。详见第 23.1 节。该值是在数据库被创建时确定的。

create index idx_c on goods(name collate "C");

执行计划显示耗时为10ms以内：

explain (analyze,verbose,timing,costs,buffers) select * from goods where name like '123%' collate "C"; 优化2，建立索引，操作符类varchar_pattern_ops方式 create index idx_varchar on goods(name varchar_pattern_ops);

执行计划显示耗时为5ms以内：

explain (analyze,verbose,timing,costs,buffers) select * from goods where name like '123%' collate "C"; ====================================== Bitmap Heap Scan on public.goods (cost=86.60..7681.10 rows=1000 width=37) (actual time=0.740..4.628 rows=2364 loops=1) Output: id, name Filter: ((goods.name)::text ~~ '123%'::text) Heap Blocks: exact=2330 Buffers: shared hit=2351 -> Bitmap Index Scan on idx_varchar (cost=0.00..86.35 rows=2179 width=0) (actual time=0.487..0.487 rows=2364 loops=1) Index Cond: (((goods.name)::text ~>=~ '123'::text) AND ((goods.name)::text ~<~ '124'::text)) Buffers: shared hit=21 Planning time: 0.139 ms Execution time: 4.891 ms PostgreSQL扩展

关键字ILIKE可以替换LIKE， 表示大小写无关，这是一个PostgreSQL扩展。

参考

https://www.postgresql.org/docs/10/indexes-opclass.html
https://github.com/digoal/blog/blob/master/201704/20170426_01.md