pg中btree-like左匹配索引

使用方法

select * from public.t1_like where f2  like  '111%’;

select * from public.t1_like where f2  COLLATE "C" =  'AAA';

索引本身是以排序为基础的，而每个索引只能支持一种排序规则，这个排序规则由Collate指定。

例如：在zh_CN的区域下中文按拼音排序，其它区域按字符编码排序。

因此，如果用一种Collate进行排序建立索引，用另一种Collate查询，索引会失效。

create table public.t1_like(f1 int not null,f2 varchar(20),primary key(f1）)；

create index t1_like_f2_idx on public.t1_like(f2 collate "C");

insert into t1_like values(1,'静安寺法案山旮旯十公里');
insert into t1_like values(2,'阿富汗老公恶搞红色代表公开');
insert into t1_like values(3,'拿到绿卡感觉浪费机会了对方你看');
insert into t1_like values(4,'法艰苦拉萨宫颈癌过来看');
insert into t1_like values(5,'与哦人与荣誉颈癌过来看');
insert into t1_like values(6,'嘿嘿嘿u儿童我i二hi杀毒软');
insert into t1_like values(7,'经理反馈给joy愉快');
insert into t1_like values(8,'凤凰更深刻的结构包括宝宝辛苦');
insert into t1_like values(9,'凤凰时间管理的风景和');
insert into t1_like values(10,'发货了十几个垃圾地方两个世界');

postgres=# explain analyze select * from public.t1_like where f2 like '发货了%';
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t1_like  (cost=4.19..12.65 rows=4 width=62) (actual time=0.017..0.018 rows=1 loops=1)
   Filter: ((f2)::text ~~ '发货了%'::text)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on t1_like_f2_idx  (cost=0.00..4.19 rows=4 width=0) (actual time=0.009..0.009 rows=1 loops=1)
         Index Cond: (((f2)::text >= '发货了'::text) AND ((f2)::text < '发货亇'::text))
 Planning Time: 0.143 ms
 Execution Time: 0.046 ms
(7 行记录)

postgres=# drop index t1_like_f2_idx;
DROP INDEX

postgres=# explain analyze select * from public.t1_like where f2 like '发货了%';
                                            QUERY PLAN                                             
---------------------------------------------------------------------------------------------------
 Seq Scan on t1_like  (cost=0.00..21.25 rows=4 width=62) (actual time=0.009..0.009 rows=1 loops=1)
   Filter: ((f2)::text ~~ '发货了%'::text)
   Rows Removed by Filter: 9
 Planning Time: 0.101 ms
 Execution Time: 0.020 ms
(5 行记录)
 

至于为什么顺序扫描比位图索引扫描要快，应该是数据量比较少引起的。