なぜ「EXPLAIN ANALYZE」だけでは足りないのか？
現場でPostgreSQLのクエリチューニングをしていると、よく「`EXPLAIN ANALYZE` を叩いたけど、実行計画は妥当に見える。なのになぜか遅いんだよね……」という相談を受けます。
実行計画（コスト）はあくまでPostgreSQLのオプティマイザが算出した「見積もり」です。でも、現実のシステムでは「ディスクI/O」という物理的な壁が立ちはだかります。
そこで今日紹介したいのが、`EXPLAIN (ANALYZE, BUFFERS)` です。これを知っているかどうかで、トラブルシューティングの解像度が段違いに変わります。
—
BUFFERSオプションで何が見えるのか？
結論から言うと、`BUFFERS` オプションをつけると、そのクエリが「共有バッファ（メモリ）」と「ディスク」の間でどれだけデータをやり取りしたかが丸裸になります。
出力には主に以下の3つの指標が出てきます。
- Shared Hit: 共有バッファ上にデータがあり、ディスク読み込みが発生しなかった回数。
- Shared Read: 共有バッファになく、OSやディスクから物理的に読み込んだ回数。
- Shared Dirtied / Written: バッファを書き換えた、あるいはディスクに書き出した回数。
ポイントは 「Shared Read」 です。ここが異常に高い場合、それはメモリに乗り切っていないか、インデックスが効いておらずフルスキャンに近い状態になっている証拠です。
—
実戦での読み解き方
例えば、こんなクエリを投げてみたとしましょう。
EXPLAIN (ANALYZE, BUFFERS)
SELECT FROM users WHERE last_login > ‘2023-01-01’;
出力結果の末尾に、こんな行が表示されるはずです。
Shared Hit=1200 Read=800 Dirtied=0 Written=0
これを見て、どう考えますか？
「お、1200回もキャッシュにヒットしているから優秀じゃん」なんて思ったら大間違いですよ。注目すべきは Read=800 です。800ブロック分、毎回ディスクに頭を下げてデータを読みに行っているわけです。これが高頻度で実行されるクエリなら、間違いなくI/Oネックの犯人です。
逆に、`Hit` が極端に少なく、`Read` が多い場合は、インデックスが全く機能していない（あるいはインデックス自体がメモリに載っていない）可能性が高い。この「メモリとディスクの比率」を客観的な数字で突きつけられるのが、このオプションの最大の強みです。
—
先輩からのアドバイス：どう使い分ける？
私が現場でよくやるのは、以下の3ステップです。
1. まずは普通に `EXPLAIN ANALYZE`： そもそも実行プランがおかしくないか（Seq Scanばかり選ばれていないかなど）を確認する。
2. 次に `BUFFERS` を追加して再実行： 「プランは正しいはずなのに遅い」原因が、物理I/Oの多さにあるのかを確認する。
3. インデックスを調整して比較： インデックスを貼った後、再度 `BUFFERS` を見て `Read` が劇的に減っているかを確認する。
特に、「インデックスを貼ったのに速度が変わらない」 というケースでは、`BUFFERS` を見るとインデックスの読み込みだけで大量の `Read` が発生していることがあり、「ああ、インデックスが肥大化しすぎてメモリに乗っていないんだな」という推論が立てられます。
—
最後に：数字に踊らされないために
最後に一つだけ。`BUFFERS` の数値は絶対的な正解ではありません。キャッシュの状況や他のプロセスとの兼ね合いで数値は揺れます。
でも、「I/Oという、データベースで最も重い処理を、どの程度使ったのか」 という事実は、チューニングにおいて最も信頼できる手がかりになります。
「なんとなく遅い」を「I/Oがボトルネックになっている」と言い切れるようになるだけで、エンジニアとしての頼もしさはグッと上がります。ぜひ、明日のパフォーマンス調査から `BUFFERS` を添えてみてください。
これだけで、解決までの時間が数時間、あるいは数日単位で短縮されるはずですよ。それでは、良いチューニングライフを！
コメント