PolarDB:pg_bigm

拡張の有効化

CREATE EXTENSION pg_bigm;

GIN インデックスの作成

pg_bigm を使用して GIN インデックスを作成する際は、gin_bigm_ops 演算子クラスを指定します。

CREATE TABLE pg_tools (tool text, description text);

INSERT INTO pg_tools VALUES ('pg_hint_plan', 'PostgreSQL に対してオプティマイザヒントを指定できるツール');
INSERT INTO pg_tools VALUES ('pg_dbms_stats', 'PostgreSQL のプランナ統計情報を安定化できるツール');
INSERT INTO pg_tools VALUES ('pg_bigm', 'PostgreSQL で 2-gram 全文検索機能を提供するツール');
INSERT INTO pg_tools VALUES ('pg_trgm', 'PostgreSQL で 3-gram 全文検索機能を提供するツール');

-- 単一列インデックス
CREATE INDEX pg_tools_idx ON pg_tools USING gin (description gin_bigm_ops);

-- FASTUPDATE 無効化済みの複数列インデックス
CREATE INDEX pg_tools_multi_idx ON pg_tools USING gin (tool gin_bigm_ops, description gin_bigm_ops) WITH (FASTUPDATE = off);

全文検索の実行

インデックス対象列を検索するには、LIKE 演算子を使用します。

SELECT * FROM pg_tools WHERE description LIKE '%search%';

実行結果：

   tool   |                             description
----------+---------------------------------------------------------------------
 pg_bigm  | PostgreSQL で 2-gram 全文検索機能を提供するツール
 pg_trgm  | PostgreSQL で 3-gram 全文検索機能を提供するツール
(2 行)

類似検索の実行

キーワードと類似した文字列を検索するには、=% 演算子を使用します。

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

実行結果：

   tool
---------
 pg_bigm
(1 行)

拡張の無効化

DROP EXTENSION pg_bigm;

likequery

入力キーワードを % で囲み、さらにキーワード内に含まれるリテラルの % 文字をエスケープすることで、LIKE 互換の検索文字列を生成します。

構文：likequery(keyword text) → text

例 1： 入力内のリテラル % をエスケープします。

SELECT likequery('pg_bigm は全文検索のパフォーマンスを 200% 向上させました');

実行結果：

                            likequery
-------------------------------------------------------------------
 %pg_bigm によって全文検索のパフォーマンスが 200\%% 向上しました
(1 行)

例 2： likequery を WHERE 句内で使用し、手動での % パターン記述を回避します。

SELECT * FROM pg_tools WHERE description LIKE likequery('search');

実行結果：

   tool   |                             description
----------+---------------------------------------------------------------------
 pg_bigm  | PostgreSQL で 2-gram 全文検索機能を提供するツール
 pg_trgm  | PostgreSQL で 3-gram 全文検索機能を提供するツール
(2 行)

show_bigm

文字列から抽出されたすべての 2-gram 要素を配列として返します。この関数は、抽出前に入力文字列の先頭および末尾に半角スペースを付与（パディング）します。

構文：show_bigm(string text) → text[]

例：

SELECT show_bigm('全文検索');

実行結果：

                          show_bigm
------------------------------------------------------------------
 {" f"," s"," t",ar,ch,ea,ex,fu,"h ","l ",ll,rc,se,"t ",te,ul,xt}
(1 行)

bigm_similarity

2 つの文字列間の共有 2-gram 要素に基づき、0（完全に異なる）～1（同一）の範囲で浮動小数点型の類似度スコアを返します。

構文：bigm_similarity(string1 text, string2 text) → float4

この関数は比較前に各文字列の先頭および末尾に半角スペースを付与（パディング）し、比較は大文字小文字を区別します。

例：

SELECT bigm_similarity('full text search', 'text similarity search');

実行結果：

 bigm_similarity
-----------------
        0.571429
(1 行)

-- スペースパディングにより、「A」は「ABC」と「 A」の 2-gram を共有しますが、「AB」や「BC」は共有しません
SELECT bigm_similarity('ABC', 'A');

実行結果：

 bigm_similarity
-----------------
            0.25
(1 行)

-- 「B」はスペースパディング後も「ABC」と重複する 2-gram を持ちません
SELECT bigm_similarity('ABC', 'B');

実行結果：

 bigm_similarity
-----------------
               0
(1 行)

-- 大文字小文字を区別：「abc」と「ABC」は共通の 2-gram を持ちません
SELECT bigm_similarity('ABC', 'abc');

実行結果：

 bigm_similarity
-----------------
               0
(1 行)

pg_gin_pending_stats

GIN インデックスの保留リスト（pending list）に含まれるページ数およびタプル数を返します。

構文：pg_gin_pending_stats(index regclass) → (pages int, tuples int)

インデックスの FASTUPDATE が false に設定されている場合、GIN インデックスには保留リストが存在せず、この関数は 0, 0 を返します。

例：

SELECT * FROM pg_gin_pending_stats('pg_tools_idx');

実行結果：

 pages | tuples
-------+--------
     0 |      0
(1 行)

pg_bigm.enable_recheck

GIN インデックスから候補行を取得した後に再チェック（recheck）ステップを実行するかどうかを制御します。

デフォルト値：on

全文検索の内部動作： pg_bigm は検索キーワードを 2-gram 要素に分割し、GIN インデックスを用いて候補行を取得します。インデックス検索は近似検索であるため、候補セットには誤検知（false positive）——すなわち、一致する 2-gram を含むものの、実際のキーワードを含まない行——が含まれる可能性があります。再チェックステップでは、各候補行に対して元の検索条件を評価し、これらの誤検知を除外します。

たとえば、trial を検索する場合、「He is awaiting trial」と「It was a trivial mistake」の両方が候補として取得されます。これは、trivial が trial のすべての 2-gram（tr、ri、ia、al）を含むためです。再チェックが有効な場合、正しい結果のみが返されます。

例 — 再チェック有効（デフォルト）：

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

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

インデックスは 2 件の候補を返しますが、再チェックにより誤検知が除外され、1 件の正しい結果のみが残ります。

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

実行結果：

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

例 — 再チェック無効：

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

正確な結果を得るには、pg_bigm.enable_recheck をデフォルト値（on）のままにしてください。

pg_bigm.gin_key_limit

GIN インデックススキャン時に使用される 2-gram 要素の最大数を設定します。

デフォルト値：0（すべての 2-gram 要素を使用）

すべての 2-gram を使用すると最も正確な候補セットが得られますが、長いキーワードでは多数の 2-gram が生成され、GIN インデックススキャンのコストが増加する可能性があります。この値を減らすことで使用する 2-gram 数を制限し、スキャン速度を向上させることができます。

ただし、使用する 2-gram 数を減らすと、候補セット内の誤検知数が増加し、再チェックステップのワークロードが増大します。このパラメーターは、GIN インデックススキャンのパフォーマンスがボトルネックとなっている場合にのみ調整し、結果の精度への影響を十分に検証してください。

pg_bigm.similarity_limit

類似検索における類似度しきい値を設定します。=% 演算子は、このしきい値以上または等しい類似度スコアを持つ行のみを返します。