postgresql gin索引使用

    由于属于老项目，postgresql使用版本9.6，主要解决‘%name%"查询无法使用索引问题。pg_trgm模块提供函数和操作符测定字母，数字，文本基于三元模型匹配的相似性， 还有支持快速搜索相似字符串的索引操作符类。

1. 增加pg_trgm拓展

CREATE EXTENSION pg_trgm;

2. 采用pg_trgm 建立gin索引

CREATE INDEX trgm_idx_users_username ON users USING gin (username gin_trgm_ops);

 

3. 第二步采用gin_trgm_ops建立索引完成，但对于联合索引，gin_trgm_ops将合并成一个字符串查询， 例如

CREATE INDEX trgm_idx_users_username ON users USING gin ((user_name|| ' ' || last_name) gin_trgm_ops);

 

4. 有时候需要建立联合索引，但同时不同的列不愿意合成一个字段，这个时候可以gin建立联合索引， 先修改默认pg_opclass

UPDATE pg_opclass SET opcdefault = TRUE WHERE opcname = 'gin_trgm_ops';

 

5. 建立gin索引, 示例使用"UPPER" 建立索引，主要是针对django 1.11不支持ilike搜索，全转为大写之后再建立索引，之后版本可取消“UPPER”

CREATE INDEX index_name ON table_name USING gin ( UPPER ( first_name ), UPPER ( last_name ), UPPER ( email ), UPPER(username));

 

 6. 注意

     gin联合索引占用空间比btree大，索引数量与列数有关，执行过程中会锁表，为不影响插入，修改等操作，可以使用CONCURRENTLY不锁表建立索引。

CREATE INDEX CONCURRENTLY index_name ON table_name USING gin ( UPPER ( first_name ), UPPER ( last_name ), UPPER ( email ), UPPER(username));

 

7. 使用一段时间后，发现gin索引存在无法命中的情况

    1.  搜索字段少于3个字符时，不会命中索引，这是gin自身机制导致。

    2. 当搜索字段过长时，比如email检索，可能也不会命中索引，造成原因暂时未知。

 

 

本文参考

　　gin 索引建立         :  https://razeencheng.com/post/pg-like-index-optimize 

　　安全操作postgresql: https://www.braintreepayments.com/blog/safe-operations-for-high-volume-postgresql/