「またCTEでハマってるの？」
新人のコードレビューをしていると、本当によく見る光景なんだよね。PostgreSQLの`WITH`句。可読性が高いからみんな大好きなんだけど、実は「見かけの良さ」の裏で、DBが裏切ってくることがよくある。
今回は、そんなCTEの「思わぬ裏切り」を防ぐための、`MATERIALIZED`と`NOT MATERIALIZED`の制御について話そうと思う。これを知っているだけで、クエリチューニングの引き出しが一段階深くなるはずだよ。
—
なぜ「最適化」が「改悪」になるのか
まず前提として、PostgreSQL 12以降、CTEの挙動は賢くなった。昔はCTEを書くと強制的に「マテリアライズ（一時テーブル的なものにデータを書き出す）」されていたんだけど、今はデフォルトで「インライン化（クエリのメイン部分に展開）」されるようになったんだ。
これ、基本的には良いことなんだよ。オプティマイザがCTEの中身をメインクエリと統合して、インデックスをフル活用した実行計画を立てられるようになるからね。
でもね、現場でたまにこういうことが起きる。
「数千万行あるテーブルをCTEでフィルタリングして、その結果をJOINしてるだけなのに、なぜかインライン化されたせいで結合順序がめちゃくちゃになって、フルスキャンが走ってる……！」
そう、オプティマイザが「最適化しよう」として逆に墓穴を掘るケースだ。こういうとき、僕たちはDBに対して「余計な気を遣わなくていい、一旦ここを計算して固めてくれ！」と命令する必要がある。それが今回のテーマだ。
—
MATERIALIZED vs NOT MATERIALIZED の使い分け
PostgreSQLでは、CTE名の直後にキーワードを置くことで、挙動を強制できる。
1. MATERIALIZED：一旦、箱に詰める
「結果を一旦メモリ（あるいはディスク）に書き出して固定しろ」という指示だ。
WITH fast_filter AS MATERIALIZED (
SELECT user_id, count() as cnt
FROM logs
WHERE created_at > ‘2023-01-01’
GROUP BY user_id
)
SELECT u.name, f.cnt
FROM users u
JOIN fast_filter f ON u.id = f.user_id;
こんな時に使う：
- CTE内のクエリが非常に重く、何度も参照されるわけではないが、インライン化すると実行計画が非効率になる場合。
- 複雑な集計を一度完了させてから、次の処理に渡したい場合。
2. NOT MATERIALIZED：中身をバラして展開する
「インライン化して、メインクエリと一体化しろ」という指示だ。
WITH user_segment AS NOT MATERIALIZED (
SELECT id FROM users WHERE status = ‘active’
)
SELECT FROM orders
WHERE user_id IN (SELECT id FROM user_segment);
こんな時に使う：
- CTEがメインクエリの抽出条件と統合できる場合。
- インライン化することで、CTE側にインデックスを効かせられる場合。
—
実務での「勘所」
ここからは教科書には書いていない、現場の肌感覚の話をしよう。
僕がチューニングする際、最初にやるのは`EXPLAIN ANALYZE`を叩いて、「コスト」を見ることじゃない。「CTE Scan」という文字が出ているかどうかを確認することだ。
もし、CTE Scanが走っていて、その中のコストが異常に高いなら、まずはそのCTEが「本当にインライン化されてほしいのか、マテリアライズされてほしいのか」を自問自答する。
- 「インデックスが効いていないな」と感じたら？ → `NOT MATERIALIZED`を試す。メインクエリのインデックスを拝借できるかもしれない。
- 「実行計画が変なJOIN順序になってるな」と感じたら？ → `MATERIALIZED`を試す。一度計算結果を固定することで、オプティマイザの迷いを断ち切るんだ。
注意点：マテリアライズの代償
`MATERIALIZED`を使うと、一時テーブルを作るコスト（メモリ消費やディスクI/O）が発生する。だから、何でもかんでもマテリアライズすればいいってもんじゃない。特にメモリ（`work_mem`）がカツカツの環境で巨大なデータをマテリアライズすると、即座にディスク溢れを起こして爆速で遅くなる。
「便利だから使う」んじゃなくて、「オプティマイザを導くために使う」。これが、できるエンジニアのやり方だよ。
—
まとめ：結局、どう向き合うか
1. 基本は放置： PostgreSQLのオプティマイザを信じる。
2. 遅いなら疑う： `EXPLAIN ANALYZE`で実行計画を見て、CTEの展開がボトルネックになっていないか確認する。
3. 明示的に制御する： 必要に応じて `MATERIALIZED` / `NOT MATERIALIZED` を使い分け、DBに対して「どう計算してほしいか」を伝えてあげる。
クエリチューニングは、DBという名の相棒との対話だよ。相手がどこで詰まっているのか、どこで迷っているのかを紐解いていく作業だ。
もしまたクエリが遅くて夜眠れなくなったら、まずはCTEを疑ってみて。案外、ちょっとしたキーワード一つで世界が変わるかもしれないよ。
それじゃあ、今日はこの辺で。また何かあればいつでも聞いてくれ。
コメント