1 索引类型
Postgresql索引包括B-Tree索引,Hash索引,GIN(Generalized Inverted Index)索引。
B-Tree索引用于操作符 <, <=, =, >=, >。也可用于like pattern 'bar%'。'%bar' 不会使用索引。
Hash索引用于等值查询。使用范围很小,用处不大。
GIN索引一般用于全文索引。
2 创建索引
2.1 建表
create table testtab01(id uuid, note text);
2.2 创建B-Tree索引
create index idx_testtab01_note on testtab01 using btree(note);
下面的查询语句可以使用到这个索引
select * from testtab01 where note like 'search_string';
select * from testtab01 where note like 'search_string%';
下面的查询语句不会使用这个索引
select * from testtab01 where note like '%search_string%'
因此这个索引不能满足我们的要求。
2.3 创建GIN索引
2.3.1 GIN索引用于全文检索,可以匹配'%search_string%'的模式。
2.3.2 GIN索引的工作原理
2.3.2 创建GIN索引
创建GIN索引需要指定操作符。pg_trgm模块提供了字符模糊匹配的操作符。
create extension pg_trgm;
create index idx_testtab01_note on testtab01 using gin(note gin_trgm_ops);
以下查询可以使用索引 idx_testtab01_note
select * from testtab01 where note like '%search_string%';
2.3.3 GIN索引大小