RDBMS のクエリ実行を理解する

ブラウザの中で動く Postgres を使う。EXPLAIN のコストも Buffers: も Postgres が計算した値がそのまま返り、SQL は書き換えて再実行できる。select version() を実行すると、Postgres のバージョンが返る。

すべての手順で同じインスタンスを使う。ある手順で作った索引や入れた行は、後の手順にも残る。

使うのは events テーブル 1 つである。実行ボタンを押した時点で、次の状態で用意される。

全 200,000 行で、analyze 済みである。payload は、1 行のバイト数を増やし、1 ページに入る行数を減らすために入れている。行が小さいと 1 ページに入る行数が増え、1% の行でもほぼすべてのページに散らばる。そうなると、どの条件でも全ページを読むので、手順 04 で purchase と click の Heap Blocks に差が出ない。

計画を読む

EXPLAIN は、プランナが選んだ計画と、その見積もりを返す。EXPLAIN だけでは、クエリは実行されない。

explain
select kind, count(*) from events group by kind;

cost=A..B の A は最初の 1 行を返すまでの推定コスト、B は全行を返すまでの推定コストである。単位は秒ではない。1 ページを順に読むコスト (seq_page_cost、既定値 1.0) を 1 とした相対値である。

計画の木は、内側のノードから先に実行される。字下げが深く、-> が右にある行から読む。

Seq Scan の行の rows= が 200000 であることを確かめる。where が無いので、見積もりは全行になる。その上の HashAggregate の rows=3 は、kind の種類数の見積もりである。プランナがこの 3 を何から求めたかは、手順 03 で確かめる。

group by kind を group by user_id に変えて実行し、HashAggregate の rows= の値を見る。

見積もりと実測を並べる

ANALYZE を付けると、クエリを実際に実行し、各ノードの実測を (actual ...) に出す。

explain analyze
select kind, count(*) from events group by kind;

同じ行に rows= が 2 回出る。左の rows= が見積もり、(actual ...) の中の rows= が実測である。 以降の手順では、この 2 つの比を見る。

Buffers: shared hit= も出ている。この値は、ノードが読んだ 8 KB ページの枚数である。Postgres 18 から、EXPLAIN ANALYZE に BUFFERS を指定しなくても Buffers: が出る。

計画を比べるときは、Execution Time ではなく Buffers の値を比べる。 Execution Time は同じクエリでも実行のたびに変わるが、読んだページ数は、データと計画が同じなら毎回同じ値になる。手順 05 では、Execution Time が短いほうの計画が、Buffers では多くのページを読んでいる例が出てくる。

Seq Scan の Buffers の値は、テーブルの全ページ数と一致する。Seq Scan が全ページを読んだことを示している。

where kind = 'purchase' を足して実行し、Seq Scan の Buffers の値を見る。値は減らない。理由は手順 04 で確かめる。

見積もりを手で計算する

プランナは、行数を見積もるときにテーブルの行を読まない。ANALYZE が pg_statistic に書いた統計を読む。

kind = '...' のような等価条件の行数の見積もりは、値が最頻値の一覧にあれば、その値の頻度に総行数を掛けた値である。この掛け算を、pg_stats と pg_class から再現する。

select v.kind, v.freq,
       round(v.freq::numeric * c.reltuples::numeric) as planner_rows
from pg_stats s
cross join lateral unnest(s.most_common_vals::text::text[], s.most_common_freqs)
       as v(kind, freq)
join pg_class c on c.relname = s.tablename
where s.tablename = 'events' and s.attname = 'kind'
order by v.freq desc;

most_common_vals は anyarray 型なので、::text::text[] と二段でキャストする。

次に索引を作り、計画の rows= を planner_rows と比べる。

create index if not exists events_kind_idx on events (kind);

explain
select * from events where kind = 'purchase';

計画の rows= が、上の結果の purchase の行の planner_rows と一致する。

planner_rows は、実際の行数 2,000 から数 % ずれている。ANALYZE が全行ではなく、標本として選んだ行だけを読むためである。planner_rows の値は、ANALYZE を実行するたびに数 % 変わる。

'purchase' を 'click'、'view' に変えて実行し、そのつど計画の rows= が planner_rows と一致することを確かめる。

選択率が走査方法を決める

kind の索引は events_kind_idx の 1 本だけである。それでも、kind に指定する値によって計画が変わる。

計画を分けるのは、索引の有無ではなく選択率である。選択率は、全行のうち条件を満たす行の割合である。

explain analyze
select * from events where kind = 'purchase';

explain analyze
select * from events where kind = 'click';

explain analyze
select * from events where kind = 'view';

3 つの結果の Bitmap Heap Scan の行にある Heap Blocks の値を並べる。

purchase (1%) では、Heap Blocks がテーブルの全ページ数の半分ほどになる。索引を使うと読むページ数が減るのは、purchase のように選択率が低い条件である。

click (24%) の Heap Blocks は、テーブルの全ページ数と同じ値になる。索引のページを読んだうえで、テーブルのすべてのページを読んでいる。選択率が 1% から 24% に上がると、索引を使っても、読むテーブルのページ数が Seq Scan と同じになる。

view (75%) では、計画から Bitmap Index Scan のノードが消え、Seq Scan が選ばれる。

捨てられた計画を出す

EXPLAIN は、プランナが選ばなかった計画を表示しない。enable_seqscan を off にすると、プランナはほかの走査方法がある限り Seq Scan を選ばないので、Seq Scan を除いた計画が出る。

set で変えた設定はセッションが終わるまで残るので、同じフェンスの末尾で必ず reset する。

set enable_seqscan = off;

explain analyze
select * from events where kind = 'view';

reset enable_seqscan;

手順 04 の view の結果と見比べる。enable_seqscan = off で実行した側は、Heap Blocks の値がテーブルの全ページ数と同じで、そのうえ索引のページも読んでいる。そのため、Buffers の値が手順 04 の Seq Scan より大きい。

ところが Execution Time は、enable_seqscan = off で実行した側のほうが短いことがある。

コストモデルは、ランダム読み取りの 1 ページのコスト (random_page_cost、既定値 4.0) を、順次読み取り (seq_page_cost、既定値 1.0) の 4 倍として見積もる。この比は、ディスクのランダム読み取りが順次読み取りより時間がかかることを前提にしている。ブラウザの中で動く Postgres はデータをメモリに置き、ディスクを読まないので、この前提が当てはまらない。それでもプランナは、この環境でも既定値の random_page_cost と seq_page_cost で計画を選ぶ。

末尾の reset enable_seqscan; を消して実行し、そのあと手順 04 の view のフェンスを実行し直す。enable_seqscan が off のまま残るので、view の計画が Seq Scan に戻らない。確かめたら、reset enable_seqscan; だけを書いたフェンスを実行して設定を戻す。

境目を自分で動かす

user_id の値は 5,000 種ある。user_id < N の N を 1 増やすと、選択率が 0.02% (5,000 分の 1) ずつ上がるので、選択率を 0 から 100% まで細かく変えられる。

create index if not exists events_user_idx on events (user_id);
analyze events;

explain
select * from events where user_id < 500;

500 を 1000、2000、2500、2600 と上げて実行し、計画の最上位のノードが Seq Scan に変わる N の範囲を絞る。

Seq Scan が選ばれたあとの総コスト (cost= の右の値) は、N を変えてもほとんど変わらない。全ページを読むコストは、条件を満たす行の数に関係しないためである。Bitmap Heap Scan の総コストだけが選択率に比例して上がり、Seq Scan の総コストを超えたところで計画が切り替わる。

切り替わる N は 2,500 前後で、選択率は全体の半分あたりである。「索引は選択率が 10% を越えると使われない」という通説の 5 倍ほどの選択率である。読むページがテーブルの全ページに近づくと、プランナは Bitmap Heap Scan の 1 ページのコストを、順次読み取りと同じ seq_page_cost で見積もるためである。

切り替わる N を見つけたら、フェンスの先頭に set random_page_cost = 16; を足して探し直す。random_page_cost を既定値の 4 から 16 に上げると、プランナはより小さい N で Seq Scan に切り替える。終わったら、フェンスの末尾に reset random_page_cost; を足す。

統計を古くして計画を壊す

ここまでは、統計がテーブルの中身と一致していた。ここからは、行を足したあとに ANALYZE を実行せず、統計を古いままにする。

kind = 'refund' の行を 40,000 行入れ、ANALYZE を実行しない。統計の最頻値の一覧に refund が無いまま、プランナが計画を選ぶ。

delete from events where kind = 'refund';
insert into events (kind, user_id, at, payload)
select 'refund', g % 5000, now() - (g || ' milliseconds')::interval, repeat('x', 80)
from generate_series(1, 40000) g;

explain analyze
select * from events where kind = 'refund';

先頭の delete は、何度押しても refund の行が 40,000 行になるようにするためのものである。

見積もりの rows=1 に対して、実測の rows= は 40000 である。見積もりが実測の 40,000 分の 1 になっている。

Index Scan の行の総コストを見る。1 行だけ取り出すと見積もったときのコストである。このコストが Seq Scan より低く見積もられたので、Index Scan が選ばれている。手順 08 で ANALYZE を実行すると、プランナは Index Scan を選ばなくなる。

generate_series(1, 40000) を (1, 4000) に減らして実行し直す。見積もりは rows=1 のままである。kind のように、最頻値の一覧の頻度を合計すると全行になる列では、一覧に無い値は、何行入れても 1 行と見積もられる。

ANALYZE で直す

ANALYZE で標本を取り直し、refund を統計の最頻値の一覧に加える。

analyze events;

explain analyze
select * from events where kind = 'refund';

見積もりの rows= が、実測の rows= とほぼ一致する。総コストは、手順 07 の数百倍に増えている。

Execution Time が短くなったのではない。見積もりの行数が実際の行数に合ったので、コストも 40,000 行を読む値になった。その結果、プランナは Index Scan をやめて別の計画を選ぶ。

手順 03 の pg_stats のフェンスへ戻って実行し直す。結果に refund の行が加わっている。

統計では直らないずれ

プランナは、条件を満たす行が索引の並びの中に均等に散らばっていると仮定する。条件を満たす行が索引の並びの一方の端に固まっていると、読む行数の見積もりが実際とずれる。

refund の行は、手順 07 で at が挿入した時点から 40 秒前までの範囲だけに入れた。at の昇順で並べると、refund の行は末尾に固まっている。order by at の昇順と降順の 2 本を比べる。

create index if not exists events_at_idx on events (at);
analyze events;

explain analyze
select * from events where kind = 'refund' order by at limit 10;

explain analyze
select * from events where kind = 'refund' order by at desc limit 10;

2 本の Limit の行の総コストが完全に一致することを確かめる。プランナは、昇順と降順で読む行数が違うことを見積もりに入れていない。

一方で、2 本の Buffers の値の差は 3 桁、Execution Time の差は 3 桁近くある。

昇順の側の Rows Removed by Filter の値を見る。LIMIT 10 の 10 行を返すために、20 万行を読んで捨てている。LIMIT が付いた計画では、最初に Rows Removed by Filter の値を見る。

プランナは「refund は全体の 2 割弱だから、at 順に数十行を読めば 10 件そろう」と見積もる。実際は、refund の行はすべて at の昇順の末尾にある。ANALYZE を何度実行しても、この見積もりは変わらない。ANALYZE が記録する列ごとの頻度は、値が並びのどこに固まっているかを含まないためである。

limit 10 を limit 100、limit 1000 と増やして実行する。総コストは limit に合わせて変わるが、昇順と降順の総コストは一致したままである。

昇順で読んで捨てる行を減らす方法を考える。例として、フェンスの先頭に create index if not exists events_kind_at_idx on events (kind, at); を足して実行し直す。Rows Removed by Filter の行が計画から消える。

まとめ

手順 07 と手順 09 で、プランナは実際より少ない行を読むと見積もった計画を選んだ。どちらも、コストの計算ではなく、計算に使う材料が実際と違っていた。手順 07 では古い統計、手順 09 では行が均等に散らばっているという仮定である。

explain analyze
select * from events where kind = 'refund' order by at limit 10;

計画を読むときに見る場所は次の 3 つ。