実行計画を解剖する

目標

EXPLAINの出力を最初から最後まで読み、どの数字をどの順序で見るべきかを、身につけます。

なぜ重要なのか

実行計画では、ノード名そのものは、良し悪しを教えてくれません。Seq Scanがインデックススキャンより速い状況は明確に存在し、Nested Loopが最善の場合も多くあります。オプティマイザーは、自分が持っている統計の中では、ほとんど常に合理的に判断します。

そのため、計画が変に見えるときに投げるべき質問は、「なぜオプティマイザーは愚かなのか」ではなく、自分がオプティマイザーに何を誤って教えたのかです。予想行数と実際の行数の乖離が、その答えを教えてくれます。

読む順序を整理すると、次のとおりです。まず、Execution TimeとPlanning Timeを見ます。次に、各ノードの予想rowsと実際のrowsを比較して、1桁以上の倍率で開いた最も内側のノードを探します。loopsが1でないノードは、掛け算をして、実際の寄与を計算します。Rows Removed by Filterが大きいノードで、インデックスの機会を探し、最後に、Buffersのreadの割合で、計画の問題かキャッシュの問題かを切り分けます。

ステップ

すべての出力ファイルは、/root/exp/の下に保存します。

1. ordersを照会する任意のクエリにEXPLAINだけを付けて実行し、出力を/root/exp/plan_basic.txtに保存します。実測値(actual time)がない必要があります。
2. 同じクエリを実際に実行するオプションまで付けて、/root/exp/plan_analyze.txtに保存します。actual timeとExecution Timeがある必要があります。
3. ブロック読み取りの統計まで含めた結合クエリの計画を、/root/exp/plan_buffers.txtに保存します。Buffers: shared ...の行がある必要があります。
4. skewed_eventsという表を作ります。カラムはid、kindで、10万行以上あり、kindがrareの行は、全体の1パーセント以下である必要があります。作った後に、統計を収集して、プランナーが行数を把握できるようにします。
5. 他の結合方式を一時的にオフにした状態で結合クエリを実行し、計画を/root/exp/plan_loops.txtに保存します。Nested Loopと、3桁以上のloops=がある必要があります。
6. ハッシュ結合が選ばれるクエリを実測実行し、/root/exp/plan_hash.txtに保存します。Hash JoinとBatches:がある必要があります。
7. 同じクエリを、デフォルトの状態とシーケンシャルスキャンを止めた状態で、それぞれ実測実行して、2つの結果を/root/exp/plan_forced.txtに連結します。Seq Scan、インデックスを使うノード、そしてExecution Timeが、2回以上ある必要があります。
8. 条件がIndex Condで処理され、Rows Removed by Filterがない計画を実測実行して、/root/exp/plan_fixed.txtに保存します。

参考

• 便利な組み合わせ: EXPLAIN (ANALYZE, BUFFERS) SELECT ...
• 結合方式のオフ: SET enable_hashjoin = off; SET enable_mergejoin = off;
• シーケンシャルスキャンのオフ: SET enable_seqscan = off;。診断用です。本番の設定にしないでください。
• よくある間違い1: ステップ1にANALYZEを付けると、実測値が入って採点に失敗します。
• よくある間違い2: ステップ4で表だけを作って統計収集を抜かすと、プランナーが行数を把握できません。

実行せずに計画だけを見る

ordersを照会する任意のクエリにEXPLAINだけを付けて実行し、出力を/root/exp/plan_basic.txtに保存します。実測値(actual time)がない必要があります。

オプションなしでキーワードを1つだけ付けると、クエリを実行せずに、推定値だけを見せてくれます。

実測値が付いた計画を見る

同じクエリを実際に実行するオプションまで付けて、/root/exp/plan_analyze.txtに保存します。actual timeとExecution Timeがある必要があります。

実際に実行して測定するオプションがあります。書き込みクエリに使うときは、トランザクションで囲んでください。

ブロック読み取りの統計をオンにする

ブロック読み取りの統計まで含めた結合クエリの計画を、/root/exp/plan_buffers.txtに保存します。Buffers: shared ...の行がある必要があります。

キャッシュヒットとディスク読み取りを区別してくれるオプションがあります。括弧の中に、複数のオプションをカンマで並べられます。

値が偏った表を作って統計を収集する

skewed_eventsという表を作ります。カラムはid、kindで、10万行以上あり、kindがrareの行は、全体の1パーセント以下である必要があります。作った後に、統計を収集して、プランナーが行数を把握できるようにします。

generate_seriesで10万行以上を作り、特定の値が1パーセント以下で、とてもまれに出るようにします。作った後に統計を収集して初めて、プランナーが把握します。

繰り返しの多いネステッドループを観察する

他の結合方式を一時的にオフにした状態で結合クエリを実行し、計画を/root/exp/plan_loops.txtに保存します。Nested Loopと、3桁以上のloops=がある必要があります。

他の結合方式を一時的に止めると、ネステッドループが選ばれます。計画で、loopsの値を確認してください。

ハッシュ結合のバッチ数を確認する

ハッシュ結合が選ばれるクエリを実測実行し、/root/exp/plan_hash.txtに保存します。Hash JoinとBatches:がある必要があります。

ハッシュノードの実測情報は、実際に実行して初めて出ます。Batchesが1か、それ以上かを見てください。

強制の前後を実測で比較する

同じクエリを、デフォルトの状態とシーケンシャルスキャンを止めた状態で、それぞれ実測実行して、2つの結果を/root/exp/plan_forced.txtに連結します。Seq Scan、インデックスを使うノード、そしてExecution Timeが、2回以上ある必要があります。

同じクエリを、デフォルトの状態とシーケンシャルスキャンを止めた状態で、それぞれ実行して、1つのファイルに連結します。

FilterをIndex Condに移す

条件がIndex Condで処理され、Rows Removed by Filterがない計画を実測実行して、/root/exp/plan_fixed.txtに保存します。

読んで捨てる代わりに、最初から読まないようにするのが目標です。計画から、Rows Removed by Filterの行が消える必要があります。