pg_trgm

pg_trgm简介

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

pg_trgm安装

手动安装

访问MogDB下载页面，下载所需版本的pg_trgm插件。

解压插件包，例如：

tar -xzvf pg_trgm-1.0-x.x.x-01-CentOS-x86_64.tar.gz

进入插件所在目录下，执行make install命令。
```
cd pg_trgm/
make install
```

PTK安装

参见插件安装。

pg_trgm使用

SQL端执行：

-- 创建pg_trgm拓展
MogDB=# create extension pg_trgm;
CREATE EXTENSION

创建一张表插入一百万条数据。

MogDB=# create table trgm_test(id int, name varchar);
CREATE TABLE
MogDB=# insert into trgm_test select generate_series(1,1000000),md5(random()::name);
INSERT 0 1000000

不使用pg_trgm进行查询。

MogDB=# explain analyze select * from trgm_test where name like '%69089%';
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Seq Scan on trgm_test  (cost=0.00..21404.74 rows=1664 width=36) (actual time=0.614..294.552 rows=28 loops=1)
   Filter: ((name)::text ~~ '%69089%'::text)
   Rows Removed by Filter: 999972
 Total runtime: 294.632 ms
(4 rows)

MogDB=# select * from trgm_test where name like '%69089%';
   id   |               name
--------+----------------------------------
   1963 | 41548aaf690895ab526701ec069c3484
  15325 | 8ac653eb69089f810490f541a108fd5e
 138354 | 9f4d1104450e5274b577690897d39412
 147373 | 5207db333292dfabf7d18e4d06908957
 156414 | bc460fa269089ed4c54dc7b7b8153026
 167103 | e0ba78ae5089ab69089d0de4161121a0
 172947 | e069089ba545a2206f168efc2f27c81e
 195044 | 846908962af1bf6d49891ee73db77b98
 312372 | 5a690898a7d8872888336b861050399a
 330669 | 9f3e1a37cb66690896ee0526bb565ffe
 368276 | 3870ed690891a6f6e674bdbfbfe22d64
 436808 | 804baa3798566faa4826978297690893
 490045 | 930c7bfa38d03b5180a14eb12669089a
 536880 | d2afa55e75402accce69089c8ff0389a
 576956 | 690896fdb2625efe4d4fe41566b2aefa
 614211 | 12d7f13b40690892cfeb05334f9b2f7a
 635148 | 364434da9386db3346908901118ff0cc
 640641 | 1cd90264e6908972f0324f0a90b8542e
 641060 | f25e1ba7fdaec19d969089086036ddaa
 666530 | c91ec497920c63690893c3f1cded2fa0
 670240 | 2eac8a09bbef1c82a48f69089b77f377
 762091 | 6269089b5d8328290cd1e4e2879d8606
 795320 | c996b43bb31c5469089795948e733e81
 841274 | 7c47280e9e69089943a8aa493644e6ef
 862375 | ef438b9242f3469089a577c7930190d6
 957079 | df1fb9b48d69089ee752e410a3d963f4
 984230 | cd5fd4b8980cc4690892b05c072128c2
 999687 | 2b02c6908908159d8a1983dd7768dfd5
(28 rows)

创建索引。

MogDB=# create index idx_trgm_test_1 on trgm_test using gin(name gin_trgm_ops);
CREATE INDEX

MogDB=# explain analyze select * from trgm_test where name like '%ad44%';
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on trgm_test  (cost=40.77..409.30 rows=100 width=37) (actual time=0.935..1.781 rows=458 loops=1)
   Recheck Cond: ((name)::text ~~ '%ad44%'::text)
   Rows Removed by Index Recheck: 33
   Heap Blocks: exact=474
   ->  Bitmap Index Scan on idx_trgm_test_1  (cost=0.00..40.75 rows=100 width=0) (actual time=0.852..0.852 rows=491 loops=1)
         Index Cond: ((name)::text ~~ '%ad44%'::text)
 Total runtime: 2.426 ms
(7 rows)

MogDB=# select * from trgm_test where name like '%305696%';
   id   |               name
--------+----------------------------------
 246738 | c625a29b2fba160cba305696e1a1d851
(1 row)