- ブラウザURL入力
- DNS名前→IP
- ルータ経路選択
- ロードバランサ振り分け
- Nginx受け口
- アプリ処理
- DB保存今ここ
ここまで観測器の側を見てきました。今回は装置が自ら申告する数字を扱います。申告は測った結果ではなく、予想です。
まず結論
実行計画に並ぶ数字は統計情報からの推定であって、実測ではありません。PostgreSQL 公式は、推定が的中した例の直後に but that's quite unusual in practice と書いています。
だから計画を読むとは、cost の大小を比べることではなく、推定した行数と実際の行数がどれだけ離れているかを見ることです。
実行計画の cost がまったく同じ2つのクエリがあります。実行時間についてどこまで言えますか。
当てることが目的ではありません。外した予測ほど記憶に残るので、直感で選んでください。
計画に並ぶ数字は、測る前の予想である
実行計画は、データベースが「こう処理するつもりだ」と示したものです。並ぶ数字は統計情報から計算した推定であって、実行した結果ではありません。
推定は2種類。行数と cost です。行数は「この条件に何行当たるか」の予想、cost はその予想から導いた選択の根拠です。
cost の単位について、公式は言い切っています。
The costs are measured in arbitrary units determined by the planner's cost parameters
任意単位です。慣例として逐次読み1ページを 1.0 に置き、他をその相対値とします。時間の単位ではありません。 同じ文書は、実測値と並べたときの読み方も添えています。
Note that the “actual time” values are in milliseconds of real time, whereas the cost estimates are expressed in arbitrary units; so they are unlikely to match up.
- ・上3つは実行前に決まる。下1つだけが実行の産物
- ・cost は任意単位、actual time はミリ秒。単位が違う
そして公式は、推定が的中した例を示した直後にこう書きます。
In this example the estimates were all dead-on, but that's quite unusual in practice.
公式が自ら「的中するのは実際には珍しい」と書いているわけです。
同じ cost、878倍の実行時間
確かめます。PostgreSQL 16.15(Docker Desktop の Linux VM、aarch64)に 500,000 行の表を作り、列の値を click 499,995 行 / purchase 5 行と偏らせました。統計を空にし、条件だけを変えた2つの問い合わせを測ります。
=== click(実際は 499,995 行) ===Aggregate (cost=3843.21..3843.22 ...) (actual time=56.952..56.953 ...)-> Bitmap Heap Scan on events (cost=31.80..3836.96 rows=2500 width=0)(actual time=6.605..31.618 rows=499995 loops=1)Execution Time: 57.047 ms=== purchase(実際は 5 行) ===Aggregate (cost=3843.21..3843.22 ...) (actual time=0.039..0.039 ...)-> Bitmap Heap Scan on events (cost=31.80..3836.96 rows=2500 width=0)(actual time=0.025..0.029 rows=5 loops=1)Execution Time: 0.065 ms
読みどころは2つ。
推定行数がどちらも 2500 です。 列の分布を知らないプランナは、値を見ずに同じ数を答えます。実際は一方が 499,995 行、もう一方が 5 行。約200倍の過小と、500倍の過大。外れる向きすら逆です。
cost は小数以下まで一致しています。 3843.21 対 3843.21。それでいて実行時間は 57.047 ms 対 0.065 ms、およそ878倍の開きでした。
この878倍はこの実験環境での値です。偏りを変えれば比も変わります。一般則にできるのは定性の部分だけ——cost が同じでも実行時間は桁で違いうる。
実行計画の cost について、公式の記述に即して正しいのはどれですか。
推定が外れる装置は、データベースだけではない
一般化します。装置が出す数字には、実行前に決まるものと実行の産物の2種類があり、前者は必ず何らかのモデルに乗った推定です。
推定である以上、外れます。問題は外れること自体ではなく、外れているかを外から確かめられるかです。計画に rows= と actual rows= が並ぶのは、この照合のためです。
ただし「推定は疑わしく、実測は正しい」という二分法では読めません。公式はマージ結合について、内側の再走査が
counts these repeated emissions of the same inner rows as if they were real additional rows
と重複して数えられる、と注意しています。実測の側にも、数え方という仕掛けがあります。
人が桁で見積もるときと、同じ構造
アルゴリズム編『桁で見積もってから測る』で、処理をたどって「この規模ならこの桁」と当たりを付ける手順を扱いました。あれは人が頭の中で立てる実行計画です。
そこでも見積もりは仮定の上に乗っていました。仮定が外れれば結論の桁が動きます。だから見積もりの後に必ず測り、ずれた箇所から仮定を洗い直す。オプティマイザも同じです。統計という仮定を選択率の式に通して行数を出し、そこから cost を出す。仮定が古ければ、出てくる桁が丸ごとずれます。
違うのは、その仮定が自分で書いたものではない点です。
DB編『索引が使われない理由 — 左端プレフィックスと、その反例』では EXPLAIN を、索引が使われたかを見る道具として使いました。今回は同じ出力を、推定と実測の差という軸で読んでいます。
統計を更新したところ、ある計画の cost が 3843 から 8292 へ増えました。どう読むべきですか。
プランナの推定行数
統計情報という仮定に選択率の式を当てはめて推定行数を出し、そこから cost を計算します。統計が古いと値の偏りを写せず、同じ数を返します。実行すると actual rows が並ぶので差が見えます。
対象 推定行数 実際の行数 向きclick 2,500 499,995 過小purchase 2,500 5 過大
人が桁で見積もってから測る
処理をたどり、規模と1件あたりの費用を仮定して桁を出します。仮定は自分で置いたものなので、測った値とずれたときにどれを疑うか追えます。アルゴリズム編で扱った手順です。
段階 出どころ ずれたときに疑う先見積もり 置いた仮定 仮定そのもの実測 実行した結果 測り方・数え方
データベースの推定行数と、人が紙の上で立てる見積もり。共通する構造は何でしょうか。
どちらも「外れること」が前提で、そのあとに何をするかが決まっています。
統計を入れると計画が変わる。ただし当たるわけではない
先ほどの表に ANALYZE events; を走らせました。問い合わせもデータも索引も変えていません。変えたのは統計だけです。
更新前 更新後click の推定行数 2,500(実際 499,995) (要確認・省略)click の計画 Bitmap Heap Scan Parallel Seq Scanclick の cost 3843.21 8292.21click の実行時間 44.5 ms 15.8 mspurchase の推定行数 2,500(実際 5) 1(実際 5)purchase の計画 Bitmap Heap Scan Index Only Scanpurchase の cost 3843.21 4.44
3つ読み取れます。
計画の形が変わりました。 索引を使う走査から並列の全走査へ、あるいは索引だけで済む走査へ。統計は cost を微調整する入力ではなく、どの手順を選ぶかを決める入力です。
cost は増えたのに、実行時間は減りました。 3843 から 8292 へ、44.5 ms から 15.8 ms へ(44.5 ms は上の 57.047 ms とは別の実行の値)。「cost が下がれば速くなる」は成り立ちません。前後で統計が変わった以上、cost の計算の前提が入れ替わっており、更新をまたいだ比較は同じ物差しではないからです。
そして、当たるようにはなっていません。 purchase の推定は 1 行、実際は 5 行のままでした。統計そのものが標本であって全数ではありません。 言えるのは「統計が更新されると推定と計画が変わる」まで。click の更新後の推定行数は、元の出力と照合できず表から外しました。少数派の推定が外れ続けた理由は確かめていません。
この章のまとめ
- 実行計画の数字は統計情報からの推定。実行前に決まる。
- cost は時間ではない。 公式が arbitrary units と明記し、actual time とは
unlikely to match upと書いている。 - 見るべきは cost ではなく、推定行数が実際の行数にどれだけ近いか。
- 公式は推定が的中した例の直後に
but that's quite unusual in practice。外れるのが普通。 - 統計を更新すると計画の形が変わる。ただし cost が増えて実行時間が減ることもあり、推定が当たるとも限らない。
EXPLAIN ANALYZEは文を実際に実行する。 更新系はBEGIN;とROLLBACK;で包む。
自分の現場で確かめる
読んだだけでは身につきません。手元の環境で実際に見てみてください。
理解度チェック
読んだ直後は「分かった」と感じやすいものです。実際に解いて確かめてください。
EXPLAIN ANALYZE の出力を読むとき、公式が「たいてい最も重要」と述べているのはどこですか。
用語集(4語)
- 実行計画 (plan)
- 問い合わせをどう処理するかの手順。プランナが統計をもとに候補を見積もり、cost が最小のものを選ぶ。
- cost
- 計画の見積もり値。公式いわく arbitrary units で、時間の単位ではない。慣例として逐次読み1ページを 1.0 とした相対値。
- 推定行数 / 実際の行数
- rows= が推定、EXPLAIN ANALYZE が付ける actual ... rows= が実行時に数えた値。両者の開きが計画を読む手がかりになる。
- ANALYZE(統計収集)
- 列の分布などの統計を集めるコマンド。標本にもとづくので全数調査ではない。EXPLAIN の ANALYZE オプションとは別物。