pg_bigm拡張を使用してファジー一致ベースのクエリを実行する - ApsaraDB RDS

ApsaraDB RDS for PostgreSQLによって提供されるpg_bigm拡張機能は、全文検索をサポートしています。この拡張機能を使用して、2グラムの一般化逆インデックス (GIN) インデックスを作成できます。これらのインデックスは、フルテキスト検索クエリの迅速化に役立ちます。

前提条件

RDSインスタンスはPostgreSQL 10以降を実行します。
RDSインスタンスは、20230830以降のマイナーエンジンバージョンを実行します。
重要
拡張機能は、20230830より前のマイナーエンジンバージョンでサポートされています。 ApsaraDB RDS for PostgreSQLの拡張機能管理を標準化し、拡張機能のセキュリティを強化するために、ApsaraDB RDSはマイナーエンジンバージョンのイテレーションで脆弱な拡張機能を最適化する予定です。その結果、以前のマイナーエンジンバージョンを実行するRDSインスタンスに対して、一部の拡張機能を作成できなくなりました。詳細については、「 [製品の変更 /機能の変更] ApsaraDB RDS For PostgreSQLインスタンスの拡張機能作成の制限」をご参照ください。
- 20230830より前のマイナーエンジンバージョンを実行するRDSインスタンスの拡張機能を作成した場合、その拡張機能は影響を受けません。
- RDSインスタンスの拡張機能を初めて作成する場合、または拡張機能を再作成する場合は、RDSインスタンスのマイナーエンジンバージョンを最新バージョンに更新する必要があります。詳細については、「マイナーエンジンバージョンの更新」をご参照ください。
pg_bigmは、RDSインスタンスのshared_preload_librariesパラメーターの値に追加されます。
ApsaraDB RDSコンソールで、またはAPI操作を呼び出して、shared_preload_librariesパラメーターの値にpg_bigmを追加できます。詳細については、「ApsaraDB RDS For PostgreSQLインスタンスのパラメーターの変更」をご参照ください。

pg_bigmエクステンションとpg_trgmエクステンションの比較

pg_trgm拡張機能は、ApsaraDB RDS for PostgreSQLによっても提供されます。 pg_trgm拡張機能は、3グラムモデルを使用してフルテキスト検索を実行します。 pg_bigm拡張は、pg_trgm拡張に基づいて開発されています。次の表に、2つの拡張機能の違いを示します。

機能性	pg_trgm	pg_bigm
フレーズマッチングモデル	3グラム	2グラム
インデックスタイプ	ジンとギスト	ジン
演算子	`LIKE` \| `ILIKE` \| `~` \| `~ *`	`好き`
アルファベット以外のフルテキスト検索	非対応	対応
1 ~ 2文字のキーワードを使用したフルテキスト検索	低	速い
類似検索	対応	対応
インデックス付き列の最大長	238,609,291バイト (約228 MB)	107,374,180バイト (約102 MB)

注意事項

GINインデックスを作成する列の長さは、107,374,180バイト (約102 MB) を超えることはできません。
RDSインスタンスのデータがASCII形式でエンコードされていない場合は、エンコード形式をUTF8に変更することを推奨します。
説明 select pg_encoding_to_char(encoding) from pg_database where datname = current_database(); コマンドを実行して、RDSインスタンスのエンコード形式を照会できます。

基本操作

pg_bigm拡張を有効にします。

postgres=> create extension pg_bigm;
拡張の作成

GINインデックスを作成します。

postgres=> CREATE TABLE pg_tools (ツールテキスト、説明テキスト);
テーブルの作成
postgres=> INSERT INTO pg_tools VALUES ('pg_hint_plan '、' ユーザーがオプティマイザHINTをPostgreSQL' に指定できるようにするツール);
挿入0 1
postgres=> INSERT INTO pg_tools VALUES ('pg_dbms_stats '、' ユーザーがPostgreSQLでプランナー統計を安定させることを可能にするツール ');
挿入0 1
postgres=> INSERT INTO pg_tools VALUES ('pg_bigm' 、'PostgreSQLで2グラムの全文検索機能を提供するツール ');
挿入0 1
postgres=> INSERT INTO pg_tools VALUES ('pg_trgm' 、'PostgreSQLで3グラムの全文検索機能を提供するツール ');
挿入0 1
postgres=> CREATE INDEX pg_tools_idx ON pg_tools USING gin (説明gin_bigm_ops);
インデックスの作成
postgres=> CREATE INDEX pg_tools_multi_idx ON pg_tools USING gin (tool gin_bigm_ops、説明gin_bigm_ops) WITH (FASTUPDATE = off);
インデックスの作成

フルテキスト検索クエリを実行します。

postgres=> SELECT * FROM pg_tools WHERE説明LIKE '% search %';
  ツール | 説明
---------- + ---------------------------------------------------------------------
 pg_bigm | PostgreSQLで2グラムのフルテキスト検索機能を提供するツール
 pg_trgm | PostgreSQLで3グラムのフルテキスト検索機能を提供するツール
(2行)

=% 演算子を使用して、類似検索クエリを実行します。

postgres=> SET pg_bigm.similarity_limit TO 0.2;
セット
postgres=> SELECT tool FROM pg_tools WHERE tool =% 'bigm';
  ツール
---------
 pg_bigm
 pg_trgm
(2行)

pg_bigm拡張機能を無効にします。

postgres=> drop extension pg_bigm;
ドロップ延長

基本機能

likequery

目的: この関数は、LIKEキーワードに基づいて識別できる文字列を生成するために使用されます。
リクエストパラメーター: この関数にはリクエストパラメーターが1つ含まれます。このパラメーターのデータ型はSTRINGです。
戻り値: この関数は、LIKEキーワードに基づいて識別できる文字列を返します。
実装:
- キーワードの前後にパーセント記号 (%) を追加します。
- パーセント記号 (%) をエスケープするには、後方スラッシュ (\) を使用します。

例:

postgres=> SELECT likequery('pg_bigmは 'を200% することで全文検索パフォーマンスを向上させました);
                             likequery
-------------------------------------------------------------------
 % pg\_bigmにより、フルテキスト検索のパフォーマンスが向上し200た \%%
(1行)

postgres=> SELECT * FROM pg_toolsここでの説明のようにlikequery('search');
  ツール | 説明
---------- + ---------------------------------------------------------------------
 pg_bigm | PostgreSQLで2グラムのフルテキスト検索機能を提供するツール
 pg_trgm | PostgreSQLで3グラムのフルテキスト検索機能を提供するツール
(2行)

show_bigm
- 目的: この関数は、文字列のすべての2グラム要素を取得するために使用されます。
- リクエストパラメーター: この関数にはリクエストパラメーターが1つ含まれます。このパラメーターのデータ型はSTRINGです。
- 戻り値: このパラメーターは、文字列のすべての2グラム要素で構成される配列を返します。
- 実装:
  - 文字列の前後にスペースを追加します。
  - 文字列内のすべての2グラム要素を識別します。
- 例:
```
postgres=> SELECT show_bigm('full text search');
                            show_bigm
------------------------------------------------------------------
 {"f" 、"s" 、"t" 、ar、ch、ea、ex、fu、"h" 、"l" 、ll、rc、se、"t" 、te、ul、xt}
(1行) 
```
bigm_similarity
- 目的: この関数は、2つの文字列間の類似性を取得するために使用されます。
- リクエストパラメーター: この関数には2つのリクエストパラメーターが含まれます。これらのパラメーターのデータ型はSTRINGです。
- 戻り値: この関数は、2つの文字列間の類似性を示す浮動小数点数を返します。
- 実装:
  - 2つの文字列の両方に含まれる2グラム要素を特定します。
  - 戻り値は0 ~ 1の範囲内です。値0は、2つの文字列が異なることを示します。値1は、2つの文字列が同じであることを示します。
  説明
  - この関数は、各文字列の前後にスペースを追加します。したがって、ABC文字列とB文字列との間の類似度は0であり、ABC文字列とA文字列との間の類似度は0.25である。
  - この関数は、大文字と小文字を区別します。たとえば、この関数は、ABC文字列とabc文字列の類似度が0であると判断します。
- 例:
```
postgres=> SELECT bigm_similarity('full text search', 'text similarity search');
 bigm_similarity
-----------------
       0.5714286
(1行)

postgres=> SELECT bigm_similarity('ABC '、'A');
 bigm_similarity
-----------------
            0.25
(1行)

postgres=> SELECT bigm_similarity('ABC '、'B');
 bigm_similarity
-----------------
               0
(1行)

postgres=> SELECT bigm_similarity('ABC ', 'ABC');
 bigm_similarity
-----------------
               0
(1行) 
```
pg_gin_pending_stats
- 目的: この関数は、GINインデックスの保留リスト内のページ数とタプル数を取得するために使用されます。
- リクエストパラメーター: この関数には1つのパラメーターが含まれます。このパラメーターには、GINインデックスの名前またはOIDを指定します。
- 戻り値: この関数は、GINインデックスの保留中のリスト内のページ数とタプル数の2つの値を返します。
  説明 GINインデックスのFASTUPDATEパラメーターをFalseに設定した場合、GINインデックスには保留リストがありません。この場合、この関数は0と0の2つの値を返します。
- 例:
```
postgres=> SELECT * FROM pg_gin_pending_stats('pg_tools_idx ');
 ページ | タプル
------ ---------
     0 | 0
(1行) 
```

行動制御

pg_bigm.last_update
このパラメーターは、pg_bigm拡張子に対して最新の更新が行われた日付を示します。このパラメーターのみを表示できます。このパラメーターの値は変更できません。
例：
```
pg_bigm.last_updateを表示します。
```

pg_bigm.enable_recheck

再チェックを実行するかどうかを指定します。

説明デフォルト値はONのままにすることを推奨します。これにより、正確なクエリ結果を取得できます。

例：

postgres=> CREATE TABLE tbl (docテキスト);
テーブルの作成
postgres=> INSERT INTO tbl VALUES (「彼はトライアルを待っています」);
挿入0 1
postgres=> INSERT INTO tbl VALUES('それは些細な間違いでした');
挿入0 1
postgres=> CREATE INDEX tbl_idx ON tblジンを使用して (doc gin_bigm_ops);
インデックスの作成
postgres=> SET enable_seqscanオフ;
セット
postgres=> EXPLAIN ANALYZE SELECT * FROM tblどこdoc LIKE likequery('trial');
                                                   クエリ計画
-----------------------------------------------------------------------------------------------------------------
 tblのビットマップヒープスキャン (コスト=20.00 .. 24.01行=1幅=32) (実時間=0.020 .. 0.021行=1ループ=1)
   再確認Cond :( doc ~~ '% trial %'::text)
   インデックスによって削除された行Recheck: 1
   ヒープブロック: exact=1
   -> tbl_idxのビットマップインデックススキャン (コスト=0.00 .. 20.00行=1幅=0) (実際の時間=0.013 .. 0.013行=2ループ=1)
         インデックスCond: (doc ~~ '% trial %'::text)
 計画時間: 0.117 ms
 実行時間: 0.043 ms
(8行)

postgres=>
postgres=> SELECT * FROM tbl WHERE doc LIKE likequery('trial');
         doc
----------------------
 彼は裁判を待っている
(1行)

postgres=> SET pg_bigm.enable_recheck = off;
セット
postgres=> SELECT * FROM tbl WHERE doc LIKE likequery('trial');
           doc
--------------------------
 彼は裁判を待っている
 それは些細な間違いだった
(2行)

pg_bigm.gin_key_limit
このパラメーターには、フルテキスト検索クエリの実行に使用できる2グラムの要素の最大数を指定します。デフォルト値は0で、2グラムの要素がすべて使用されていることを示します。
説明すべての2グラム要素の使用によりクエリのパフォーマンスが低下した場合、このパラメーターの値を減らすことができます。
pg_bigm.similarity_limit
このパラメータは、類似度のしきい値を指定します。類似度が指定された閾値を超えるタプルは、類似度検索結果として返される。