pg_bigm 拡張 - PolarDB - Alibaba Cloud ドキュメントセンター

前提条件

pg_bigm 拡張は、次の PolarDB for PostgreSQL バージョンでサポートされています。

PostgreSQL 14 (エンジンリビジョン 14.5.2.0 以降)
PostgreSQL 11 (エンジンリビジョン 1.1.28 以降)

説明

お使いの PolarDB for PostgreSQL クラスターのエンジンのリビジョンは、次のステートメントを実行して確認できます。

PostgreSQL 14
```
SELECT version();
```
PostgreSQL 11
```
SHOW polar_version;
```

pg_bigm と pg_trgm の違い

pg_trgm は、全文検索に 3-gram モデルを使用する PolarDB 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)

使用上の注意

pg_bigm の GIN インデックスが作成された列の最大サイズは 107,374,180 バイト (約 102 MB) です。次の例では、この制限を超えるデータを挿入しようとしているため、エラーが返されます。
```
CREATE TABLE t1 (description text);

CREATE INDEX t1_idx ON t1 USING gin (description gin_bigm_ops);

INSERT INTO t1 SELECT repeat('A', 107374181);
```
データが ASCII でエンコードされていない場合は、UTF-8 エンコーディングを使用することを推奨します。現在のデータベースのエンコーディングを確認するには、次のコマンドを実行します。
```
SELECT pg_encoding_to_char(encoding)
FROM pg_database
WHERE datname = current_database();
```

基本的な操作

拡張を作成します。
```
CREATE EXTENSION pg_bigm;
```

GIN インデックスを作成するときは、pg_bigm 拡張によって提供される演算子クラスを指定します。

CREATE TABLE pg_tools (tool text, description text);

INSERT INTO pg_tools VALUES ('pg_hint_plan', 'Tool that allows a user to specify an optimizer HINT to PostgreSQL');
INSERT INTO pg_tools VALUES ('pg_dbms_stats', 'Tool that allows a user to stabilize planner statistics in PostgreSQL');
INSERT INTO pg_tools VALUES ('pg_bigm', 'Tool that provides 2-gram full text search capability in PostgreSQL');
INSERT INTO pg_tools VALUES ('pg_trgm', 'Tool that provides 3-gram full text search capability in PostgreSQL');

CREATE INDEX pg_tools_idx ON pg_tools USING gin (description gin_bigm_ops);
CREATE INDEX pg_tools_multi_idx ON pg_tools USING gin (tool gin_bigm_ops, description gin_bigm_ops) WITH (FASTUPDATE = off);

全文検索を実行します。

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)

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

SELECT tool FROM pg_tools WHERE tool =% 'bigm';

結果：

  tool   
---------
 pg_bigm
(1 row)

拡張を削除します。
```
DROP EXTENSION pg_bigm;
```

組み込み関数

likequery

目的：LIKE 演算子で認識できる文字列を生成します。
パラメータ：1つの文字列パラメータ。
戻り値：LIKE 演算子と互換性のある検索文字列。
仕組み：
- キーワードの前後に % 文字を追加します。
- \ を使用して % 文字を自動的にエスケープします。

例：

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)

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 要素を配列として返します。
- パラメータ：1つの文字列パラメータ。
- 戻り値：すべての 2-gram 要素を含む配列。
- 仕組み：
  - 文字列の先頭と末尾にスペースを追加します。
  - すべての 2-gram の部分文字列を抽出します。
- 例：
```
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 つの文字列間の類似度を計算します。
- パラメータ：2つの文字列パラメータ。
- 戻り値：類似度を表す浮動小数点数。
- 仕組み：
  - 両方の文字列に共通する 2-gram 要素をカウントします。
  - 類似度の範囲は [0, 1] です。0 は文字列が完全に異なることを意味し、1 は文字列が同一であることを意味します。
  説明
  - 2-gram の計算時に文字列の前後にスペースが追加されるため、ABC と B の類似度は 0 になり、ABC と A の類似度は 0.2 になります。
  - bigm_similarity は大文字と小文字を区別します。たとえば、ABC と abc の類似度は 0 になります。
- 例：
  - ```
  SELECT bigm_similarity('full text search', 'text similarity search');
```
  結果：
```
   bigm_similarity 
  -----------------
          0.571429
  (1 row)
```
- ```
SELECT bigm_similarity('ABC', 'A');
```
    結果：
```
 bigm_similarity
-----------------
             0.2
(1 row)
```
  - ```
  SELECT bigm_similarity('ABC', 'B');
```
  結果：
```
   bigm_similarity
  -----------------
                 0
  (1 row)
```
- ```
SELECT bigm_similarity('ABC', 'abc');
```
    結果：
```
 bigm_similarity
-----------------
               0
(1 row)
```
pg_gin_pending_stats
- 目的：GIN インデックスのペンディングリスト内のページ数とタプル数を返します。
- パラメータ：GIN インデックスの名前または OID。
- 戻り値：ペンディングリスト内のページ数とタプル数の 2 つの値。
  
  説明
  GIN インデックスが FASTUPDATE を False に設定して作成された場合、ペンディングリストは存在せず、関数は 0 を返します。
- 例：
```
SELECT * FROM pg_gin_pending_stats('pg_tools_idx');
```
  結果：
```
 pages | tuples
-------+--------
     0 |      0
(1 row)
```

動作制御パラメータ

pg_bigm.enable_recheck

GIN インデックススキャンの後に再チェックステップが実行されるかどうかを制御します。

説明

結果の精度を確保するために、デフォルト値 (ON) のままにすることを推奨します。

例：

テストデータを準備します。

CREATE TABLE tbl (doc text);

INSERT INTO tbl VALUES('He is awaiting trial');
INSERT INTO tbl VALUES('It was a trivial mistake');

CREATE INDEX tbl_idx ON tbl USING gin (doc gin_bigm_ops);

次のクエリを実行します。

pg_bigm.enable_recheck が on に設定されている場合、再チェックが実行されます：

SET enable_seqscan TO off;

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)

次のクエリを実行します：

SELECT * FROM tbl WHERE doc LIKE likequery('trial');

結果：

         doc
----------------------
 He is awaiting trial
(1 row)

pg_bigm.enable_recheck が off に設定されている場合、再チェックは実行されません：

SET pg_bigm.enable_recheck = off;

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

類似度のしきい値を設定します。類似度スコアがこのしきい値を超えるタプルが、類似検索の結果として返されます。