はじめに

遅いクエリは、負荷がかかったバックエンドシステムで最も目立つ症状です。PostgreSQLでは、何百万行もスキャンするクエリとちょうど10行を取得するクエリの違いは、通常ひとつに集約されます。それは適切なインデックスです。このレッスンでは、バックエンド業務で最も頻繁に使う3つのインデックスタイプ——B-tree、複合、部分——を順に解説し、EXPLAIN ANALYZEプランの読み方を通じてどのインデックスが必要かを見極められるようにします。

B-treeインデックス：デフォルトの働き手

B-treeインデックスは、単にCREATE INDEXと記述したときにPostgreSQLが作成するものです。カラムの値をソートして保持するため、等価（=）や範囲（>, <, BETWEEN）による検索が高速になります。WHERE句やJOIN条件で頻繁に使用されるカラムには、常にB-treeインデックスを使用してください。

CREATE INDEX idx_users_email ON users (email);

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

インデックスがないと、PostgreSQLはシーケンシャルスキャンを実行し、すべての行を読み込みます。インデックスがあれば、一致するエントリに直接ジャンプします。1,000万行のテーブルでは、その差は200msと2msの差になります。

複合インデックス：順序が重要です

クエリが複数のカラムでフィルタリングまたはソートを行う場合、複合インデックスがその両方に対応できます。重要な詳細はカラムの順序です。インデックスは、先頭カラムが制約されている場合にのみ効率的に使用できます。

CREATE INDEX idx_orders_user_date ON orders (user_id, created_at);

SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

(user_id, created_at)のインデックスにより、PostgreSQLはuser_idでフィルタリングしつつ、行をすでにcreated_atでソート済みの状態で取得できます。別途ソート処理は不要です。よくある間違いは、インデックスを間違ったカラム順序で作成することです。(created_at, user_id)のインデックスはこのクエリには役立ちません。なぜなら、user_idが先頭カラムではないからです。

部分インデックス：重要な行のみをインデックス化する

部分インデックスは、インデックス自体にWHERE句を追加し、条件に一致する行のみを格納します。これにより、テーブル内の一部のみをクエリが対象とする場合に、インデックスを小さく高速に保つことができます。

CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at
LIMIT 50;

注文の99%がすでに完了済みで、保留中はわずか1%しかない場合、statusに対する完全なインデックスは無駄が大きいです。部分インデックスは1,000倍小さくなり、実際に重要なクエリに対しては同じだけの効果を持ちます。

EXPLAIN ANALYZEの読み方

EXPLAIN ANALYZEはクエリを実行し、PostgreSQLが選択した実際のプランを実際のタイミングと行数とともに報告します。確認すべき最も重要なシグナル：

- 大きなテーブルでのSequential Scan → インデックスが必要である可能性が高い。- Rows removed by filterがプランナーのRows estimatedよりもはるかに多い → 統計情報が古いため、ANALYZE table_nameを実行する。- 大きな集合の上にあるSortノード → ORDER BYに合致するインデックスを検討するか、より早い段階でLIMITを追加する。

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

出力内でIndex Scan using idx_orders_user_dateを探してください。代わりにSeq Scanが表示されている場合、インデックスは使用されていません——通常、テーブルが小さすぎてプランナーがインデックスを使うまでもないか、統計情報が古くなっていることが原因です。

よくある落とし穴

すべてのカラムにインデックスを張ることは、何にもインデックスを張らないことよりも悪い結果になります。PostgreSQLはインデックスも更新しなければならないため、すべてのインデックスは書き込み（INSERT、UPDATE、DELETE）を遅くさせます。良いルールは、頻繁に実行され、多くの行に触れるクエリのWHERE、ORDER BY、JOINに現れるカラムにインデックスを作成することです。

もうひとつのよくある間違いは、WHERE句でインデックス対象カラムに関数を使用することです。これによりインデックスの使用が妨げられます：

-- This WILL use an index on users.email
SELECT * FROM users WHERE email = 'alice@example.com';

-- This WILL NOT use that index
SELECT * FROM users WHERE lower(email) = 'alice@example.com';

大文字小文字を区別しない検索が必要な場合は、正規化されたカラムを保存するか、関数インデックスを作成してください：CREATE INDEX ON users (lower(email))。

まとめ

PostgreSQLでの効果的なインデックス作成は、実際のクエリパターンにインデックスタイプとカラムの順序を合わせることに尽きます。B-treeはほとんどの等価検索と範囲検索を処理します。複合インデックスは、先頭カラムが制約されている場合にフィルタリングとソートを両立します。部分インデックスは、重要な行のみにインデックスを絞り込みます。EXPLAIN ANALYZEはフィードバックループです。インデックスを追加する前と後に必ず実行し、プランが実際に変更されたことを確認してください。