Postgresql优化like '%%'模糊查询

1.优化模糊匹配

优化模糊查询 like '%content%'此类查询。需要安装插件pg_trgm，此插件只能匹配3个字符及以上，如 like '%aaa%'会走索引，而 like '%aa%'则不会走索引

切换用户 su -postgres
使用psql
切换数据库 \c testDB
创建插件create extension pg_trgm;

su - postgres
Last login: Thu Nov 26 15:43:38 CST 2020 on pts/0
[postgres@bbd01 ~]$ psql
psql (12.4)
Type "help" for help.

postgres=# create extension pg_trgm;
CREATE EXTENSION
postgres=# \c testDB
You are now connected to database "testDB" as user "postgres".
testDB=# create extension pg_trgm;
CREATE EXTENSION

创建索引

create index idx_qyxx_basic_company_name on qyxx_basic using gin(company_name gin_trgm_ops);

2. Docker中给postgresql安装pg_trgm插件

进入容器

  / $ psql -U 用户 -W 数据库
  Password: 
  psql (12.1)
  Type "help" for help.

  testDB=# create extension pg_trgm;
  CREATE EXTENSION

3. 使用pg_bigm插件任意模糊匹配

此插件能匹配2个及以上字符，如 like '%aa%'会走索引，like '%a%'不会走索引

下载对应版本的插件如pg_bigm-1.2.20200228-1.pg12.el7.centos.x86_64.rpm
安装插件rpm -ivh pg_bigm-1.2.20200228-1.pg12.el7.centos.x86_64.rpm
登录pg账户安装插件

su -postgres
[postgres@bbd01 ~]$ psql
psql (12.4)
Type "help" for help.

postgres=# \c testDB
You are now connected to database "testDB" as user "postgres".
testDB=# create extension pg_bigm;
CREATE EXTENSION
testDB=# \dx
                  List of installed extensions
 Name  | Version |  Schema  |              Description              
---------+---------+------------+-------------------------------------------------------------------
 pg_bigm | 1.2   | public   | text similarity measurement and index searching based on bigrams
 pg_trgm | 1.4   | public   | text similarity measurement and index searching based on trigrams
 plpgsql | 1.0   | pg_catalog | PL/pgSQL procedural language
(3 rows)

创建索引

create index if not exists idx_qyxx_basic_company_name_bgim on qyxx_basic using gin(company_name gin_bigm_ops);