観測・診断図鑑 · 基礎から応用へ

PostgreSQLの索引使用状況を読む

索引統計の観測期間と単位を確認し、検索・更新・制約・まれな業務を含めて必要性を判断します。

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

更新日

この記事の目次
  1. 使用回数ゼロだけで削除しない
  2. 観測窓と制約の役割を確認する
  3. 走査数・行数・サイズを分ける
  4. 代表クエリの計画と費用を比べる
  5. この記事をロゲイアに質問する理解を確かめる

使用回数ゼロだけで削除しない

PostgreSQLの索引使用統計は、どの索引が検索に使われているかを調べる手掛かりです。pg_stat_user_indexesのidx_scanが小さいことは候補を絞る材料になりますが、不要だという結論ではありません。観測期間に少ない月次処理や障害対応の問い合わせで重要かもしれません。主キーや一意制約を支える索引には、通常の検索回数とは別の整合性の役割もあります。最初に対象DB、表、索引の定義、制約、統計の期間を確認し、数字を実際の用途へ結び付けます。

観測窓と制約の役割を確認する

例えば一週間の通常処理ではidx_scanがゼロでも、月末の集計で必要な索引はあり得ます。逆に使用回数が多い索引でも、返す行が多すぎて費用が大きい場合があります。この例は運用の考え方を示すもので実測値ではありません。統計リセットやクラッシュ、監視開始の時期、レプリカ側の負荷を確認します。プライマリの一つの観測だけでは、別ノードで使われる索引の価値を判断できません。変更前に、業務周期を含む観測窓を用意します。

sql
SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch,
       pg_relation_size(indexrelid) AS index_bytes
FROM pg_stat_user_indexes ORDER BY idx_scan, index_bytes DESC;

走査数・行数・サイズを分ける

idx_scan、idx_tup_read、idx_tup_fetchは同じ単位ではなく、走査と索引エントリ、ヒープ行の取得を区別します。bitmap scanやindex-only scanなどの実行方式によっても対応は単純な一対一になりません。サイズはpg_relation_sizeで追加して読めますが、大きい索引だから不要、小さい索引だから無料ということでもありません。書き込み時の更新、WAL、キャッシュ占有などの費用を、読み取りの利益と合わせて評価します。単一のヒット率へまとめると、制約や処理方式の違いが見えなくなります。

代表クエリの計画と費用を比べる

代表的な条件の実行計画と、実際の要求の頻度・遅延を調べ、索引の有無や定義変更の候補を作ります。EXPLAIN ANALYZEはSQLを実行するため、変更や負荷のある文には適切な検証環境を使います。本番で索引を削除する前には、制約や依存、再作成に必要な時間とI/O、ロック、復旧方法を確認します。統計は判断材料であり、未使用の候補を自動でDROPする許可ではありません。採用後も同じ仕事と観測期間で読み取りと書き込みの両方を比べ、負担が他へ移っていないか確かめます。

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

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

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

学習コースから次を選ぶ