設計パターン・最適化図鑑 · 基礎から応用へ

Predicate pushdownを結果と計画で確かめる

内部結合で同じ計画になる例と、LEFT JOINの条件移動で結果が変わる例を、使い捨てPostgreSQLで検証します。

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

更新日

この記事の目次
  1. 後段へ渡す行を早く減らす
  2. 内部結合で移せる条件を読む
  3. 固定データのSQLを実行する
  4. 同じ計画になったことをそのまま読む
  5. LEFT JOINで結果が変わる反例
  6. 選択度と読取り量を別に数える
  7. 外部結合と行数の課題
  8. この記事をロゲイアに質問する理解を確かめる

後段へ渡す行を早く減らす

有効な顧客の注文だけを集計するとき、全顧客と結合した後に除外するより、顧客側で有効な行を先に選べば、結合へ渡す行を減らせる場合があります。Predicate pushdownは、意味を保てる条件を処理の早い段階へ移す最適化です。前提は関係演算と実行計画です。SQLのWHEREを先に書ける位置へ動かせば常に速い、という書き方の規則ではありません。DBが既に同じ変換を行う場合もあり、書換えが結果を変える場合もあります。

実行例は使い捨てPostgreSQL 18.6で、1000顧客のうち100がactive、注文は10,000件です。通常のJOINとWHEREの形と、activeの顧客をサブクエリで選んでJOINする形を比べます。どちらも1000件の注文になります。さらにLEFT JOINの条件を移すと結果が変わる二行の反例を入れます。答え、計画、意味の三つを同時に比較し、速いように見える誤ったSQLを採用しないための教材です。

内部結合で移せる条件を読む

c.activeは顧客の列だけを見る条件です。この例の内部結合では、結合後にactiveを残す形と、顧客からactiveだけを選んで結合する形は同じ行を得ます。条件が両方の表を見るなら、単純に片側だけへ置けません。式の型やNULL、関数の性質、集約やLIMITなどをまたぐ場合も、同値性を確認します。条件の計算に副作用がある関数を入れた場合、評価回数や順序が変わってもよいとは仮定しません。採用製品が変換を許す前提を読みます。

初めに「どの行を残すか」を小さい入力で手計算し、書換え前後の結果を照合します。その後、EXPLAINで実際にどこへFilterが置かれたかを読みます。論理上の処理の説明と、物理計画の順序は同じとは限りません。サブクエリで囲んだから中間表が必ず全部作られるとも言えません。構文の入れ子を、そのまま実行時の一時データ量と読むのではなく、optimizerが作った木を確認します。

固定データのSQLを実行する

コードをpushdown.sqlへ保存し、実験用と確認したローカルDBへpsql -X -v ON_ERROR_STOP=1 -f pushdown.sqlで実行します。BEGINとROLLBACKの中の一時テーブルだけを使います。顧客IDの十の倍数をactiveとし、各顧客に10件ずつ注文が対応するよう固定データを作ります。ANALYZE後に両方の件数とJSONのEXPLAIN ANALYZEを保存します。更新の本番表や恒久設定へ触る手順ではありません。

期待値は100顧客×10注文=1000です。JOINは顧客の主キーへ結ぶため、この入力では一注文が複数顧客へ増えることもありません。別のデータで顧客IDが重複するなら、結合の件数も変わるので同じ計算を流用しません。EXPLAIN ANALYZEは実際に処理を実行することを意識し、一般の更新SQLを観察する場合は効果の境界を別に確認します。今回はSELECTの結果と計画の構造を検査する例です。エラーで途中終了した出力を成功記録へ含めません。

sql
BEGIN;
CREATE TEMP TABLE phase3_customers(id integer PRIMARY KEY,active boolean);
CREATE TEMP TABLE phase3_orders(id integer, customer_id integer);
INSERT INTO phase3_customers SELECT g,g%10=0 FROM generate_series(1,1000) g;
INSERT INTO phase3_orders SELECT g,(g%1000)+1 FROM generate_series(1,10000) g;
ANALYZE phase3_customers;
ANALYZE phase3_orders;
SELECT count(*) AS expected_1000 FROM phase3_orders o JOIN phase3_customers c ON o.customer_id=c.id WHERE c.active;
EXPLAIN (ANALYZE,BUFFERS,FORMAT JSON) SELECT count(*) FROM phase3_orders o JOIN phase3_customers c ON o.customer_id=c.id WHERE c.active;
SELECT count(*) AS expected_1000 FROM phase3_orders o JOIN (SELECT * FROM phase3_customers WHERE active) c ON o.customer_id=c.id;
EXPLAIN (ANALYZE,BUFFERS,FORMAT JSON) SELECT count(*) FROM phase3_orders o JOIN (SELECT * FROM phase3_customers WHERE active) c ON o.customer_id=c.id;
CREATE TEMP TABLE phase3_left(id integer);
CREATE TEMP TABLE phase3_right(id integer,active boolean);
INSERT INTO phase3_left VALUES(1),(2);
INSERT INTO phase3_right VALUES(1,true),(2,false);
SELECT l.id,r.active FROM phase3_left l LEFT JOIN phase3_right r ON l.id=r.id WHERE r.active ORDER BY l.id;
SELECT l.id,r.active FROM phase3_left l LEFT JOIN phase3_right r ON l.id=r.id AND r.active ORDER BY l.id;
ROLLBACK;

同じ計画になったことをそのまま読む

今回の両計画は、注文10,000行のSeq Scanと、顧客のactiveをFilterして100行をHashへ入れるHash Joinでした。顧客側では900行がFilterで除かれています。書換え前からDBが条件を結合の前へ適用しているため、サブクエリ版だけが新しく仕事を減らしたとは言えません。この結果を隠さず、「書き方を変えたが同じ最適化が既に行われた」と説明します。答えはどちらも1000です。

顧客の100行が結合へ進んでも、1000行の顧客表の走査自体は行っています。Filterのpushdownを、索引で100行だけ読んだことや、partition pruningで区画を読まなかったことへ読み替えません。どこで行数が減ったかと、どのページを読んだかは別の観測です。この小さい一時表での時間差を大規模DBの速度比へ一般化せず、必要なら本物のSQL、統計、索引、保存先へ合わせて追加検証します。

LEFT JOINで結果が変わる反例

後半は左にID1と2、右に1のactive=trueと2のactive=falseを置きます。LEFT JOINの後にWHERE r.activeを使うと、activeが真のID1だけが残ります。一方、ONの中へAND r.activeを移すと、左のID2も残り、右側がNULLになります。条件を早く適用しただけに見えても、外部結合が保存する左の行の意味が変わっています。NULLを含む行がWHEREで残るかどうかも、三値論理の規則へ合わせて読みます。

実行結果は前者が1|tの一行、後者が1|tと2|NULL相当の二行です。空欄はpsqlの表示方法で、この出力では文字列NULLと印字していません。出力の形式とSQLの値を混同しないでください。外部結合で常に何も移せないという結論ではなく、正当な変換には意味を保つ追加条件が必要という反例です。最適化の候補では、最初に結果の比較を行い、その後に費用を評価します。

選択度と読取り量を別に数える

activeの顧客100行は入力1000行の10%なので、この述語の選択度はこの入力では0.1です。注文側は10,000行のまま走査し、結合結果1000行を集計します。10%という値を、問い合わせ全体のI/Oや時間が10%になったと読まないでください。どの演算子の入力と出力を割った値かを示し、結合の多重度が変わる別データでは再計算します。

外部結合と行数の課題

課題はONへ条件を移した後、ID2が消えるかです。解答は消えず、左の保存側の行として残り、右の列がNULLになる、です。WHERE r.activeではNULLは真ではないので残りません。境界の違いを、二行の入力と結果で説明してください。次に、顧客のFilter後100行という値から、顧客の読取りが100行だけだったと結論できるかを考えます。解答はできません。元のSeq Scanと除外900行を確認します。

追加課題は、サブクエリ版の時間が一回だけ短かったことを理由に推奨する案です。解答では、同じ計画と入力なら環境の揺れも含むため、全標本と区間を確認し、意味と実際の仕事が変わった証拠を探すと説明します。今回の目的は特定のSQL表記を勝者にすることではありません。条件、同値性、計画、読んだ量、出力を対応させ、ROLLBACK後に一時資源が残らないことまで確認できれば到達点です。

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

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

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

学習コースから次を選ぶ