chicchi.work

Article

🐘 PostgreSQLのEXPLAIN ANALYZE入門：インデックスが効かない理由を実行計画から読む

PostgreSQL, SQL, Database, Performance

SQLが遅いとき、いきなりインデックスを追加していませんか。

PostgreSQLでは、まず EXPLAIN / EXPLAIN ANALYZE で「PostgreSQLがそのSQLをどう実行しようとしているか」を確認するのが基本です。

この記事では、実行計画の読み方を初心者向けに整理しつつ、インデックスを作ったのに Seq Scan になる理由や、複合インデックスの列順をどう考えるかまで実務目線でまとめます。

EXPLAINとは

PostgreSQLには プランナ（planner） という仕組みがあります。

プランナはSQLを受け取ると、テーブルを最初から最後まで読むか、インデックスを使うか、どの順番でJOINするかなどを検討し、コストが低いと見積もった実行方法を選びます。

その結果である 実行計画（query plan） を表示するのが EXPLAIN です。

EXPLAIN
SELECT *
FROM orders
WHERE user_id = 100;

EXPLAIN は原則としてSQLそのものを実行せず、プランナが選んだ計画を表示します。

一方、

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE user_id = 100;

とすると、SQLを実際に実行して、実測時間や実際に処理した行数まで表示します。

ここは重要です。EXPLAIN ANALYZE は本当にSQLを実行します。UPDATE や DELETE に対して安易に実行するとデータが変更されます。

更新SQLを調査する場合は、検証環境を使うか、必要に応じてトランザクションで囲んで ROLLBACK するなどの対策が必要です。

まず見る4項目

実行計画には多くの情報がありますが、最初は次の4つを見るだけでもかなり役立ちます。

1. Seq Scan / Index Scan

例えば、

Seq Scan on orders

と表示された場合は Sequential Scan（シーケンシャルスキャン） です。

テーブルを先頭から順番に読みます。

一方、

Index Scan using idx_orders_user_id on orders

なら、インデックスを使って対象行を探しています。

ただし、Seq Scan = 悪 ではありません。

テーブルが小さい場合や、大部分の行を取得する場合は、インデックスをたどるより最初から読む方が安いとPostgreSQLが判断することがあります。

2. cost

(cost=0.29..8.31 rows=1 width=32)

cost はミリ秒ではありません。

PostgreSQL内部で実行方法を比較するための相対的な見積もり値です。

左側が最初の行を返すまでの startup cost、右側が処理全体の total cost です。

3. rows と actual rows

EXPLAIN ANALYZE では、例えば次のように表示されます。

rows=10
actual ... rows=10000

前者はプランナの推定行数、後者は実際の行数です。

この差が極端に大きい場合、プランナがデータ分布を正しく推定できていない可能性があります。

統計情報が古い場合は、

ANALYZE orders;

で統計情報を更新できます。

ANALYZE は、列の値の分布などを統計情報として収集し、プランナがより現実的な実行計画を選ぶために利用します。

4. actual time

actual time=0.025..0.030

ANALYZE を付けた場合に表示される実測値です。

単に「Index Scanになった」だけを見るのではなく、最終的には実際の実行時間が改善したかを確認します。

BUFFERSも付ける

性能調査では、次の形をよく使います。

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE user_id = 100;

BUFFERS は、PostgreSQLが処理中にどれだけデータページを扱ったかを確認するための情報です。

例えば、

Buffers: shared hit=10 read=3

のように表示されます。

  • hit: PostgreSQLの共有バッファ上にあり、ディスクから読む必要がなかったページ
  • read: ストレージから読み込んだページ

「SQLの時間だけ」ではなく、どの処理でI/Oが多く発生しているかを探すときに役立ちます。

PostgreSQL 18の公式ドキュメントでは、EXPLAIN の ANALYZE オプションを使うと BUFFERS も暗黙的に有効になります。明示的に BUFFERS を書いておくと、調査意図が分かりやすいので個人的にはこの形を使いやすいと感じます。

インデックスを作ったのにSeq Scanになる

例えば次のインデックスがあるとします。

CREATE INDEX idx_orders_user_id
ON orders (user_id);

それでも、

SELECT *
FROM orders
WHERE user_id IS NOT NULL;

で Seq Scan が選ばれることがあります。

理由の一つは、条件に一致する行が多すぎることです。

仮に100万行のうち99万行が user_id IS NOT NULL なら、インデックスで99万件の位置を探してからテーブル本体へアクセスするより、テーブルを順番に読む方が安い場合があります。

つまり、

インデックスが存在する
=
必ずIndex Scanになる

ではありません。

プランナはデータ量、条件の選択性、統計情報、I/Oコストなどから実行方法を決めます。

複合インデックスは列順が重要

次の検索が多いとします。

SELECT *
FROM orders
WHERE user_id = 100
  AND status = 'PAID';

この場合、次のような複合B-treeインデックスを検討できます。

CREATE INDEX idx_orders_user_status
ON orders (user_id, status);

複合インデックス（multicolumn index） は、複数列を1つのインデックスとして持つ仕組みです。

B-treeの複合インデックスでは、先頭側の列に条件があるほど効率よく検索範囲を絞り込みやすくなります。

例えば (user_id, status) なら、

WHERE user_id = 100

や、

WHERE user_id = 100 AND status = 'PAID'

といった検索と相性が良くなります。

一方で、

WHERE status = 'PAID'

だけを頻繁に検索するなら、同じインデックスが最適とは限りません。

なお、最近のPostgreSQLには skip scan の最適化もあるため、「先頭列が条件にないと絶対に使えない」と覚えるのは正確ではありません。公式ドキュメントでも、複合B-treeインデックスは任意の列の条件で利用可能な場合がある一方、先頭列に制約があるとき最も効率的と説明されています。

重要なのは暗記ではなく、実際のクエリに対して EXPLAIN で確認することです。

インデックスを増やしすぎない

検索を速くしたいからといって、すべての列にインデックスを付けるのも良くありません。

インデックスは検索を高速化できる一方で、INSERT、UPDATE、DELETE の際にはインデックス側も更新する必要があります。

また、ディスク容量も使用します。

そのため、

  1. 遅いSQLを特定する
  2. EXPLAIN (ANALYZE, BUFFERS) で確認する
  3. ボトルネックを考える
  4. 必要ならインデックスを追加する
  5. もう一度実行計画と実測値を比較する

という順番で改善するのがおすすめです。

よくある勘違い

「Seq Scanが出たらインデックスを追加する」

必ずしも必要ありません。

取得行数が多い場合や小さいテーブルでは、Seq Scanが合理的なことがあります。

「cost=100なら100ms」

違います。

cost はプランナが候補を比較するための相対値です。

実測時間を見る場合は EXPLAIN ANALYZE の actual time や Execution Time を確認します。

「複合インデックスは列順を気にしなくていい」

B-treeでは先頭側の列が特に重要です。

実際に使われるWHERE句やORDER BYを基準に設計します。

「EXPLAIN ANALYZEは安全な確認コマンド」

ANALYZE を付けるとSQLは実際に実行されます。

更新系SQLでは特に注意が必要です。

実務での調査テンプレート

SQLが遅いときは、まず次を確認します。

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;

見る順番は、

1. Execution Time
2. Seq Scan / Index Scan / Bitmap Scan
3. 推定 rows と actual rows の差
4. 各ノードの actual time
5. Buffers
6. SortやJOINで大きな処理が発生していないか

くらいから始めると読みやすいです。

インデックス追加後は、同じSQLでもう一度計測します。

「インデックスを作ったから速くなったはず」ではなく、実行計画と実測値で確認することが重要です。

まとめ

PostgreSQLの性能改善では、インデックスの知識だけでなく「プランナがなぜその実行方法を選んだか」を確認することが重要です。

まず覚えておきたいのは次の点です。

  • EXPLAIN は実行計画を見る
  • EXPLAIN ANALYZE はSQLを実際に実行して実測値を見る
  • BUFFERS でI/Oの状況も確認できる
  • Seq Scanは必ずしも悪ではない
  • 推定行数と実行行数の大きなズレにも注目する
  • 複合B-treeインデックスは先頭側の列が特に重要
  • インデックス追加後は必ず実行計画を再確認する

SQLが遅いときに「とりあえずインデックス」ではなく、まず実行計画を見る癖をつけると、原因をかなり切り分けやすくなります。

参考資料