Index-only scans/ja

原文最終更新日：13 December 2012‎

インデックスオンリースキャンは、Postgres 9.2に追加された主要な性能向上機能です。テーブルからではなく、インデックスからデータの参照を行うことによって満足させるクエリの明確な形式を許可します。これは、クエリの条件を満足させるのに必要なI/Oの著しい低下という結果を生じます。

標準のインデックススキャンの実行中、インデックスは、他の木構造と同様の挙動でインデックスに格納されたデータに対して絶えず照合することによってトラバースされます。B-treeでインデックスされた型は三分律が成立する必要があります；これはその型が反射法則、対称法則、推移法則に沿っている必要があります。これらの法則は、いずれにせよある型がどのように振る舞うべきかという我々の直観的な理解と一致していますが、インデックスの物理構造がデータの相対値を射影するという事実は、型により従うこれらの法則を適用します。Btreeインデックスはインデックスされたカラムデータの技術的な冗長コピーを格納しています。

PostgreSQLのインデックスは、可視性のある情報を格納していません。それゆえ、もしなんらかの与えられたタプルがカレントトランザクションにおいて明らかであっても直接的に確認することができません。これがインデックスオンリースキャンの実装に時間がかかった理由です。チープではあるが、信頼できる可視性のあるルックアサイドの実装を記述することは、チャレンジであると言えます。

この機能の実装は、可視性マップクラッシュセーフと呼ばれる現存のオンディスク構造の作成を不均衡に伴っていました。それは、確実に（そして安価に）インデックスタプルの可視性を表すための構造として必要なものでした。この少なくない行いはインデックスオンリースキャンが不正な結果を提供する可能性を伴い、もちろんまったく許容できるものではありませんでした。

インデックスが現ににインデックスされたデータを含むだけという事実、そしてその他インデックスされていないカラムは含まれていない事実は、（例えばクエリの選択リストの適用によって）ほかのカラムがクエリの対象となる時にインデックスオンリースキャンの使用を自然に排除します。

インデックスオンリースキャンが原則的に使用されるクエリの例

あるカラムにいくつかの（式でない）インデックスが存在すると仮定すると（一般にはプライマリキー）:

 select count(*) from categories;

(1st_indexed_col, 2nd_indexed_col)のカラムに対する複合インデックスが存在すると仮定すると:

 select 1st_indexed_col, 2nd_indexed_col from categories;

Postgres 9.2では、明白なインデックススキャンとインデックスオンリースキャンにおいてインデックスされたカラムへのANY(ARRAY[...])（訳注：下記例に出てくるin句などを指しています）条件のようなop演算子の使用が許容されました。以前、このような条件はビットマップインデックススキャンにおいてのみ使用することが出来ました。この理由により、これらのScalarArrayOpExprクエリにおいてインデックスオンリースキャンが確認できます。:

 select indexed_col from categories where indexed_col in (4, 5, 6);

インデックスオンリースキャンとインデックスアクセスメソッド

インデックスオンリースキャンは、実際にはbtreeインデックスのスキャンを行うことを制限しません。SP-GiSTオペレータクラスはインデックスオンリースキャンをサポートする可能性があります。

 postgres=# select amname, amcanreturn from pg_am where amcanreturn != 0;
  amname | amcanreturn
 --------+--------------
  btree  | btcanreturn
  spgist | spgcanreturn
 (2 rows)

SP-GiSTオペレータクラスは事実上のオンディスクインデックスが損失を伴うもの("lossy")であるとは限りません;明確なオペレータクラスのみで格納されたデータの完全な冗長化コピーが存在するでしょうし、それゆえインデックスオンリースキャンは実際はいくつかのSP-Gistインデックスによってのみサポートされています。追加のインデックスアクセスメソッドのサポートは確かに将来のリリースに予定されています - btree_gistやbtree_ginのようなGistとGinオペレータクラス、または9.3では"レンジオーバー四分木"オペレータクラスが損失が無く(not lossy)、インデックスオンリースキャンを原理的にサポートします。損失を伴うインデックスであっても、"select count(*)"を解くことは原理的には可能であり、将来のリリースに含まれる可能性があります。

可視性マップ（とその他リレーションフォーク）

可視性マップとは、全てのヒープリレーション（テーブル）と関連する単純なデータ構造です。それは"リレーションフォーク"と言い、特定のリレーション（テーブルまたはインデックス）と関係するディスク上の補助的なファイルとなります。インデックスリレーション（つまり、インデックス）はそれらに関連した可視性マップを持っていないことに注意してください。可視性マップは全てのトランザクションに対してどのタプルが参照されているかをハイレベルに追跡することに関心をもっています。あるトランザクションのタプルは他のトランザクションから参照可能であるとは限らず、それらの源となるトランザクションが明確にコミットされた（されない、もしくはまだされていない、トランザクションがアボートした）かどうかに依存し、我々のトランザクションのカレントスナップショットに関連のある事象が生じた時に可能となります。正確な振る舞いは我々のトランザクション分離レベルに依存することに注意してください。また、あるトランザクションにおいて、別のトランザクションがその他のトランザクションを参照したり、同一の論理タプル内のdistinct値を参照している間に、ある物理的なタプル/ある論理的なタプルの値のセットを参照する可能性は十分にあり、なぜなら、実際には、2つのトランザクションそれぞれが、"現時点(now)"の別の見解を有しているからとなります。これはMVCCのアイディアの中心となるものです。ヒープページ内のすべての物理タプル（複数の行バージョン）が参照可能という断固たるコンセンサスが存在する時は、そのページに対応するビットが参照可能とセットされる可能性があります。

もう一つの慣れ親しんでいるかもしれないリレーションフォークは空き領域マップです。可視性マップと対比して、ヒープとインデックスリレーション（ハッシュインデックスリレーションの空のものを含む）の両方のためにFSM（空き領域マップ）が存在します。

空き領域マップの用途は、格納されたタプルを保持するための十分な空き領域を伴うページを素早く特定すること、または そのようなページが存在しないことを確認したり、リレーションが拡張されるべきかを決定をすることです。

PostgreSQL 8.4において、現在の空き領域マップの実装が追加されました。空き領域マップをディスク上のリレーションフォークにしました。以前の実装は、リレーションの数を推測するために管理者権限を要求し、それぞれの空き領域マップのサイズが必要となり、空き領域マップは共有メモリに固定で確保された領域にのみ存在していました。これは、領域不足のせいで中核システムのストレージマネージャの不必要なリレーションの拡張として無駄な領域を生ずる傾向がありました。

 [peter@peterlaptop 12935]$ ls -l -h -a
 -rw-------. 1 peter peter 8.0K Sep 28 00:00 12910
 -rw-------. 1 peter peter  24K Sep 28 00:00 12910_fsm
 -rw-------. 1 peter peter 8.0K Sep 28 00:00 12910_vm
 ***SNIP***

FSM（空き領域マップ）はバイナリツリーとして構成されています[1]。ヒープページごとに1つのリーフノードが存在し、非リーフノードはその子供が持つ空き領域の最大値が格納されています。それゆえ、EXPLAINではノードコストの値が累積値でないために違う値として出力されます。

可視性マップはより単純な構造です。可視性マップと一致するヒープリレーションのそれぞれのページごとに1ビットが存在します。

可視性マップの所有と管理の主要な実践的な理由はVACUUMの最適化のためです。セットビットはヒープページに一致するすべてのタプルが全てのトランザクションに対して参照可能であると知らせることを示していることからページをVACUUMすることは不要となります。新しい空き領域マップの実装のように、可視性マップはPostgres 8.4で追加されました。

可視性マップはセットビット(1)がページ上の全てのタプルが参照可能を示すことにおいて真であることに保守的ですが、アンセットビット(1)ではそうとは限りません[2]。

障害安全性、復旧と可視性マップ

これはVACUUM中の可視性マップの範囲内において、およびリカバリ中のさまざまな特別な尺度を持つことにおいてWAL-Loggingの設定がほんの少し関係します。

Postgresの先行書込みログは障害安全性を確保するために幅広く使われていますが、ビルトインされたホットスタンバイ/ストリーミングレプリケーション機能に必要不可欠なものでもあります。

リカバリは、ページ上のXIDの参照に失敗し続けているスナップショットのリカバリコンフリクトのようなことへの対応として全て可視なページの作成を行います。PostgreSQLは将来的にこれを軟化させる可能性があることから、実装は強いコンフリクトのスローというより、問題が存在している場合にヒープがフェッチするために単純にインデックススキャンを強制しています。

カバリングインデックス

カバリングインデックスは、インデックスオンリースキャンにて使用される目的の表現のために作成されるインデックスです。これらは一般的に、あるインデックス、特定の高コストな頻繁に実行されるクエリーの選択リストの一部であるカラムに、別の意味づけを持たせたものより多くのカラムを"カバー"します。PostgreSQLはインデックスの最初の数カラムがクエリの述語に存在していたとしても通常のインデックススキャンにおいての使用をサポートしていることから、カバリングインデックスは通常のインデックススキャンにおいて完全に無用ではありません。

HOTを利用した相互作用

HOT（Heap-only tuples）はPostgres 8.3に追加された主要な性能向上機能です。この機能は、新しい物理的なヒープタプルのみ行挿入時に作成する必要があり、更新がインデックスされたカラムに影響しない場合に新たなインデックスタプルを作成せず、行（これは、PostgresのMVCCアーキテクチャに起因する、物理的なタプルの削除部分（更新前の値）と挿入部分（更新後の値）が実装されています）のUPDATEを許可します。

HOTを使用することによって、インデックススキャンの為にいわゆるHOTチェインと呼ばれるトラバースが可能となりました; 物理的なインデックスタプル（独自のINSERTによって作成されたと思われる、そして論理タプルのより早期のバージョンに関連したもの）から、一致する物理的なヒープタプルを入手できるようになりました。ヒープタプルはタプルの次のバージョンへのポインタ（タプルのctid）を、巡り巡って自身を示すポインタを持つ可能性がありますが、それ自身で格納しています。インデックススキャンはやがて実行時のクエリのスナップショットに準じたタプルに到達します。

HOTはまた、日和見的にミニVACUUMを可能とし、HOTチェインは"刈り取られ"ます。

総じて、この性能最適化はとても価値があることがわかり、特にOLTPワークロードに対して効果的です。それは、一般にインデックスされない頻繁に更新されるタプルにおいて、まったくもって自然なことです。しかしながら、カバリングインデックスの作成時の考慮を行う時、HOT更新における最大数の要求は注意深く計量してください。

クエリで利用しているそれぞれのリレーションにおけるHOT更新の全体比率をモニタすることが出来ます。

 postgres=# select n_tup_upd, n_tup_hot_upd from pg_stat_user_tables;

どのタイプのクエリがインデックスオンリースキャンを誘起させますか？

クエリがインデックスオンリースキャンを使用するために、単独のインデックスによってインデックスされていないカラムの参照ができないような明確な制限は別として、全てのタプルが参照可能かわかっていないヒープアクセスの要求は比較的高コストとなります。プランナはインデックスオンリースキャンを考慮するときはこの要素を重視して計量します。また、一般には可視性マップのビットがセットされたテーブルのタプルの容量を確保するというニーズは、おそらくインデックスオンリースキャンの実用性をまれに更新されるテーブルに対するクエリに制限することなります。

全てのビットがセットされていることは重要ではありません; インデックスオンリースキャンは、必要がある場合に"ヒープを探索する"可能性があります。インデックスオンリースキャンは、何か誤った捉え方をされ、事実 - インデックス大部分スキャンという名称がより適切かもしれません。インデックスオンリースキャンを含んだexplain analyzeは、実際にどの程度頻繁に発生するかを表示します。

 postgres=# explain analyze select count(*) from categories;
                                                                 QUERY PLAN
 ------------------------------------------------------------------------------------------------------------------------------------------
  Aggregate  (cost=12.53..12.54 rows=1 width=0) (actual time=0.046..0.046 rows=1 loops=1)
    ->  Index Only Scan using categories_pkey on categories  (cost=0.00..12.49 rows=16 width=0) (actual time=0.018..0.038 rows=16 loops=1)
          Heap Fetches: 16
  Total runtime: 0.108 ms
 (4 rows)

プランナの実行により必要だと見積もられたヒープフェッチ（または"探索"）の数量として、プランナは結局のところ、そのコストモデルにそった最も安価なプランの可能性ではないものとして、インデックスオンリースキャンが望ましくないと決定づけられます。インデックスオンリースキャンの価値は、ヒープアクセス（部分的であれば）の省略とI/Oを最小化することを許容するポテンシャルがあることです。

"count(*)"は、より高速になっていますか？

PostgreSQLの伝統的な苦情は、一般的にMySQLと比べて見劣りする（少なくともMVCCを使用せず、MyIsamストレージエンジンを利用した時）"count(*)が遅い"です。インデックスオンリースキャンは返却される行数を制限する述語の記載なしに、また、インデックスされたカラムにより並び替えられるべきタプルを特定することにインデックスの使用を強制することなしにこれらのクエリを満足させることが*可能*です。しかしながら、実際問題として特にこのような使用はしません。

プランナがクエリのトータルコストを最小化する振る舞いを決定することが重要です。データベースを使用する場合、I/Oのコストは特に優先順位が高いです。それゆえ、"述語の無いcount(*)"クエリは、インデックスがテーブルのサイズよりも著しく小さい時にのみインデックスオンリースキャンが使用されます。これは一般に、テーブルの行幅サイズがいくつかのインデックスの幅サイズに比べてよりはるかに大きい場合に発生します。

何故私のクエリではインデックスオンリースキャンが使われないのでしょうか？

VACUUMは、インデックスオンリースキャンのより頻繁な利用の促進のための積極的に振る舞いに対する特定の傾向は持ち合わせていません。VACUUM中は様々な方法でより積極的に振る舞うように設定できますが、インデックスオンリースキャンの思慮ある一連の行動の提示をより頻繁に生じさせるための明確な指定方法は明白ではありません。

プランナはインデックスオンリースキャンの検討中に一つのリレーションの可視性マップの全体を直接的に検査しません（しかしながら、エグゼキュータは実行記録を維持しているので、explain analyzeの出力にて参照できます）。しかしながら、プランナは全てに可視であると知られているページの大きさをいつも通りに計算します。

Postgres 9.2において、統計情報には全て可視であると知られているページの大きさの合算値が格納されています。pg_class.relallvisibleカラムは、どれだけの可視ページが存在するか明示されています（pg_class.relpagesの大きさによって計算された結果が表示されます）。これらの統計情報はVACUUM実行時に更新されます。 PostgeSQL 9.2にアップグレードした後は、全て可視のページ総量が大まかに現実の値と一致することを確実にするために、直ちにVACUUM ANALYZEを実行することが賢明です。

pg_stat_user_indexes.idx_scanを調査することによって（インデックスオンリースキャンとビットマップインデックススキャンを含む）インデックススキャンの数を調査することができます。カバリングインデックスが使用されていないようであれば、カバリングインデックスへの書き込みの維持処理のオーバーヘッドを利益なく本質的に費やしていることになります。そのインデックスを削除しましょう！

まとめ

インデックスオンリースキャンがいくつかのクエリで要求されるI/Oの量を大きく減少させることが可能です。明確なクエリにおいては、特にデータウェアハウスの特徴的なクエリ（例えば比較的容量の大規模な、履歴データで報告される、まれに更新されるようなデータが頻繁に要求される時）では、性能がかなり改善できます。このようなクエリはインデックスオンリースキャンによって2～12倍高速であることが実行時に観測できます。しかし、以下のことを気に留めてください：

• インデックスオンリースキャンは日和見的な性質があり、ヒープアクセスの省略を可能とする既存の状態に対するアドバンテージがあります。しかしながら、サーバはインデックスオンリースキャンを促進するようなことはなく、考慮された要求へのレスポンスにおけるカバリングインデックスの定義を除き（例えば取得した小さめのテーブルカラムのサブセットを使い、まあまあスタティックなデータに対してクエリを実行した際の不相応なI/Oの要求がpg_stat_statementsにて確認された場合）、インデックスオンリースキャンをより頻繁に生じさせるための一連の行動を推奨することは困難です。

• カバリングインデックスの作成時、HOT更新の類似的な効果が重く計量されます。テーブルの大量のHOT更新が何によって始まるのか？これは一般的な考慮点で、なぜならインデックスの作成はHOT更新の発生を阻害する可能性があり、HOT更新の数はテーブルがどれくらいスタティックであるかの合理的な良い代弁者であり（たとえば挿入、更新、削除の総数）、それゆえ任意の時点で全て可視であると知られているたいていのヒープページがどれだけ類似しているかを考慮する必要があります。

• インデックスオンリースキャンは、その不完全なコストベースモデルにそって、要求されたI/Oの総量を減少しようとプランナが推測する時にのみ使用されます。これは、インデックスがいずれの場合も使用されない（例えばどの程度選択的な述語があるかなど）、そして原則的にインデックスオンリースキャンが利用できるインデックスが利用可能であるときに、タプルの可視性に全て重く依存します。