ApsaraDB RDS:pg_bigm拡張を使用してファジー一致ベースのクエリを実行する

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

pg_trgm拡張機能は、ApsaraDB RDS for PostgreSQLによっても提供されます。 pg_trgm拡張機能は、3グラムモデルを使用してフルテキスト検索を実行します。 pg_bigm拡張は、pg_trgm拡張に基づいて開発されています。 次の表に、2つの拡張機能の違いを示します。

使用上の注意

• GINインデックスを作成する列の長さは、107,374,180バイト (約102 MB) を超えることはできません。

• RDSインスタンスのデータがASCII形式でエンコードされていない場合は、エンコード形式をUTF8に変更することを推奨します。

  説明

  select pg_encoding_to_char(encoding) from pg_database where datname = current_database(); コマンドを実行して、RDSインスタンスのエンコード形式を照会できます。

基本操作

• pg_bigm拡張を有効にします。

  postgres=> create extension pg_bigm;
  CREATE EXTENSION

• GINインデックスを作成します。

  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)

• pg_bigm拡張機能を無効にします。

  postgres=> drop extension pg_bigm;
  DROP EXTENSION

基本機能

• likequery

  • 目的: この関数は、LIKEキーワードに基づいて識別できる文字列を生成するために使用されます。

  • リクエストパラメーター: この関数にはリクエストパラメーターが1つ含まれます。 このパラメーターのデータ型は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グラム要素を取得するために使用されます。

  • リクエストパラメーター: この関数にはリクエストパラメーターが1つ含まれます。 このパラメーターのデータ型はSTRINGです。

  • 戻り値: このパラメーターは、文字列のすべての2グラム要素で構成される配列を返します。

  • 実装:

    • 文字列の前後にスペースを追加します。

    • 文字列内のすべての2グラム要素を識別します。

  • 例：

    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つの文字列間の類似性を取得するために使用されます。

  • リクエストパラメーター: この関数には2つのリクエストパラメーターが含まれます。 これらのパラメーターのデータ型はSTRINGです。

  • 戻り値: この関数は、2つの文字列間の類似性を示す浮動小数点数を返します。

  • 実装:

    • 2つの文字列の両方に含まれる2グラム要素を特定します。

    • 戻り値は0 ~ 1の範囲内です。 値0は、2つの文字列が異なることを示します。 値1は、2つの文字列が同じであることを示します。

    説明

    • この関数は、各文字列の前後にスペースを追加します。 したがって、ABC文字列とB文字列との間の類似度は0であり、ABC文字列とA文字列との間の類似度は0.25である。

    • この関数は、大文字と小文字を区別します。 たとえば、この関数は、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インデックスの保留リスト内のページ数とタプル数を取得するために使用されます。

  • リクエストパラメーター: この関数には1つのパラメーターが含まれます。 このパラメーターには、GINインデックスの名前またはOIDを指定します。

  • 戻り値: この関数は、GINインデックスの保留中のリスト内のページ数とタプル数の2つの値を返します。

    説明

    GINインデックスのFASTUPDATEパラメーターをFalseに設定した場合、GINインデックスには保留リストがありません。 この場合、この関数は0と0の2つの値を返します。

  • 例：

    postgres=> SELECT * FROM pg_gin_pending_stats('pg_tools_idx');
     pages | tuples
    -------+--------
         0 |      0
    (1 row)

行動制御

• pg_bigm.last_update

  このパラメーターは、pg_bigm拡張子に対して最新の更新が行われた日付を示します。 このパラメーターのみを表示できます。 このパラメーターの値は変更できません。

  例：

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

  説明

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

• pg_bigm.similarity_limit

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