行数の見積もりが方式の選択を変える
クエリ最適化器は、候補となる走査や結合の費用を比較して実行計画を選びます。その基礎になるのが、各操作が何行を返すかという見積もりです。外側が10行なら安い探索を繰り返す方式でも、10万行なら繰り返しの費用が大きくなる場合があります。計画の名前だけを見て、nested loopが悪い、索引が常に良いと決めないようにします。まず、どの入力条件に対してどの量を見込んだかを読みます。推定の誤差と、費用モデルが実際の装置やキャッシュをどの程度表しているかは別の問題です。一方を直すと必ず全体が最適になるわけではありません。
選択度から基本の行数を計算する
100万行の表で条件に合う割合を2%と見積もるなら、結果の推定は2万行です。状態列の値が二種類あるから50%ずつとする計算は、実際の分布に偏りがあると外れます。PostgreSQLは頻出値やヒストグラムなどの統計を使って見積もりを作ります。統計は全更新のたびに完全な分布へ更新されるわけではなく、標本や更新時点による誤差があります。大きな一括投入の直後に変化した分布を、以前の統計が表していない場合もあります。まずデータの範囲と統計の鮮度を確認し、実際の偏りに対応する仮説を立てます。
複数条件の独立性を疑う
地域と郵便番号のように、二つの列が強く関係することがあります。地域の条件が1%、郵便番号の条件が1%だからといって、両方の条件の割合が必ず0.01%になるとは限りません。一方が他方をほぼ決めるなら、積は過小な推定になります。拡張統計は、複数列の関係を扱う助けになりますが、任意の式や結合条件に対する万能な補正ではありません。どの種類の統計がどの条件に使われるかを公式文書で確認します。業務上の制約と、プランナが収集した統計上の関係も別です。統計を作るだけで参照整合性が保証されることはありません。
実測のrowsとloopsを一緒に読む
EXPLAIN ANALYZEは実際に文を実行するので、更新系の文や重い本番検索を不用意に使いません。安全な検証環境で、推定rowsと実際のrowsを比較します。反復するノードのactual rowsは一回当たりの平均として表示されるため、loops=100でrows=5なら、合計はおよそ500行です。表示上の丸めにも注意します。親ノードの時間には子の処理が含まれるので、すべての時間を足して全体時間を作る読み方も不適切です。単位をそろえて初めて、量のずれが繰り返しや時間へどう伝わったかを説明できます。
誤差の発生点を下から追う
最上位の行数が大きく外れていても、原因がそこで生じたとは限りません。下位の走査やフィルタから順に、推定と実測の差を追います。例えば一つのフィルタで推定20行、実測5000行なら、後続の結合にも過小な入力が渡っています。まずこの条件の偏り、統計、式、パラメータを調べます。上位の結合方式だけを強制して症状が改善しても、別の入力では逆効果になる場合があります。変更は一つずつ行い、同じ問い合わせと入力で結果の正しさも確認します。速さだけでなく、どの誤差をどう説明できたかを記録します。
入力分布の違いを評価へ入れる
特定の利用者だけ注文数が非常に多い場合、同じSQLでも利用者IDによって適した計画が変わり得ます。少数件の例だけで最適化を完了とせず、多い側と少ない側を含めて試します。キャッシュが温まった実行と初回実行、同時実行の有無も分けます。統計目標値を上げる、ANALYZEを行う、拡張統計を作る、索引やSQLを変えるといった対処には、それぞれ作成・維持や計画の費用があります。全件に同じ対処を適用せず、最初に立てた仮説に対応する変更を選び、効果と副作用を確認します。