2025/05/04 16:15 Another look into PostgreSQL CTE materialization and non-idempotent subqueries
やっほー、ロボ子！今日のITニュース、PostgreSQLの`LIMIT`に関する面白い話があるのじゃ。
博士、こんにちは。`LIMIT`ですか？基本的な機能ですけど、何か問題でも？
そう、それがね、`LIMIT N`を含むクエリが、プランナによって予期しない実行をされることがあるらしいのじゃ。特に`DELETE`文の中で`LIMIT 1`を含むサブクエリが複数回実行されて、意図したより多くの行が削除される可能性があるんだって。
それは困りますね。原因は何なのでしょう？
PostgreSQLのクエリプランナが、クエリ全体の実行を最適化しようとするからなのじゃ。コストベースの分析で、サブクエリを複数回実行するプランが選択されることがあるんだって。
なるほど。でも、サブクエリが毎回同じ行を返すとは限らない場合、問題が起きるということですね。
その通り！例えば、`ORDER BY`がない場合や、`FOR UPDATE SKIP LOCKED`を使っている場合ね。各実行で違う行が対象になって、`LIMIT`で指定した数より多く削除されることがあるのじゃ。
`ORDER BY`で決定性を追加するのは有効ですか？
`ORDER BY id`を追加すると、行の選択は決定的になるけど、`FOR UPDATE SKIP LOCKED`と組み合わせると、サブクエリの結果が実行ごとに変わる可能性があるのじゃ。プランナがサブクエリを複数回実行する場合、各パスで違う行がロックされてスキップされるかもしれないのじゃ。
`SKIP LOCKED`を削除して`ORDER BY id`を保持するとどうなりますか？
そうすると、サブクエリは完全に冪等になるのじゃ！`id`のセットが安定するから、`DELETE`は`LIMIT`を尊重して、指定された行のみに影響を与えるのじゃ。
なるほど。`LIMIT 1`の場合はどうですか？
`LIMIT 1`を扱う場合は、`=`を使うとプランナに強力なヒントになるのじゃ。`=`演算子は単一のスカラー値を期待するから、プランナはサブクエリを1回だけ評価してその値を取得することを強制されるのじゃ。
それなら安心ですね。でも、`LIMIT`が常に1である場合にしか使えないのですね。
そう、そこで登場するのがCTE（Common Table Expression）なのじゃ！PostgreSQL 12以降なら、CTEを明示的に具体化できるのじゃ。
CTEですか。どのように使うのですか？
`MATERIALIZED`キーワードを使うのじゃ！これはPostgreSQLにCTEクエリを正確に1回実行して、その結果を一時的に保存するように指示するのじゃ。メインの`DELETE`クエリはその固定された結果セットで動作するから、サブクエリの再評価の問題を完全に回避できるのじゃ！
`MATERIALIZED`は強力ですね。正確さと予測可能なパフォーマンスが保証されるのは素晴らしいです。
そういうことなのじゃ！まとめると、CTEサブクエリ内の冪等性と決定性が重要で、特に`MATERIALIZED`を使っていない場合は注意が必要なのじゃ。`LIMIT`と`ORDER BY`を組み合わせて、決定的な行選択を保証するのも大事。そして、`EXPLAIN ANALYZE`を使って、クエリの実行計画をチェックするのじゃ！
勉強になります！`loops > 1`を探して再評価が発生しているか確認するんですね。
そうそう！DML操作内でサブクエリやCTEノードで`loops > 1`がないか確認するのじゃ。あと、これらの再評価のリスクがあるCTE内で、DMLを直接使うことが本当に安全かどうかを検討するのも大事なのじゃ。
了解しました！博士、今日もありがとうございました。
どういたしまして！最後に一つ。PostgreSQLの`LIMIT`の挙動は、まるで私の部屋の掃除みたいじゃな。最初は少しだけ片付けるつもりが、いつの間にか部屋全体がめちゃくちゃになっている、みたいな？
博士、それは`DELETE`されすぎですね…。
⚠️この記事は生成AIによるコンテンツを含み、ハルシネーションの可能性があります。
