注意: この記事は特定のスキーマ・データ量・クエリパターンでの検証結果をまとめたものです。インデックスの効果はテーブル設計やワークロードによって大きく変わるため、あくまで一例として参考にしてください。実際のプロダクトでは自身の環境で計測することを推奨します。
この記事でやること
「インデックスを貼れば SELECT は速くなる」というのはよく知られた話ですが、自分が未経験だった頃は、ただ貼ればいいというのであれば、全カラムにindexを貼ればいいのでは？と考えたりしたことがあります。
ですが、インデックスを貼ると同時にデメリットももちろん存在します。
具体的に言えばINSERT / UPDATE が遅くなるコストと、ストレージ容量の増加です。
ただ、一概にデメリットがあると言えど、
- どのくらい速くなるのか？
- どのくらい遅くなるのか？
- インデックスはストレージをどれだけ食うのか？
このあたりはいつも曖昧なままインデックスを貼ってしまう事が多いので、
本記事ではこれらを同じデータ・同じクエリで計測して、インデックスを貼る/貼らないの判断材料を整理することを目的とします。
検証環境
| 項目 | 値 |
|---|---|
| DB | PostgreSQL 18 (Docker) |
| ORM | Prisma v6.19 |
| 計測回数 | 15回 (ウォームアップ3回) |
| バッチサイズ | 1,000件/回 |
スキーマ (抜粋)
model BenchmarkRecord {
id Int @id @default(autoincrement())
tenantId Int
status Int // 0 or 1 (低選択性)
score Int
email String // ユニークに近い (高選択性)
payload String
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}
比較する 3 シナリオ
| シナリオ | 内容 |
|---|---|
| baseline | 主キー (id) のみ。インデックスなし |
| selective_indexes | 選択性が高い列だけにインデックス (email, tenantId + status) |
| over_indexed | 上記に加え、低選択性の列にもインデックス (status, score, createdAt) |
計測するクエリ
| クエリ | 内容 | 想定 |
|---|---|---|
| pointReadByEmail | email で 1件取得 | 高選択性 READ |
| selectiveReadByTenantStatus | tenantId + status で COUNT | 複合キー READ |
| lowSelectivityReadByStatus | status だけで COUNT | 低選択性 READ |
| selectiveUpdateByTenantStatus | tenantId + status で UPDATE | インデックス列の更新 |
| createMany | 1,000件バッチ INSERT | 書き込みコスト |
結果
20,000 行
| クエリ | baseline | selective | over_indexed |
|---|---|---|---|
| pointReadByEmail | 0.665ms | 0.212ms (3.1x) | 0.247ms (2.7x) |
| selectiveReadByTenantStatus | 0.894ms | 0.196ms (4.6x) | 0.217ms (4.1x) |
| lowSelectivityReadByStatus | 1.012ms | 0.647ms (1.6x) | 0.649ms (1.6x) |
| selectiveUpdateByTenantStatus | 2.917ms | 1.688ms (1.7x) | 2.327ms (1.3x) |
| createMany | 15.091ms | 14.466ms (1.0x) | 16.183ms (0.93x) |
100,000 行
| クエリ | baseline | selective | over_indexed |
|---|---|---|---|
| pointReadByEmail | 2.654ms | 0.216ms (12.3x) | 0.267ms (9.9x) |
| selectiveReadByTenantStatus | 4.410ms | 0.360ms (12.3x) | 0.367ms (12.0x) |
| lowSelectivityReadByStatus | 5.066ms | 2.812ms (1.8x) | 3.266ms (1.6x) |
| selectiveUpdateByTenantStatus | 8.186ms | 2.473ms (3.3x) | 5.174ms (1.6x) |
| createMany | 15.869ms | 15.662ms (1.0x) | 18.650ms (0.85x) |
当たり前ですが、データ量が増えるほどインデックスの効果が大きくなるのが顕著です。email の pointRead は 20K 行で 3.1 倍、100K 行で 12.3 倍と、データ量に応じて差が開いています。
ちょっと意外だったのはselectiveとover_indexedでpointReadByEmailの読み取りが悪化していることです。計測回数による誤差の範囲かもしれませんが、インデックスを増やせば READが速くなるとは限らない可能性はありそうです。
インデックスのストレージコスト
レイテンシだけでなく、インデックスがどれだけディスクとメモリを消費するかも見ておきます。
計測前の私の勝手なイメージですが、テーブルが単純に1つ増えるので2倍近く（1.5~1.7？）に膨らむんじゃないかと予想していました。
100,000 行での BenchmarkRecord のサイズ
| シナリオ | テーブル本体 | インデックス合計 | 合計 | Index / Table |
|---|---|---|---|---|
| baseline | 21.4 MB | 2.2 MB | 23.6 MB | 10.1% |
| selective | 22.7 MB | 14.2 MB | 36.9 MB | 62.6% |
| over_indexed | 21.5 MB | 15.8 MB | 37.3 MB | 73.7% |
baseline でもPKインデックス分の 2.2 MB が存在しますが、selective ではテーブル本体の 62% 、over_indexed では 74% にまでインデックスが膨らんでいます。
大体予想通りでしたね。
個別インデックスのサイズ (100K 行)
| インデックス | 対象列 | サイズ |
|---|---|---|
| idx_br_email | email (文字列) | 11.2 MB |
| idx_br_score | score (整数) | 1.3 MB |
| idx_br_status | status (整数, 低選択性) | 984.0 kB |
| idx_br_tenant_status | tenantId + status (複合) | 904.0 kB |
| idx_br_created_at | createdAt (タイムスタンプ) | 816.0 kB |
文字列カラム (email) のインデックスは整数カラムの 約10倍 のサイズです。インデックスはメモリ (shared_buffers) にキャッシュされるため、不要なインデックスは他のデータのキャッシュを圧迫します。
インデックスを貼る判断基準
以上を踏まえると、シンプルな判断式は以下の通りです。
インデックスを維持する条件:
読み取り改善量 (ms) × 読み取り頻度 > 書き込み劣化量 (ms) × 書き込み頻度
はい、そうですね。当たり前の計算式になってしまいますね。
たとえば 100K 行で selective_indexes の場合:
- pointReadByEmail の改善: 2.654 - 0.216 = 2.438ms/回
- createMany の劣化: 15.662 - 15.869 = ほぼ 0ms/回 (誤差範囲)
この場合、read が 1回でもあれば元が取れます。
一方、over_indexed の lowSelectivityReadByStatus:
- 改善: 5.066 - 3.266 = 1.8ms/回
- createMany の劣化: 18.650 - 15.869 = 2.78ms/回
write 1回あたりの劣化が read 1回あたりの改善を上回っています。read/write 比率が 2:1 以上でないとこのインデックスは割に合いません。さらに status の選択性が低いことを考えると、このインデックスは不要と判断できます。
まとめ
今回の検証で、インデックスの READ 改善効果と WRITE のペナルティを具体的な数字で確認してみました。
個人的には、READ の改善幅が想像以上に大きかったのが印象的でした。100K 行で 12 倍というのは、貼らない理由を探す方が難しいレベルです。
一方で INSERT / UPDATE のペナルティは、selective であれば正直あまり気にならない程度でした。
それよりも、インデックスを増やしたときのストレージの膨らみ方の方が気になりました。over_indexed ではテーブル本体の 74% に相当するサイズがインデックスに使われており、これがキャッシュ効率やディスク消費に効いてくると考えると、WRITE の数ms よりもこちらの方が実運用上のコストとしては大きいかもしれないです。
おまけ: PostgreSQL 16 vs 18 の比較
同じスクリプト・同じスキーマで PostgreSQL 16 でも計測していたので、100K 行 / selective_indexes の結果を並べてみます。
| クエリ | PG16 | PG18 | 変化 |
|---|---|---|---|
| pointReadByEmail | 0.385ms | 0.216ms | 44% 速い |
| selectiveReadByTenantStatus | 0.498ms | 0.360ms | 28% 速い |
| lowSelectivityReadByStatus | 4.149ms | 2.812ms | 32% 速い |
| selectiveUpdateByTenantStatus | 3.493ms | 2.473ms | 29% 速い |
| createMany | 20.851ms | 15.662ms | 25% 速い |