クエリ最適化・実行計画 くえりさいてきか・じっこうけいかく
簡単に言うとこんな感じ!
SQL は「何がほしいか」だけを書く言葉で、「どう探すか」はデータベースが自分で考えるんだ。その作戦を選ぶのがクエリ最適化、選んだ作戦の中身が実行計画だよ。カーナビのルート案内と同じで、遅いときはまず「どの道を通ったか」を見るってこと!
クエリ最適化・実行計画とは
SQL には「東京の顧客の今月の注文を出して」のように 結果だけ を書き、どの順番で表を読むか、索引を使うかは書きません。それを決めるのがデータベースの中の オプティマイザ(PostgreSQL では プランナー)です。同じ結果を出す方法は何通りもあるので、オプティマイザは表の行数や値の偏りといった 統計情報 をもとに手間(コスト)を見積もり、いちばん安そうな方法を選びます。これが クエリ最適化 です。
選ばれた手順書が 実行計画(Execution Plan) です。「注文の表を索引で絞り込み、顧客の表とこの方法でつなぐ」といった段取りが木の形で並びます。EXPLAIN という命令を SQL の前に付けると、この計画を見ることができます。
大事なのは、オプティマイザは 見積もりで判断している という点です。統計が古い、条件の書き方が索引を使えない形になっている、といった理由で見積もりが外れると、普段は一瞬で終わる画面が何十秒もかかることがあります。「急に遅くなった」の理由は、たいてい実行計画に出ています。
SQL が実行されるまで
実行計画の見方
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 123;
Index Scan using orders_customer_id_idx on orders
(cost=0.43..8.45 rows=5 width=64)
(actual time=0.02..0.03 rows=4 loops=1)
| 表示 | 意味 |
|---|---|
Seq Scan | 表を頭から全部読む。大きな表で少しだけ取るなら遅い |
Index Scan | インデックス で場所を調べてから読む |
Nested Loop / Hash Join / Merge Join | 2つの表のつなぎ方(結合) |
cost | 見積もった手間(単位は相対的なもの) |
rows | 見積もった行数。actual の行数と大きくずれていたら要注意 |
EXPLAIN ANALYZE は 実際に実行して 時間も測ります。更新の SQL に付けると本当に書き換わるので注意します。
まず「どの画面の、どの操作が、何秒かかるか」を伝えるのがいちばん助かるんだ。そのうえで、開発会社には「遅い SQL を特定して、実行計画を見せてください」って頼むといいよ。
DB には遅い SQL を記録する仕組み(スロークエリログや pg_stat_statements)があるから、それが動いているかも聞いてみよう。
そこが落とし穴なんだ。インデックスは 登録や更新のたびに作り直す手間 が増えるし、場所も食う。それに、WHERE の書き方しだいでは、付けても使われないんだよ。
だから、実行計画を見て「ここで全件読んでいる」と確かめてから、必要なものだけ足すのが正しい順番なんだ。
遅くなる典型的な原因
| 原因 | 何が起きているか | 主な手当て |
|---|---|---|
| 索引がない | 大きな表を毎回全部読む | 条件に合う インデックス・複合インデックス を足す |
| 列に関数をかけている | WHERE date(created_at) = ... で索引が使えない | 範囲の条件に書き直す |
| 統計が古い | 行数の見積もりが外れ、悪い計画を選ぶ | ANALYZE で統計を更新する |
| N+1 問題 | 一覧の件数ぶん SQL を繰り返し投げる | ORM でまとめて読む設定にする |
| 取りすぎ | 使わない列や行まで読む | 必要な列だけ選ぶ、ページ分けする |
PostgreSQL では、統計の更新は VACUUM と同じ自動の仕組み(autovacuum)が行います。大量に取り込んだ直後は、更新が追いつかず遅くなることがあります。
歴史と背景
- 1970年代:IBM の研究用データベース System R で、統計からコストを見積もって計画を選ぶ方法が研究され、1979年にパトリシア・セリンジャーらの論文で示される
- 1980年代〜1990年代:SQL が規格になり、商用データベースが「速く答えを返す計画を選ぶ力」で競うようになる
- 2000年代:Oracle などで、決まった規則で計画を選ぶ方式から、コストで選ぶ方式への移行が進む
- 2010年代以降:クラウドの DB や監視サービスで、遅い SQL と実行計画を画面から見られるようになる
速くするための他の手段との比べ方
| 手段 | 効く場面 | 注意点 |
|---|---|---|
| SQL と索引の見直し | ほとんどの遅さ。まずここから | 実行計画で効果を確かめる |
| マテリアライズドビュー | 重い集計を何度も見る | 中身が少し古くなる |
| パーティショニング | 期間で絞ることが多い巨大な表 | 設計の手間が増える |
| リードレプリカ | 読み取りの量が多すぎる | 1本の SQL は速くならない |
実務のポイント
- 遅い SQL を記録する設定(スロークエリログ、
pg_stat_statementsなど)が本番で有効か - 主な画面の 目標の応答時間 を要件に書いているか
- 本番に近い データ量でテスト しているか(少ない件数では遅さが出ない)
- 性能の改善報告に、改善前と後の実行計画 が付いているか
- 大量の取り込みの後に 統計を更新する 手順があるか
関連する規格・RFC
| 規格 | 内容 |
|---|---|
| ISO/IEC 9075(SQL) | SQL の国際規格。結果として何を返すかを定め、どう実行するかは各製品に任せる。EXPLAIN は規格になく、製品ごとの機能 |
参考文献・外部リンク
- Using EXPLAIN(PostgreSQL) — 実行計画の読み方の公式解説
- EXPLAIN(PostgreSQL) —
EXPLAINの書き方とオプション - Statistics Used by the Planner(PostgreSQL) — プランナーが使う統計情報
- Performance Tips(PostgreSQL) — 性能を上げるための公式の手引き
- pg_stat_statements(PostgreSQL) — SQL ごとの実行回数や時間を集める拡張機能
関連用語
- インデックス — 検索を速くするための索引
- 複合インデックス — 複数の列を組み合わせた索引
- JOIN(結合) — 実行計画でつなぎ方が選ばれる、表どうしの結合
- バキューム・VACUUM — 統計の更新とも関わる PostgreSQL の掃除の仕組み
- マテリアライズドビュー — 重い集計の結果を保存しておく仕組み
- パーティショニング — 巨大な表を分けて読む量を減らす方法
- ORM — N+1 問題の原因になりやすい、SQL を自動で作る道具
- データベースオブザーバビリティ — 遅い SQL や負荷を見える化する取り組み