【テクニカル・上級編】 EXPLAIN ANALYZE – PostgreSQL

魔法の杖ではないけれど：EXPLAIN ANALYZEで「真実」を読み解く作法

PostgreSQLを長年触っていると、ある種の直感のようなものが養われてくる。「このクエリは恐らくNested Loopで回るだろうな」とか「このインデックスは使われないはずだ」といった予測だ。しかし、データベースの内部エンジンは時として、我々の想像を軽々と裏切る。

そんな時、僕らが頼るべきは `EXPLAIN ANALYZE` という名の「真実」だ。だが、多くのエンジニアはこれの結果を、ただ漫然と眺めているだけではないだろうか？

今日は、ただ実行計画を見るだけでは終わらない、プロフェッショナルがどこに目を光らせているのか。その深淵に少しだけ踏み込んでみたい。

1. 予測と現実の乖離：COSTは見えない歪みを映し出す

`EXPLAIN ANALYZE` の最大の価値は、オプティマイザが算出した「推定コスト」と、実際の「実行結果」を並べて比較できる点にある。

多くの人が見落とすのは、`rows`（推定行数）と `actual rows`（実行数）の乖離だ。ここが大きくズレている場合、オプティマイザは統計情報を正しく活用できていない可能性が高い。

なぜ乖離するのか？
統計情報の鮮度: `ANALYZE` が実行されていない、あるいはサンプリングレートが低すぎて分布が偏っている。
相関関係の欠如: 複数カラムにまたがる条件があるのに、個別の列統計しか参照していない（拡張統計情報の出番だ）。
式や関数の使用: `WHERE UPPER(name) = ‘…’` のような記述で、インデックスのカーディナリティをオプティマイザが見積もれていない。

この乖離が激しいと、Nested Loopを選択すべき場面でHash Joinが選ばれたり、その逆が起きたりする。実行計画が「おかしい」と感じた時、まずはこの「予測 vs 実績」の溝を埋めることから始めるのが、熟練の流儀だ。

2. 「ループの罠」を視覚化する

`actual time` に注目してほしい。特に注意すべきは、Nested Loopで外側のループが何回実行され、内側のループがどれだけのコストを支払っているかだ。

例えば、`loops` の値が非常に大きい場合、インデックスは効いているはずなのに、I/O負荷が想定以上に跳ね上がっている可能性がある。これは、インデックスのページを読み込む際のランダムI/Oが、メモリのキャッシュ効率を悪化させている典型的な兆候だ。

僕がトラブルシュートの際に見るのは、「どのノードが最も時間を消費し、かつそのノードが何回ループしているか」の掛け算だ。見た目上のコストが小さくても、高頻度で実行されるノードが全体のレイテンシを腐らせていることは珍しくない。

3. JITコンパイルは「諸刃の剣」である

PostgreSQL 11以降、`EXPLAIN ANALYZE` を叩くと、しばしば `JIT` に関する情報が表示される。

JITコンパイルは、複雑なクエリの実行速度を劇的に改善する可能性を秘めている。しかし、小規模なクエリや、実行時間がミリ秒単位で終わるようなクエリでは、コンパイルそのもののコストが無視できないオーバーヘッドとなる。

もし `Actual Time` の内訳に `JIT generation time` が含まれていて、かつ実行時間が期待値より長いなら、それは「PostgreSQLが頑張りすぎている」証拠だ。`SET jit = off;` して実行計画の変化と応答速度を比較してみる。この実験的なアプローチこそが、チューニングの醍醐味ではないだろうか。

4. 最後に：ツールに振り回されないために

`EXPLAIN ANALYZE` は強力な武器だが、忘れてはならないことがある。それは「実際のクエリを実行している」ということだ。

本番環境の巨大なテーブルで安易に実行すれば、クエリによってはロックを取得したり、I/Oを飽和させたりするリスクがある。また、`EXPLAIN ANALYZE` 自体のオーバーヘッドもゼロではない。

僕が現場でよくやるのは、`EXPLAIN (ANALYZE, BUFFERS)` を使うことだ。`BUFFERS` を付与することで、共有バッファからヒットしたのか、それとも物理的なI/Oが発生したのか（Read）が明確になる。これを見れば、キャッシュの効率性やディスクへの依存度が手に取るようにわかるはずだ。

データベースをチューニングする行為は、まるでミステリー小説の伏線を回収する作業に似ている。断片的な情報の裏側に、どんなデータ構造と処理の連鎖が隠れているのか。

さあ、皆さんも次のクエリで `EXPLAIN ANALYZE` を叩く時、単なる数値の羅列ではなく、その背後にあるPostgreSQLの「意思」を読み解こうとしてみてほしい。そうすれば、きっと今まで見えなかったボトルネックの正体が、向こうから姿を現してくれるはずだ。