データベースインデックス

どのようなものか

データベースインデックスは、テーブルとは別に、そのそばへ保存する構造
です。データベースが、最初から最後まで全行を読む代わりに、条件に合う
行へ直接進めるようにします。普段の会話でも、PostgreSQLの文書の大半で
も、単にインデックスと呼びます。文書は、本の巻末の索引にたとえていま
す。ある話題を探す読者は、本全体をめくる代わりに、索引を調べ、そこに
載るページを開きます。

注文テーブルと、`WHERE customer_id =
42`という条件を考えます。`customer_id`にインデックスがなければ、
PostgreSQLの答え方は、シーケンシャルスキャンです。すべての行を読み、
合う行を残します。その顧客の注文が二件でも二千件でも、コストは同じ
で、テーブルが大きくなるほど増えます。列にインデックスがあれば、検索
木の数階層をたどって42の項目へ進み、その行だけを取得します。作成は一
つの文で済み、`DROP
INDEX`で削除でき、どちらもいつでも行えます。

インデックスを作ると、PostgreSQLは、書き込みのたびに、テーブルと一致
するように保ちます。クエリープランナーは、クエリーごとに、インデック
スを使う方が走査より処理コストが低いかを判断します。SQL内でインデッ
クスの名前を指定する必要はないので、一つのインデックスが、`SELECT`、
検索条件のある`UPDATE`や`DELETE`、結合を速くできます。

PostgreSQLには、問いの形に合わせて作った、複数の種類があります。

  * B-treeは既定の種類で、別の指定がなければ、`CREATE
INDEX`が作ります。等値と範囲の比較（`<`、`<=`、`=`、`>=`、`>`）、
`BETWEEN`、`IN`、`IS
NULL`、先頭が固定されたパターンを使う`LIKE`に答えます。項目を順番に
保持するので、行を整列済みで返すこともできます。

  * Hashは、各値の32ビットのハッシュコードを保存し、等値だけに答えま
す。

  *
GiSTとSP-GiSTは、幾何データなどを扱う枠組みです。「等しい」ではな
く、「重なる」「含む」「最も近い」といった問いに使います。

  * GINは、配列など、多数の部分からなる値向けの転置インデックスで、
指定した要素を含む値を持つ行を探します。

  *
BRINは、テーブルのブロックの範囲ごとに、要約だけを保持します。挿入時
刻のタイムスタンプのように、値が行の物理的な順番に沿う列に向いていま
す。

種類に加え、問いに合わせて形を変えられます。一意インデックスは、重複
値を拒否します。複合インデックスは、最大32の列を対象にします。部分イ
ンデックスは`WHERE`句を持ち、それを満たす行だけを保持します。式イン
デックスは、`lower(email)`のような計算結果を保存します。`INCLUDE`リ
ストは、検索には使わない追加の列をインデックスに載せ、インデックスだ
けでクエリーへ答えられるようにします。

なぜ重要なのか

インデックスの効果は、テーブルとともに大きくなります。小さなテーブル
なら走査のコストが低く、プランナーがそちらを選ぶのは、しばしば正しい
判断です。数ページを順番に読む方が、それを得るために木をたどるより処
理コストが低いためです。数千万行になると、全走査と索引検索の差は、
ページが表示されるか、タイムアウトするかの差になります。公開時は速
かったクエリーが、一行もコードを変えずに、一年後には遅くなることがあ
ります。読むテーブルが大きくなっただけでも起こります。

インデックスには代償があり、その負担は書き込みに現れます。挿入のたび
に、そのテーブルのすべてのインデックスへ項目を追加します。更新でそれ
を避けられるのは、heap-only
tuple（HOT）更新の場合だけです。新しい行バージョンが同じページにとど
まり、既存のインデックス項目から、その行へ到達できます。PostgreSQLが
それを認めるのは、テーブルのインデックスが使う列を、更新が一つも変え
ない場合だけです。ただし、要約しか持たないBRINインデックスは除きま
す。インデックスを一つ増やすごとに、低コストで処理できる条件が狭くな
ります。そのため、文書は、ほとんど使わない、またはまったく使わないイ
ンデックスの削除を勧めます。十二のインデックスを持つテーブルは、挿入
ごとに十二個分の更新コストを負担します。

作成にも処理コストがかかります。普通の`CREATE
INDEX`では、ほかのセッションからテーブルを読めますが、作成が終わるま
で書き込みをブロックします。非常に大きなテーブルでは、何時間もかかり
得ると、文書は警告しています。

最後に、インデックスは、プランナーが使うと判断してこそ役立ちます。判
断には、`ANALYZE`が集めた統計を使います。問うべきなのは、「インデッ
クスがあるか」ではなく、「重要なクエリーが使っているか」です。それを
知る方法が、`EXPLAIN`です。

実際の活用例

オンラインショップのアカウントページが、顧客の直近二十件の注文を表示
します。

```sql
SELECT id, created_at, total
FROM   orders
WHERE  customer_id = 42
ORDER  BY created_at DESC
LIMIT  20;
```

`id`の主キーしかなければ、PostgreSQLは、`orders`全体を読み、顧客の行
を残し、並べ替え、二十件以外を捨てます。`EXPLAIN`には、それが現れま
す。`orders`の`Seq
Scan`に条件が`Filter`として付き、その上にソートがあります。`EXPLAIN
ANALYZE`は、クエリーも実行し、推定値の横に、実際の行数と所要時間を表
示します。

この問いに合うインデックスは、等値条件の列を先、並べ替えの列を次に置
きます。

```sql
CREATE INDEX CONCURRENTLY orders_customer_created_idx
ON orders (customer_id, created_at);
```

もう一度`EXPLAIN`を実行すると、走査は、`orders_customer_created_idx`
を使うインデックススキャンになり、`customer_id
i= 42`は、フィルターではなく、`Index		engineer.company	70
Cond`として現れます。ソートもなくなります。B-treeは、顧客42の項目を
`created_at`順に保ちます。PostgreSQLは、逆向きに読んで新しいものを先
に取得でき、`LIMIT`があれば、残りに触れず、二十項目で止まります。

列の順番は重要です。同じインデックスは、先頭列の`customer_id`だけを
使うクエリーにも対応します。しかし、昨日の全注文を調べ、`created_at`
だけで絞るレポートには、はるかに効果が小さくなります。複合B-treeで、
読む部分を狭めるのは、先頭からの列の等値条件と、その次の列の範囲条件
だからです。PostgreSQL
18は、先頭列の異なる値ごとに検索を繰り返す、スキップスキャンを使えま
す。ただし、プランナーが通常それを選ぶのは、先頭列の値の種類が少ない
場合だけで、顧客IDには多くの種類があります。重要なレポートなら、
`created_at`に専用のインデックスを作ります。`(created_at,
customer_id)`と逆にすると、顧客42の注文が、インデックス全体へ散らば
ります。

シャーディングする前にデータベースを最適化するというノートは、
`EXPLAIN (ANALYZE,
BUFFERS)`で負荷の高いクエリーの走査を特定した後、効果の順に並べた改
善策の先頭に、インデックスを置きます。例は、まだ販売中の商品単位だけ
を保持する部分インデックス、`units_unsold`です。販売が進むにつれて、
大きくなるのではなく、小さくなります。ノートは、その代償も数えていま
す。対象のテーブルでは、`reserved`、`reserved_at`、`order_id`を設定
する2,000回の予約取得は、すべてHOT更新でした。一方、部分インデックス
が読む列の`sold`を設定する2,000回の更新には、HOT更新が一つもありませ
んでした。`pg_stat_user_indexes`内の`idx_scan`がゼロのままのインデッ
クスは、削除することを勧めています。

よりよいキーの選択という事例は、B-treeへ入る値が、列と同じくらい重要
な理由を示します。ランダムなUUIDは、インデックス全体に挿入を散らし、
断片化を引き起こすことがあります。プラットフォームは、エンティティー
の主キーの既定を、時刻順のUUID
v7にしました。先頭のビットにタイムスタンプを持たせ、新しい識別子が時
刻の順序を持つようにしています。断片化の軽減とクエリーの高速化は、設
計上の狙いでした。記録された結果は、キーの規約を設定したことで、この
事例からは、実際の負荷を使った変更前後の性能比較は確認できません。

問題が起きるところ

インデックスの追加で、テーブルの書き込みが止まります。 書き込みの多
いテーブルで普通の`CREATE
INDEX`を実行すると、作成中ずっと、挿入、更新、削除をブロックし、定例
のデプロイがサービス停止になります。`CREATE INDEX
CONCURRENTLY`を使います。テーブルを二回走査し、インデックスを使い得
るトランザクションを待つので、大幅に時間が増えますが、書き込みは続け
られます。トランザクションブロック内では実行できません。各段階をトラ
ンザクションで包む移行ツールには、この文だけを単独で実行するように指
定する必要があります。一つのテーブルで、同時に実行できる並行作成は一
つだけです。

並行作成に失敗し、無効なインデックスが残ります。 デッドロックや、一
意インデックスの作成中の重複値によって、`CREATE INDEX
CONCURRENTLY`が失敗します。しかし、作り始めたインデックスは、無効と
印を付けたままカタログに残ります。クエリーは無視しますが、すべての書
き込みで、それを維持する処理コストがかかり続けます。無効な一意イン
デックスは、一意性を強制し続ける場合もあります。psqlの`\d`は、定義の
後ろに`INVALID`と表示します。削除して並行作成をやり直すか、`REINDEX
INDEX CONCURRENTLY`で再作成します。

クエリーが、列を関数の中に隠します。 `email`のインデックスは、
`WHERE lower(email) =
'ana@example.com'`には役立ちません。保持しているのは`email`で、
`lower(email)`ではないためです。クエリーが使う式に、インデックスを作
ります。

```sql
CREATE INDEX users_lower_email_idx ON users (lower(email));
```

式は、挿入のたびと、HOTではない更新のたびに計算します。そのため、文
書は、書き込み速度より取得速度が重要な場所で、式インデックスを勧めて
います。

誰も使わないインデックスが、積み重なります。
「念のため」に足すたびに、すべての書き込みに処理コストがかかり、HOT
更新を不可能にする場合もあります。`pg_stat_user_indexes`は、各イン
デックスで始めた走査を`idx_scan`で数え、最後の走査時刻を
`last_idx_scan`へ記録します。

```sql
SELECT relname, indexrelname, idx_scan, last_idx_scan
FROM   pg_stat_user_indexes
WHERE  idx_scan = 0
ORDER  BY relname;
```

削除する前に、確認します。主キーや一意制約を支えるインデックスは、ク
エリーから一度も読まれなくても、役割を果たします。また、トラフィック
を受け始めたばかりのサーバーでは、ゼロという数値に大きな意味はありま
せん。

プランナーが、よいインデックスを無視します。
大量読み込みの後は、統計がデータに追い付かず、推定が外れ、計算上は全
走査が有利になります。`ANALYZE`を実行します。文書は、それなしでイン
デックスの利用を評価することを、「見込みのない試み」と呼びます。現実
的な量でもテストします。ディスクの一ページに収まる百行なら、そのペー
ジを読むより速いインデックスはありません。そもそも使えるかを知るに
は、テスト用のセッションで`enable_seqscan
i= off`を設定し、`EXPLAIN`を再実行します。それでも走査する計画なら、		engineer.company	70
i条件がインデックスに合っていません。		engineer.company	70

参考資料

  * インデックス：概要 — インデックスの用途、作成と削除、書き込みご
とに加わる負担です。

  * インデックスの種類 — B-tree、Hash、GiST、SP-GiST、GIN、BRINと、
それぞれが答えられる演算子です。

  * 複合インデックス — B-treeで絞り込める範囲を列の順番がどう決める
かと、スキップスキャンが適用される場合です。

  * CREATE INDEX —
構文全体です。`CONCURRENTLY`、`INCLUDE`、部分インデックス、無効に
なった作成からの回復も含みます。

  * EXPLAINの利用 — 走査の種類、推定コスト、`EXPLAIN ANALYZE`の実測
値など、実行計画の読み方です。

  * インデックス利用の確認 — `ANALYZE`と現実のデータを先に用意する理
由と、インデックスを使えるかを試す方法です。

hこのページのウェブ版	URL:https://engineer.company/ja/glossary/database-index/	engineer.company	70
