その「関数」、本当に毎行計算させる必要がありますか？：PostgreSQLクエリ最適化の深淵
PostgreSQLを長く触っていると、ふとした瞬間に「なぜこのクエリはこれほど重いのか」という壁にぶつかることがあります。特に、集計関数や複雑な式を扱うクエリにおいて、実行計画のコスト見積もりが現実と乖離し、非効率なループ処理に陥っているケースです。
今日は、PostgreSQLにおける「式評価（Expression Evaluation）」の最適化、特に定数畳み込みの限界と、エンジニアが手動で行うべきクエリ書き換えの美学について、少し深く掘り下げてみましょう。
PostgreSQLのオプティマイザはどこまで賢いか
まず前提として、PostgreSQLのオプティマイザは非常に優秀です。`WHERE x = 1 + 1` と書けば、コンパイル時に `x = 2` へと「定数畳み込み（Constant Folding）」を行ってくれます。しかし、我々が扱う実務のクエリはもっと複雑です。
例えば、ユーザー定義関数（UDF）や、 volatile 性（関数の出力が引数のみに依存するか否か）が曖昧な関数を `WHERE` 句や `SELECT` リストに混ぜた場合、オプティマイザは安全側に倒して「毎行計算」を選択することがあります。
— 悪い例：毎行、現在の時刻から30日前の計算が走る可能性がある
SELECT FROM orders
WHERE created_at > (NOW() – INTERVAL ’30 days’);
厳密には、PostgreSQLは `NOW()` をクエリ開始時の定数として扱う最適化を行いますが、これが複雑な外部ライブラリを叩く関数や、複雑な演算を伴うカスタム関数になると、オプティマイザは「この関数は本当に毎行同じ値を返すのか？」を保証できず、インデックスをフル活用できないケースが多々あります。
なぜ「式」がボトルネックになるのか
パフォーマンスチューニングの現場でよく見るのは、「式をインデックスに貼っていないがゆえに、全行走査せざるを得ないケース」です。
もし、カラムに対して計算式を適用して検索しているなら、それはクエリ側で調整すべきサインです。
- 改善のポイント：
- 計算対象を右辺に寄せる（SARGableな記述にする）。
- 計算結果を `GENERATED ALWAYS AS` カラムとして保存し、そこにインデックスを貼る。
- 定数式をアプリケーション側で事前計算し、プレースホルダとしてバインドする。
特に、`GENERATED COLUMN` は強力です。PostgreSQL 12以降、物理的に値を保持できるようになったため、複雑な計算結果をインデックス化するコストを劇的に下げることができます。
パフォーマンストラブルシューティングの勘所
もし、あなたが「どうもクエリのコスト計算が怪しい」と感じたら、まずは `EXPLAIN (ANALYZE, BUFFERS)` を見てください。ここで注目すべきは `Rows Removed by Filter` です。
ここが膨大な数字になっている場合、オプティマイザは「インデックスを使えば速い」と判断しているのに、実際にはフィルタリングで多くの行を捨てているという不整合が起きています。
そんな時、私が実践しているのは以下のステップです。
1. 関数の `IMMUTABLE` 指定の見直し:
カスタム関数がもし `STABLE` や `VOLATILE` になっているなら、純粋な計算処理であれば `IMMUTABLE` に変更できないか検討してください。これにより、クエリプランナーは計算結果をキャッシュしたり、定数として扱えるようになります。
2. CTE（Common Table Expressions）の罠:
PostgreSQL 12以前はCTEがオプティマイザの障壁（Optimization Fence）となっていましたが、現在でも複雑なCTEはインライン化されないことがあります。式評価を最適化したい場合、CTEを `LATERAL JOIN` に書き換えることで、評価のタイミングを制御し、効率的なインデックスアクセスを誘発できることが多いです。
3. 式そのものの単純化:
`CASE` 文がネストしすぎていませんか？ 複雑な条件分岐は、データモデリング側でフラグカラムを持たせるか、あらかじめフラットなテーブルにマテリアライズしておく方が、長い目で見てDBの負荷を下げます。
最後に：エンジニアとしての矜持
データベースは、あくまで「宣言的」な言語であるSQLを解釈するエンジンです。しかし、その解釈の裏側には、物理的なI/OとCPUサイクルが厳然と存在しています。
「とりあえず動くクエリ」を書くことは誰でもできます。しかし、式一つひとつの評価コストを想像し、オプティマイザが迷わないような「明快な記述」を心がけることこそが、熟練のエンジニアの仕事ではないでしょうか。
複雑な計算をデータベースに押し付けるのではなく、データベースが最も得意とする「高速なインデックス検索」を最大限に引き出すための道筋を整える。その一手間が、数年後のシステムの安定稼働を支える大きな資産になります。
皆さんのクエリが、今日も効率的にインデックスを駆け巡ることを願っています。
コメント