統計情報とカーディナリティ

入れたばかりのテーブルで、クエリが返ってこない

移行作業で新しいテーブルを作り、500 万行を流し込み、動作確認のクエリを 1 本投げた。返ってきません。索引はちゃんと作ってあります。

原因は、DB がまだそのテーブルを「ほぼ空」だと思っていることです。プランナは実行のたびに行を数えているわけではありません。別に持っている記録、統計情報を見て見積もっています。作った直後の記録は 0 行に近い値のままなので、「どうせ数行だから 1 行ずつ相手を探しに行けばよい」という手順を選びます。実際には 500 万行あるので、それが延々と続きます。

大量に入れた直後に ANALYZE（MySQL では ANALYZE TABLE）を打つ習慣は、このためにあります。

プランナは数えていない、見積もっている

WHERE age > 30 AND country = 'JP' が何行返すかを、プランナは次のように計算します。

1. テーブルの総行数を記録から取る（100 万行）
2. country = 'JP' を満たす割合を推定する（0.3）
3. age > 30 を満たす割合を推定する（0.5）
4. 掛け合わせる（100 万 × 0.3 × 0.5 = 15 万行）

2 と 3 の割合を出すのに使うのが、列ごとの値の散らばり方の記録です。異なる値がいくつあるか（カーディナリティ）、どの範囲にどれだけ集まっているか（ヒストグラム）が入っています。

異なる値が多い列ほど、1 つ指定したときに残る行が少なくなります。索引が効く列と効かない列の差は、ここから来ています。

4 の掛け算には、注意すべき仮定が 1 つあります。2 つの条件が互いに無関係だと見なしていることです。country = 'JP' AND city = 'Tokyo' のように、片方が決まればもう片方もほぼ決まる組み合わせでは、掛けた結果は実際よりずっと小さくなります。

外れていることは、実測と並べれば分かる

見積もりが当たっているかは EXPLAIN ANALYZE で確かめられます。見込みの rows と実測の actual rows を見比べて桁が違っていれば、そのクエリの手順は当てにならない前提で選ばれたことになります。

外れる原因はだいたい 2 つです。統計が古いか、上の無関係という仮定です。前者は ANALYZE で解消します。増減の激しいテーブルは、自動実行の閾値を下げてこまめに取り直す設定にしておきます。後者は、PostgreSQL なら列の組み合わせを登録して、相関ごと記録させることができます。

3 つ目として、抜き出し方の問題もあります。統計は全行を読んで作るわけではなく、一部を抜き出して作られます。ですから、ごく一部の値だけが極端に多いような偏りは、抜き出しから漏れて記録に載らないことがあります。何度 ANALYZE を打っても見積もりが合わないなら、抜き出す量を増やす設定を検討してください。

「索引を貼ったのに遅い」の犯人が、索引ではなく古い統計だったという話は珍しくありません。