PostgreSQL

PostgreSQLインデックス入門｜クエリを高速化する仕組み

PostgreSQL インデックス パフォーマンス

PostgreSQLインデックス入門
クエリを高速化する仕組み

PostgreSQLのインデックスの種類、作成方法、パフォーマンスへの影響を解説します。

こんな人向けの記事です

  • クエリの実行速度を改善したい人
  • インデックスの種類と使い分けを知りたい人
  • インデックスの管理方法を学びたい人

Step 1インデックスとは

インデックスは、テーブル内のデータを高速に検索するための仕組みです。本の索引のように、目的のデータの場所を素早く特定できます。

トレードオフ: インデックスは検索を高速化しますが、INSERT/UPDATE/DELETE時にインデックスの更新が発生するため、書き込み性能には影響があります。

Step 2インデックスの作成

SQL 
-- 基本的なインデックス
CREATE INDEX idx_user_email ON users(email);

-- 複合インデックス
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- ユニークインデックス
CREATE UNIQUE INDEX idx_unique_username ON users(username);

-- 部分インデックス（条件付き）
CREATE INDEX idx_active_users ON users(user_id) WHERE is_active = TRUE;

Step 3インデックスの種類

種類用途特徴
B-tree（デフォルト）等価・範囲検索最も汎用的
Hash等価検索のみB-treeより高速な場合あり
GIN全文検索、配列複数値を含むカラム向け
GiST地理データ、範囲型空間検索向け
BRIN大規模テーブルサイズが小さく高速

Step 4インデックスの管理

SQL 
-- テーブルのインデックス一覧
\di+ テーブル名

-- インデックスの削除
DROP INDEX idx_user_email;

-- インデックスの使用状況確認
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- インデックスの再構築
REINDEX INDEX idx_user_email;

Step 5インデックス設計のベストプラクティス

  • WHERE句やJOIN条件で頻繁に使われるカラムにインデックスを作成する
  • カーディナリティ（値の種類数）が高いカラムほどインデックスの効果が大きい
  • 過剰なインデックスは書き込み性能を低下させるので必要最小限にする
  • EXPLAIN ANALYZEでクエリプランを確認し、効果を検証する

注意: PRIMARY KEYとUNIQUE制約には自動的にインデックスが作成されるため、重複して作成する必要はありません。