引言

• postgresql 查询语句如果查询条件是 like’%xx%’ 那么查询得是全表数据，就算你建了INDEX索引也不管用。
  查询相关资料，发现利用pg_trgm拓展可以有效解决此类问题。

pg_trgm和btree_gin

• pg_trgm模块提供函数和操作符测定字母数字文本基于三元模型匹配的相似性， 还有支持快速搜索相似字符串的索引操作符类

新建pg_trgm拓展

-- 新增扩展
CREATE EXTENSION pg_trgm;
CREATE EXTENSION btree_gin;

查询拓展

• 查询拓展检查是否创建成功

-- 查询扩展
select * from pg_extension;

删除拓展

-- 删除扩展
drop extension pg_trgm;

首先观察未建索引是SQL查询效率

关键字 EXPLAIN ANALYZE 查看sql执行时间

EXPLAIN ANALYZE SELECT
	ST_GeomFromText ( 'POINT(' || lng || ' ' || lat || ')', 4326 ) AS geom,
	comp_id,
ID 
FROM
	sys_org_opemng_bas_comp_zz 
WHERE
	 industry LIKE'%051%' 
	AND business_status = 1 
	AND ( lng IS NOT NULL OR lat IS NOT NULL );

• 发现执行用时1684毫秒

创建索引观察SQL查询效率

CREATE INDEX CONCURRENTLY ix_industry_name ON sys_org_opemng_bas_comp_zz USING gin (industry gin_trgm_ops);

CONCURRENTLY不锁表建立索引

关键字 EXPLAIN ANALYZE 查看sql执行时间

EXPLAIN ANALYZE SELECT
	ST_GeomFromText ( 'POINT(' || lng || ' ' || lat || ')', 4326 ) AS geom,
	comp_id,
ID 
FROM
	sys_org_opemng_bas_comp_zz 
WHERE
	 industry LIKE'%051%' 
	AND business_status = 1 
	AND ( lng IS NOT NULL OR lat IS NOT NULL );

• 发现执行用时1.227毫秒，提升了1000多倍，索引也用上了

参考地址：

• postgresql gin索引使用
• PostgreSQL pg_trgm扩展安装 模糊查询 使用原理
• pgsql查询优化之模糊查询实例详解
• pg_trgm
• PostgreSQL LIKE 查询效率提升实验

题外话

postgrepSql可以在Btree索引上指定操作符

• text_pattern_ops 对应 text
• varchar_pattern_ops 对应 varchar
• bpchar_pattern_ops 对应 char

官方解释

• 它们与默认操作符类的区别是值的比较是严格按照字符进行而不是根据区域相关的排序规则。这使得这些操作符类适合于当一个数据库没有使用标准“C”区域时被使用在涉及模式匹配表达式（LIKE或POSIX正则表达式）的查询中。
• 有些抽象，官方文档
• 例如：

-- 创建索引
CREATE INDEX ix_industry ON sys_org_opemng_bas_comp_zz (industry varchar_pattern_ops);