プランナと実行計画、EXPLAINをいつ使うか

この記事はAIとの対話をもとに書いています。内容は参考程度にとどめ、重要な判断の際はご自身でご確認ください。

学習用にまとめているDBのノートに、「どういう手順でその部分集合を求めるかはプランナが決める話であり、SQLを書く側の関心事ではない」と書いた箇所がある。宣言的なクエリ言語の説明としてはそのとおりだが、そのプランナが何者なのかはノート内のどこにも書かれていなかった。

そこを起点に、プランナとは何か、実行計画を持つDBには何があるか、EXPLAIN は実務でいつ使うのか、性能の検証をテストや自動化されたループに組み込めるのか、をAIと話した。以下はそのログを整理したもの。

プランナとは誰か

Q

ノートに出てくる「プランナ」って、結局何なの？

A

実行計画を決めるコンポーネントのこと。PostgreSQLがSQLを処理する流れは、ざっくり4段階に分かれている。

  1. パーサ — SQL文を構文解析して構文木にする
  2. リライタ — ビュー展開やルール適用で書き換える
  3. プランナ/オプティマイザ — 「どう取ってくるか」の候補を列挙してコストを見積もり、一番安いものを選ぶ
  4. エグゼキュータ — 選ばれた計画を実際に実行する

プランナが下している判断は、たとえばこういうものだ。全件走査（Seq Scan）にするか索引を使うか。JOINの方式（Nested Loop / Hash Join / Merge Join）と結合の順序をどうするか。ソートを実際に行うか、索引の順序で済ませるか。

判断材料は ANALYZE で集めた統計情報（行数、値の分布、カーディナリティ）から見積もった行数と、コストパラメータの掛け合わせ。あくまで見積もりなので、統計が古ければ平気で外す。EXPLAIN が見せているのは、このプランナが出した答えそのものである。

Q

よくMongoDBを使っているけれど、実行計画を持っているDBには他に何がある？

A

宣言的なクエリ言語を持つDBなら、基本的にすべてある、というのが答えになる。「何が欲しいか」だけを書かせる以上、「どう取るか」を決める役が内部に必要で、その決定を外から覗く口が実行計画だからだ。逆に、操作が手続き的なもの（キーを指定して取ってくるだけ、など）には存在しない。選択の余地がないので、決める人もいらない。

MongoDBにもある。

db.orders.find({ status: "pending" }).explain("executionStats")

queryPlanner.winningPlan に COLLSCAN / IXSCAN / FETCH / SORT が木構造で出て、executionStats に nReturned と totalDocsExamined が出る。この2つの乖離が、PostgreSQLでいう見積もり行数と実測行数の乖離に相当する診断ポイントになる。10件返すのに10万件走査していれば索引が効いていない。

ただし計画の選び方が根本的に違うので、そこは意識しておいたほうがいい。

PostgreSQLMongoDB
選び方統計情報からコストを見積もって選ぶ候補計画を実際に少し走らせて速かったものを選ぶ
見るもの見積もり行数 vs 実測行数nReturned vs totalDocsExamined
落とし穴統計が古いと外すキャッシュされた計画が状況変化に取り残される

他のDBもざっと挙げると、MySQL/MariaDBは EXPLAIN（8.0.18以降は EXPLAIN ANALYZE で実測付き）、SQLiteは EXPLAIN QUERY PLAN（かなり簡素でコスト表示はない）、SQL ServerとOracleはGUIや DBMS_XPLAN で計画を表示できる。Elasticsearchは ?profile=true、Neo4jは EXPLAIN（見積もりのみ）と PROFILE（実測）、ClickHouseやDuckDBにも EXPLAIN があり、BigQueryやSnowflakeはquery profileでステージごとの内訳が出る。

面白いのはCassandraで、TRACING ON はあるが厳密な意味でのプランナは薄い。パーティションキーの指定が必須で、そもそも選択の余地が少ないからだ。クエリの自由度を捨てた分、プランナが要らなくなっている。宣言性とプランナはセットで、片方だけを取ることはできない。

EXPLAINはいつ使う道具か

Q

EXPLAINってどういうときに使うもの？　普通に本番のコードで使う？

A

アプリのコードに EXPLAIN を埋め込むことは基本的にない。開発や調査のときに人間が叩く道具である。ただし「本番環境で使う」ことはあって、そこが分かれ目になる。

使うタイミングは主に5つ。

  1. 書いた直後（一番リターンが大きい）。新しいクエリを実装したら、本番相当のデータ量で一度見る。開発用DBの1000件では全件走査が最速なので、何も分からない
  2. 遅いと分かってから。ただし体感から入るのは非効率で、pg_stat_statements で総実行時間の上位を特定してから、そのクエリだけを見る順番になる
  3. 索引を追加・削除する前後。「効くはず」の索引が本当に使われるかは、見ないと分からない
  4. データ量が増えたとき。同じクエリでも計画は変わる。件数が閾値を超えて索引走査から全件走査に切り替わったのが劣化の原因、というのはよくある
  5. 本番と開発で挙動が違うとき。たいていは統計情報の差

そして「本番で使う」の正しい形は、アプリに埋め込むのではなく auto_explain を使うことだ。

auto_explain.log_min_duration = '3s'
auto_explain.log_analyze = on      # 実測値も出るが、オーバーヘッドがある

これなら「夜中に一度だけ遅かった」ような再現困難なものが捕まる。ただし log_analyze = on は全クエリに計測コストが乗るので、まずoffで始めて必要なら段階的に。

注意点として、EXPLAIN ANALYZE は実際にクエリを実行する。SELECT なら害はないが、UPDATE や DELETE でやると本当に更新される。トランザクションで囲んで捨てるのが安全である。

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) DELETE FROM events WHERE created_at < '2024-01-01';
ROLLBACK;

MongoDBも同じ構図で、explain("executionStats") は開発時に手で叩くもの。本番の恒常監視はDatabase Profilerが auto_explain に相当する担当になる。

テストに組み込めるか

Q

テスト駆動とかで使うのは正しい使い方？

A

方向は正しいが、「実行計画をアサートする」は大半がアンチパターンになる。

assert "Index Scan" in explain_output

これが落ちる理由は多すぎる。DBのバージョン、統計情報の状態、テストデータの件数、コストパラメータの設定。特に致命的なのが件数で、テスト用DBに1000行しかなければ全件走査を選ぶのが正しい。つまりこのテストは、プランナが正しい判断をしたときに赤くなる。テストが正解を罰する構造になっている。

計画の形は「実装の詳細」の側にある。SQLは「何が欲しいか」の宣言で、計画は「どう取るか」。宣言的なインターフェースのテストで実装詳細を固定しているのだから、そりゃ壊れる。

代わりに守るべきは、計画の形ではなく効率の性質のほうだ。

  1. クエリ発行回数のアサート（N+1検出）。件数に依存せず、ORMの遅延ロードで簡単に壊れるものを確実に捕まえる。計画非依存で安定していて、一番リターンが大きい
  2. 効率比のアサート。返した行数に対して読んだ行数やバッファ数が異常でないか。「10件返すのに10万行読んでいたら失敗」なら、方式を断定せずに非効率だけを弾ける
  3. 索引が存在することのマイグレーションテスト。「使われるか」ではなく「あるか」を見る。これは事実なので安定している
  4. 本番相当データでのベンチマーク。ただしCIの必須レーンではなく、nightlyか手動で。閾値は絶対時間より前回比の劣化率のほうが環境差に強い

Red-Green-Refactorの内側には合わない。あのループは速く決定的であることが生命線で、性能は環境依存で遅いからだ。1〜3は速いので通常のテストに入れられて、4は分ける、という切り分けになる。

そして全部が「テスト環境に本番相当のデータ量があるか」に帰着する。なければ性能テストはすべてノイズになる。先に投資すべきはseedデータの整備で、そこがない状態で計画をアサートしても偽の安心にしかならない。

もうひとつ。「300ms以内」のような閾値をテストに書くなら、その数字はどこから来たのか。それは仕様でも設計でもなく要求の話（成功基準）である。「なぜ索引が使われないか」より前に、「そもそも何msなら合格なのか」を誰が決めたのか、が先にある。

Q

エージェントにDBとの繋ぎ込みを実装してもらうとき、フローに「必ず実行計画を立てて、xxx ms以内で返ることを確認する」というチェックを入れて修正ループを回す、というのはよくやるもの？

A

よくやるかで言えば、エージェント文脈ではまだ定石になっていない。CIの性能ゲートやDBレビューの一環としては昔からあるパターンだが、エージェントの実装ループに組み込むのは各自が試している段階に見える。

それを抜きに筋がいいかで言うと、方向は良い。ただしその形のままだと修正ループが暴走する。

良い理由は、エージェントの弱点にちゃんと刺さっているからだ。エージェントは「動くSQL」は書けるが「速いSQL」は書けない。テストが通れば正解と見なすので、N+1でも全件走査でも緑になる。人間がレビューしないと分からない失敗モードがそこにあって、実行計画は機械可読な形でそれを暴ける。

危ない理由は、xxx ms以内 を修正ループの判定条件にすると、エージェントがそれを満たす最短経路を探すからだ。ミリ秒しか見ていないので、選択肢に次のものが全部含まれてしまう。

これは「仕様を守る」という制約が判定に入っていないから起きる。性能ゲートは必ず機能テストが通っていることを前段の必須条件に置く。緑のまま速くなった場合のみ合格、という順序が要る。

判定はミリ秒単位ではなく、こう組むのが現実的だ。機能テストが通ること（必須）、クエリ発行回数が上限以内であること、効率比が異常でないこと。そしてミリ秒は参考値としてログに出すだけで、失敗条件にはしない。回数と比率なら「テストデータを減らす」で誤魔化せない。誤魔化せるのはミリ秒だけである。

修正ループには必ず上限を切って（3回程度）、超えたらエージェントが直すのではなく人間にエスカレーションする。「索引を足せば直る」ケースと「データモデルが間違っている」ケースを実行計画からは区別できないからで、後者を自動修正させると根本原因の上に索引が積み上がる。

実務的な落とし所としては、指示書に「実行計画を確認せよ」と書くより、チェック自体をコマンド化して「これを通せ」にするほうが効く。判定基準がコードとして固定されるので、エージェントは基準そのものをいじれない（いじったら差分に出る）。プロンプトに書いた基準は、解釈でずらせてしまう。

Elasticsearchの場合

Q

Elasticsearchでも同じことができる？　ESは実行計画を立てられる？

A

できるが、意味がだいぶ違う。ESには2つの世界があって、答えが分かれる。

Query DSL側にはコストベースのプランナが存在しない。 候補計画を列挙してコストを比較する工程がない。転置インデックスという単一のアクセス経路しかないので、「全件走査か索引走査か」という選択自体が発生しないからだ。

代わりにあるのが3つ。_validate/query?explain=true はLuceneクエリへの書き換え結果を見せてくれる（計画というより正規化後のクエリ）。?profile=true はshardごとに、どのクエリノードで何ナノ秒使ったかの内訳を出す——これが一番実用的で、スコアリング段の時間、書き換えコスト、収集段の時間、集約の内訳が分かる。そしてslow logが auto_explain 相当で、本番の恒常監視はこちらが担当する。

紛らわしいのが _explain APIで、これは1ドキュメントのスコア内訳を出すもの。性能ではなく関連度のデバッグ用である。名前だけ見て飛びつくと違うものが出てくる。

一方、ES|QL側には論理計画・物理計画を持つ本物のプランナがある。ES|QLで書かれたクエリはQuery DSLへの変換を経ずに直接解析され、最適化されたうえで分散実行計画が生成される。EXPLAIN の実装も進んでいて、コーディネータ側だけでなくデータノードのローカル計画まで出す拡張が入っている。こちらはRDBMSの EXPLAIN に発想が近い。

エージェントの検証ループに使えるかで言うと、使えるがPostgreSQLより判定を作りにくい。「効率比でアサートする」手が使いにくく、返した件数と読んだ件数の対応がprofile出力にきれいには出てこない（shardごとにネストした時間の木が出るだけ）。

さらに厄介なのが、took の値がフィルタキャッシュやリクエストキャッシュで2回目から速くなること。単発のミリ秒はまったく信用できない。ウォームアップして複数回実行し、中央値を取らないと安定しない。ミリ秒を判定条件にすると誤魔化される・揺れる度合いが、RDBMSより強く出る。だからESでは、なおさら構造的な指標（書き換え時間、リクエスト回数、対象shard数）のほうに寄せたほうがいい。

整理してみて要点は3つに絞られた。

1つ目は、実行計画の形をアサートしてはいけないこと。計画は統計情報とデータ件数に依存して変わる実装詳細であり、固定するとプランナが正しく判断したときにテストが落ちる。安定して検証できるのはクエリ発行回数と効率比のほうである。

2つ目は、実行時間を失敗条件にすると誤魔化されること。データ件数を減らす、LIMIT を足す、索引を乱造する、キャッシュを効かせる——どれもミリ秒は改善するが、問題は解決していない。回数と比率は比なので、この種の回避ができない。

3つ目は、閾値の出どころが要求層にあること。「300ms以内」は仕様でも設計でもなく成功基準であり、実測値からの現状追認にすると劣化を検出できなくなる。実行計画の読み方より前に、合格ラインを誰が決めたかを確認する必要がある。