PostgreSQL の発見の旅

Postgres にはいくつかのインデックス タイプがあり、新しいバージョンごとにいくつかの新しいインデックス タイプが追加されるようです。各インデックス タイプは便利ですが、どのタイプを使用するかは、(1) データのタイプ、場合によっては (2) テーブル内の基になるデータ、および (3) 実行される検索のタイプ​​によって決まります。次のコンテンツでは、Postgres で使用できるインデックスの種類と、どのような場合にどのインデックスの種類を使用する必要があるかを紹介します。始める前に、ここで説明するインデックス タイプのリストを示します:

Bツリー
一般化転置インデックス (GIN)
一般化反転検索ツリー (GiST)
スペース分割された GiST (SP-GiST)
ブロック範囲インデックス (BRIN)
ハッシュ＃＃＃ それではインデックス作成から始めましょう。

Postgres では、B ツリー インデックスが最も一般的に使用されるインデックスです コンピューター サイエンスの学位を取得している場合は、B ツリー インデックス作成が最初に学習するインデックスになる可能性があります。 B ツリー インデックスは、常にバランスを維持するツリーを作成します。インデックスに基づいて何かを検索する場合、ツリーをたどってキーを見つけ、探しているデータを返します。インデックスを使用すると、数千のレコードを順次スキャンするのではなく、数ページを読み取るだけで済むため (少数のレコードのみを返す場合)、順次スキャンよりも大幅に高速になります。

標準の CREATE INDEX ステートメントを実行すると、B ツリー インデックスが作成されます。 B ツリー インデックスは、テキスト、数値、タイムスタンプなど、ほとんどのデータ型で役立ちます。データベースでインデックスを使い始めたばかりで、データベースで Postgres の高度な機能をあまり使用していない場合は、標準の B ツリー インデックスを使用するのがおそらく最良の選択肢です。

複数値列の GIN インデックス 汎用逆索引 (一般に GIN と呼ばれます) は、単一の列に複数の値が含まれるデータ型に最も適しています。

Postgres ドキュメントによると:

"GIN は、インデックス付けされているエントリが複合値であり、インデックスによって処理されるクエリが複合エントリ内で発生する値を検索する必要がある状況を処理するように設計されています。たとえば、このエントリはドキュメントである可能性があります。 、クエリはドキュメントに含まれる値を検索できます。文字を指定してください。"

この範囲に含まれる最も一般的なデータ型は次のとおりです:

hストア

配列＃＃＃ 範囲＃＃＃ JSONB
GIN インデックスで最も満足できる点の 1 つは、複合値に格納されたデータを理解できることです。ただし、GIN インデックスには追加される個々の型のデータ構造に関する特定の知識が必要であるため、すべてのデータ型が GIN インデックスでサポートされているわけではありません。

GiST インデックス、値が重複する行の場合

Generalized Inverted Search Tree (GiST) インデックスは、データが同じ列内の他のデータ行と重複している場合に最も適しています。 GiST インデックスの最適な使用方法は、ジオメトリ データ型を宣言し、2 つのポリゴンにいくつかの点が含まれているかどうかを知りたい場合です。ある場合には、特定の点がボックス内に含まれる一方で、他の点は多角形内にのみ存在することがあります。 GiST を使用してインデックス付けされる一般的なデータ型は次のとおりです: ジオメトリ タイプ

全文検索が必要なテキスト タイプ

GiST インデックスのサイズには多くの固定制限があり、そうしないと、GiST インデックスが非常に大きくなる可能性があります。その代償として、GiST インデックスには損失が生じます (不正確)。

公式ドキュメントによると:

"GiST インデックスには損失があり、インデックスが誤った一致を生成する可能性があることを意味します。そのため、実際のテーブルの行をチェックして誤った一致を排除する必要があります。(PostgreSQL は必要に応じてこのアクションを自動的に実行します)"

これは、誤った結果が得られるという意味ではなく、Postgres がデータを返す前にこれらの誤った結果をフィルタリングするための小さな追加作業を行うことを意味します。

特記事項: GIN インデックスと GiST インデックスは、多くの場合、同じデータ型で使用できます。通常、パフォーマンスは優れていますが、多くのディスク領域を占有し、その逆も同様です。 GIN と GiST に関しては、あらゆる状況で機能する万能のソリューションはありませんが、上記のルールはほとんどの一般的な状況に適用されるはずです。

大規模データ用のSP-GiSTインデックス

空間分割 GiST (SP-GiST) インデックスは、Purdue Research の空間分割ツリーを採用しています。 SP-GiST インデックスは、データに自然なクラスタリング要素があり、バランスの取れたツリーではない場合によく使用されます。電話番号はその良い例です (少なくとも米国の電話番号はそうです)。形式は次のとおりです:

3桁の市外局番
3 桁のプレフィックス番号 (古い電話交換機に関連)
4桁の回線番号
これは、最初のセットの最初の 3 桁に自然なクラスタリング係数があり、次に 2 番目の 3 桁セットが続き、数値が均等に分散されることを意味します。ただし、電話番号の一部の市外局番では、他の局番よりも飽和状態が高くなります。その結果、非常にバランスの悪いツリーが作成される可能性があります。フロントには自然な集約要素があり、データは均等に分散されていないため、電話番号などのデータは SP-GiST に適している可能性があります。

BRIN インデックス、より大きなデータ用

ブロック範囲インデックス (BRIN) は、SP-GiST などのいくつかの状況に焦点を当てており、データに自然な順序があり、データ量が多くの場合に大きい場合に最適です。時系列順に 10 億件のレコードがある場合、BRIN が役立つ可能性があります。複数の郵便番号など、自然にグループ化された大規模なデータ セットをクエリする場合、BRIN を使用すると、同様の郵便番号がディスク上の近い場所に保存されるようにすることができます。

日付または郵便番号でソートされた非常に大規模なデータベースがある場合、BRIN インデックスを使用すると、一部の不要なデータを非常に迅速にスキップまたは除外できます。さらに、BRIN インデックスは全体のデータ サイズに比べて比較的小さいため、大規模なデータ セットがある場合は BRIN インデックスの方がパフォーマンスが向上します。

ハッシュインデックス、ついにクラッシュの心配がなくなる

ハッシュ インデックスは Postgres に長年存在していましたが、Postgres 10 がリリースされるまでは、その使用について大きな注意事項があり、WAL に記録されませんでした。つまり、サーバーがクラッシュし、スタンバイにフェイルオーバーできないか、wal-g などを使用してアーカイブから復元できない場合、インデックスを再構築するまでそのインデックスは失われます。 Postgres 10 のリリースにより、これらは WAL に記録されるようになったので、再び使用することを検討するかもしれませんが、本当の問題は、使用すべきかどうかです。

ハッシュ インデックスは、B ツリー インデックスよりも高速な検索を提供する場合があり、作成も高速です。最大の問題は、これらの比較演算が「等価」比較演算のみに制限されているため、完全一致検索にしか使用できないことです。このため、ハッシュ インデックスは一般的に使用される B ツリー インデックスよりも柔軟性が大幅に低くなり、代替品としてではなく、特殊な場合のインデックスとして考える必要があります。

どれを使うべきですか?

ここまでたくさん紹介してきましたが、少し怖がっていてもそれは正常です。これを事前に知っていれば、CREATE INDEX は常に B-Tree を使用してインデックスを作成します。幸いなことに、Postgres はほとんどのデータベースで非常に優れたパフォーマンスを発揮します。 :) さらに Postgres 機能の使用を検討している場合は、他の Postgres インデックス タイプを使用する場合のチートシートを以下に示します:

B ツリー - ほとんどのデータ型とクエリに適しています
GIN - JSONB/hstore/arrays 用
GiST - 全文検索と幾何学的データ型に適しています
SP-GiST - 自然な集計要素はあるものの、不均一に分散している大規模なデータ セットに適しています
BRIN - シーケンシャルな順序を持つ非常に大規模なデータセットに適しています
ハッシュ - 等価演算には適していますが、通常は B ツリー インデックスだけで十分です。
この記事に関してご質問やフィードバックがございましたら、お気軽に Slack チャンネルにご参加ください。

以上がPostgreSQL の発見の旅の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。