PolarDB:インデックスのみのスキャンとカバーインデックス

すべてのインデックスinPostgreSQLare secondaryインデックスです。つまり、各インデックスはテーブルのメインデータ領域 (PostgreSQLtementationではテーブルのヒープと呼ばれます) とは別に格納されます。 つまり、通常のインデックススキャンでは、各行の取得にはインデックスとヒープの両方からデータをフェッチする必要があります。 さらに、特定のインデックス可能なWHERE条件に一致するインデックスエントリは、通常、インデックス内で近接していますが、参照するテーブル行はヒープ内のどこにでもある可能性があります。 したがって、インデックススキャンのヒープアクセス部分は、ヒープへの多くのランダムアクセスを伴い、これは、特に従来の回転媒体では低速である可能性がある。

このパフォーマンスの問題を解決するために、PostgreSQLはインデックスのみのスキャンをサポートしています。これにより、ヒープアクセスなしでインデックスのみからクエリに応答できます。 基本的な考え方は、関連するヒープエントリを調べる代わりに、各インデックスエントリから直接値を返すことです。 このメソッドを使用できる場合には、2つの基本的な制限があります。

1. インデックスタイプは、インデックスのみのスキャンをサポートする必要があります。 B-treeインデックスは常に行います。 GiSTおよびSP-GiSTインデックスは、一部の演算子クラスのインデックスのみのスキャンをサポートしますが、他のクラスはサポートしません。 他のインデックスタイプはサポートされていません。 基本的な要件は、インデックスが、各インデックスエントリの元のデータ値を物理的に格納するか、または再構築できなければならないことです。 反例として、各インデックスエントリは通常、元のデータ値の一部のみを保持するため、GINインデックスはインデックスのみのスキャンをサポートできません。

2. クエリは、インデックスに格納されている列のみを参照する必要があります。 たとえば、列zも持つテーブルの列xとyのインデックスが与えられた場合、これらのクエリはインデックスのみのスキャンを使用できます。

   SELECT x, y FROMタブWHERE x = 'key';
   SELECT x FROMタブWHERE x = 'key' AND y < 42; 

   しかし、これらのクエリはできませんでした:

   SELECT x, z FROMタブWHERE x = 'key';
   SELECT x FROMタブWHERE x = 'key' AND z < 42; 

これらの2つの基本的な要件が満たされる場合、クエリによって必要とされるすべてのデータ値がインデックスから利用可能であるため、インデックスのみのスキャンが物理的に可能です。 ただし、PostgreSQLのテーブルスキャンには追加の要件があります。検索された各行がクエリのMVCCスナップショットに「表示」されていることを確認する必要があります。 可視性情報はインデックスエントリには格納されず、ヒープエントリにのみ格納されます。そのため、一見すると、すべての行の取得にはヒープアクセスが必要になるようです。 テーブル行が最近変更された場合、これは実際に当てはまります。 PostgreSQLtracksは、テーブルのヒープ内の各ページについて、そのページに格納されているすべての行が、現在および将来のすべてのトランザクションに見えるほど古いかどうかを追跡します。 この情報は、テーブルの可視性マップにビットで格納されます。 インデックスのみのスキャンは、候補インデックスエントリを見つけた後、対応するヒープページの可視性マップビットをチェックします。 設定されている場合、行は表示されていることがわかっているため、データは追加の作業なしで返すことができます。 設定されていない場合、ヒープエントリにアクセスして表示されているかどうかを確認する必要があるため、標準のインデックススキャンよりもパフォーマンス上の利点は得られません。 成功した場合でも、このアプローチは、可視性マップアクセスをヒープアクセスと交換します。しかし、可視性マップはそれが記述するヒープよりも4桁小さいため、アクセスに必要な物理I/Oははるかに少なくなります。 ほとんどの場合、可視性マップは常にメモリにキャッシュされたままです。

つまり、2つの基本的な要件を考慮すると、インデックスのみのスキャンが可能ですが、テーブルのヒープページのかなりの部分にすべて表示可能なマップビットが設定されている場合にのみ勝ちになります。 しかし、行の大部分が不変であるテーブルは、このタイプのスキャンを実際に非常に有用にするのに十分に一般的である。

インデックスのみのスキャン機能を効果的に使用するには、カバーインデックスを作成します。これは、頻繁に実行する特定の種類のクエリに必要な列を含めるように特別に設計されたインデックスです。 クエリは通常、検索する列よりも多くの列を取得する必要があるため、PostgreSQLを使用すると、一部の列が単なる「ペイロード」であり、検索キーの一部ではないインデックスを作成できます。 これは、追加の列をリストするINCLUDE句を追加することによって行われます。 たとえば、のようなクエリを一般的に実行する場合

SELECT y FROMタブWHERE x = 'key';

このようなクエリを高速化する従来のアプローチは、xのみにインデックスを作成することです。 ただし、インデックスは

CREATE INDEX tab_x_y ONタブ (x) INCLUDE (y);

yはヒープにアクセスせずにインデックスから取得できるため、これらのクエリをインデックスのみのスキャンとして処理できます。

列yはインデックスの検索キーの一部ではないため、インデックスが処理できるデータ型である必要はありません。列yはインデックスに格納されるだけで、インデックス機構によって解釈されません。 また、インデックスが一意のインデックスの場合、

CREATE UNIQUE INDEX tab_x_y ON tab(x) INCLUDE (y);

一意性条件は列xのみに適用され、xとyの組み合わせには適用されません。 (INCLUDE句は、UNIQUEおよびPRIMARY KEY制約で記述することもでき、このようなインデックスを設定するための代替構文を提供します。)

非キーペイロード列、特にワイド列をインデックスに追加することについては、保守的であることが賢明です。 インデックスタプルがインデックスタイプに許可されている最大サイズを超えると、データ挿入は失敗します。 いずれの場合も、非キー列はインデックスのテーブルからのデータを複製し、インデックスのサイズを大きくするため、検索が遅くなる可能性があります。 また、インデックスのみのスキャンでヒープにアクセスする必要がないほどテーブルがゆっくりと変化しない限り、インデックスにペイロード列を含めることにはほとんど意味がありません。 とにかくヒープタプルを訪問する必要がある場合、そこから列の値を取得するのにそれ以上の費用はかかりません。 その他の制限は、式が現在含まれる列としてサポートされていないこと、およびBツリー、GiST、およびSP-GiSTインデックスのみが現在含まれる列をサポートしていることです。

BeforePostgreSQL INCLUDE機能を使用すると、ペイロード列を通常のインデックス列として書き込むことで、インデックスをカバーすることがあります。

CREATE INDEX tab_x_y ONタブ (x、y);

WHERE句の一部としてyを使用するつもりはありませんでしたが。 これは、余分な列が後続の列である限り問題なく機能します。それらを先頭の列にすることは賢明ではありません。 ただし、この方法では、インデックスでキー列に一意性を適用する場合はサポートされません。

接尾辞の切り捨ては、常に上位B-Treeレベルから非キー列を削除します。 ペイロード列として、インデックススキャンのガイドには使用されません。 切り捨てプロセスはまた、キー列の残りのプレフィックスが最低のBツリーレベルでタプルを記述するのに十分である場合に、1つまたは複数の末尾のキー列を削除する。 実際には、INCLUDE句なしでインデックスをカバーすると、効果的にペイロードである列を上位レベルに格納することを回避できます。 ただし、ペイロード列を非キー列として明示的に定義すると、上位レベルのタプルは確実に小さくなります。

原則として、インデックスのみのスキャンは式インデックスとともに使用できます。 たとえば、xがテーブル列であるf(x) のインデックスが与えられると、実行できるはずです。

SELECT f(x) FROMタブWHERE f(x) < 1;

これは、f() が計算に費用がかかる関数である場合に非常に魅力的である。 ただし、PostgreSQLのプランナーは現在、そのようなケースについてあまり賢くありません。 クエリは、クエリに必要なすべての列がインデックスから使用可能な場合にのみ、インデックスのみのスキャンによって実行可能であると見なされます。 この例では、コンテキストf(x) を除いてxは必要ありませんが、プランナーはそれに気付かず、インデックスのみのスキャンは不可能であると結論付けます。 インデックスのみのスキャンが十分に価値があると思われる場合は、含まれる列としてxを追加することで回避できます。

CREATE INDEX tab_f_x ONタブ (f(x)) INCLUDE (x);

f(x) の再計算を回避することが目標である場合、プランナーがインデックス可能なWHERE句にないf(x) の使用をインデックス列に必ずしも一致させないことに注意してください。 通常、上記のような単純なクエリではこれが正しくなりますが、結合を含むクエリではそうではありません。 これらの欠陥は、PostgreSQLの将来のバージョンで修正される可能性があります。

部分インデックスには、インデックスのみのスキャンとの興味深い相互作用もあります。 部分的なインデックスを考えます。

CREATE UNIQUE INDEX tests_success_constraint ON tests (subject、target)
        どこで成功; 

原則として、このインデックスに対してインデックスのみのスキャンを実行して、次のようなクエリを満たすことができます。

SELECT target FROM tests WHERE subject = 'some-subject' AND成功;

しかし、問題があります。WHERE句は、インデックスの結果列として使用できないsuccessを指します。 ただし、プランは実行時にWHERE句のその部分を再チェックする必要がないため、インデックスのみのスキャンが可能です。インデックスにあるすべてのエントリには必ずsuccess = trueがあるため、これをプランで明示的にチェックする必要はありません。PostgreSQLversions 9.6以降は、そのようなケースを認識し、インデックスのみのスキャンを生成できますが、古いバージョンでは生成できません。