PostgresのGINインデックスとjsonb
hmarui66
hmarui66
hmarui66GINとは汎用転置インデックス(Generalized Inverted Index)を表します。
GINは、以下のような状況を取り扱うために設計されました。
(1)インデックス対象の項目が複合型である。
(2)そのインデックスにより処理される問い合わせは、複合型の項目内に存在する要素の値を検索する必要がある。
例えば、項目は文書であり、問い合わせは特定の単語を含む文書の検索です。
hmarui66GINインデックスは(キー、ポスティングリスト(posting list))の組み合わせの集合を格納します。 ここでポスティングリストはキーが発生した行IDの集合です
hmarui66PostgreSQLにおけるGINの実装は、主にTeodor SigaevとOleg Bartunovにより保守されています。 GINに関する情報は彼らのwebサイトにより多く記載されています。
hmarui66
hmarui66PostgreSQLのコア配布物は表 70.1に示すGIN演算子クラスを含みます。
jsonb型の2つの演算子クラスのうち、jsonb_opsがデフォルトです。 jsonb_path_opsはより少数の演算子しかサポートしませんが、その演算子に対してはより良いパフォーマンスを提供します。 詳細は8.14.4を参照してください。
hmarui66
hmarui66GINインデックスはキー全体に対するB-treeインデックスを持ちます。
そのキーはそれぞれインデックス対象項目の要素（例えば配列のメンバ）であり、リーフページ内のタプルはそれぞれ、
ヒープポインタのB-treeへのポインタ（「ポスティングツリー(posting tree)」）か、
もしリストがキー値と共に単一インデックスタプルに合う程度十分に小さければヒープポインタの単純なリスト（「ポスティングリスト(posting list)」）です
hmarui66
hmarui66pointing 対象が十分に少なければ単なるリストで扱い、多ければ木構造で扱う。
木構造は何をキーにしているかは不明。
hmarui66PostgreSQL 9.1からNULLキー値をインデックスに含められるようになりました。 またプレースホルダとしてのNULLが、NULLまたはextractValueによるとキーを含まないインデックス対象項目についてインデックスに含められます。 これにより空の項目を見つけ出すための検索を行うことができます。
hmarui661つのヒープ行の挿入または更新によりインデックスへの挿入が多く発生するという、転置インデックスの本質的な性質のためGINインデックスの更新は低速になりがちです。
GINは、新しいタプルを一時的なソートされていない、待機中の項目リストに挿入することにより、この作業の大部分を遅延させることができるようになりました。
hmarui66この手法の大きな欠点は、検索時に通常のインデックス検索に加え待機中の項目リストのスキャンを行わなければならない点です。 このため、待機中の項目リストが大きくなると検索が顕著に遅くなります。 他の欠点は、ほとんどの更新は高速ですが、待機中の項目リストが「大きくなりすぎる」きっかけとなった更新は即時の整理処理を招くことになり、他の更新に比べ大きく低速になります。 自動バキュームを適切に使用することで、これらの両方の問題を最小化することができます。
hmarui6670.6. 制限事項 #
GINは、インデックス付け可能な演算子は厳密であると仮定します。 これはextractValueはNULL項目値についてはまったく呼び出されない（代わりにインデックス項目のプレースホルダが自動的に生成される）こと、および、extractQueryは問い合わせの値がNULLの場合に呼び出されない（代わりに問い合わせは不一致であるとみなされる）ことを意味します。 しかし非NULLの複合型項目内または問い合わせ値内のNULLキー値はサポートされます。
hmarui66内部実装の詳細はここを見た方が良さそう。
hmarui66
hmarui66jsonb型へのインデックスとして GIN が使われる
hmarui66
当たり前だが、jsonで扱えるプリミティブのみサポート
hmarui66GINインデックスは、多数のjsonbドキュメント(データ)のキーやキー／値ペアを効率的に検索するときに用いることができます。 異なるパフォーマンスと柔軟性のトレードオフを持つ、2つのGIN 「演算子クラス」 が提供されています。
「多数の」がポイント。定型的な特定のクエリ向けではない。
hmarui66jsonb型の問い合わせでサポートしているデフォルトのGIN演算子クラスは、キーが存在するかの演算子として?、?|、?&があり、包含演算子として@>があり、jsonpathマッチング演算子として@?、@@があります。 （これらの演算子の意味の詳細は、表 9.46を参照してください。） この演算子クラスのインデックスを作成する例。
hmarui66デフォルトでないGIN演算子クラスjsonb_path_opsは、キーが存在するかの演算子をサポートしませんが、@>、@?、@@をサポートします。 この演算子クラスのインデックスを作成する例。
hmarui66テーブル名 apiにjsonb型でjdocをカラム名として格納します。 このカラムにGINインデックスを作成した場合、以下のような問い合わせがインデックスを利用することができます。
-- "company"キー が "Magnafone"値であるものを見つける
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';
しかし 次のような問い合わせはインデックスを使用しません。なぜなら、?演算子はインデックス可能ですが、jdocカラムのインデックスが直接適用されていないためです。
-- キー "tags" の配列要素に "qui"が含まれているか見つける
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui';
それでも、上記の問い合わせは、式インデックスを適切に使用することでインデックスを使用することができます。一般的な "tags"キーから特定の項目を照会する場合、このようなインデックスを定義すると良いかもしれません。
CREATE INDEX idxgintags ON api USING GIN ((jdoc -> 'tags'));
jsonb_path_ops演算子クラスは、@>、@?、@@演算子をサポートしているだけですが、デフォルト演算子クラスのjsonb_opsよりも顕著なパフォーマンス上の利点があります。
hmarui66jsonb_opsとjsonb_path_opsのGINインデックスの技術的差異は、前者はデータのキーと値のための独立したインデックスを作成しますが、後者は、データの値に対してのみインデックスを作成します。 [7] 基本的に、jsonb_path_opsインデックス項目は、値とキーのハッシュです。例えば、{"foo": {"bar": "baz"}}のインデックスはハッシュ値にfoo、bar、 bazすべてを組み込んで作成されます。 したがって、包含問い合わせのためのインデックス検索は、非常に特定の構造を返すようになっています。 しかしfooがキーとして表示されるかどうかを調べるには全く方法はありません。 一方、jsonb_opsインデックスは個別にはfoo、bar、bazを表す3つのインデックス項目を作成します。 その後、包含問い合わせをおこなうには、これらの項目の3つすべてを含む行を探します。 GINインデックスは、かなり効率的に検索することができますが、特に3つの索引項目のいずれかで、非常に多数の行が単一の場合に、同等のjsonb_path_ops検索よりも遅くなります。
hmarui66jsonb_path_opsアプローチの欠点は、{"a": {}}のような、任意の値を含まないJSON構造のためのインデックスエントリを生成しません。 このような構造を含むドキュメントの検索が要求された場合、それは、フルインデックススキャンを必要とします。それは非常に遅くなります。そのため、 jsonb_path_opsは、多くの場合、そのような検索を実行するのには不適当です。
hmarui66jsonb型は、btree と hash インデックスもサポートします。 これらは通常、JSONドキュメントの完全性をチェックすることが重要な場合のみ有用です。
意味があまり取れないが、値も含むドキュメントの完全一致ベースでしかクエリできないということ？
hmarui66
かなり色々な関数がサポートされている