データベースの規模が大きくなるにつれて、特定のデータを検索する速度は急激に低下します。数百万件ものレコードから一件のデータを取得する際、インデックスがない状態ではすべての行を端からスキャンする**フルスキャン（Seq Scan）**が発生し、膨大なディスクI/Oを消費してしまいます。

PostgreSQLにおける最も基本的かつ強力な最適化手法であるB-Tree（Balanced Tree）インデックスの仕組みと効果的な使い方を、3分でマスターしましょう。

1. インデックスの仕組み：本の「索引」と同じ

データベースのテーブルが「本の本文」だとすれば、インデックスは「巻末の索引」です。
B-Treeインデックスは、データをソートされたツリー構造で保持しており、任意の値を O(log N) の時間計算量で高速に探索できます。これにより、数百万行のレコードがあっても、わずか数回のデータ比較で目的の行（TID）を見つけ出すことができます。

2. インデックスの効果を検証する

以下のような、100万人のユーザーデータを保持する users テーブルを想定します。

1
SELECT * FROM users WHERE email = 'target@example.com';

インデックスなし（Seq Scan：全件探索）

クエリの実行計画を調べるために EXPLAIN ANALYZE を実行してみます。

1
2
3
4
5
6
7
8
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'target@example.com';

-- 実行結果の抜粋
Seq Scan on users  (cost=0.00..20834.00 rows=1 width=84) (actual time=48.210..98.543 rows=1 loops=1)
  Filter: (email = 'target@example.com'::text)
  Rows Removed by Filter: 999999
Planning Time: 0.120 ms
Execution Time: 98.610 ms
  • 結果：テーブル全体を走査（Seq Scan）したため、実行に 98.6ミリ秒 かかりました。

インデックスを作成する

次に、email カラムに対してB-Treeインデックスを作成します。

1
CREATE INDEX idx_users_email ON users(email);

インデックスあり（Index Scan：索引探索）

インデックス作成後に、再び同じクエリの実行計画を確認します。

1
2
3
4
5
6
7
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'target@example.com';

-- 実行結果の抜粋
Index Scan using idx_users_email on users  (cost=0.42..8.44 rows=1 width=84) (actual time=0.045..0.048 rows=1 loops=1)
  Index Cond: (email = 'target@example.com'::text)
Planning Time: 0.180 ms
Execution Time: 0.065 ms
  • 結果：テーブル走査が Index Scan に切り替わり、実行時間はなんと 0.065ミリ秒 まで短縮されました！元のスキャンに比べて 1500倍近く高速化 されたことになります。

インデックス設計の注意点・ベストプラクティス

  • カーディナリティ（選択度）の高いカラムに貼る：
    gender（性別）や status（ステータス）のように、値の種類が少なく同じ値が大量に存在するカラムにインデックスを貼っても、オプティマイザはインデックスを使用せず、フルスキャンを選択してしまいます。email や user_id のように、値がほぼ一意になるカラムに作成するのが最も効果的です。
  • 過剰なインデックスは避ける：
    インデックスは読み込み（SELECT）を劇的に高速化しますが、データの書き込み（INSERT / UPDATE / DELETE）のたびにインデックスの更新処理が発生するため、書き込みパフォーマンスが低下します。本当に必要な検索クエリに対してのみインデックスを作成しましょう。