2026年6月25日

PostgreSQLのインデックス設計と最適化（EXPLAINの読み方・複合・部分・式インデックス）

データベースサーバー

PostgreSQL のインデックスは、「遅いクエリを特定し、EXPLAIN で読み方を確認してから、そのクエリの条件に合うインデックスを1つずつ足す」という順で設計すると、むだが少なくなります。思いつきで列ごとにインデックスを作ると、書き込みが遅くなり、容量も増えるのに、肝心のクエリでは使われない、ということが起こりがちです。この記事では、遅いクエリの見つけ方、EXPLAIN の読み方、B-tree の複合インデックスの列の順番、部分インデックスと式インデックス、用途別のインデックスの種類、本番での安全な作成方法、使われていないインデックスの整理までを説明します。

WordPress のコアは MySQL・MariaDB を対象にしており、PostgreSQL は公式にはサポートされていません。この記事は、WordPress と並べて動かす別のアプリや分析用のデータベースで PostgreSQL を使う場面を想定しています。

遅いクエリを特定する

インデックスは特定のクエリのために作るものなので、まず対象を決めます。拡張機能の pg_stat_statements を有効にすると、クエリごとの実行回数と合計時間を集計できます。

-- postgresql.conf の shared_preload_libraries に pg_stat_statements を追加し、再起動したうえで
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 1)  AS mean_ms,
       left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

1回あたりは速くても、実行回数が非常に多いクエリは合計時間で上位に来ます。改善の効果が大きいのは、合計時間の上位にあるクエリです。PostgreSQL の機能や統計ビューの列はバージョンごとに追加・変更されています（2026年10月現在）。この列名も古いバージョンでは total_time などと異なるため、エラーになる場合は使っているバージョンのドキュメントで確認してください。

EXPLAIN で実行計画を読む

対象のクエリが決まったら、EXPLAIN (ANALYZE, BUFFERS) で実際の実行計画を確認します。

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total
FROM orders
WHERE customer_id = 1234
  AND created_at >= '2026-09-01'
ORDER BY created_at DESC
LIMIT 20;

ANALYZE を付けるとクエリが実際に実行されます。更新や削除のクエリを調べるときは、BEGIN; と ROLLBACK; で囲んで、変更を取り消せるようにしてください。

読むときのポイントは次のとおりです。

Seq Scan：テーブルを最初から最後まで読んでいます。大きなテーブルで少数の行を探しているのに Seq Scan なら、条件に合うインデックスがない可能性があります。ただし、小さなテーブルや、条件に当てはまる行が多い場合は、Seq Scan のほうが速いため、プランナーが正しく選んでいることもあります。
Index Scan / Index Only Scan / Bitmap Heap Scan：インデックスを使っています。Index Only Scan はテーブル本体を読まずにインデックスだけで答えを返す、最も効率のよい形です。
推定行数（rows）と実際の行数（actual rows）の差：大きくずれていると、プランナーが統計情報に基づいて誤った計画を選んでいる可能性があります。ANALYZE テーブル名; で統計を更新して変わるかを確認します。
Sort：ORDER BY のために並べ替えています。インデックスの順番で取り出せれば、この処理を省けます。

B-tree の複合インデックスは列の順番が要点

PostgreSQL の既定のインデックスは B-tree で、=、<、>、範囲、ORDER BY に使えます。複数の列にまたがる複合インデックスでは、列の順番によって使える条件が変わります。

上のクエリなら、次のインデックスが合います。

CREATE INDEX idx_orders_customer_created
  ON orders (customer_id, created_at DESC);

基本の考え方は「= で絞る列を先に、範囲や並べ替えに使う列を後に」です。このインデックスは customer_id が同じ行の中で created_at の順に並んでいるため、絞り込みと並べ替えを同時に済ませられます。逆の順番（created_at, customer_id）にすると、日付の範囲全体を読んでから顧客で絞ることになり、効率が落ちます。

また、(customer_id, created_at) のインデックスがあれば、customer_id だけの条件にも使えます。同じ先頭列を持つインデックスを別に作る必要はありません。

INCLUDE で Index Only Scan にする

検索条件には使わないが、結果として返す列を INCLUDE で持たせると、テーブル本体を読まずに済む場合があります（PostgreSQL 11 以降）。

CREATE INDEX idx_orders_customer_created_inc
  ON orders (customer_id, created_at DESC) INCLUDE (total);

Index Only Scan が実際に効くかどうかは、テーブルの「可視性マップ」が最新かどうかにも左右されます。更新が多いテーブルでは、VACUUM が追いついていないと、結局テーブル本体を読みに行きます。

部分インデックスと式インデックス

部分インデックス

特定の条件の行だけを対象にするインデックスです。たとえば、未処理の注文だけを頻繁に探す場合です。

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

対象の行が少ないほど、インデックスは小さく、更新の負担も減ります。クエリの WHERE 句に同じ条件（status = 'pending'）が含まれていないと使われない点に注意してください。

式インデックス

列を関数で加工した条件には、その式でインデックスを作ります。

-- 大文字・小文字を区別せずにメールアドレスで探す場合
CREATE INDEX idx_users_lower_email ON users (lower(email));

SELECT id FROM users WHERE lower(email) = lower('user@example.com');

逆に言えば、インデックスのある列を関数や型変換で加工すると、通常のインデックスは使われません。日付で絞るときに created_at::date = '2026-10-01' と書くより、created_at >= '2026-10-01' AND created_at < '2026-10-02' のように範囲で書くほうが、既存のインデックスを使えます。

用途に合わせてインデックスの種類を選ぶ

B-tree 以外にも、データの種類に合わせたインデックスがあります。

GIN：jsonb、配列、全文検索のように、1つの値に複数の要素が含まれるデータ向けです。jsonb の @>（含む）での検索などに使います。更新の負担は B-tree より大きめです。
pg_trgm 拡張と GIN：LIKE '%語句%' のような中間一致は B-tree では速くなりません。pg_trgm 拡張の gin_trgm_ops を使うと、中間一致の検索にインデックスが使えるようになります。
BRIN：アクセスログのように、時刻の順に追加され、物理的な並びと値の並びがそろっている巨大なテーブル向けです。非常に小さいインデックスで、大まかな範囲の絞り込みができます。
GiST：地理情報（PostGIS）や範囲型、排他制約などに使います。

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING gin (name gin_trgm_ops);

なお、前方一致（LIKE 'abc%'）は B-tree で速くできますが、データベースの照合順序が C 以外の場合は、text_pattern_ops を指定したインデックスが必要になることがあります。

本番では CONCURRENTLY で作る

通常の CREATE INDEX は、作成中にそのテーブルへの書き込みを止めます。大きなテーブルでは、数分から数十分にわたって更新が待たされることがあります。本番では CONCURRENTLY を付けて、書き込みを止めずに作ります。

CREATE INDEX CONCURRENTLY idx_orders_customer_created
  ON orders (customer_id, created_at DESC);

注意点が3つあります。

トランザクションの中（BEGIN の後）では実行できません。
通常の作成より時間がかかり、その間の負荷も続きます。アクセスの少ない時間帯に実行します。
途中で失敗すると、「無効（INVALID）」な状態のインデックスが残ります。\d テーブル名 で INVALID と表示されたら、DROP INDEX CONCURRENTLY で消してから作り直します。

作成の前には、ステージング環境で同程度のデータ量を使って所要時間と効果を確かめ、本番ではバックアップを取ってから作業します。

使われていないインデックスを整理する

インデックスは、増やすほど INSERT や UPDATE が遅くなり、容量も増えます。また、更新される列にインデックスがあると、PostgreSQL の HOT 更新（インデックスを書き換えずに済む更新）が使えなくなります。定期的に、使われていないものを見直します。

SELECT s.relname AS table_name,
       s.indexrelname AS index_name,
       s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
ORDER BY pg_relation_size(s.indexrelid) DESC;

idx_scan が0のインデックスが削除の候補です。ただし、次の点を確かめてから判断します。

統計は最後にリセットされてからの値です。月末にだけ動く集計などで使われているインデックスもあるため、十分な期間を見ます。
レプリカで実行される読み取りクエリの利用は、プライマリの統計には出ません。レプリカ側でも確認します。
主キーや一意制約のためのインデックスは、検索に使われていなくても制約として必要です（上のクエリでは一意のものを除いています）。

肥大化したインデックスは REINDEX INDEX CONCURRENTLY（PostgreSQL 12 以降）で、書き込みを止めずに作り直せます。

まとめ

pg_stat_statements で合計時間の大きいクエリを見つけ、そのクエリのためにインデックスを作る
EXPLAIN (ANALYZE, BUFFERS) で Seq Scan、推定行数と実際の行数の差、Sort の有無を確認する
複合インデックスは = の列を先に、範囲・並べ替えの列を後に。INCLUDE で Index Only Scan も狙える
部分インデックス・式インデックス・GIN・BRIN を、条件とデータの性質に合わせて使い分ける
本番では CONCURRENTLY で作成し、使われていないインデックスは十分な期間の統計を見て整理する