필사 모드: SQL実行計画完全ガイド: オプティマイザはどのように計画を選ぶのか

• はじめに
• 1. 一文が計画になるまで
• 2. 統計情報 — プランナーが世界を見る方法
• 3. 選択度の推定と独立性の仮定
• 4. コストモデル — costの数値は何から組み立てられるか
• 5. 経路の比較 — なぜこのスキャンで、なぜこの結合なのか
• 6. 結合順序の探索とGEQO
• 7. 推定が外れたときの補正
• 8. 計画が揺れるとき — 準備された文と一般計画
• クイズ: 理解度を確認しましょう
• おわりに
• 参考資料
• 続けて読む

はじめに

実行計画を扱う文章の多くはEXPLAINの出力の読み方を教えてくれます。このブログのEXPLAIN ANALYZEの読み方がまさにその記事です。ノードをどの順序で読むか、loopsがどう掛け合わされるか、BUFFERSから何が分かるかを扱っています。

この記事は反対側から見ます。出力を読む人ではなく、その出力を作り出したプランナーの立場です。プランナーはどんな情報を持ち、その情報から何を計算し、計算結果が間違っていたとき私たちは何を正してやれるのか。計画を読めるのにチューニングで行き詰まる地点は、たいていここにあります。「なぜこの計画が出たのか」が分からなければ、「どう変えればよいのか」も分かりません。

基準エンジンはPostgreSQL 18で、引用した既定値はすべてPostgreSQL 18の文書で確認した値です。MySQLのオプティマイザはコストモデルも統計構造も異なるため、この記事では混同しません。MySQLの実行計画には別の記事が必要です。

1. 一文が計画になるまで

SQLの一行が結果になるまでに四つの段階を経ます。

1. パーサー — 文法を検査し、構文木を作ります。この段階ではテーブルが存在するかどうか程度しか見ません。
2. 書き換え器(rewriter) — ビューを実際の定義に展開し、ルール(rule)を適用します。ビューを照会すると、この段階で元のクエリに置き換わります。
3. プランナー/オプティマイザ — 可能な実行経路を作り、それぞれのコストを計算して最も安いものを選びます。この記事の主題です。
4. エグゼキュータ(executor) — 選ばれた計画木に沿って実際にデータを読みます。

ここで重要な事実が一つあります。プランナーはデータそのものを見ません。プランナーが見るのはデータの要約、つまり統計情報です。統計が実際とずれていれば、どれほど精巧なコストモデルでも間違った答えを出します。実行計画の問題の圧倒的多数は、コストモデルの欠陥ではなく統計と現実の乖離です。

2. 統計情報 — プランナーが世界を見る方法

ANALYZEはテーブルから標本を抽出して統計情報を作り、pg_statisticに保存します。人が読みやすい形にしたのがpg_statsビューです。

SELECT attname, null_frac, n_distinct,
       most_common_vals, most_common_freqs,
       correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname IN ('status', 'created_at');

各項目の意味は次の通りです。

• null_frac — NULLの割合です。IS NULL条件の選択度がここから出ます。
• n_distinct — 異なる値の個数です。正の数ならその絶対値そのもの、負の数なら行数に対する比率です。-1はすべての値が一意であることを意味します。
• most_common_vals / most_common_freqs — 最頻値のリストとその頻度です。値の分布が偏っているカラムで決定的な役割を果たします。
• histogram_bounds — 最頻値を除いた残りの値の分布を、等頻度の区間に分けた境界です。範囲条件の選択度がここから出ます。
• correlation — カラムの値の論理的な順序と物理的な行の順序の相関係数です。1に近いほど、インデックススキャンのランダムアクセスが事実上シーケンシャルアクセスに近くなるため、コストが大きく下がります。BRINインデックスが有効かどうかを判断する指標でもあります。

標本サイズはdefault_statistics_targetが決めます。PostgreSQL 18の文書によると既定値は100で、値が大きいほど最頻値リストとヒストグラムの区間が増えて推定は正確になりますが、ANALYZEの時間と計画立案の時間が伸びます。カラム単位でも調整できます。

-- 値の分布が大きく偏っているカラムだけ標本を増やす
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

ALTER TABLE ... SET STATISTICSはSHARE UPDATE EXCLUSIVEロックしか取らないため、読み取りと書き込みを止めません。ALTER TABLEの他の多くの形態がACCESS EXCLUSIVEを取るのとは対照的です。

統計情報はautovacuumのanalyze作業によって更新されます。PostgreSQL 18の文書によると、analyzeの閾値はautovacuum_analyze_threshold(既定値50タプル)にautovacuum_analyze_scale_factor(既定値0.1、つまりテーブルの10%)をテーブルの行数に掛けた値を加えて計算されます。1億行のテーブルなら1000万行が変更されて初めてanalyzeが走ります。大規模テーブルで統計情報が古びるのはこのためで、テーブル単位でscale factorを下げるのが標準的な対応です。

3. 選択度の推定と独立性の仮定

プランナーが統計情報から計算する値が選択度(selectivity)です。条件が全行のうち何パーセントを通過させるかを意味し、これにテーブルの行数を掛けると推定行数になります。

単一条件の選択度は比較的正確です。status = 'PAID'が最頻値リストにあればその頻度をそのまま使い、なければ残りの値に均等に配分します。

問題は条件が二つ以上のときです。プランナーは既定で条件同士が互いに独立していると仮定し、選択度を掛け合わせます。この仮定が崩れた瞬間、推定は崩壊します。

-- 都市と郵便番号は実質的に関数従属の関係にある
SELECT * FROM addresses
WHERE city = '서울특별시' AND postal_code = '06236';

cityの選択度が0.2でpostal_codeの選択度が0.0001なら、プランナーは0.00002を計算します。しかし実際には郵便番号が決まれば都市も自動的に決まるため、両者は独立ではなく、実際の選択度は0.0001に近い値になります。プランナーは実際の五分の一の行数しか見込まず、五倍の過小推定が発生し、その誤差は結合の上流に向かうほど雪だるま式に膨らんでいきます。

これが実行計画が誤る最も多い原因です。表面的には「Nested Loopを選んだが実際の行数が多くて遅くなった」ように見えますが、根本原因は結合方式の選択ではなく、その下流ですでに狂っていた行数推定にあります。7節で補正方法を扱います。

4. コストモデル — costの数値は何から組み立てられるか

EXPLAINが示すcostの値は時間の単位ではありません。順次ページ読み取り一回を1.0とする相対的で抽象的な単位です。プランナーはこの値を五つのコスト定数から組み立てます。以下はPostgreSQL 18の文書に基づく既定値です。

実務上もっとも重要な数値がrandom_page_costの既定値4.0です。この値は、回転式ディスクの時代に由来する「ランダムアクセスはシーケンシャルアクセスの四倍のコストがかかる」という仮定を反映しています。SSDやNVMeではこの比率ははるかに小さくなります。既定値のままにしておくと、プランナーはインデックススキャンを実際より高く評価し、シーケンシャルスキャンを過度に好むようになります。SSD環境では1.1から2.0の間に下げるのが広く使われる調整ですが、値を変える前には必ず代表的なクエリ群で前後を比較してください。この値は計画全体に影響するため、下げすぎると他のクエリが壊れます。

effective_cache_sizeも同様に注意が必要です。既定値は4GBで、この値はメモリを実際に割り当てるのではなく、OSのキャッシュを含めてどれだけのデータがキャッシュされているかについてのプランナーの仮定だけを変えます。実サーバのメモリよりはるかに小さく設定されたままだと、プランナーはインデックススキャンがディスクを頻繁に叩くだろうと判断し、シーケンシャルスキャンに傾きます。

並列実行に関するコストも同じ仕組みで動きます。parallel_setup_costの既定値は1000、parallel_tuple_costの既定値は0.1です。ワーカーを起動する固定コストが1000であるため、小さなクエリはそもそも並列計画の候補に上がりません。並列シーケンシャルスキャンの最小テーブルサイズはmin_parallel_table_scan_sizeが決め、既定値は8MB、インデックス側はmin_parallel_index_scan_sizeで既定値512kBです。

5. 経路の比較 — なぜこのスキャンで、なぜこの結合なのか

プランナーは各テーブルについて可能な限りのアクセス経路を作り、それらを組み合わせて結合経路を作ります。各段階で最もコストの低い経路と、「必要な順序で並んだ行を返す」といった有用な性質を持つ経路を残します。

スキャン経路選択の核心は返却比率です。インデックススキャンはインデックスを読み、テーブルの行を事実上ランダムに訪問します。返却比率が上がるほどランダムアクセスの回数が増え、ある点を超えるとテーブル全体を物理順序で一度読む方が安くなります。だからSeq Scanは失敗ではなく正解であることが多いのです。小さなテーブルや、行の大半を返すクエリでインデックスを強制すると、かえって遅くなります。

中間地帯にあるのがBitmap Heap Scanです。まずインデックスをすべて読んで対象のブロック番号をビットマップに集め、その後ブロック番号順にテーブルを読みます。ランダムアクセスをシーケンシャルに近い形へ変える折衷案で、返却比率が中途半端なときによく登場します。

結合経路は三つの中から選びます。

• Nested Loop — 外側の行一つごとに内側を照会します。外側の結果が小さく、内側に良いインデックスがあるときに最適です。外側の行数推定が外れると最悪になります。実行計画事故の常連です。
• Hash Join — 片方でハッシュテーブルを作り、もう片方をなめます。等価結合にのみ使われ、ハッシュテーブルがwork_memに収まれば非常に高速です。収まらなければディスクに分割されます。
• Merge Join — 両側を整列してマージします。すでに整列済みの入力(インデックススキャン)があれば有利で、なければ整列のコストを払う必要があります。

work_memの既定値は4MBです。この値がコネクション単位ではなく整列・ハッシュ演算単位で適用される点が重要です。一つのクエリが複数の整列とハッシュを含めば、その分だけ倍数で使われます。ハッシュ関連の演算はhash_mem_multiplier(既定値2.0)を掛けた値まで使えます。

6. 結合順序の探索とGEQO

テーブル二つを結合する順序は二通りですが、十個になると組み合わせが爆発します。プランナーは動的計画法で結合順序を探索しますが、テーブル数が増えるとこの探索自体が手に負えなくなります。

PostgreSQLはこれを三つのつまみで扱います。いずれも文書で確認した既定値です。

• from_collapse_limit — 既定値8。サブクエリを上位クエリに展開するかどうかを判断する基準です。展開した結果FROM項目数がこの値を超えると展開しません。
• join_collapse_limit — 既定値はfrom_collapse_limitと同じです。明示的なJOIN構文を平坦なリストに展開する基準です。この値を1にすると、プランナーは結合順序を並べ替えず、書かれた順序をそのまま使います。
• geqo_threshold — 既定値12。FROM項目数がこの値以上になると、完全探索の代わりに遺伝的アルゴリズム(GEQO)で結合順序を探します。

GEQOは確率的探索なので、同じクエリでも実行するたびに異なる計画が出ることがあります。十二個を超えるテーブルを結合するレポートクエリが、ある日は3秒、別の日は40秒かかるなら、GEQOを疑ってみる価値があります。確認方法はgeqoを一時的に切り、計画立案時間と実行時間を比較することです。

-- セッションだけで実験する
SET geqo = off;
SET join_collapse_limit = 20;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
RESET geqo;
RESET join_collapse_limit;

計画立案時間が数秒に跳ね上がるなら、完全探索が処理しきれない規模だという証拠であり、そのときはクエリを分割するか中間結果を実体化する方が答えになります。

7. 推定が外れたときの補正

3節で見た独立性の仮定の問題を、PostgreSQLは拡張統計(extended statistics)で解決します。CREATE STATISTICSでカラムの組み合わせに対する統計情報を別途作ります。文書がサポートすると明記する種類は三つです。

• dependencies — カラム間の関数従属を記録します。複数の条件が実は重複している場合に起きる深刻な過小推定を防ぎます。
• ndistinct — カラムの組み合わせにおける異なる値の個数を記録します。GROUP BYが複数カラムにまたがるときの推定を正します。
• mcv — カラムの組み合わせの最頻値リストを記録します。特定の組み合わせが際立って多い場合を正確に反映します。

-- 都市と郵便番号の従属関係をプランナーに教える
CREATE STATISTICS stat_addr_city_postal (dependencies, mcv)
  ON city, postal_code FROM addresses;

-- 統計情報はANALYZEを走らせて初めて埋まる
ANALYZE addresses;

CREATE STATISTICSはSHARE UPDATE EXCLUSIVEロックを取るため、運用中に実行しても読み取りと書き込みを止めません。ただし作った直後は空なので、必ずANALYZEを走らせる必要があります。文書の例でもCREATE STATISTICSの後には必ずANALYZEが続けて書かれています。

式に対する推定が外れるケースもよくあります。date_trunc('month', a) = ...のような条件は、プランナーが既定の統計情報だけではまったく推定できません。この場合も式の統計情報を作ることができます。

CREATE STATISTICS stat_events_month
  ON date_trunc('month', occurred_at) FROM events;
ANALYZE events;

補正の順序をまとめるとこうなります。まずEXPLAIN ANALYZEで推定行数と実際の行数の乖離が最も大きいノードを見つけます。そのノードが単一カラムの条件ならSET STATISTICSで標本を増やします。複数カラムの条件ならCREATE STATISTICSを作ります。式なら式の統計情報か式インデックスを作ります。この三つで解決しなければ、そこで初めてクエリ構造を変えます。

8. 計画が揺れるとき — 準備された文と一般計画

昨日まで速かったクエリが今日突然遅くなり、EXPLAINで直接実行するとまた速い、という症状の有力な容疑者が準備された文(prepared statement)の一般計画(generic plan)です。

準備された文はパラメータの値を知らないまま計画を作ることができます。値が分かった状態で毎回作る計画がカスタム計画(custom plan)、値を知らないまま一度だけ作って使い回すのが一般計画です。一般計画は計画立案のコストを節約しますが、値の分布が偏ったカラムでは、特定の値に対して最悪の計画になることがあります。

この動作はplan_cache_modeで制御します。許容値はauto(既定値)、force_custom_plan、force_generic_planです。既定値autoは数回分のカスタム計画のコスト平均と一般計画のコストを比較して自動的に決定します。

値を入れずに一般計画だけを見たいなら、PostgreSQL 16以降で提供されるGENERIC_PLANオプションを使います。

EXPLAIN (GENERIC_PLAN)
SELECT * FROM orders WHERE tenant_id = $1 AND status = $2;

 Bitmap Heap Scan on orders  (cost=25.41..1290.88 rows=512 width=124)
   Recheck Cond: (tenant_id = $1)
   Filter: (status = $2)
   ->  Bitmap Index Scan on idx_orders_tenant  (cost=0.00..25.28 rows=1024 width=0)
         Index Cond: (tenant_id = $1)

症状が確認できたら、対応は二つです。該当セッションやアプリケーションでplan_cache_mode = force_custom_planにして常に値を反映した計画を作らせるか、値の分布が偏ったカラムの統計情報を強化して一般計画の品質を上げるかです。前者は計画立案のコストを毎回払うため、秒間数千件が実行される短いクエリではそれ自体が負担になり得ます。

JITも同様の揺れの原因です。jitの既定値はon、jit_above_costの既定値は100000です。推定コストがこの値を超えるとJITコンパイルが有効になりますが、推定が過大評価された短いクエリではコンパイル時間が実行時間を上回る逆転が起きることがあります。EXPLAIN (ANALYZE)出力のJITセクションでコンパイル時間を確認してください。

クイズ: 理解度を確認しましょう

Nested Loop  (cost=0.86..3421.55 rows=12 width=88)
             (actual time=0.09..48210.33 rows=184032 loops=1)
  ->  Index Scan using idx_a on orders o
        (cost=0.43..118.20 rows=4 width=40)
        (actual time=0.03..92.11 rows=61344 loops=1)
        Index Cond: ((tenant_id = 42) AND (status = 'PAID'::text))
  ->  Index Scan using idx_b on order_items i
        (cost=0.43..825.31 rows=3 width=48)
        (actual time=0.30..0.78 rows=3 loops=61344)
        Index Cond: (order_id = o.id)

ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.02);

おわりに

実行計画のチューニングを長く続けていると、結論は単純になります。コストモデルはおおむね正しく、間違っているのは入力値です。プランナーに誤った統計情報を渡しておいて、プランナーを責めることがほとんどです。enable_nestloop = offのような手段で計画を無理やり捻じ曲げる対応が魅力的に見えるのは、効果がすぐに見えるからですが、それはデータ分布が少し変わるだけでまた崩れる一時しのぎに過ぎません。

順序はいつも同じです。推定と実際の乖離が最も大きいノードを見つけ、その乖離の原因が標本不足なのか相関なのか式なのかを見極め、該当する統計ツールで補正します。それでも残る問題だけをクエリ構造やパラメータで扱います。

この記事のSQLはPostgres プレイグラウンドで直接実行してみることができます。

参考資料

• PostgreSQL 18, Query Planning: https://www.postgresql.org/docs/18/runtime-config-query.html (2026-08-15 確認)
• PostgreSQL 18, EXPLAIN: https://www.postgresql.org/docs/18/sql-explain.html (2026-08-15 確認)
• PostgreSQL 18, CREATE STATISTICS: https://www.postgresql.org/docs/18/sql-createstatistics.html (2026-08-15 確認)
• PostgreSQL 18, Resource Consumption: https://www.postgresql.org/docs/18/runtime-config-resource.html (2026-08-15 確認)
• PostgreSQL 18, Automatic Vacuuming: https://www.postgresql.org/docs/18/runtime-config-autovacuum.html (2026-08-15 確認)
• PostgreSQL 18, Monitoring Database Activity: https://www.postgresql.org/docs/18/monitoring-stats.html (2026-08-15 確認)
• PostgreSQL 18, ALTER TABLE: https://www.postgresql.org/docs/18/sql-altertable.html (2026-08-15 確認)

続けて読む

• 前回: PostgreSQL インデックス完全ガイド — インデックスのライフサイクル
• 次回: トランザクション分離レベル完全ガイド — 分離レベルの運用契約
• EXPLAIN ANALYZEの読み方 — 出力を読む順序
• ボルケーノモデルとベクトル化実行 — エグゼキュータが計画を動かす仕組み
• Postgres プレイグラウンド — 実行計画を直接出してみる
• DuckDB プレイグラウンド — 分析エンジンの計画と比較してみる