実験・実装 · 基礎から応用へ

Index-only scanと可視性マップを観察する

被覆索引だけではヒープ参照を省けない理由を、更新前後のHeap Fetchesと可視性の条件から調べます。

この記事をロゲイアに質問する確認問題3問に進むこれまでの誤答を復習する

更新日

この記事の目次
  1. 索引に値があることと読めることを分ける
  2. 実験用の表と被覆索引を用意する
  3. 三つの実行計画を同じ条件で取る
  4. Heap FetchesとBUFFERSを読み分ける
  5. 更新後にビットが使えない条件を考える
  6. 索引を増やす判断へ結び付ける
  7. この記事をロゲイアに質問する理解を確かめる

索引に値があることと読めることを分ける

PostgreSQLのindex-only scanは、必要な列の値を索引から取り出せる場合に選択肢となります。しかしMVCCによって、そのタプルが今回の読み取りから見えるかも確かめる必要があります。可視性マップのall-visibleビットが利用できるページでは、ヒープを見に行く費用を省ける場合があります。この実験は、その条件を更新前後で比較する手順です。以下はPostgreSQL18用の再現手順であり、ここに実測した実行計画は掲載していません。専用の使い捨てローカルDBで試し、本番の表へ適用しないでください。

実験用の表と被覆索引を用意する

一つのセッションで一時表を作り、2万行を入れます。索引のキーはid、付加列はpayloadです。INCLUDEした列は検索のキーではなく、必要な値を索引に保持するためのものです。payloadを問い合わせから除く別実験と混ぜないようにします。fillfactorはページに空きを残す設定ですが、後述の更新でヒープ参照が何回になるかを固定するものではありません。表が小さすぎると逐次走査が有利になる場合もあるため、行数や統計を記録して、計画を見ずに結果を断定しないようにします。

三つの実行計画を同じ条件で取る

最初にVACUUMとANALYZEを行い、同じ範囲のidからpayloadを読む計画を取ります。次に、その範囲を更新して同じ問い合わせを実行し、最後に再度VACUUMして比較します。VACUUMは明示的なトランザクションブロックの外で実行してください。EXPLAIN ANALYZEは実際に問い合わせを実行します。この例はSELECTですが、同じ習慣で更新文へANALYZEを付けるとデータが変わるので、観察コマンドなら全て読み取り専用だとは考えないでください。出力のノード名とHeap Fetchesを対応させて記録します。

PostgreSQL 18 SQL — procedure
-- PostgreSQL 18: use a disposable local database and one session.
CREATE TEMP TABLE lab_visibility (id integer, payload text) WITH (fillfactor=70);
INSERT INTO lab_visibility SELECT i, repeat('x', 40) FROM generate_series(1, 20000) AS i;
CREATE INDEX lab_visibility_cover ON lab_visibility(id) INCLUDE(payload);
VACUUM (ANALYZE) lab_visibility;
EXPLAIN (ANALYZE, BUFFERS) SELECT payload FROM lab_visibility WHERE id BETWEEN 100 AND 110;
UPDATE lab_visibility SET payload=repeat('y',40) WHERE id BETWEEN 100 AND 110;
EXPLAIN (ANALYZE, BUFFERS) SELECT payload FROM lab_visibility WHERE id BETWEEN 100 AND 110;
VACUUM (ANALYZE) lab_visibility;
EXPLAIN (ANALYZE, BUFFERS) SELECT payload FROM lab_visibility WHERE id BETWEEN 100 AND 110;

Heap FetchesとBUFFERSを読み分ける

Heap Fetchesはindex-only scanが可視性確認などのためにヒープを参照した件数の指標です。BUFFERSに出るshared hitやreadと同じ単位ではありません。ヒープ参照があっても、そのページが共有バッファへ既に載っていれば物理ディスクから読むとは限りません。反対にHeap Fetchesが少ないことだけで、問い合わせ全体が必ず速いとも言えません。返す行数、索引や表のページ数、キャッシュ状態も影響します。index-onlyというノード名が出たからHeap Fetchesは常に0、という読み方をしないことが、この手順の中心です。

更新後にビットが使えない条件を考える

ページの内容が更新されると、以前のall-visibleという情報をそのまま使えなくなります。VACUUMが走っても、古いスナップショットが必要とする版などの条件によって、期待した状態へ直ちに変わるとは限りません。この一時表の手順と、多数の接続がある通常の表では、保持される版や自動VACUUMの状況も異なります。結果が説明例と違うときは、別セッションの長いトランザクション、現在の計画、統計、実行した更新の対象を確認します。数字を合わせるために本番の設定を変更しないようにします。

索引を増やす判断へ結び付ける

被覆索引は表の参照を減らせる場合がありますが、保持する値が増えると索引が大きくなり、更新や保存の費用も増えます。頻繁に変わるpayloadをINCLUDEすることが、読み取り改善に見合うかは実際のワークロードで評価します。この実験から言えるのは、必要な値の所在と可視性の確認が別の条件だという点です。索引を一つ追加すれば全問い合わせが速くなる、という結論にはなりません。検証後は一時表を置いたセッションを終了し、取得した計画と設定だけを再現用の記録として残します。

読了と問題の結果を、言語ごとにこのブラウザへ保存します。

コースの順番に沿って学ぶ

サーバーの中で起きていること · STEP 12

学習コースから次を選ぶ