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 for 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。

函數

操作符

索引操作符