RDS であいまい検索を使用する方法 - ApsaraDB RDS - Alibaba Cloud ドキュメントセンター

pg_bigm は、Alibaba Cloud RDS for PostgreSQL の拡張機能で、全文検索機能を提供します。2-gram の汎用逆引きインデックス (GIN) を作成して、検索プロセスを高速化できます。

前提条件

RDS for PostgreSQL インスタンスは、次の要件を満たす必要があります。

メジャーインスタンスバージョン	マイナーエンジンバージョン
PostgreSQL 17	20250830 以降
PostgreSQL 16	マイナーバージョンの制限なし
PostgreSQL 10 から 15	20230830 以降

重要

この拡張機能は、20230830 より前のマイナーエンジンバージョンでサポートされていました。ただし、拡張機能管理を標準化し、セキュリティを強化するために、RDS for PostgreSQL は、その後のエンジンバージョンの反復でセキュリティリスクのある拡張機能を最適化しました。20230830 より前のマイナーエンジンバージョンを実行するインスタンスでは、この拡張機能を作成できなくなりました。詳細については、「拡張機能の作成に関する制限」をご参照ください。

インスタンスが 20230830 より前のマイナーエンジンバージョンを実行しており、すでにこの拡張機能を使用している場合、その機能は影響を受けません。
この拡張機能を初めて作成する場合、または再作成する場合は、マイナーエンジンバージョンをアップグレードして最新バージョンにする必要があります。

インスタンスパラメーターを設定し、shared_preload_libraries パラメーターの [値] に pg_bigm を追加しました。たとえば、[値] を 'pg_stat_statements,auto_explain,pg_bigm' に設定します。

pg_trgm との比較

pg_trgm は、3-gram モデルを全文検索に使用する RDS for PostgreSQL の別の拡張機能です。pg_bigm 拡張機能は pg_trgm に基づいています。それらの違いは次のとおりです。

機能と属性	pg_trgm	pg_bigm
全文検索のフレーズ一致メソッド	3-gram	2-gram
サポートされているインデックスタイプ	GIN と GIST	GIN
サポートされている全文検索オペレーター	`LIKE`、`ILIKE`、`~`、および `~*`	`LIKE`
非アルファベット言語の全文検索	サポートされていません	サポートされています
1〜2 文字のキーワードの全文検索	遅い	速い
類似検索	サポートされています	サポートされています
インデックス可能な列の最大サイズ	238,609,291 バイト (約 228 MB)	107,374,180 バイト (約 102 MB)

注意

GIN インデックスが作成される列の長さは 107,374,180 バイト (約 102 MB) を超えることはできません。
データベースに保存されているコンテンツが非 ASCII の場合は、データベースのコーデックを UTF8 に変更する必要があります。
説明
現在のデータベースのコーデックを確認するには、次のコマンドを実行します: select pg_encoding_to_char(encoding) from pg_database where datname = current_database();。

基本的な操作

拡張機能の作成

postgres=> create extension pg_bigm;
CREATE EXTENSION

インデックスの作成

postgres=> CREATE TABLE pg_tools (tool text, description text);
CREATE TABLE
postgres=> INSERT INTO pg_tools VALUES ('pg_hint_plan', 'Tool that allows a user to specify an optimizer HINT to PostgreSQL');
INSERT 0 1
postgres=> INSERT INTO pg_tools VALUES ('pg_dbms_stats', 'Tool that allows a user to stabilize planner statistics in PostgreSQL');
INSERT 0 1
postgres=> INSERT INTO pg_tools VALUES ('pg_bigm', 'Tool that provides 2-gram full text search capability in PostgreSQL');
INSERT 0 1
postgres=> INSERT INTO pg_tools VALUES ('pg_trgm', 'Tool that provides 3-gram full text search capability in PostgreSQL');
INSERT 0 1
postgres=> CREATE INDEX pg_tools_idx ON pg_tools USING gin (description gin_bigm_ops);
CREATE INDEX
postgres=> CREATE INDEX pg_tools_multi_idx ON pg_tools USING gin (tool gin_bigm_ops, description gin_bigm_ops) WITH (FASTUPDATE = off);
CREATE INDEX

全文検索の実行

postgres=> SELECT * FROM pg_tools WHERE description LIKE '%search%';
  tool   |                             description
---------+---------------------------------------------------------------------
 pg_bigm | Tool that provides 2-gram full text search capability in PostgreSQL
 pg_trgm | Tool that provides 3-gram full text search capability in PostgreSQL
(2 rows)

=% オペレーターを使用した類似検索の実行

postgres=> SET pg_bigm.similarity_limit TO 0.2;
SET
postgres=> SELECT tool FROM pg_tools WHERE tool =% 'bigm';
  tool
---------
 pg_bigm
 pg_trgm
(2 rows)

拡張機能のアンインストール

postgres=> drop extension pg_bigm;
DROP EXTENSION

一般的な拡張機能関数

likequery 関数

関数: LIKE キーワードで認識できる文字列を生成します。
パラメーター: string 型の単一のパラメーター。
戻り値: LIKE キーワードで使用できる検索文字列。
仕組み:
- キーワードの前後に % 記号を追加します。
- \ を使用して % 文字を自動的にエスケープします。

例:

postgres=> SELECT likequery('pg_bigm has improved the full text search performance by 200%');
                             likequery
-------------------------------------------------------------------
 %pg\_bigm has improved the full text search performance by 200\%%
(1 row)

postgres=> SELECT * FROM pg_tools WHERE description LIKE likequery('search');
  tool   |                             description
---------+---------------------------------------------------------------------
 pg_bigm | Tool that provides 2-gram full text search capability in PostgreSQL
 pg_trgm | Tool that provides 3-gram full text search capability in PostgreSQL
(2 rows)

show_bigm 関数
- 関数: 指定された文字列のすべての 2-gram 要素のコレクションを返します。
- パラメーター: string 型の単一のパラメーター。
- 戻り値: すべての 2-gram 要素を含む配列。
- 仕組み:
  - 文字列の前後にスペース文字を追加します。
  - すべての 2-gram 部分文字列を計算します。
- 例:
```
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 row)
```
bigm_similarity 関数
- 関数: 2 つの文字列間の類似度を計算します。
- パラメーター: string 型の 2 つのパラメーター。
- 戻り値: 類似度を示す浮動小数点数。
- 仕組み:
  - 2 つの文字列間の共通の 2-gram 要素をカウントします。
  - 類似度の範囲は 0 から 1 です。値 0 は 2 つの文字列が完全に異なることを示します。値 1 は 2 つの文字列が同一であることを示します。
  説明
  - 2-gram が計算されるときに文字列の前後にスペースが追加されるため、ABC と B の類似度は 0 で、ABC と A の類似度は 0.25 です。
  - `bigm_similarity` 関数は大文字と小文字を区別します。たとえば、ABC と abc の類似度は 0 です。
- 例:
```
postgres=> SELECT bigm_similarity('full text search', 'text similarity search');
 bigm_similarity
-----------------
       0.5714286
(1 row)

postgres=> SELECT bigm_similarity('ABC', 'A');
 bigm_similarity
-----------------
            0.25
(1 row)

postgres=> SELECT bigm_similarity('ABC', 'B');
 bigm_similarity
-----------------
               0
(1 row)

postgres=> SELECT bigm_similarity('ABC', 'abc');
 bigm_similarity
-----------------
               0
(1 row)
```
pg_gin_pending_stats 関数
- 関数: GIN インデックスの保留中リスト内のページ数とタプル数を返します。
- パラメーター: GIN インデックスの名前または OID。
- 戻り値: 保留中リスト内のページ数とタプル数。
  説明
  GIN インデックスの作成時に FASTUPDATE パラメーターが False に設定されている場合、GIN インデックスには保留中リストがありません。この場合、関数は 0 を返します。
- 例:
```
postgres=> SELECT * FROM pg_gin_pending_stats('pg_tools_idx');
 pages | tuples
-------+--------
     0 |      0
(1 row)
```

拡張機能の動作の制御

pg_bigm.last_update
この拡張機能の最終更新日。これは読み取り専用パラメーターであり、変更できません。
例:
```
SHOW pg_bigm.last_update;
```

pg_bigm.enable_recheck

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

説明

正しい結果を保証するために、デフォルト値 (ON) を維持することをお勧めします。

例:

postgres=> CREATE TABLE tbl (doc text);
CREATE TABLE
postgres=> INSERT INTO tbl VALUES('He is awaiting trial');
INSERT 0 1
postgres=> INSERT INTO tbl VALUES('It was a trivial mistake');
INSERT 0 1
postgres=> CREATE INDEX tbl_idx ON tbl USING gin (doc gin_bigm_ops);
CREATE INDEX
postgres=> SET enable_seqscan TO off;
SET
postgres=> EXPLAIN ANALYZE SELECT * FROM tbl WHERE doc LIKE likequery('trial');
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tbl  (cost=20.00..24.01 rows=1 width=32) (actual time=0.020..0.021 rows=1 loops=1)
   Recheck Cond: (doc ~~ '%trial%'::text)
   Rows Removed by Index Recheck: 1
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on tbl_idx  (cost=0.00..20.00 rows=1 width=0) (actual time=0.013..0.013 rows=2 loops=1)
         Index Cond: (doc ~~ '%trial%'::text)
 Planning Time: 0.117 ms
 Execution Time: 0.043 ms
(8 rows)

postgres=>
postgres=> SELECT * FROM tbl WHERE doc LIKE likequery('trial');
         doc
----------------------
 He is awaiting trial
(1 row)

postgres=> SET pg_bigm.enable_recheck = off;
SET
postgres=> SELECT * FROM tbl WHERE doc LIKE likequery('trial');
           doc
--------------------------
 He is awaiting trial
 It was a trivial mistake
(2 rows)

pg_bigm.gin_key_limit
全文検索に使用される 2-gram 要素の最大数を制限します。デフォルト値は 0 で、すべての 2-gram 要素が使用されることを意味します。
説明
すべての 2-gram 要素を使用するとパフォーマンスが低下することがわかった場合は、このパラメーターを調整して 2-gram 要素の数を制限し、パフォーマンスを向上させることができます。
pg_bigm.similarity_limit
類似度のしきい値を設定します。このしきい値を超える類似度のタプルが類似検索によって返されます。