Zenn
🎃

PostgreSQLにおけるインデックスの内部構造と最適化

に公開
PostgreSQL
DB
tech

✅ はじめに

最近パフォーマンス改善でインデックスについて改めて学ぶ機会があったので内容を紹介します。

インデックスはデータベースの検索性能を飛躍的に向上させる基本技術です。PostgreSQLではB-treeをはじめとする多彩なインデックスタイプが提供されており、状況に応じて適切に使い分けることでクエリ性能を最適化できます。

この記事では、PostgreSQLにおけるインデックスの基本、利点、注意点、そして設計のベストプラクティスについて実践的に解説します。

🔍 インデックスとは？

インデックスは、特定の列に対する検索を高速化するためのデータ構造です。一般に使われるインデックスはB-tree型で、範囲検索や一致検索に最適化されています。

-- 名前にインデックスを作成
CREATE INDEX idx_users_name ON users(name);

🌲 B-treeインデックスとは？

B-tree（Balanced Tree）インデックスは、PostgreSQLで最も一般的なインデックスタイプで、次のような特徴を持ちます：

  • 階層的な木構造（通常は高さ2〜3）で、データをソート済みで格納
  • ノードは「キー」と「TID（テーブル内行位置）」のペアを持つ
  • ルート → 内部ノード → リーフノード の構造で、探索は高速かつ効率的
  • キーの挿入・削除時にもバランスが保たれる（自動的に再編成）

B-tree探索のイメージ

         [M]
        /   \
     [C G]  [R W]
    /  |  \    |  \
  ... ... ... ... ...
  • Mより小さい → 左へ、M〜R → 中央へ、Rより大 → 右へ
  • リーフノードに到達したら、そこからTIDを元に実データを取得

向いている検索

  • = での一致検索
  • <, <=, >, >= での範囲検索
  • BETWEEN, ORDER BY, IS NULL など

向いていないケース

  • ワイルドカード検索の %abc（LIKEの後方一致）
  • JSON/配列型の部分一致（→ GIN/GiST）

✅ PostgreSQLのインデックスタイプ

タイプ 用途
B-tree 一致検索・範囲検索（デフォルト）
Hash 厳密一致のみ（=）
GIN 配列、全文検索（tsvector, jsonb）
GiST 幾何データ、類似検索、全文検索
BRIN 巨大なテーブルでの範囲スキャン（列の値が物理順と近いとき）

⚙️ インデックスの仕組みと使用される演算子

インデックスが検索性能を向上させる仕組みは、「全行を走査するのではなく、あらかじめソート・構造化されたデータ構造を探索する」ことで無駄な読み込みを減らすことにあります。

たとえば、B-treeインデックスは以下のように動作します：

  1. インデックスに登録された値が順序付きで保持される（木構造）
  2. 検索条件にマッチするキーがインデックス上で探索される
  3. 対応するテーブルの実体データ（ヒープ）への参照（TID）が見つかる
  4. 必要な行だけをテーブルから読み出す

🔍 インデックススキャンの種類

スキャン方法 説明
Index Scan インデックスで一致または範囲条件を満たすキーを探索し、必要な行を1件ずつ取得
Bitmap Index Scan 一括で複数の一致行の位置（TID）を取得し、まとめて読み込む（OR条件などに最適）
Index Only Scan インデックスに必要な列データが全て含まれている場合、テーブル本体を読まずにインデックスだけで完結

🔧 インデックスを使う判断基準（詳細）

  • 統計情報（ANALYZE で更新）に基づくコスト見積もり
    PostgreSQL は pg_statistic テーブルに格納された統計情報を元に、「インデックススキャン」と「シーケンシャルスキャン」のどちらが低コストかを推測します。統計情報には以下のような内容が含まれます：

    • テーブルの行数
    • カラムの値の分布（選択性）
    • NULLの割合
    • 最頻値やMCV（most common values）

    これらの情報をもとに、インデックスの使用可否をコストベースオプティマイザが自動で判断します。

    ANALYZE や VACUUM ANALYZE を通して統計情報を更新しないと、誤った実行計画になることがあります。

  • 条件に一致する行数（選択性）が少ないと判断された場合
    "選択性（selectivity）" とは、ある検索条件に合致する行数の割合です。たとえば、100万件中10件しかマッチしない条件なら選択性は非常に高く、インデックスが有効です。

    一方で、数万件がマッチするような低選択性の条件では、インデックスよりも全件走査（シーケンシャルスキャン）のほうが速いと判断されることもあります。

    特に、ブール値や性別（M/F）のように値のバリエーションが少ない列には注意が必要です。

  • インデックススキャンのほうがシーケンシャルスキャンよりも高速と予測される場合

このような仕組みを踏まえて、適切なインデックス設計を行うことがクエリ最適化の鍵になります。

PostgreSQLのインデックスは、問い合わせにおいて検索条件に使われる演算子や関数との組み合わせで利用されるかどうかが決まります。

🔧 インデックスが使われる基本条件

  • WHERE 句にインデックス対象の列が含まれる
  • 対応するインデックスタイプと演算子の組み合わせであること
  • PostgreSQLがコストベースでインデックスを選択すると判断した場合

主なB-treeインデックス対象の演算子

演算子 内容
= 等価比較（最も基本的）
<, <=, >, >= 範囲検索（昇順/降順対応）
BETWEEN 範囲指定
IS NULL / IS NOT NULL B-treeではNULLも扱える
LIKE 'abc%' 前方一致のみに対応（ワイルドカード後方は不可）

演算子がインデックスに効かない例

クエリ例 理由
LOWER(name) = 'john' 関数をかけるとインデックスが無効化される（関数インデックスで対応）
LIKE '%abc' 前方ワイルドカードはB-tree対象外
CAST(age AS TEXT) = '30' キャスト後の比較は通常インデックス非活用

対応策：関数付きインデックス

-- 小文字化されたnameに対してインデックスを作成
CREATE INDEX idx_users_lower_name ON users (LOWER(name));

🚀 インデックスの利点

利点 説明
高速検索 WHERE、JOIN、ORDER BY によるデータ検索が高速化
重複防止 CREATE UNIQUE INDEX により一意性を保証可能
統計情報改善 PostgreSQLのオプティマイザがより良い実行計画を選べるようになる
デッドロック回避 UPDATE対象をインデックスで絞れるとロック競合が減る

⚠️ インデックスの注意点

注意点 内容
書き込み性能の低下 INSERT/UPDATE/DELETE時にインデックスも更新される
ディスク使用量増加 インデックスの数に応じてサイズが膨らむ
過剰なインデックス 無意味なインデックスは逆にパフォーマンスを悪化させる
ANALYZE の偏り インデックスで偏ったデータにアクセスし、統計が歪むことも

🧠 インデックス設計の実践

  • 頻繁に検索される列・JOINに使われる列に付与する
  • インデックスの順序はクエリの ORDER BY や WHERE と一致させる
  • 複数列のAND検索 → 複合インデックスが有効
-- 複合インデックス（customer_id, order_date）
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);
  • 頻度の低いカラムや低選択性カラムには慎重に
  • 不要になったインデックスは DROP INDEX で削除

🛠 インデックスのメンテナンスと確認

使用状況の確認

SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0;

→ 使用されていないインデックスの発見に有効

実行計画の確認

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'a@example.com';

→ Index Scan と表示されていればインデックスが利用されている

✅ まとめ

  • インデックスは検索性能を大きく向上させるが、書き込み性能やストレージに注意
  • PostgreSQLでは多様なインデックスタイプがあり、用途に応じた選択が重要
  • 定期的なメンテナンス・監視・見直しが健全なDB運用に不可欠

Discussion