PolarDB:pg_bigm

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

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

使用上の注意

• 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を使用することを推奨します。 これにより、正確なクエリ結果を取得できます。

  例：

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

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

  2. 次のステートメントを実行します。

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

       サンプル結果:

                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グラムの要素の最大数を指定します。 デフォルト値は0で、すべての2グラム要素が使用されることを指定します。

  説明

  すべての2グラム要素の使用によりクエリのパフォーマンスが低下した場合、このパラメーターの値を減らすことができます。

• pg_bigm.similarity_limit

  このパラメータは、類似度のしきい値を指定します。 類似度が指定された閾値を超えるタプルは、類似度検索結果として返されます。