云原生数据仓库AnalyticDB：pg_trgm

功能简介

pg_trgm插件引入了Trigram概念，一个Trigram是从一个字符串中取出的由三个连续字符组成的文本组。在pg_trgm插件中，从文本提取的Trigram长度为3，对于长度小于3的Trigram，将以空格前后缀填充得到最终的Trigram，且默认只能包含两个空格前缀和一个空格后缀。例如：

postgres=# SELECT show_trgm('abc');
        show_trgm
-------------------------
 {"  a"," ab","abc","bc "}
(1 row)

将文本拆解为Trigram后，可以通过对两个字符串之间共享的Trigram计数来度量它们的相似度。这种简单的思想已经成为在很多自然语言中度量词相似度的有效方法。下文介绍pg_trgm插件的几个使用场景。

注意事项

• AnalyticDB PostgreSQL 6.0版且小版本为V6.3.8.9及以上的实例支持pg_trgm插件。

• AnalyticDB PostgreSQL 7.0版且小版本为V7.0.2及以上的实例支持pg_trgm插件。

安装插件

在使用pg_trgm插件进行文本相似度查询之前，请您在云原生数据仓库 AnalyticDB PostgreSQL 版实例插件管理中安装pg_trgm插件。具体操作，请参见安装、升级与卸载插件。

文本相似度匹配查询

在安装pg_trgm插件后，使用Trigram可以对文本的相似度进行匹配查询，并对文本的相似度进行排序。例如，创建测试表并插入测试数据：

CREATE TABLE test_trgm (t text);
INSERT INTO test_trgm values('word'), ('This is a pg_trgm test'), ('word test'), ('w0rd'), ('test word');

您可以通过以下两种方式进行文本相似度匹配查询。

• 方式一：使用pg_trgm插件中的similarity函数及%操作符实现。例如，进行包含关键词word的相似度查询。similarity函数及%操作符的更多信息，请参见附录。

  SELECT t, similarity(t, 'word') AS sml
    FROM test_trgm
    WHERE t % 'word'
    ORDER BY sml DESC, t;

  包含关键词word的结果被输出，并按照相似度进行了排序。查询结果如下：

       t     | sml
  -----------+-----
   word      |   1
   test word | 0.5
   word test | 0.5
  (3 rows)

• 方式二：使用pg_trgm插件中的<->操作符实现。例如在如下查询中，查询得到测试数据与关键词之间的“距离”（相似度的反义），并按照距离从近到远排序。<->操作符的更多信息，请参见附录。

  postgres=# SELECT t, t <-> 'word' AS dist
    FROM test_trgm
    ORDER BY dist LIMIT 10;
             t            | dist
  ------------------------+------
   word                   |    0
   word test              |  0.5
   test word              |  0.5
   w0rd                   | 0.75
   This is a pg_trgm test |    1
  (5 rows)

结合索引加速模糊匹配查询

在未使用pg_trgm插件时，模糊匹配查询（LIKE查询）不能使用索引，查询性能通常较慢。pg_trgm插件提供了GIST和GIN索引操作符类，允许在文本列创建索引用于加速相似度查询。下面通过示例说明如何结合pg_trgm插件和GIN索引加速查询。

首先创建一张测试表，并插入测试数据：

CREATE TABLE test_trgm (t text);
INSERT INTO test_trgm 
SELECT md5(random()::text) FROM generate_series(1,1000000) i; 

通过Explain Analyze执行模糊匹配查询并查看执行计划，可以看到查询使用了Seq Scan全表扫描，在数据量大时查询性能较慢：

postgres=# explain analyze  SELECT * FROM test_trgm WHERE t LIKE '%abcd%';
                                                           QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..259.63 rows=422 width=32) (actual time=137.606..179.674 rows=431 loops=1)
   ->  Seq Scan on test_trgm  (cost=0.00..254.00 rows=141 width=32) (actual time=0.961..136.977 rows=146 loops=1)
         Filter: (t ~~ '%abcd%'::text)
         Rows Removed by Filter: 333458
 Optimizer: Postgres-based planner
 Planning Time: 0.328 ms
   (slice0)    Executor memory: 37K bytes.
   (slice1)    Executor memory: 36K bytes avg x 3 workers, 36K bytes max (seg0).
 Memory used:  128000kB
 Execution Time: 180.533 ms
(10 rows)

使用pg_trgm插件中的gin_trgm_ops索引操作符创建GIN索引。gin_trgm_ops索引操作符的更多信息，请参见附录。

CREATE INDEX trgm_idx ON test_trgm USING GIN (t gin_trgm_ops);

再次进行相同的查询，可以看到查询使用了Bitmap Index Scan，查询性能大幅度提升：

postgres=# explain analyze  SELECT * FROM test_trgm WHERE t LIKE '%abcd%';
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=48.67..882.67 rows=8000 width=32) (actual time=4.353..4.550 rows=431 loops=1)
   ->  Bitmap Heap Scan on test_trgm  (cost=48.67..776.00 rows=2667 width=32) (actual time=1.612..3.091 rows=146 loops=1)
         Recheck Cond: (t ~~ '%abcd%'::text)
         Rows Removed by Index Recheck: 10
         ->  Bitmap Index Scan on trgm_idx  (cost=0.00..48.00 rows=2667 width=0) (actual time=1.536..1.537 rows=163 loops=1)
               Index Cond: (t ~~ '%abcd%'::text)
 Optimizer: Postgres-based planner
 Planning Time: 1.353 ms
   (slice0)    Executor memory: 44K bytes.
   (slice1)    Executor memory: 2438K bytes avg x 3 workers, 2438K bytes max (seg0).
 Memory used:  128000kB
 Execution Time: 5.385 ms
(12 rows)

附录

GUC参数

pg_trgm.similarity_threshold

设置%操作符使用的当前词相似度阈值。该阈值必须位于0~1之间，默认为0.3。

pg_trgm.word_similarity_threshold

设置<%和%>操作符使用的当前词相似度阈值。该阈值必须位于0~1之间，默认为0.6。

函数

操作符

索引操作符