障害・アンチパターン図鑑 · 基礎から応用へ

見積もりの誤差による遅いクエリ

推定行数と実際の行数が食い違う場所を探し、統計・条件・計画を同じ入力で確かめます。

この記事をロゲイアに質問する確認問題3問に進むこれまでの誤答を復習する

更新日

この記事の目次
  1. 索引の有無より行数の仮定を追う
  2. 推定と実績を同じ単位へそろえる
  3. 相関する条件の具体例を作る
  4. 統計の種類と対応範囲を選ぶ
  5. 計画の変化と実行の改善を分ける
  6. 再現条件と別の入力を保存する
  7. この記事をロゲイアに質問する理解を確かめる

索引の有無より行数の仮定を追う

PostgreSQLの最適化器は、条件を通る行数を推定し、その集合をどう走査し、どの順序と方法で結合するかを選びます。予測が外れると、小さい入力に向く処理を大きい入力へ繰り返し、想定以上の探索やソートを行う場合があります。遅い計画を見たときに「索引がないから」と即断せず、各ノードで想定した行数を読みます。上位ノードの大きな誤差は、下位ノードの条件から引き継いだ結果かもしれません。最初にどこで差が広がるかをたどると、統計、述語、結合条件のどこを検討するかを絞れます。ただし誤差があることと、それが遅延の主要因であることは別です。ロック待ちやストレージの問題も同時に存在し得るため、実行時間と待機情報を合わせて仮説を立てます。

推定と実績を同じ単位へそろえる

EXPLAINのrowsは推定、EXPLAIN ANALYZEのactual rowsは実行に基づく情報です。複数回動くノードでは、actual rowsをloopsと一緒に読み、一回当たりの表示値と総処理量を混同しません。例えば一回に10行返すノードが1000回なら、総出力は概算で1万行です。親子ノードの時間には包含関係があるため、全ノードの時間を単純に足すと二重に数える場合があります。費用値は通常のミリ秒ではなく、計画を比べるためのモデル上の単位です。EXPLAIN ANALYZEは対象のSQLを本当に実行し、更新文なら変更を行い得ます。検証環境を用意し、文、パラメータ、データ量、版、設定を保存して、同じ対象の推定と実績を比べます。

相関する条件の具体例を作る

説明用に1万行の注文があり、国Xが1000行、都市Yが1000行で、Yの全件がXに属すると仮定します。独立と仮定すれば各条件の選択率0.1を掛け、同時成立は100行と見積もりたくなります。しかしこのデータの実際の積集合は1000行です。単一列の分布を最新にしても、国と都市の関係を独立として扱えば誤差が残ります。これは仮定したデータによる計算例で、稼働環境の実測ではありません。条件が実際にどの行を表すかを確認し、型変換、関数、NULL、偏った値も調べます。似た列名があるから同じ関係とは限らず、データの意味を確かめずに統計を増やしても目的へ届きません。

統計の種類と対応範囲を選ぶ

大量投入や分布の変化後ならANALYZEで統計を更新することが候補です。相関する複数列には拡張統計の依存関係、頻出値の組合せ、異なる値の組数など、目的に対応した種類を選びます。すべての統計が任意の述語や結合の推定へ使われるわけではありません。CREATE STATISTICSを作っただけで収集が完了したとも考えず、必要なANALYZEと対象版の利用範囲を確認します。統計ターゲットを上げる方法にも収集時間や容量の費用があります。まず対象の条件がどの情報を必要とするかを説明し、変更後に最初の誤差が縮まったか、目的の計画判断へ反映されたかを確認します。広範な設定を一度に変えると、何が効いたかが分からなくなります。

計画の変化と実行の改善を分ける

推定が改善してNested Loopから別の結合へ変わっても、応答が必ず速くなるとは限りません。入力件数やキャッシュ条件によって適切な方法は違い、特定の結合方式を悪者にする説明は避けます。ハッシュやソートの一時領域への退避、I/O、結果の転送、並行処理も全体の時間へ関係します。索引を追加すれば検索の選択肢は増えますが、書き込み時の維持費用も発生します。推定誤差を小さくすることは手段であり、目標は利用者が必要とする応答時間と処理率です。実行計画の形、実績の行数、資源使用、要求の成功率を同じ仕事で比べ、見た目の計画が変わったことだけを改善の根拠にしないようにします。

再現条件と別の入力を保存する

問題が起きるパラメータだけでなく、頻繁に使う値、空の結果、大きな顧客、期間の端などを含めて再確認します。一つの値で計画を強制すると、別の値や将来の分布で大きく悪化する恐れがあります。Prepared statementの一般計画と値ごとの計画の違いも、必要なら切り分けます。変更を記録するときは、最初の推定誤差、分布の根拠、採用した統計やSQLの修正、改善した業務指標を残します。改善しなかった場合も、統計で扱えなかった条件か、別の資源が支配したかを説明できる記録にします。最適化器を信用するか否かという二択ではなく、モデルの仮定と実データを比較して、検証できる変更を一つずつ選ぶ姿勢が重要です。

読了と問題の結果を、言語ごとにこのブラウザへ保存します。

コースの順番に沿って学ぶ

サーバーの中で起きていること · STEP 12

学習コースから次を選ぶ