【テクニカル・上級編】 EXPLAIN ANALYZEの読み解き – PostgreSQL

実行計画という「沈黙の告白」を読み解く：PostgreSQLにおけるボトルネック特定術

「クエリが遅い」という連絡がチャットで届くとき、多くのエンジニアはまず `EXPLAIN ANALYZE` を叩く。だが、出力された膨大なテキストを前にして、どこから手をつけるべきか迷ったことはないだろうか。

単に「Cost」の数値だけを見て溜息をつくのはもう終わりにしよう。PostgreSQLのオプティマイザが吐き出す実行計画は、データベースがそのクエリを実行するために辿った「思考のプロセス」そのものだ。今日は、この沈黙の告白をどう読み解き、どこにメスを入れるべきか、深いレイヤーから紐解いていきたい。

—

1. 「見積もり」と「現実」の乖離を見抜く

`EXPLAIN ANALYZE` を見るとき、最初に目を向けるべきは `rows` と `actual rows` の乖離だ。

Seq Scan on users (cost=0.00..1234.00 rows=1000 width=32) (actual time=0.01..5.00 rows=50000 loops=1)

この例を見てほしい。オプティマイザは「1,000行くらいだろう」と予測したが、現実は50,000行だった。この乖離は、統計情報の鮮度が落ちているか、データ分布が複雑すぎてプランナが騙されている証拠だ。

この乖離が大きいと、ネステッドループを選択すべきところでハッシュジョインが選ばれたり、インデックスを無視してシーケンシャルスキャンに倒れ込んだりする。`ANALYZE` を実行して統計情報を更新しても改善しないなら、`CREATE STATISTICS` を検討すべきだ。相関関係のあるカラム（例えば「都道府県」と「郵便番号」など）がある場合、デフォルトの単一カラム統計ではプランナは一生正しい予測ができない。

—

2. Buffers：I/Oの真実を追う

パフォーマンスチューニングの本質は、結局のところ「いかに不要なI/Oを削るか」に集約される。そこで私が必ず注目するのが `Buffers` の項目だ。

shared hit: メモリ（バッファキャッシュ）上にあったページ数。これは速い。
shared read: ディスクから読み込んだページ数。ここがボトルネックの主戦場だ。

例えば、`loops` が大きいノードで `shared read` が多発している場合、それはインデックスが効いていないか、あるいはインデックスが断片化していてランダムアクセスが激増している可能性が高い。

もし `hit` が極端に少ないのに `read` が多いなら、それはメモリサイズが足りないのではなく、インデックスの設計自体が「全件走査に近い動き」を強要している可能性を疑うべきだ。インデックスのサイズが物理メモリのキャッシュ容量を圧迫し始めると、途端にレスポンスが崩壊する。いわゆる「インデックスの肥大化」だ。

—

3. 「Nested Loop」の罠と「Hash Join」の判断基準

`EXPLAIN` を眺めていると、結合方式に目がいくはずだ。特に `Nested Loop` が多用されている場合、それが「外側のループ」に対して「内側のインデックス」が効率的に引けているかを確認してほしい。

もし、内側のテーブルの `actual rows` が巨大で、なおかつ `loops` も多い場合、そのクエリは指数関数的に遅くなる。ここで無理にインデックスを貼るよりも、メモリ（`work_mem`）を一時的に解放して `Hash Join` に持ち込む方が、トータルコストが下がるケースが多い。

一方で、`Hash Join` はハッシュテーブルを構築するためのコストが初期段階でかかる。短時間のクエリを大量に捌くシステムでは、ハッシュ構築のオーバーヘッドが無視できないこともある。このあたりの「バランス」を見極めるのが、データベースエンジニアとしての腕の見せ所だ。

—

4. 最後に：なぜ「遅い」のかを深掘りする

ボトルネックは、往々にして「プランナが選んだプラン」と「データの実態」の間のミスマッチにある。

インデックスの冗長性: 似たようなインデックスが複数ないか？
型変換: `WHERE column::text = ‘123’` のような書き方をしていないか？（これだけでインデックスは無力化する）
VACUUMの状態: `Index Scan` なのに `read` が多い場合、ヒープ上のデータが古いバージョンのゴミ（dead tuples）で溢れかえっていないか？

`EXPLAIN ANALYZE` は、単なるデバッグツールではない。データベースというブラックボックスの中で何が起きているのか、その「心拍数」を読み取るための聴診器だ。

数値の裏側にある物理構造を想像し、なぜそのプランが選ばれたのかという「理由」にまで思考を巡らせる。そうすれば、ただのクエリチューニングが、システム全体を最適化する高度なパズルへと変わるはずだ。

さて、あなたの目の前にあるその実行計画。そこには、どんなストーリーが隠されているだろうか。