目录 引言pg_trgm和btree_gin新建pg_trgm拓展查询拓展删除拓展首先观察未建索引是SQL查询效率关键字 EXPLAIN ANALYZE 查看sql执行时间 创建索引观察SQL查询效率CONCURRENTLY不锁表建立索引关键字 EXPLAIN ANALYZE 查看sql执行时间 参考地址： 题外话postgrepSql可以在Btree索引上指定操作符官方解释

引言 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_trgmPostgreSQL LIKE 查询效率提升实验 题外话 postgrepSql可以在Btree索引上指定操作符 text_pattern_ops 对应 textvarchar_pattern_ops 对应 varcharbpchar_pattern_ops 对应 char 官方解释 它们与默认操作符类的区别是值的比较是严格按照字符进行而不是根据区域相关的排序规则。这使得这些操作符类适合于当一个数据库没有使用标准“C”区域时被使用在涉及模式匹配表达式（LIKE或POSIX正则表达式）的查询中。有些抽象，官方文档例如： -- 创建索引 CREATE INDEX ix_industry ON sys_org_opemng_bas_comp_zz (industry varchar_pattern_ops);

---

1.本站遵循行业规范，任何转载的稿件都会明确标注作者和来源；2.本站的原创文章，会注明原创字样,如未注明都非原创，如有侵权请联系删除！；3.作者投稿可能会经我们编辑修改或补充；4.本站不提供任何储存功能只提供收集或者投稿人的网盘链接。