実験・実装 · 基礎から応用へ

選択度を変えてJOINの計画を比較する

絞り込みが一件の場合と広い場合でJOIN計画を比較し、見積もり行数・実行行数・loopsを読み分けます。

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

更新日

この記事の目次
  1. JOINの前に何行残るかを見る
  2. データと索引を固定する
  3. 狭い条件と広い条件を実行する
  4. rowsとloopsの単位をそろえる
  5. 方式の違いを優劣の順位にしない
  6. 分布を変える次の実験へ進む
  7. この記事をロゲイアに質問する理解を確かめる

JOINの前に何行残るかを見る

同じ二つの表を結合しても、入力が一件へ絞られる場合と大部分が残る場合では、候補となる計画の費用が違います。この手順では顧客表と注文表を作り、特定の顧客IDで絞る問い合わせと、広い地域条件の問い合わせを比べます。結合方式の名前を暗記するのでなく、何行を何回扱う予定なのかを説明するのが目的です。以下はPostgreSQL18の再現手順で、実測計画を掲載したものではありません。専用の使い捨てローカルDBで実行し、返す意味が違う二問い合わせであることを明示して記録します。

データと索引を固定する

顧客は1万行、注文は20万行とし、注文のcustomer_idへ索引を作ります。顧客IDと地域の分布は、式で決めた単純な人工データです。実務の偏りや顧客ごとの注文数の差を再現したものではありません。ANALYZEを実行してから計画を取ることで、統計を取っていない状態との混同を避けます。それでも計画器が常に同じ結合方式を選ぶわけではありません。設定、版、行の幅、キャッシュ、費用パラメータも影響します。どの条件を固定して何を変えたかを最初にメモします。

狭い条件と広い条件を実行する

EXPLAIN ANALYZEを付けたSELECTを二つ実行します。前者は顧客ID42を選び、後者は地域番号90未満を選びます。集計結果は一行でも、その前に多数の注文を処理している場合があります。最終出力が一行だから結合も一行しか扱っていない、とは読まないでください。計画を末端から上へ読み、走査で残った行、結合が返した行、集計が受けた行を分けます。必要ならANALYZEなしのEXPLAINと比べ、計画時の見積もりと実行で初めて分かった値を区別します。

PostgreSQL 18 SQL — procedure
-- PostgreSQL 18, one disposable local session.
CREATE TEMP TABLE lab_customers(id integer PRIMARY KEY, region integer);
CREATE TEMP TABLE lab_orders(id integer, customer_id integer, amount integer);
INSERT INTO lab_customers SELECT i, i%100 FROM generate_series(1, 10000) AS i;
INSERT INTO lab_orders SELECT i, 1+(i%10000), i%200 FROM generate_series(1, 200000) AS i;
CREATE INDEX lab_orders_customer ON lab_orders(customer_id);
ANALYZE lab_customers;
ANALYZE lab_orders;
EXPLAIN (ANALYZE, BUFFERS)
SELECT sum(o.amount) FROM lab_customers c JOIN lab_orders o ON o.customer_id=c.id WHERE c.id=42;
EXPLAIN (ANALYZE, BUFFERS)
SELECT sum(o.amount) FROM lab_customers c JOIN lab_orders o ON o.customer_id=c.id WHERE c.region<90;

rowsとloopsの単位をそろえる

実行計画のactual rowsは、複数回実行されるノードでは一回当たりの平均として表示されます。たとえばactual rowsが20でloopsが100なら、総処理量を考えるときは概ね2000行分という見方が必要です。ただし表示の丸めやノードの役割には注意します。親ノードの時間には子の処理が含まれることもあり、全ノードの時間を単純に足すと重複します。見積もりが外れた箇所を探すときは、同じ単位の見積もりと実績を比べ、最終結果の一行と走査行数を比較しないようにします。

方式の違いを優劣の順位にしない

入れ子ループは、外側が小さく内側を索引で探せると有利な場合があります。ハッシュ結合は、一方から作ったハッシュ表を使って多くの行を照合できますが、メモリと分割や一時ファイルの費用も考えます。マージ結合は入力の整列状態などが関わります。どれかが常に最良とは限りません。方式が変わらなかった場合も失敗ではなく、見積もった費用や他の条件を確認する材料です。設定を強制して望むノード名を出すより、実際の入力件数と処理量から選択を説明することを優先します。

分布を変える次の実験へ進む

この手順の次は、一部の顧客へ注文が集中するデータ、地域と他列が強く関係するデータ、行の幅が大きいデータを別条件として試せます。その都度ANALYZEの時点と問い合わせの結果を確認します。統計の追加が全ての結合見積もりを自動で直すわけではないため、どの関係を表せる資料なのかを公式文書で確認します。検証後は一時表のセッションを閉じます。性能改善へ結び付ける場合も、同じ結果を返す書き換えか、要件そのものを変えた比較かを分けて報告します。

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

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

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

学習コースから次を選ぶ