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

PolarDB for PostgreSQL (Compatible with Oracle) のpg_bigm拡張は、フルテキスト検索を高速化するために使用される2グラムの一般化逆インデックス (GIN) を作成します。

前提条件

次のエンジンバージョンのいずれかを実行するPolarDB for PostgreSQL (Compatible with Oracle) クラスターが作成されます。

PolarDB for Oracle 2.0 (バージョン2.0.14.2.0以降)
PolarDB for Oracle 1.0 (バージョン1.1.28以降)

説明

次のステートメントを実行して、PolarDB for PostgreSQL (Compatible with Oracle) クラスターのリビジョンバージョンを表示できます。

SHOW polar_version;

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

PolarDB for PostgreSQL (Compatible with Oracle) のpg_trgm拡張は、3グラムモデルを使用して全文検索を実装します。 pg_bigm拡張は、pg_trgm拡張に基づいて開発されています。次の表に、2つの拡張機能の違いを示します。

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

使用上の注意

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

PolarDBクラスター内のデータがASCII形式でエンコードされていない場合は、エンコード形式をUTF-8に変更することを推奨します。次の文を実行して、現在のデータベースのエンコード形式を照会します。
```
SELECT pg_encoding_to_char(encoding)
FROM pg_database
WHERE datname = current_database();
```

基本操作

pg_bigm拡張子を作成します。
```
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)

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

基本機能

likequery

目的: LIKEキーワードに基づいて識別できる文字列を生成します。
リクエストパラメーター: STRING型の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グラム要素を取得します。
- リクエストパラメーター: STRING型の1つのリクエストパラメーター。
- 戻り値: 文字列のすべての2グラム要素で構成される配列。
- 実装:
  - 文字列の前後にスペースを追加します。
  - 文字列内のすべての2グラム要素を識別します。
- 例：
```
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つの文字列の両方に含まれる2グラム要素を特定します。
  - 戻り値の範囲は0から1です。値0は、2つの文字列が異なることを示します。値1は、2つの文字列が同じであることを示します。
  説明
  - この関数は、各文字列の前後にスペースを追加します。したがって、ABC文字列とB文字列との間の類似度は0であり、ABC文字列とA文字列との間の類似度は0.25です。
  - この関数は大文字と小文字を区別します。たとえば、この関数は、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.25
(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を指定するパラメーター。
- 戻り値: GINインデックスの保留リストのページ数とタプル数。
  説明
  GINインデックスのFASTUPDATEパラメーターをFalseに設定した場合、GINインデックスには保留リストがありません。この場合、この関数は0と0の2つの値を返します。
- 例：
```
SELECT * FROM pg_gin_pending_stats('pg_tools_idx');
```
  サンプル結果:
```
 pages | tuples
-------+--------
     0 |      0
(1 row)
```

動作制御パラメータ

pg_bigm.enable_recheck

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

説明

デフォルト値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パラメーターのデフォルト値を使用して、再チェックを実行します。

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

サンプル結果:

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

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