pg_bigm与pg_trgm的安装、测试、对比

1.介绍

PostgreSQL的模糊查询功能十分强大，可以通过pg_trgm或者pg_bigm插件来支持前模糊、后模糊和前后模糊场景的模糊查询的场景。

主要的区别：

pg_trgm是将对象进行切词，每3个字节为一组进行切割，所以单、双字节的就无法覆盖到。

pg_bigm是将对象进行切词，每2个字节为一组进行切割，可以支持任意字数模糊查询2.安装

pg_bigm的安装
版本：pg_bigm 1.2

$ tar zxf pg_bigm-1.2-20200228.tar.gz
$ cd pg_bigm-1.2-20200228
$ make USE_PGXS = 1  PG_CONFIG = /opt/pgsql-XYZ/bin /pg_config
$ make USE_PGXS = 1  PG_CONFIG = /opt/pgsql-XYZ/bin /pg_config  install

USE_PGXS：构建pg_bigm时必须始终指定USE_PGXS = 1。
PG_CONFIG：指定pg_config的路径（存在于PostgreSQL安装的bin目录中）。如果PATH环境变量包含pg_config的路径，则无需指定PG_CONFIG。

postgres=# create extension pg_bigm;

pg_trgm
pg安装包中自带，只需要创建插件就可以
postgres=# create extension pg_trgm ;

修改postgresql.conf配置文件
shared_preload_libraries ='pg_bigm,pg_trgm'
重启数据库实例
3.测试

pg_bigm
创建表
添加数据：500w条数据
创建索引：创建索引的时间约3分钟，索引大小173MB

pg_trgm
创建表
添加数据：500w条数据
创建索引：创建索引的时间约3分钟，索引大小332MB
注：为了保证数据的一致性，表结构和数据都和pg_bigm创建的表一样

提供一个字符：左模糊查询 （%3）
pg_bigm走索引，pg_trgm不走索引

提供两个字符：左模糊查询 （%3d）
pg_bigm、pg_trgm都走索引，并且总时间差不多

提供多个字符：左模糊查询 （%3dc）

pg_bigm、pg_trgm都走索引，并且总时间差不多

提供1个字符：右模糊查询 （3%）

pg_bigm、pg_trgm都走索引，并且总时间差不多

提供2个字符：右模糊查询 （3d%）

pg_bigm、pg_trgm都走索引，并且总时间差不多

提供多个字符：右模糊查询 （3dd1%）
pg_bigm、pg_trgm都走索引，并且总时间差不多

提供1个字符：左右模糊查询 （%3%）

pg_bigm、pg_trgm都不走索引

提供2个字符：左右模糊查询 （%3d%）

pg_bigm走索引，pg_trgm不走索引，pg_bigm的用时少

提供3个字符：左右模糊查询 （%3dc%）

pg_bigm、pg_trgm都走索引，并且总时间差不多

pg_trgm支持ilike，pg_bigm不支持

pg_trgm、pg_bigm都支持中文

4.总结

pg_trgm

（1）有前缀的模糊查询，例如a％，至少需要提供1个字符。（搜索的是token='a'

（2）有后缀的模糊查询，例如％ab，至少需要提供2个字符。（搜索的是token ='ab'）

（3）前后模糊查询，例如％abcd％，至少需要提供3个字符。（这个使用数组搜索，搜索的是token（s）包含{“a”，“ab”，abc，bcd，“cd”}）

（4）支持ilike

（5）索引占用空间较大

（6）索引列大小限制：238,609,291字节（〜228MB）--官方文档说明的

pg_bigm

（1）有前缀的模糊查询，例如a％，至少需要提供1个字符。（搜索的是token='a'

（2）有后缀的模糊查询，例如％a，至少需要提供1个字符。（搜索的是token ='a'）

（3）前后模糊查询，例如％ab％，至少需要提供2个字符。（这个使用数组搜索，搜索的是token（s）包含{“a”，“ab” }）

（4）不支持ilike

（5）索引占用空间比pg_trgm小 

（6）索引列大小限制：107,374,180字节（〜102MB）

pg_bigm比pg_trgm的优势

（1）可以支持任意字数模糊查询，覆盖的范围更广

（2）索引占用空间比pg_trgm小