その「実行計画」、本当の姿が見えていますか？ — PostgreSQL `EXPLAIN (VERBOSE, BUFFERS)` を使い倒す
PostgreSQLと長く付き合っていると、必ずぶち当たる壁があります。「なぜか遅い」。
`EXPLAIN` を叩いて、Costを見て、Seq Scanの有無を確認する。これは日常茶飯事ですが、中級者からその先へ進もうとしたとき、標準出力の情報量だけでは「プランナの深層心理」を読み解くには不足を感じることがありませんか？
今日は、そんな皆さんに、私が現場でトラブルシューティングの最終兵器として愛用している `EXPLAIN (VERBOSE, BUFFERS)` について語らせてください。
VERBOSEが暴く「隠れた変換」
まず、`VERBOSE` オプションを付けると何が起こるのか。単に詳細が出るだけではありません。プランナが内部的に行っている「式の書き換え」や「暗黙の型変換」が可視化されます。
例えば、`WHERE` 句での比較。アプリケーション層から送られてくるパラメータの型が、テーブルのカラム型と微妙にズレていた場合、通常の `EXPLAIN` では見落とすことがあります。しかし `VERBOSE` を使えば、`Filter` 句の中に `(column::text = $1::text)` のようなキャストが明示されます。
これ、地味ですが重要です。インデックスが効かない原因の多くは、実はこの「プランナによる暗黙の型変換」でインデックスのソート順が使えなくなっていることにあります。プランナがSQLをどう解釈したか、その「脳内」を覗き見ることで、無駄なキャストを排除し、インデックスを本来の姿で働かせることができるようになります。
プランナの「列の選び方」を追跡する
`VERBOSE` を使うと、各ノードが出力するカラムリスト（Output）が表示されます。
大規模なJOINが発生しているとき、PostgreSQLは本当に必要なカラムだけをメモリ（Work Mem）に載せようと努力します。しかし、複雑なサブクエリやViewを多用していると、不必要なデータまで中間結果に含めてしまうケースがあります。
`Output` 項目を眺めていて、「なぜこのテーブルから、この列をわざわざ抽出しているんだ？」という違和感を持つこと。これがクエリチューニングのセンスです。不要なカラムが投影されていれば、その分メモリ消費量が増え、ディスクI/Oが膨れ上がります。この「情報の過剰供給」を特定できるのは、`VERBOSE` の特権といってもいいでしょう。
BUFFERSとの併用が「真実」を語る
私が `VERBOSE` を使うときは、決まって `BUFFERS` もセットにします。これがないと、現場の「本当の負荷」は見えてきません。
- Shared Read: ディスクから物理的に読み込んだブロック数
- Shared Hit: メモリ（バッファキャッシュ）からヒットした数
この比率を見ることで、「インデックスは効いているはずなのに、なぜか遅い」という現象の正体が分かります。インデックスのランダムアクセスが多すぎて、キャッシュに乗っていないページを求めてディスクが悲鳴を上げている（＝Hit率が低い）のか、あるいは単にテーブル全体をスキャンしているのか。
`VERBOSE` でプランの構造を確認し、`BUFFERS` で「物理的な痛み」を計測する。このセットは、PostgreSQLエンジニアにとっての聴診器のようなものです。
最後に：プランナを信じすぎないために
もちろん、`EXPLAIN` が示すプランは、あくまでプランナが「現時点で最善と判断した」仮説に過ぎません。統計情報が古ければ、その仮説自体が的外れなこともあります。
しかし、`VERBOSE` を通じて「なぜプランナがそのインデックスを選んだのか」「なぜその結合順序を選んだのか」という背景を言語化できるようになると、PostgreSQLの挙動が単なるブラックボックスではなく、論理的な挙動の積み重ねとして見えてくるはずです。
皆さんのクエリが、今日も効率的に、そして美しく実行されることを願っています。さて、次はどのクエリの実行計画を深掘りしましょうか？
—
エンジニアとしての経験則ですが、もし皆さんの現場で特定のクエリが「なぜか再現性なく遅い」という現象があれば、ぜひ `BUFFERS` 付きの実行計画を3回ほど取得して比較してみてください。キャッシュの汚染具合や、コンカレントな更新による影響までが見えてくるはずです。
コメント