PostgreSQLにおけるすべてのインデックスは二次的なインデックス、つまり各インデックスはテーブルの主要なデータ領域(PostgreSQLの用語ではテーブルのヒープと呼ばれます)とは別に格納されています。
このことは、通常のインデックススキャンにおいて、各行の検索にはインデックスとヒープの両方からデータを取得する必要があることを意味します。
さらに、指定のインデックス可能なWHERE
条件に適合するインデックスのエントリは、通常、インデックス内の近い位置にあるのに対し、そこから参照されるテーブルの行はヒープ内のあらゆるところにあるかもしれません。
このため、インデックススキャンにおけるヒープアクセスの部分では、ヒープに対する多くのランダムアクセスがありますが、これは遅い可能性があり、特に伝統的な回転型メディアでは遅くなります。
(11.5で説明したように、ビットマップインデックスはヒープアクセスをソートした順で行うことでこのコストを緩和しようとするものですが、それはある程度までしかできません。)
このパフォーマンス問題を解決するため、PostgreSQLはインデックスオンリースキャンをサポートします。 これは、問い合わせに対してヒープアクセスをせずにインデックスのみで回答できるものです。 基本的な考え方は、関連するヒープのエントリを参照せずに、各インデックスエントリから直接に値を返すというものです。 この方法が使用できるためには2つの基本的な制限があります。
インデックスの種類がインデックスオンリースキャンをサポートしている必要があります。 B-treeインデックスはいつでもインデックスオンリースキャンをサポートしています。 GiSTとSP-GiSTは一部の演算子クラスでインデックスオンリースキャンをサポートしていますが、サポートしない演算子クラスもあります。 他のインデックスの種類はインデックスオンリースキャンをサポートしていません。 根本的な必要条件は、インデックスが各インデックスのエントリに対応する元のデータ値を物理的に格納していなければならない、あるいはそれを再構築できる必要がある、ということです。 その反例として、GINインデックスでは、各インデックスエントリが通常は元のデータ値の一部しか保持していないため、インデックスオンリースキャンをサポートすることができません。
問い合わせはインデックスに格納されている列だけを参照しなければなりません。
例えばテーブルの列x
とy
にインデックスがあり、そのテーブルにはさらに列z
がある場合、次の問い合わせはインデックスオンリースキャンを使用できます。
SELECT x, y FROM tab WHERE x = 'key'; SELECT x FROM tab WHERE x = 'key' AND y < 42;
しかし、以下の問い合わせはインデックスオンリースキャンを使用できません。
SELECT x, z FROM tab WHERE x = 'key'; SELECT x FROM tab WHERE x = 'key' AND z < 42;
(以下で説明するように、式インデックスや部分インデックスは、この規則を複雑にします。)
この2つの基本的な要件が満たされるなら、問い合わせで要求されるすべてのデータ値はインデックスから利用できるので、インデックスオンリースキャンが物理的に可能になります。 しかし、PostgreSQLのすべてのテーブルスキャンにおいて、さらなる必要条件があります。 それは、第13章で説明するように、検索された各行が問い合わせのMVCCスナップショットに対して「可視」であることを確認しなければならない、ということです。 可視性の情報はインデックスのエントリには格納されず、ヒープのエントリにのみあります。 そのため、一見すると、すべての行検索はいずれにせよヒープアクセスが必要なように思われます。 そして、テーブルの行が最近に更新された場合は、まさにその通りなのです。 しかし、あまり更新されないデータについてはこの問題を回避する方法があります。 PostgreSQLではテーブルのヒープの各ページについて、そのページに格納されているすべての行が、十分に古く、すべての現在および将来のトランザクションに対して可視であるかどうかを追跡しています。 この情報はテーブルの可視性マップのビットに格納されます。 インデックスオンリースキャンでは、候補となるインデックスのエントリを見つけた後、対応するヒープページの可視性マップのビットを検査します。 それがセットされていれば、行が可視であることがわかるので、それ以上の作業をすることなく、データを返すことができます。 セットされていない場合は、それが可視かどうかを調べるためにヒープエントリにアクセスする必要があり、そのため標準的なインデックススキャンに対するパフォーマンス上の利点はありません。 うまくいく場合であっても、この方法はヒープアクセスと引き換えに可視性マップにアクセスします。 しかし、可視性マップはヒープに比べ、4桁の規模で小さいため、アクセスに必要な物理的I/Oははるかに少ないです。 ほとんどの状況では、可視性マップは常にメモリ内にキャッシュされて残っています。
要するに、2つの基本的条件が満たされていればインデックスオンリースキャンが可能ですが、テーブルのヒープページのかなりの部分に対し、その全可視のビットがセットされている場合にのみ、性能が向上します。 しかし大部分の行が変化しないテーブルは一般的であり、現実にはこの種のスキャンは非常に有効です。
インデックスオンリースキャンの機能を有効に利用するため、カバリングインデックスの作成を選択できます。
これは、頻繁に実行する特定の種類の問い合わせに必要な列を含めるように特別に設計されたインデックスです。
問い合わせは通常、検索対象の列よりも多くの列を取得する必要があるため、PostgreSQLはいくつかの列を単に「ペイロード」として検索キーの一部ではないインデックスを作成できます。
これは追加の列リストをINCLUDE
句に加えることで実行出来ます。
例えば、次のような問い合わせをよく実行する場合を考えます。
SELECT y FROM tab WHERE x = 'key';
このような問い合わせを高速化する伝統的な手法は、x
のみにインデックスを作成することです。
しかし、次のようなインデックス定義では、
CREATE INDEX tab_x_y ON tab(x) INCLUDE (y);
y
はヒープにアクセスしなくてもインデックスから取得できるため、この問い合わせをインデックスオンリースキャンとして処理できます。
y
列はインデックスの検索キーの一部ではないため、インデックスが処理できるデータ型である必要はありません。
単にインデックスに格納されているだけで、インデックス機構によって解釈されることはありません。
また、インデックスが一意インデックスの場合は、
CREATE UNIQUE INDEX tab_x_y ON tab(x) INCLUDE (y);
x
列のみに一意性条件が適用されます。
x
とy
の組み合わせではありません。
(INCLUDE
句は、インデックスを設定するための代替構文を提供するUNIQUE
やPRIMARY KEY
の制約として書くことも出来ます。)
キー以外のペイロード列、特に幅の広い列をインデックスに追加することについては慎重になることが賢明です。 インデックス型の最大サイズを超えるタプルをインサートしようとすると失敗します。 いかなる場合でもキー以外の列が重複データだったり、インデックスサイズが膨張すると、検索が遅くなる可能性があります。 それから、覚えておくべきもう一つの小さなポイントは、インデックスオンリースキャンがヒープへのアクセスが必要がないほどテーブルがゆっくり変化しない限り、インデックスにペイロード列を含めることにほとんど意味が無いことです。 とにかくヒープタプルを訪れなければならなくなった場合は、そこから列値を取得するためにそれ以上のコストはかかりません。 他の制限は、式を列に含めることは、現在のところサポートされていません。また、列を含めるサポートは現在のところB-treeとGiSTとSP-GiSTインデックスのみサポートされています。
INCLUDE
機能がない以前のPostgreSQLでは、ペイロード列を通常のインデックス列としてカバリングインデックスを作成することがありました。
CREATE INDEX tab_x_y ON tab(x, y);
これは、y
をWHERE
句の一部で使用するつもりがなかったとしても書いています。
余分な列が末尾の列である限り、これはうまく機能します。
それらを先頭側の列にすることは、11.3で説明されている理由から賢明ではありません。
しかし、この方法では、キー列に一意性を強制するインデックスがサポートされません。
末尾消去は、常に上位のB-treeレベルから非キーの列を削除します。
ペイロード列として、それらはインデックススキャンを導くためには使われません。
また、この消去プロセスは、キー列の残りのプレフィックスが、最下位のB-treeレベルのタプルを記述するのに十分である場合、1つ以上の後続キー列も削除します。
実際上、INCLUDE
句を使用しないカバリングインデックスは、実質的に上位レベルにペイロードが含まれるカラムの格納を避けられます。
ただし、ペイロード列を非キー列として明示的に定義すると確実に上位レベルのタプルが小さくなります。
原則として、インデックスオンリースキャンは式インデックスでも使うことができます。
例えば、x
がテーブルの列で、f(x)
上にインデックスがある場合、次の問い合わせをインデックスオンリースキャンとして実行できるはずです。
SELECT f(x) FROM tab WHERE f(x) < 1;
そして、関数f()
の計算が高価なら、この方法は非常に魅力的です。
しかしPostgreSQLのプランナは現在のところ、このような場合についてあまり賢くありません。
プランナは、問い合わせで必要となるすべての列がインデックスから利用可能な場合にのみ、その問い合わせが潜在的にインデックスオンリースキャンで実行可能と考えます。
この例では、f(x)
という文脈でしかx
は必要になりませんが、プランナはそのことに気付かないため、インデックスオンリースキャンは不可能であると結論します。
インデックスオンリースキャンは十分に価値があると思われるなら、含める列としてx
を追加することで回避できます。
例をあげます。
CREATE INDEX tab_f_x ON tab (f(x)) INCLUDE (x);
目的がf(x)
の再計算を避けることの場合、さらなる注意として、プランナはインデックス可能なWHERE
句にないf(x)
の使用を必ずしもインデックス列とマッチしないという事があります。
上記のような単純な問い合わせの場合は通常は正しく処理できるでしょうが、結合を含む問い合わせでは駄目でしょう。
これらの欠点はPostgreSQLの将来のバージョンで解決されるかもしれません。
部分インデックスもインデックスオンリースキャンとの間に興味深い関係があります。 例 11.3に示す部分インデックスを考えます。
CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target) WHERE success;
原則として、次のような問い合わせに対して、このインデックスを使ったインデックスオンリースキャンが可能です。
SELECT target FROM tests WHERE subject = 'some-subject' AND success;
しかし、WHERE
句で参照されるsuccess
がインデックスの結果列として利用できないという問題があります。
それにも関わらず、インデックスオンリースキャンが可能です。
なぜなら、このプランではWHERE
句のその部分を実行時に再検査する必要がない、つまりインデックス内にあるすべてのエントリは必ずsuccess = true
なので、プラン内でこれを明示的に検査する必要がないからです。
PostgreSQLのバージョン9.6およびそれ以降ではこのような場合を認識し、インデックスオンリースキャンを生成可能ですが、それより古いバージョンではできません。