質問への回答

質問の歴史

PostgreSQLは、繰り返し実行されるSQLクエリのパースおよび計画オーバーヘッドを排除するために準備済みステートメントを導入しました。初期のバージョンでは、常に特定のパラメータ値に合わせたカスタム実行プランを生成していましたが、これには複雑なクエリに対して重要なCPUコストがかかっていました。この最適化のために、PostgreSQL 9.2では、複数回の実行にわたって単一のプラン構造を再利用するジェネリックプランメカニズムによるプランキャッシングが実装されました。このアプローチは計画の待機時間を短縮しますが、初期の計画段階ではすべてのパラメータをオペークなプレースホルダとして扱います。

問題点

PostgreSQLにおけるパーティションプルーニングは、2つの異なる段階で動作します。プラン時プルーニングは、クエリ計画中にプランナーがリテラル値に対してパーティション制約を調べる際に発生し、ランタイムプルーニングは、実行中にパーティション単位の結合またはアペンドノードフィルタリングを使用してパーティションをフィルタリングします。準備済みステートメントに対して生成されるジェネリックプランは、計画時に具体的なパラメータ値を欠いているため、プラン時プルーニングが不可能になります。その結果、プランナーは実行時に提供される実際のパラメータ値に関係なく、すべてのパーティションをスキャンするプランを生成し、大規模なパーティションテーブルにおいては性能の著しい低下を引き起こします。

解決策

解決策は、評価段階で実際のパラメータ値を組み込むカスタムプランを生成するようにPostgreSQLに強制することです。これは、セッションや特定のクエリに対してplan_cache_mode設定パラメータをforce_custom_planに設定することによって実現され、ジェネリックプランキャッシュを完全にバイパスします。別のプロトコルレベルの回避策としては、リテラル値を含むBindメッセージを持つ拡張クエリプロトコルを使用するか、他のパラメータをバインドしたままパーティションキー用のリテラル値をインライン化するクライアントサイドのクエリビルダーを使用することが挙げられます。

-- このセッションのためにカスタムプランを強制する
SET plan_cache_mode = force_custom_plan;

-- または、安全にリテラルをインライン化するためにformat()を使用した動的SQLを使用する
EXECUTE format('SELECT * FROM logs WHERE log_date >= %L', '2024-01-01');

生活からの状況

高頻度取引分析プラットフォームは、毎朝イン intraday価格の動きをクエリする際に深刻な待機時間スパイクを経験しました。データベースは、取引セッション日付によってパーティション化されたテーブルにティックデータを保存しており、5年間で2,000以上のパーティションがありました。アプリケーションは、SQLインジェクションを防ぎ、パースオーバーヘッドを減らすために、日付パラメータに対して?プレースホルダを持つJDBCの準備済みステートメントを使用していました。

開発チームは、最初に「今日の」データをフィルタリングするクエリが履歴パーティションをスキャンしていることに気付き、予想される300ミリ秒ではなく45秒を消費しました。この性能低下は、ジェネリックプランが計画段階で無関係なパーティションを排除できなかったために発生しました。

1つのアプローチは、ホットデータのために別のログなしテーブルを作成し、毎晩レコードを移行することでした。この戦略は最近のクエリに対して完全にパーティション化されたテーブルをバイパスすることになりましたが、複雑なETLロジックを導入し、システムクラッシュ時にデータ損失のリスクがありました。

別の提案は、JDBC接続プール内で準備済みステートメントをグローバルに無効にすることでした。これにより、リテラル値がプランナーに公開され、プラン時プルーニングが復元されるが、ベンチマークでは、繰り返しの解析および計画オーバーヘッドにより、データベースサーバーのCPU利用率が40%増加することが明らかになりました。

チームはまた、バージョン11で導入されたPostgreSQLのランタイムパーティションプルーニング機能を使用することを評価しました。しかし、ランタイムプルーニングは、エグゼキュータがスキャンを開始した後にのみパーティションを排除し、プランナーはすべてのパーティションのリソースを割り当て続け、パーティション境界を無視した最適でない結合順序を生成しました。

最終的に、チームは接続レベルの設定切替を実装することを選択しました。彼らは接続プールを設定して、パーティション化されたテーブルをターゲットにしたクエリを検出し、特定のステートメントを送信する前にSET plan_cache_mode = force_custom_planを実行しました。これは、ユーザー入力フィルタ用のパラメータ化されたクエリのセキュリティ利点を保持しながら、プランナーにパーティションキー値を表示することを保証しました。

その結果、クエリの待機時間は280ミリ秒に減少し、データベースの全体的なCPU使用率も15%減少しました。これは、プランナーが実行前に1,999のパーティションを排除するための制約除外を利用できるようにしたからです。この最適化により、取引プラットフォームはデータの完全性やセキュリティを損なうことなく、厳格な朝の待機時間要件を満たすことができました。

候補者が見落としがちなこと

plan_cache_modeがautoに設定されているとき、PostgreSQLはどのようにジェネリックプランとカスタムプランを決定しますか？

autoモードでは、PostgreSQLは最初の5回の実行のためにカスタムプランを使用してクエリを計画および実行し、計画コストを蓄積します。5回目の実行の後、ジェネリックプランの平均実行時間（最初の実行時に推定）と、カスタムプランの平均実行時間とその計画オーバーヘッドを比較します。ジェネリックプランの推定コストがカスタムプランの平均コストよりも少ない場合、システムはその準備済みステートメントに対して永続的にジェネリックプランに切り替えます。候補者は、これらの比較にジェネリックプランを再利用することで削減された計画オーバーヘッドが含まれていることにしばしば気づかず、この決定が準備済みステートメントのライフタイムの間で永続的であり、明示的に再計画されない限り変更できないことを見落としがちです。

準備済みステートメントの文脈におけるプラン時とランタイムのパーティションプルーニングの違いは何ですか？

プラン時プルーニングは、プランナーが特定のパーティションが関連する行を含まないことを証明できるとき、すなわちパーティション制約とクエリ内のリテラル値に基づいて、計画段階で発生します。ランタイムプルーニングは、エグゼキュータが実際のパラメータ値に対してパーティション制約を確認する際に実行中に発生します。ジェネリック準備済みプランは、PostgreSQL 11以降からランタイムプルーニングをサポートしていますが、パラメータ値が不明であるためプラン時プルーニングはサポートできません。候補者はしばしばこれらのメカニズムを混同し、ランタイムプルーニングがすべての準備済みステートメントのパーティショニングの問題を解決すると考え、プラン時プルーニングが効率的な結合計画とインデックス選択に不可欠であることに気づいていません。

なぜforce_custom_planが10以前のPostgreSQLバージョンでパーティションプルーニングの問題を解決できない可能性があるのですか？

10以前のバージョンでは、PostgreSQLはランタイムパーティションプルーニングの重要なサポートを完全に欠いており、準備済みステートメントは、Bindメッセージを使用して拡張クエリプロトコルを介してパラメータが渡された場合、制約除外の利点を得ることができませんでした。プランナーはすべてのバインドされたパラメータを計画プロセスの外部として扱い、制約除外をトリガーするには、クエリ文字列自体にリテラル値を明示的に指定する必要がありました。この歴史的な制限のため、レガシーシステムでは、カスタムプランであってもすべてのパーティションをスキャンする必要があり、リテラルまたは適切なエスケープを伴うクライアントサイドの文字列連結を使用した動的SQL生成が必要でした。