PostgreSQLのCTE、その「最適化の罠」と付き合う技術
PostgreSQLの `WITH` 句、いわゆるCTE（Common Table Expressions）。
コードの可読性を劇的に高めてくれるこの機能は、複雑な分析クエリを書くエンジニアにとって欠かせない武器です。しかし、大規模なデータセットを扱う現場で、このCTEが「最適化のボトルネック」として立ちはだかる瞬間を、皆さんも一度は経験したことがあるのではないでしょうか。
今日は、PostgreSQLにおけるCTEのマテリアライゼーション（一時テーブルへの書き出し）という、一見地味ながらもクエリ性能を根底から揺るがす挙動について、深く掘り下げてみたいと思います。
—
「隠れたマテリアライゼーション」という名の副作用
PostgreSQL 12以前、CTEは「最適化のバリア」として有名でした。どんなに単純な処理であっても、CTEは必ず一度マテリアライズ（一時テーブル化）され、クエリ全体から独立して実行されていました。
これは、インデックスを活用してCTE内のデータを絞り込みたいとき、非常に厄介な挙動です。外側のクエリが「このIDに一致する行だけ欲しい」と願っても、CTE側は「とりあえず全部計算してから渡すね」と答えてしまう。結果、数百万行のテーブルをフルスキャンしてキャッシュを溢れさせ、悲惨なパフォーマンスを叩き出す……そんな現場を何度救ってきたかわかりません。
PostgreSQL 12以降の「インライン展開」という救世主
PostgreSQL 12で、この挙動は劇的に変わりました。CTE内で再帰がない、かつ副作用がない（データ更新などを含まない）場合、オプティマイザが自動的にCTEをインライン展開し、外側のクエリと統合して実行計画を立てるようになったのです。
これにより、CTEの外側にある条件句（WHEREなど）をCTEの中にプッシュダウンできるようになりました。これはエンジニアにとって「夢の改善」です。
しかし、ここで一つ問いかけたい。「すべてのCTEは、本当にインライン展開されたほうが幸せなのか？」
あえて「マテリアライズ」させるという選択肢
実は、オプティマイザの判断が常に正しいわけではありません。
例えば、非常に重い集計処理を行うCTEがあり、それをメインクエリの複数箇所で参照しているとしましょう。インライン展開された結果、その重い計算が複数回重複して実行され、CPUを浪費し尽くすケースがあります。
そんなとき、私たちは `MATERIALIZED` ヒントを明示的に指定する必要があります。
WITH heavy_calculation AS MATERIALIZED (
SELECT … — 重い処理
)
SELECT FROM heavy_calculation …
こうすることで、「このCTEは一度計算してメモリ（あるいはディスク）に保持しておけ」という指示をオプティマイザに与えることができます。逆に、インライン展開を強制したい場合は `NOT MATERIALIZED` を指定する。PostgreSQL 14で導入されたこの制御は、まさに職人が自分の指先でクエリをチューニングするためのツールです。
パフォーマンストラブルの切り分け方
現場で「なぜかこのクエリが遅い」という相談を受けたとき、私はまず `EXPLAIN (ANALYZE, BUFFERS)` を叩きます。
もしCTEがマテリアライズされているなら、実行計画の中に `CTE Scan` というノードが現れます。ここで注目すべきは、以下の点です。
- メモリ消費量: `work_mem` を超過して、一時ファイル（Temp File）への書き出しが発生していないか？
- 実行回数: 本来1回で済むはずのCTEが、ループ内で何度も再実行されていないか？
- プッシュダウンの有無: インライン展開されているはずなのに、WHERE句がCTEの外側にとどまっていないか？
もし意図しないマテリアライゼーションで遅延しているなら、それは多くの場合「インライン展開されるべきなのに、オプティマイザが安全策をとっている」か、「CTEの定義が複雑すぎて最適化を諦められている」かのどちらかです。
結論：道具を使いこなすということ
結局のところ、CTEの最適化は「可読性」と「計算コスト」のトレードオフです。
可読性を求めてCTEに切り出すのは正しい設計ですが、その結果生成される実行計画が、生のサブクエリよりも非効率になっていないかを常に疑う必要があります。「書いて終わり」ではなく、実行計画の「木」がどう成長しているかを想像する。それが、最高峰のパフォーマンスを引き出すエンジニアの矜持だと私は信じています。
皆さんのクエリが、明日も軽快に走り抜けることを願って。それでは、また別の深淵でお会いしましょう。
コメント