なぜ今さら『EXPLAIN (BUFFERS, ANALYZE)』なのか？
PostgreSQLを触り始めて数年が経つと、誰もが一度は「なぜこのクエリはこんなに遅いんだ？」という壁にぶつかります。そんな時、皆さんはまず `EXPLAIN ANALYZE` を叩くはずです。実行計画のノードを見て、Nested Loopが走っているか、Seq Scanがインデックスを無視していないかを確認する。それは基本の「き」ですよね。
でも、本当のプロフェッショナルは、その一歩先を見ています。実行計画の「構造」だけでなく、そのクエリが物理的にストレージとどう対話したのかに注目するのです。そこで欠かせないのが `EXPLAIN (ANALYZE, BUFFERS)` です。
今日は、このオプションが単なるデバッグツールを超えて、データベースの「体調」を診断する最高の手術メスであることをお話ししましょう。
—
BUFFERSオプションが教えてくれる「真実」
`BUFFERS` オプションを付けると、出力結果に `Shared Read`、`Shared Hit`、`Shared Dirtied`、`Shared Written` といった項目が現れます。これらは、クエリが共有バッファ（Shared Buffers）とどう格闘したかを示しています。
私が現場でトラブルシューティングを行う際、特に注目するのは 「HitとReadの比率」 です。
- Shared Hit: メモリ（バッファキャッシュ）上にデータがあった。
- Shared Read: メモリになかったため、OSのキャッシュを叩くか、あるいは物理ディスクまでI/Oが発生した。
もし、高頻度で実行されるクエリなのに `Shared Read` が大きい場合、それはインデックスが効いていないのか、それともメモリ設定（`shared_buffers`）が物理的なワーキングセットに対して小さすぎるのか……といった深い洞察の入り口に立てます。
なぜこれが重要なのか？
パフォーマンスチューニングの鉄則は「I/Oを減らすこと」です。CPUのクロックサイクルと物理ディスクのアクセスレイテンシを比較すれば、なぜI/Oを抑えることが最適化の至高であるかは自明でしょう。
`BUFFERS` オプションがない状態の `EXPLAIN ANALYZE` は、あくまで「論理的なコスト」や「実行時間」という結果しか見せてくれません。しかし、`BUFFERS` を使えば、「なぜその時間がかかったのか」を物理的なI/Oの観点から説明できるようになります。
—
現場で役立つ「読み解き」の勘どころ
実際に現場で使う際、私が意識しているチェックポイントをいくつか共有します。
1. 「Dirtied」と「Written」に注目する
書き込み系のクエリや、大量の更新を伴うバッチ処理では、`Shared Dirtied` が重要です。これが高いということは、クエリがバッファ上の多くのページを更新（Dirty）したことを意味します。これが多すぎると、チェックポイント（Checkpointer）の負荷が高まり、システム全体のレスポンスを悪化させる一因になります。
2. 「Read」が大きすぎるインデックススキャン
インデックスを使っているはずなのに `Shared Read` が大きい場合、それはインデックス自体が肥大化しているか、あるいはインデックスのページがメモリ上に収まりきらず、頻繁にスワップアウトされている可能性があります。`pg_stat_user_indexes` でインデックスのサイズを確認し、不要なインデックスを削る勇気を持つきっかけになります。
3. 「Hit」が多いのに遅いケース
逆に、全て `Shared Hit` なのにクエリが遅いケースもあります。この場合、I/Oの問題ではなく、純粋なCPUネック（例えば、複雑なJOINや、関数呼び出しのオーバーヘッド、膨大なソート処理）である可能性が高い。こうやって「I/Oという要因を消去法で除外できる」のが、`BUFFERS` を使う最大の利点なのです。
—
最後に：計測は「解釈」のためにある
`EXPLAIN (BUFFERS, ANALYZE)` は非常に強力ですが、あくまで実行時のスナップショットです。本番環境で実行する際は、ロックの競合や実行時間の増大に十分注意してください。特に、書き込みを伴うクエリでの `ANALYZE` は禁物です。
結局のところ、データベースエンジニアの仕事は、数字を眺めることではなく、数字から「裏側で何が起きているか」というストーリーを読み解くことです。
「なぜ、この8KBのブロックが読み込まれる必要があったのか？」
そうやって自問自答を繰り返すうちに、PostgreSQLの内部アーキテクチャが、少しずつ、しかし確実にあなたの頭の中に浸透していくはずです。ぜひ、次回のチューニングでは、忘れずに `BUFFERS` を添えてみてください。そこには、これまで見えなかった景色が広がっているはずですから。
コメント