PostgreSQL 実践講座 #4 インデックス戦略の応用 — 部分・複合・カバリング、そして GIN・BRIN

インフラ PostgreSQL データベース
読了 5分

基礎第 4 回で B-tree の基本ルール(先頭カラム、カラム加工の禁止、選択度)を立て、前回で遅いクエリを見つけるルーチンを作りました。今回はそのルーチンが持ってきたクエリに「基本のインデックスでは足りないとき」に取り出す戦略です。

部分インデックス: 条件までインデックスに #

インデックスの定義に WHERE を入れると、その条件に合う行だけが索引化されます。

部分インデックスの作成 
-- 未処理の注文だけ索引化: 全体の 1% ならインデックスも 1% のサイズ
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

典型的な居場所は、全体は大きいがクエリはいつも一部しか見ないテーブルです。注文テーブルは数億行あっても、アプリケーションが繰り返し見るのは未処理の分だけです。部分インデックスは小さいから速く、索引の外の行(完了した注文)が更新されるときこのインデックスは触る必要がないので、書き込み税まで減ります。WHERE status = 'pending' という条件のクエリだけがこのインデックスを使える、つまりクエリの条件がインデックスの条件を含んでいる必要があることだけ覚えておけば十分です。同じ構造で WHERE deleted_at IS NULL(ソフトデリートのテーブルの生きている行だけ)、UNIQUE 部分インデックス(アクティブな行にだけ一意性)も実務の定番です。

カバリングインデックス: テーブルに行かない #

基礎第 5 回の表で一番速いスキャンは Index Only Scan、つまりテーブルアクセスなしにインデックスだけで応答する形でした。条件はクエリが要求するすべてのカラムがインデックスの中にあることです。検索キーとしては不要だが結果には必要なカラムを INCLUDE で載せておくのがカバリングインデックスです。

カバリングインデックスの作成 
-- SELECT email, name FROM users WHERE email = ? をインデックスだけで
CREATE INDEX idx_users_email_covering ON users (email) INCLUDE (name);

注意点が 2 つあります。1 つ目、INCLUDE のカラムの分だけインデックスが大きくなり、そのカラムの更新のたびにインデックスも更新されるので、実測されたホットなクエリにだけ使います。2 つ目、Index Only Scan が実際に成立するかは EXPLAIN で確かめる必要があります。MVCC の構造上、「この行はすべてのトランザクションに見えるか」を記録した可視性マップ(visibility map)が古いと、結局テーブルを確認しに行くのですが、このマップを更新するのが次回の VACUUM です。インデックス戦略と VACUUM はここでつながります。

B-tree の外: GIN、GiST、BRIN #

タイプ居場所例
GIN1 つの値の中に要素が複数(ドキュメント・配列)JSONB、配列、全文検索
GiST「重なり・近さ」の問い合わせ地理データ(PostGIS)、範囲型、最近傍探索
BRIN物理的な順序と値の順序が比例する巨大テーブル時系列の created_at

GIN は基礎第 7 回で JSONB と一緒にすでに登場しました。実戦で追加で知っておくべきは BRIN です。ブロックの区間ごとに最小値・最大値だけを保存する極端に小さいインデックスで、時間順にだけ積もっていくログ・イベントテーブルの時刻カラムのように「保存の順序と値の順序がほぼ一致」するとき、B-tree の数百分の 1 のサイズで範囲検索を支えてくれます。数億行の時系列に B-tree が重いとき、思い出すべき名前です。

整理: 使われていないインデックスを見つける #

基礎第 4 回の結論が「インデックスは書き込み税」だったので、戦略には整理も含まれます。判断材料は統計ビューです。

未使用インデックスの検出 
SELECT indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0 AND NOT indisunique
ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan = 0、つまり統計の収集以降一度も読まれていないインデックスが候補です。2 つだけ気をつけます。UNIQUE インデックスは検索ではなく制約のために存在するので除外し(上のクエリの NOT indisunique)、月末バッチのようにまれに使うクエリのためかもしれないので、十分に長い期間の統計で判断します。レプリケーション構成なら、読み取りの負荷がスタンバイ側にあるかもしれない点も頭に入れておきます(実践第 8 回)。

最後に肥大化(ブロート)です。更新の多いテーブルのインデックスは、MVCC の dead tuple の痕跡でだんだん膨らみ、同じ仕事により多くの読み取りが必要になっていきます。再構築はサービスを止めない REINDEX INDEX CONCURRENTLY idx_name; でやります(実践第 1 回の CREATE INDEX CONCURRENTLY と同じ系統です)。ブロートがなぜ生まれ、普段どう抑えるのか、その本体が次回の VACUUM です。

まとめ #

  • 部分インデックスは「いつも一部しか見ない」大きいテーブルの正解です。小さく、速く、書き込み税まで減らします。
  • カバリングインデックス(INCLUDE)は Index Only Scan を狙います。成立するかどうかは EXPLAIN と可視性マップ(VACUUM)次第です。
  • B-tree の外の選択肢は居場所で覚えます。ドキュメント・配列は GIN、重なり・近さは GiST、巨大な時系列は BRIN です。
  • 使われていないインデックスは pg_stat_user_indexes の idx_scan で見つけて整理します。UNIQUE とまれなバッチ用だけ気をつけます。
  • 膨らんだインデックスは REINDEX CONCURRENTLY で再構築します。膨らむ原理と予防が次回の主題です。
X