记录postgresql 模糊查询、索引字段使用函数走索引_sql where 后面加函数能触发索引吗

近段时间，生产环境，数据库质量小组给出了一份sql名单，要优化。
主要问题是有两种:大表全表扫描、禁止在索引列上使用函数。

大表全表扫描，分三种情况。
1、分页查询 limit pageSize offSet pageStart；
2、where条件字段没有加索引
3、where条件字段是模糊查询

第一种情况，暂时还找不到好的办法.不是我们组的sql，所以先忽略；
第二种情况，对应字段加索引就行。pg库索引的默认数据结构是b-tree;
第三种情况，印象中，模糊查询是不能走索引的。查资料后，发现pg库是可以做到的。
使用插件pg_trgm 用来做相似度匹配。对于前缀匹配like ‘x%’ 和后缀匹配like ‘%x’，使用btree索引；对于中缀匹配like '%x%'和正则表达式匹配，用pg_trgm索引

--先声明
create extension pg_trgm;

--对表字段添加索引
CREATE INDEX idx_trgm_user_name ON t_user USING GIN(name gin_trgm_ops);

--很遗憾，找了很久，中文资料没找到多字段建立pg_trgm索引的。
1
2
3
4
5
6
7

索引列上使用函数，不走索引，好像是个常识哦。看来在pg库上，这个常识不管用。pg库是支持函数索引的。
我们有一个场景，表的字段，包含大小写。查询的时候，转成大写来匹配

--mybatis的一个场景
where upper(name) = upper(#{name})
1
2

pg库上创建函数索引：

CREATE INDEX 索引名 ON 表名 (upper(字段名));

--分析执行情况
CREATE INDEX idx_table_name ON table_1 (name);
CREATE INDEX idx_table_name_upper ON table_1 (upper(name));
--可以试试看区别
explain select  * from table_1 where  upper(name) = 'ssdhcb';
1
2
3
4
5
6
7