【テクニカル・上級編】コストベース最適化 – PostgreSQL

オプティマイザの「直感」を信じすぎてはいけない：PostgreSQLのコストベース最適化を解剖する

PostgreSQLのクエリチューニングをしていると、たまに「なぜそんな遠回りな道を選ぶのか？」と、オプティマイザの選択に頭を抱えたくなることはありませんか？

「インデックスは十分にあるし、テーブルもVACUUM済みだ。それなのに、なぜシーケンシャルスキャンを選択するんだ？」

そんな時、我々エンジニアが立ち返るべき場所は、PostgreSQLの心臓部、コストベース最適化（CBO: Cost-Based Optimizer）のメカニズムです。今日は、教科書的な説明はすっ飛ばして、現場のトラブルシューティングに直結する「オプティマイザの思考回路」について少し掘り下げてみましょう。

—

コスト計算の「単位」を理解する

PostgreSQLのコストモデルは、基本的に `seq_page_cost`、`random_page_cost`、`cpu_tuple_cost` といったパラメータの足し算で成り立っています。

ここで重要なのは、これらの数値は「絶対的な実行時間（ミリ秒）」ではないということです。あくまでオプティマイザが異なる実行計画同士を比較するための「相対的な重み付け」に過ぎません。

ディスクI/Oコスト: `random_page_cost` が SSD環境でもデフォルトの `4.0` のままだと、オプティマイザはインデックススキャンを極端に嫌います。最近の高速なNVMeストレージを使っているなら、ここを `1.1` や `1.0` に寄せるだけで、プランナの選択が劇的に変わることがあります。
CPUコスト: 複雑なJOINや関数呼び出しが絡むクエリでは、`cpu_operator_cost` や `cpu_tuple_cost` が支配的になります。特にJSONBの操作や複雑な正規表現を含むクエリでは、オプティマイザはここを過小評価しがちで、結果として無謀なNested Loopを選んでしまうケースが多々あります。

統計情報の「劣化」が招く悲劇

「昨日は速かったのに、今日は遅い」。この典型的なケースは、大抵の場合、`pg_statistic` の鮮度問題です。

PostgreSQLは、テーブルのデータ分布をヒストグラムと最頻値（MCV）で把握しています。しかし、統計情報の更新（ANALYZE）が追いついていないと、オプティマイザは「データの分布が均一である」という悲しい前提でコスト計算を行わざるを得ません。

特に注意が必要なのが、相関関係のあるカラム（例：郵便番号と住所）です。オプティマイザは個々のカラムの選択度は計算できても、それらの相関までは考慮できません。結果として、見積もりの行数（見積もり行数）と実際の行数（実際の行数）が100倍、1000倍と乖離し、Nested Loopで爆死する……というのが、パフォーマンスチューニングにおける「あるある」です。

トラブルシューティングの勘所：プランナの「脳内」を覗く

もしクエリが遅いなら、`EXPLAIN (ANALYZE, BUFFERS)` を叩くのは当然ですが、単に実行計画を眺めるだけでなく、以下の点に着目してみてください。

1. 「見積もり」と「実績」の差: `rows=1000, actual rows=1000000` のような乖離がないか？これがあれば、統計情報のサンプリング不足か、相関データの問題です。
2. Shared Hit/Readの割合: キャッシュされているはずなのに `Read` が多いなら、インデックスの物理的な配置が悪いか、そもそもインデックスが効いていない（あるいは不要な）スキャンが発生しています。
3. コストのブレイクダウン: `cost=0.00..12345.67` の内訳を疑うこと。特に `Filter` で大量の行を捨てているなら、インデックスの設計そのものを再考すべきサインです。

結論：魔法のパラメータはない

PostgreSQLのオプティマイザは、非常に洗練された論理エンジンです。しかし、所詮は「統計情報という名の過去の記憶」を頼りに未来を予測しているに過ぎません。

もしオプティマイザが的外れな計画を立てているなら、それはオプティマイザが悪いのではなく、「PostgreSQLに与えている情報が不十分である」と考えるのが、熟練エンジニアの視点です。

`CREATE STATISTICS` を活用してカラム間の相関を教え込むのか、それともコストパラメータを環境に合わせてチューニングするのか。あるいは、ヒント句を使わずにクエリの書き方を工夫して、プランナを「正しい道」へ誘導するのか。

そうした試行錯誤のプロセスこそが、データベースエンジニアとしての醍醐味ではないでしょうか。

皆さんのデータベースに、今日もしなやかな実行計画が選ばれますように。