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

Partition pruningで対象外の区画を除く

使い捨てPostgreSQLの同じSQLを有効・無効で比べ、結果2800と、走査しない区画・Filterで捨てる行を区別します。

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

更新日

この記事の目次
  1. 条件に合わない区画を最初から読まない
  2. 半開区間に条件を合わせる
  3. 一時テーブルのSQLを実行する
  4. 実行計画の違いを行へ戻す
  5. 効かない場合と分割の費用を考える
  6. 二月以外の件数も手で確認する
  7. 同じ件数をどう読むかの課題
  8. この記事をロゲイアに質問する理解を確かめる

条件に合わない区画を最初から読まない

月ごとに分けた売上から二月だけを集計する場合、全月を読み、二月以外の行を後で捨てる方法は余分な仕事になります。Partition pruningは、区画の境界と条件を使い、対象外の区画を計画や実行から除く仕組みです。前提は索引と問い合わせ処理です。行を読んでからFilterで落とすことと、区画を走査対象にしないことを区別します。分割しただけで全ての問い合わせが速くなるとは限らず、どの条件から除外できるかが重要です。

この実行例は使い捨てPostgreSQL 18.6で、1月、2月、3月の三つの一時区画へ9000行を入れます。90日へ毎日100行なので、2月の28日には2800行があります。二月を下端を含み三月一日を含まない条件で読み、pruningの有効と無効で同じ2800を確認します。インデックスは作らず、区画を除く性質を見ます。SQLiteの計画でPostgreSQLの動作を代用していません。本番DBへこの例を投入する必要もありません。

半開区間に条件を合わせる

区画は[2026-02-01,2026-03-01)のような半開区間です。問い合わせもday以上二月一日、day未満三月一日として、時間の境界を揃えます。date型を使うこの例では時刻の端数はありません。timestampへ変えるなら、タイムゾーンと一日の意味も決めます。二月の最終日23:59:59以下という書き方は、精度や型が変わると終端を取りこぼす場合があります。次の月の始まり未満という条件は、境界の読み方を明確にできます。

区画キーを関数で包んだ条件や、値が計画時に不明な条件では、どの段階で除外できるかが変わり得ます。製品と版の仕様、実際の計画を確認し、同じ意味の条件だから必ず同じpruningになるとはしません。インデックスの有無も別です。索引がなくても境界から区画を除ける場合があり、索引があっても全区画を訪れる問い合わせはあり得ます。二つの最適化を同じ「速い検索」という一語へまとめずに読みます。

一時テーブルのSQLを実行する

ローカルの使い捨てDBでpsql -X -v ON_ERROR_STOP=1 -f partition.sqlとして実行します。接続先が実験用であることを自分の環境で確認してください。BEGINからROLLBACKまでのSQLで、一時テーブルはこの接続の範囲です。SET LOCALの変更もトランザクション内だけです。途中のエラーで停止するので、出力が途切れた場合は最後まで通ったと記録しません。接続を閉じると一時資源も消えます。通常の売上テーブルや本番の設定を書き換えるコードではありません。

generate_seriesで固定した行を作り、ANALYZEの後に二条件のEXPLAIN ANALYZEを保存します。ANALYZEは統計を作り、EXPLAIN ANALYZEは実際にSELECTを実行します。ここでは更新ではありませんが、一般にANALYZE付きの実行計画は処理を動かす点を意識してください。JSON形式は構造を比較するためです。費用の小数点やナノ秒の一致を期待値にせず、読んだRelation、行数、Filter、実行回数を対応させます。

sql
BEGIN;
CREATE TEMP TABLE phase3_sales(id integer, day date, amount integer) PARTITION BY RANGE(day);
CREATE TEMP TABLE phase3_sales_jan PARTITION OF phase3_sales FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TEMP TABLE phase3_sales_feb PARTITION OF phase3_sales FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TEMP TABLE phase3_sales_mar PARTITION OF phase3_sales FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
INSERT INTO phase3_sales SELECT g,DATE '2026-01-01'+(g%90),1 FROM generate_series(0,8999) g;
ANALYZE phase3_sales;
SET LOCAL enable_partition_pruning=on;
SELECT count(*) AS expected_2800 FROM phase3_sales WHERE day>=DATE '2026-02-01' AND day<DATE '2026-03-01';
EXPLAIN (ANALYZE,BUFFERS,FORMAT JSON) SELECT count(*) FROM phase3_sales WHERE day>=DATE '2026-02-01' AND day<DATE '2026-03-01';
SET LOCAL enable_partition_pruning=off;
SELECT count(*) AS expected_2800 FROM phase3_sales WHERE day>=DATE '2026-02-01' AND day<DATE '2026-03-01';
EXPLAIN (ANALYZE,BUFFERS,FORMAT JSON) SELECT count(*) FROM phase3_sales WHERE day>=DATE '2026-02-01' AND day<DATE '2026-03-01';
ROLLBACK;

実行計画の違いを行へ戻す

今回の有効時の計画ではphase3_sales_febだけをSeq Scanし、Actual Rowsは2800でした。無効時はAppendの下へjan、feb、marが入り、janとmarの出力行数は0ですが、Filterで対象外を落とします。出力が0だから、その区画を一切読まなかったとは言えません。pruningの有無は、返る件数だけでは分かりません。走査対象になったRelationと、Rows Removed by Filterを照合することで、この違いを説明できます。

どちらも結果は2800です。最適化前後で答えが変わっていないことを先に確認し、次に不要な走査が消えたかを見ます。この小さい一時データの経過時間を、大きい本番表の速度倍率へ一般化しません。今回のBUFFERSには一時表に対応するlocalの情報もあります。sharedだけを見て0だから何も読んでいないと判断しないでください。キャッシュと実ディスクの読取りも区別して読み、選択した版のEXPLAINの定義へ合わせます。

効かない場合と分割の費用を考える

区画キーの条件がなく全期間を読むなら、全区画へ触る必要があるかもしれません。細かく分けすぎると、計画やメタデータ、保守の費用も増えます。対象期間、古いデータの整理、索引、更新の分布から、分割する意味を選びます。境界を追加するだけで問い合わせの意味や結果の鮮度が変わってよいわけではありません。default区画や例外的な値を使う構成では、その存在も計画の確認に含めます。

実データの検査では、期間の境界、NULL、パラメーター、prepared statement、設定を条件ごとに比較します。この例は定数のdate条件で、全ての動的条件を試していません。本番への採用前は実際のSQLと統計、分割数へ合わせます。単一の索引で十分なら、分割の管理を増やさない方が単純なこともあります。この記事の実行証跡は、掲載したSQLとPostgreSQLの版をハッシュで対応させ、手順を変えた検証は別の記録として残します。

二月以外の件数も手で確認する

固定入力では1月31日と3月31日がそれぞれ3100行です。無効時にその二区画でRows Removed by Filterが各3100なら、全体9000から6200を除いて2800が残る説明と対応します。最終のAggregateのActual Rowsは集計結果一行なので、そこを売上2800行と読み違えないでください。木のどの段階の行数を見たかを明示すると、答えと処理量を区別できます。

数える対象が集計行か入力行かを、結果の単位として残します。

同じ件数をどう読むかの課題

課題は、無効時にjanのActual Rowsが0だからjanを読まなかったと結論する案です。解答は不十分です。Filterで行を捨てて0にした可能性があるので、走査ノードと削除行数、buffersを見ます。pruning有効時にjanの走査自体が計画から除かれたことと区別してください。最終結果が同じ2800でも、処理した量が違うことを説明します。推定のPlan Rowsと実際のActual Rowsも混ぜないでください。

追加課題は二月と三月を読む条件へ変える場合です。解答ではfebとmarが必要になり、janを除けるかを計画で確認します。元の結果2800を使い回さず、固定入力の該当日数から期待値を求めます。ROLLBACKまで成功した記録と、途中のエラーは別です。条件、境界、答え、走査対象の四つを対応させられれば到達点です。

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

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

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

学習コースから次を選ぶ