PostgreSQLの「コスト」をハックする：物理層を意識したクエリ最適化の作法
PostgreSQLのクエリプランナを信頼しきっている人は多いけれど、彼らは時として「現実のハードウェア」を無視した夢想家になることがある。
私たちがクエリを投げる時、プランナは「どれが一番速いか」を計算するために、内部的なコストモデルを駆使する。その計算の根拠となるのが、`postgresql.conf`に鎮座する一連のコストパラメータたちだ。これらをデフォルトのまま放置しているのは、F1カーのエンジンを積みながら、教習所の教本通りにしか走らせていないようなものだ。
今回は、このコストモデルの心臓部を紐解き、ハードウェアの特性をプランナに「教え込む」ためのチューニングについて掘り下げていこう。
—
コストパラメータという「重力」
プランナの判断基準は、物理的な時間（秒）ではなく、抽象化された「コスト」という単位だ。このコストの算出式を支配しているのが以下の面々である。
- `seq_page_cost`: シーケンシャルアクセスでの1ページ読み取りコスト（デフォルト: 1.0）
- `random_page_cost`: ランダムアクセスでの1ページ読み取りコスト（デフォルト: 4.0）
- `cpu_tuple_cost`: 1タプルを処理するコスト（デフォルト: 0.01）
ここで重要なのは、これらが相対的な重み付けであるということだ。PostgreSQLは「ランダムアクセスはシーケンシャルアクセスの4倍遅い」と仮定して計画を立てる。これが現代のストレージ環境において、いかに不自然かという話だ。
—
HDDからSSDへ：ランダムアクセスの神話崩壊
かつてのHDD全盛期、ディスクヘッドを物理的に動かすランダムアクセスは、確かにシーケンシャルアクセスの数倍から数十倍のコストを要した。だからこそ、`random_page_cost`は「4.0」という高い値に設定されていた。
しかし、SSDが標準となった今、この「4倍」という格差は完全に形骸化している。
SSD環境でのチューニング
もし貴方のDBサーバが高速なNVMe SSD上で動いているなら、`random_page_cost`を 1.1 〜 1.0 にまで下げることを強く推奨する。
これを下げると何が起きるか？ プランナは「インデックススキャン」や「ネステッドループ」をより積極的に選択するようになる。シーケンシャルスキャン（全件走査）という安全策から脱却し、必要なデータだけをピンポイントで引き抜くプランに傾斜するわけだ。
逆に、これを下げすぎると、本来シーケンシャルスキャンが適しているケースでも無理やりインデックスを使おうとして、かえってIOPSを浪費するリスクがある。ここが腕の見せ所だ。`EXPLAIN ANALYZE`を繰り返し、コストモデルと実際の実行結果の乖離を埋めていく作業は、まさに調律師の仕事に近い。
—
cpu_tuple_cost：CPUバウンドなクエリの隠れた真実
あまり注目されないが、`cpu_tuple_cost`も侮れない。特に、数千万行のテーブルを結合（Join）したり、複雑な集計を行ったりする場合、コストの大部分はこのCPUコストが占めるようになる。
- デフォルト（0.01）が重すぎる場合: 大規模な結合処理において、プランナが「ハッシュ結合よりもネステッドループの方が速い」と勘違いし、悲惨なパフォーマンスを引き起こすことがある。
- 最適化のヒント: 大規模なデータセットを扱う分析系クエリが多い環境では、この値を少し下げてみることで、プランナの意思決定を「ハッシュ結合」や「マージ結合」側に寄せることが可能だ。
—
トラブルシューティングの鉄則：安易に全体を変えるな
これらを調整する際、絶対にやってはいけないことがある。それは、設定を「なんとなく」全体に適用することだ。
データベースというものは、OLTP（短いトランザクション）とOLAP（長い集計）が混在していることが多い。片方に最適化すれば、もう片方が死ぬ。もし特定の重いクエリに悩まされているのであれば、グローバルな設定をいじる前に、セッション単位での調整を検討してほしい。
— 特定のトランザクション内だけでコストモデルを最適化する
SET LOCAL random_page_cost = 1.0;
SELECT FROM huge_join_query;
このように、必要に応じてプランナに「魔法」をかける。それが、熟練エンジニアの流儀だ。
—
最後に：なぜ我々はパラメータをいじるのか
結局のところ、データベースエンジニアの仕事とは、ハードウェアという「物理的な制約」を、ソフトウェアという「論理的な構造」にいかにスムーズに橋渡しするか、ということだ。
`random_page_cost`を1.0にする。それは、PostgreSQLに対して「君の足元には、もはや物理的なヘッドなんて存在しない。好きなようにインデックスを使いこなしていいんだ」と語りかけることに等しい。
チューニングとは、設定値をいじくる作業ではない。データベースが持つポテンシャルと、基盤となるハードウェアの性能との間の「認識のズレ」を修正する、対話のプロセスなのだ。
さあ、あなたの環境の `EXPLAIN` の結果が、どう変わるか見てみようじゃないか。
コメント