AnalyticDB:pg_trgm

概要

pg_trgm拡張は、トリグラムの概念を導入する。 トライグラムは、文字列から取得された3つの連続した文字で構成されます。 pg_trgmは、3文字からなるトライグラムを抽出します。 3文字未満のトライグラムには、最大2つのスペースのプレフィックスまたは1つのスペースのサフィックスが含まれます。 例：

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

文字列が共有するトライグラムの数を数えることで、2つの文字列間の類似性を測定できます。 この考え方は、多くの自然言語の単語の類似性を測定するのに非常に効果的です。 このトピックでは、pg_trgm拡張機能の使用方法について説明します。

使用上の注意

• AnalyticDB for PostgreSQL V6.0 V6.3.8.9以降のインスタンスは、pg_trgm拡張機能をサポートしています。

• AnalyticDB for PostgreSQL V7.0 V7.0.2以降のインスタンスは、pg_trgm拡張機能をサポートしています。

Install the extension

pg_trgm拡張機能を使用してAnalyticDB for PostgreSQLインスタンスでテキスト類似性検索を実行する前に、インスタンスの [拡張機能] ページにpg_trgm拡張機能をインストールします。 詳細については、「拡張機能のインストール、更新、およびアンインストール」をご参照ください。

トライグラムマッチングを使用してテキスト類似性検索を実行

pg_trgm拡張機能をインストールした後、トライグラムマッチングを使用してテキストの類似性を測定し、検索結果をベストマッチからワーストマッチの順に並べ替えることができます。 たとえば、テストテーブルを作成し、テーブルにデータを挿入します。

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

次のいずれかの方法を使用して、トライグラムマッチングに基づいてテキストの類似性検索を実行できます。

• 方法1: pg_trgm拡張でsimilarity() 関数と % 演算子を使用します。 たとえば、列tの値と文字列単語の間の類似性を照会します。 similarity() 関数と % 演算子の詳細については、このトピックの「付録」を参照してください。

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

  単語を含む列の値は、類似度の高い順に表示されます。

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

• 方法2: pg_trgm拡張で <-> 演算子を使用します。 たとえば、列tの値と文字列単語の間の距離を照会します。 距離は類似性の反対です。 列の値は、最も近いものから最も遠いものまでの距離の順に表示されます。 <-> 演算子の詳細については、このトピックの「付録」を参照してください。

  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演算子を使用してファジー検索を実行できます。 ただし、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ステートメントを使用して、テーブルのファジー検索を実行します。 実行計画は、シーケンシャルスキャンがテーブル全体で実行されることを示しています。 これにより、大量のデータが含まれるシナリオでは、クエリのパフォーマンスが低下します。

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);

EXPLAIN ANALYZEステートメントを使用して、テーブルのファジー検索を実行します。 実行プランは、ビットマップ・インデックス・スキャンが実行されることを示す。 クエリのパフォーマンスが大幅に向上しました。

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)

付録

Grand Unified Configuration (GUC) パラメーター

pg_trgm.similarity_threshold

% 演算子によって使用される現在の類似度のしきい値を指定します。 しきい値は0と1の間でなければなりません。 デフォルト値: 0.3

pg_trgm.word_similarity_threshold

<% および %> 演算子によって使用される現在の単語類似度のしきい値を指定します。 しきい値は0と1の間でなければなりません。 デフォルト値: 0.6

関数

演算子

インデックス演算子