EXPLAIN ANALYZE — 何をどの順で見るのか

一言でいうと

実行計画を読むとは、ノード名をざっと眺めることではなく、オプティマイザーの予測と実際の結果の乖離を見つけることです。

なぜ必要なのか

遅いクエリにEXPLAIN ANALYZEを付けて、画面いっぱいの括弧の中の数字をしばらく眺めたあと、「Seq Scanが見えるから、インデックスを作ろう」へ飛ぶことがよくあります。その結論が正しいときもありますが、計画が伝えようとしていたのは、たいてい別の話です。

乖離がなければ、オプティマイザーは自分が知っている情報の中で最善を選んだという意味で、残った原因は物理的な問題です。乖離が大きければ、オプティマイザーは誤った前提の上で正確に計算したのであり、手を入れる対象は、クエリではなく統計です。

どう動くのか

EXPLAINは計画を立てるだけで、EXPLAIN ANALYZEは実際に実行します。そのため、EXPLAIN ANALYZE UPDATE ...は、本当にUPDATEを実行します。書き込みクエリを分析するときは、トランザクションで囲んでロールバックする必要があります。

読む順序は、最も深くインデントされたノードからです。インデントが深いほど先に実行され、親ノードは、子が終わった後に完成します。そして、親のactual timeは、子の時間を含んだ累積値です。この事実を知らないと、「結合が131msもかかっている」という誤った結論に達します。

cost=1842.00..24310.55のうち、前は最初の行まで、後ろは最後の行までのコストです。単位はミリ秒ではなく、シーケンシャルページ1枚の読み取りを1.0とした任意の単位です。そのため、他のクエリのcostと比較することには意味がなく、「costがいくつを超えたら危険」のような基準にも、根拠がありません。

本当に見るべきものは、同じノードの中の2つの数字です。

->  Index Scan using idx_orders_status on orders o
      (cost=0.42..8.44 rows=1 width=20)
      (actual time=0.031..214.882 rows=482913 loops=1)

予想1行、実際は48万行。4万倍以上外れています。オプティマイザーは、「1行しか出ないだろうから、ネステッドループで結合すればよい」と判断したはずで、その前提が崩れて、内側のノードを48万回繰り返すことになります。このとき直すべきものは、結合のヒントではなく、統計です。

loopsは、必ず掛け算して見る必要があります。表示された時間と行数は、1回の実行あたりの平均です。actual time=0.011 rows=4 loops=52310なら、合計時間は約575ms、合計行数は約20万行です。計画のどこにも575という数字は書かれていないので、掛け算を自分でしてみないと、ボトルネックを見逃します。

BUFFERSオプションのないEXPLAIN ANALYZEは、半分しか役に立ちません。shared hitはバッファーキャッシュで見つけたブロック、shared readはキャッシュの外から読んだブロックです。同じクエリが昨日は20ms、今日は900msである理由は、たいてい計画ではなく、キャッシュにあります。

現場での姿

FilterとIndex Condの違いが重要です。Filterは、行を読んだ後に捨てるもので、Index Condは、そもそも読まないものです。Rows Removed by Filterの下に大きな数字があれば、その条件をインデックスの条件に移せる余地があるというサインです。

Hash JoinでBatchesが1でなければ、ハッシュテーブルがwork_memに収まらず、ディスクに分割されたという意味です。この場合、インデックスを作るより、そのセッションのwork_memを上げるほうがはるかに効果的です。ただし、全体の設定を上げるのは危険です。work_memは、接続ごとではなく、クエリの中のソートやハッシュ演算1つごとに割り当てられるからです。

統計が間違っているとき、何をするのか

先ほど見た4万倍の乖離は、オプティマイザーが怠けたから生じたものではなく、誤った前提の上で正確に計算した結果です。それなら、前提を直す必要があります。

まず、統計が古いかどうかを見ます。大量に入れたり削除したりした直後は、統計が実際と大きく違います。自動的に更新される仕組みはありますが、それが動くしきい値は表のサイズに比例するので、大きな表では、かなりの変化が溜まってから動きます。大量の作業の後は、手で1回更新してやるのが定石です。

サンプルが足りないかを見ます。統計はサンプルから作られ、デフォルトのサンプルサイズは大きくありません。値がとても多様なカラムや、偏りが激しいカラムでは、そのサンプルが実際の分布を捉えられません。カラム単位でサンプルの目標を引き上げられ、引き上げた後は、統計を作り直して初めて反映されます。

カラム同士が絡み合っているかを見ます。これが最も見落としやすい場所です。オプティマイザーは、デフォルトで、条件が互いに独立だと仮定して、選択度を掛け合わせます。ところが、city = '서울'とregion = '수도권'は独立ではありません。掛け合わせると、実際よりはるかに小さい数が出て、その小さい数を信じて、ネステッドループを選びます。複数のカラムの相関を一緒に測る拡張統計を作っておけば、この種の誤推定がなくなります(韓国語の値は、それぞれ「ソウル」と「首都圏」を意味します)。

式で絞り込む場合も同じです。lower(email) = ...のように関数をかぶせると、その式に対する統計がないので、オプティマイザーは固定された概算値を使います。式インデックスを作ると、インデックスを使えるようになるのとあわせて、その式の統計も生まれます。

そして、直せない場合もあります。条件が別の表の値によって変わったり、同じクエリがパラメーターによってまったく違う分布に出会ったりする場合です。そのときは、計画を強制する前に、クエリを2つに分けることを先に検討します。1つのクエリが、2つのまったく異なる仕事をしているなら、オプティマイザーに1つの計画を選べと求めること自体が無理です。

次のラボですること

EXPLAINとEXPLAIN ANALYZEの違い、BUFFERSの効用、loopsの掛け算、HashのBatches、そして強制的にインデックスを使わせたときに、かえって遅くなる場合を、自分で測定してファイルに残します。