PostgreSQLインデックス最適化 - EXPLAINで計測する手順

10分 で読める | 2025.12.02

公式ドキュメント
DBパフォーマンスPostgreSQL設計

インデックスは、tableの行を探すための別のdata構造です。読み取りを速くできる一方で、作成容量とINSERT・UPDATE・DELETEの更新コストが増えます。

「このcolumnにindexが必要そう」から始めず、遅いqueryを特定し、実行計画を測り、変更後に同じ条件で再計測します。

変更前の計測、索引の設計、同じ条件での再計測を行い、読み取りの速さと更新・容量のコストを比較する図

最適化の一周

実際に遅いqueryを特定
→ 代表的なparameterとdata量を用意
→ EXPLAINでplan、EXPLAIN ANALYZEで実測
→ WHERE / JOIN / ORDER BYに合うindexを設計
→ 統計を更新し、同条件で再計測
→ read改善とwrite・容量コストを比較

indexが存在しても、PostgreSQLのplannerがSeq Scan、Index Scan、Bitmap Heap Scanなどから安いと推定したplanを選びます。Seq Scanが出たことだけを失敗と判断しません。tableの多くの行を読むqueryでは、順番に読む方が合理的な場合があります。

1. queryと測定条件を固定する

例として、ある顧客の最近の注文を50件表示するqueryを扱います。

SELECT id, created_at, total_amount
FROM orders
WHERE customer_id = 42
  AND created_at >= TIMESTAMPTZ '2026-01-01 00:00:00+00'
ORDER BY created_at DESC
LIMIT 50;

同じSQLでも、customer_idごとの注文数、期間、table全体の行数、cache状態、同時負荷で結果は変わります。実際に問題になるparameterと本番に近いdata分布を用意します。

大量投入や大きな変更の直後で統計が古い場合、plannerの行数推定が実態から外れます。通常はautovacuumがANALYZEも行いますが、検証環境で必要なら明示的に更新します。

ANALYZE orders;

2. index作成前を測る

EXPLAINはqueryを実行せず、plannerが選ぶplanと推定値を表示します。

EXPLAIN
SELECT id, created_at, total_amount
FROM orders
WHERE customer_id = 42
  AND created_at >= TIMESTAMPTZ '2026-01-01 00:00:00+00'
ORDER BY created_at DESC
LIMIT 50;

ANALYZEを付けるとqueryを実際に実行し、実測時間と実際の行数を追加します。BUFFERSではshared bufferのhitやreadなどを確認できます。

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total_amount
FROM orders
WHERE customer_id = 42
  AND created_at >= TIMESTAMPTZ '2026-01-01 00:00:00+00'
ORDER BY created_at DESC
LIMIT 50;

注意: `EXPLAIN ANALYZE`は対象statementを本当に実行します。`INSERT`、`UPDATE`、`DELETE`、function、triggerなどの副作用も起こり得ます。変更系statementを本番で安易に測らず、安全な検証環境とdataで確認してください。transactionでrollbackできない外部副作用やsequenceなどもあります。

3. 実行計画で見る場所

planは子nodeから親nodeへdataを渡すtreeです。node名だけでなく、次を組み合わせて読みます。

表示確認すること
cost=startup..totalplannerの推定コスト。millisecondsではない
rows推定行数と実際の行数が大きく外れていないか
actual time各nodeの1 loopあたりの実測時間
loopsnodeが何回実行されたか。総仕事量を読む材料
Index Condindexの走査範囲を絞る条件
Filter / Rows Removed by Filter読んだ後に捨てている行が多くないか
Buffersblockがcache hitか、readを伴ったか
Planning Time / Execution Time計画と実行にかかった全体時間

推定rowsと実測rowsが大きく違う場合は、index追加だけでなく統計やdata分布も確認します。join nodeが何度も呼ばれる場合、actual timeだけでなくloopsを見ます。

4. queryに合わせてB-treeを設計する

例のqueryは、customer_idを等価条件で絞り、created_atの範囲と降順を使います。次の複合B-treeを候補にできます。

CREATE INDEX idx_orders_customer_created_at
ON orders (customer_id, created_at DESC)
INCLUDE (id, total_amount);
  • customer_idは先頭の等価条件
  • created_atは範囲条件とORDER BYに対応
  • idとtotal_amountは検索順を決めないpayloadとしてINCLUDE

これは例のqueryに対する候補であり、すべての注文queryに最適という意味ではありません。INCLUDEを増やすほどindexが大きくなります。

Index Only Scanが選ばれても、必ずheap accessが0になるわけではありません。PostgreSQLはvisibility mapを確認し、pageがall-visibleでなければheapを参照します。planのHeap Fetchesで確認します。

複合indexの列順に万能ルールはない

PostgreSQLの複合B-treeは、一般に左端columnへの制約があると走査範囲を効率よく絞れます。ただし、後続columnだけの条件でも、plannerがcostに応じてindex scanを選ぶ場合があります。

skip scanはPostgreSQL 18で追加された最適化です。PostgreSQL 17以前では利用を前提にせず、18以降でも常に選ばれるわけではありません。利用中のversionと実際のEXPLAIN (ANALYZE, BUFFERS)で、選択されたplanと読み取ったpageを確認します。

「cardinalityが高いcolumnを必ず先」「等価条件を必ず先」といった短い規則だけで決めず、実際のqueryについて確認します。

  • どの条件がindexの走査範囲を狭めるか
  • ORDER BYとLIMITを同じ順序で処理できるか
  • 単独queryだけでなく主要なquery群にどう影響するか
  • data分布とparameterによってplanが変わらないか
  • 既存indexと重複しないか

5. 同じ条件で再計測する

index作成と必要な統計更新の後、同じquery・parameterで再度測ります。

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total_amount
FROM orders
WHERE customer_id = 42
  AND created_at >= TIMESTAMPTZ '2026-01-01 00:00:00+00'
ORDER BY created_at DESC
LIMIT 50;

次の変化を記録します。

  1. scan方式とsort nodeがどう変わったか
  2. 読んだ行と捨てた行が減ったか
  3. buffer hit・readが減ったか
  4. 実行時間が代表parameter群で改善したか
  5. index容量とwrite latencyが許容範囲か

1回目はdiskから読み、2回目はcache hitになるなど、測定順序だけで時間が変わります。複数回測り、cache状態と同時負荷を揃え、都合のよい1回だけを比較しません。

PostgreSQLの主なindex機能

機能候補になる場面注意
B-tree等価、範囲、並び順既定の方式。まずqueryとの対応を見る
GINarray、JSONB、全文検索など更新と容量のcost、operator classを確認
GiST / SP-GiSTrange、幾何、検索木に合う型extensionやoperator classの仕様に従う
BRIN物理順序と値が相関する非常に大きなtable粗い範囲要約なので用途が異なる
部分index安定したpredicateに該当する一部の行queryの条件がpredicateを含む必要がある
式indexlower(email)など同じ式で検索するquery側の式と整合させる
unique index値の一意性を保証する性能だけでなくdata制約として設計する

特殊な方式を名前だけで選ばず、利用するoperator、data型、更新頻度をPostgreSQLの公式資料で確認します。

運用で削除・作成を急がない

pg_stat_user_indexes.idx_scan = 0だけで不要indexとは断定できません。統計のreset時点、待機系での利用、月次処理、制約を支えるindex、plannerが別のindexを選んだ期間などを確認します。

本番で通常のCREATE INDEXを実行するとwriteを妨げる時間があります。CREATE INDEX CONCURRENTLYはwriteを継続しやすくしますが、通常より多くの処理と時間がかかり、transaction block内では実行できず、失敗時にinvalid indexが残る場合があります。変更手順とrollback、監視を準備します。

REINDEXも定期実行する儀式ではありません。破損や実測した肥大化など、理由を特定し、lock・追加容量・CONCURRENTLYの制約を確認して行います。

まとめ

PostgreSQLのindex最適化は、次のloopで進めます。

  1. 実際に遅いqueryと代表parameterを特定する
  2. 統計とdata分布を確認する
  3. EXPLAIN (ANALYZE, BUFFERS)でbaselineを残す
  4. WHERE、JOIN、ORDER BYに合うindexを一つ設計する
  5. 同条件で再計測する
  6. read性能とwrite・容量・運用costを比較する

手元で小さなtableを作り、Seq Scanからplanがどう変わるか試す手順は、EXPLAINでインデックスの効きを確認するで扱います。

参考リソース

おすすめ記事

← 一覧に戻る
PR
PR
PR
PR