データ面接：パラメータ化クエリに対するPostgreSQLのジェネリックプランをどのように診断しますか？

プロンプトとユースケース

プリペアドステートメントを使用するAPIがリリース後にロングテールレイテンシを発生させました。小さなテナントでは高速ですが、大きなテナントでは突然シーケンシャルスキャンが実行されます。PostgreSQLがカスタムプランとジェネリックプランをどのように選択するか、EXPLAIN (GENERIC_PLAN) を使用してそれらをどのように比較するか、統計情報、パラメータの偏り、キャッシュ、および plan_cache_mode をどのように検証するか、そしてトランザクションやコネクションプールを壊さずに問題をどのように修正するかを説明してください。

面接官がテストしていること

プランニング、実行、および結果シリアライズのコストの分離。
ジェネリックプランは具体的なパラメータ値を無視するのに対し、カスタムプランは選択度を活用できることの理解。
本番環境に書き込みの副作用を及ぼすことなく安全に EXPLAIN ANALYZE を使用すること。
統計情報、インデックス、コネクションプール、およびパラメータ化を組み合わせてリグレッションを特定すること。
根拠に基づいて auto、force_generic_plan、または force_custom_plan を選択すること。

最初に明確にすべき質問

クエリはプリペアドステートメント、ORM、プロキシのいずれを経由して実行されており、コネクションは再利用されていますか？
パラメータに偏りがあり、テナントサイズやデータのアクセス頻度（温度）に有意な差がありますか？
リグレッションはプランニング、実行、ロック待ち、IO、シリアライズのどこにありますか？
インデックス、統計ターゲット、SQL、プール、またはセッションレベルの設定を変更することは可能ですか？

30秒での回答

まず EXPLAIN (GENERIC_PLAN) を使用してパラメータ値に依存しないプランを確認し、次に代表値で EXPLAIN ANALYZE EXECUTE を使用してカスタムプランと実際の行数を調査します。ジェネリックプランはプランニングの作業を節約しますが、選択度が偏っている場合には非効率なままになる可能性があります。統計情報とプランキャッシュの挙動を検証し、プランニング、実行、テールレイテンシをベンチマークした上で、制御されたセッション内で plan_cache_mode またはクエリを変更し、すべてのプールコネクションを検証します。

詳細な解説（ステップ・バイ・ステップ）

1. プランニングと実行を分離する

プランナはSQL、統計情報、パラメータからスキャンと結合を選択し、エグゼキュータはページを読み取り、行をフィルタリングして結果を返します。アプリケーションの経過時間（wall time）だけでは、ジェネリックプランが原因であることを証明できません。

2. カスタムプランを説明する

カスタムプランは現在のパラメータに対して生成され、選択度を活用できます。小さなテナントではインデックススキャンが適しており、大きなテナントではシーケンシャルスキャンや異なる結合順序が適している場合があります。その代償はプランニングの繰り返しです。

3. ジェネリックプランを説明する

ジェネリックプランはプレースホルダーを使用し、現在の値を無視します。プランニングオーバーヘッドを償却しますが、分布が大きく偏っている場合、1つのプランがほとんどの値に対して不適切になる可能性があります。EXPLAIN (GENERIC_PLAN) は ANALYZE と組み合わせることはできません。

4. まずジェネリックプランを調査する

sql

EXPLAIN (GENERIC_PLAN)
SELECT sum(amount)
FROM invoices
WHERE tenant_id = $1 AND status = $2;

スキャンタイプ、推定行数、インデックス条件、結合順序、総コストを調査します。パラメータの型が推論できない場合は明示的なキャストを追加し、型の問題がプランニングの問題と誤認されないようにします。

5. 代表的なカスタムプランを調査する

隔離された環境で、異なるサイズのテナントの値を使用して EXPLAIN (ANALYZE, BUFFERS) EXECUTE を実行します。推定行数と実際の行数、共有バッファのヒットと読み取り、プランニング時間、実行時間、ディスクソートを比較します。コストの数値だけで比較しないでください。

6. 統計情報と分布を確認する

autovacuum または手動の ANALYZE が最近の変更をカバーしていることを確認し、カーディナリティ、相関関係、および最頻値（MCV）を調査します。偏りのあるカラムにはより高い統計ターゲットが役立つ場合がありますが、プランニングの精度とプランニングオーバーヘッドの両方を測定してください。

7. 修正の境界を選択する

セッションレベルの plan_cache_mode=force_custom_plan は、カスタムプランによってリグレッションが解消されるかをテストできます。force_generic_plan はプランニングが高コストな安定したクエリに適しています。恒久的な修正は、グローバル設定の変更単独ではなく、インデックス、クエリの分割、明示的な型指定、またはORMでの不要なプリペアドステートメントの回避である場合があります。

8. プールとロールアウトを検証する

プールが存在すると、セッション設定、プリペアドステートメントの有効期間、およびPostgreSQLのバージョン差異が重要になります。カナリアロールアウト中は、ロールバックスイッチを準備した上で、パラメータパーセンタイル、テナントサイズ、プールインスタンス、データベースノードごとに p95/p99、プランニング時間、バッファヒット、エラー率を比較します。

トレードオフと限界

ジェネリックプランはプランニング作業を削減しますが、現在の値からの選択度を失います。カスタムプランは頻繁な短いクエリに対してプランニングCPUを浪費する可能性があります。EXPLAIN ANALYZE はステートメントを実行するため、書き込みステートメントにはロールバックトランザクションまたは読み取り専用レプリカが必要です。プランコストは推定値でありミリ秒ではありません。統計情報はサンプリングされたものであり、プランはデータ、PostgreSQLのバージョン、ANALYZE によって変化する可能性があります。

ロールアウトプランと根拠

クエリテキスト、パラメータ型、プールモード、PostgreSQLバージョン、プランキャッシュの挙動を記録します。
代表的なパラメータ値に対する GENERIC_PLAN および ANALYZE プランのベースラインを取得します。
統計情報の経過時間、推定誤差、インデックスヒット、IO、プランニング時間を確認します。
最初にグローバル構成を変更するのではなく、単一の接続またはカナリアセッションで plan_cache_mode をテストします。
ロールバックスイッチを保持しながら、p95/p99、プランニングCPU、共有読み取り、ロック待ち、エラーに基づいて承認します。

よくあるミスとフォローアップ

ミス 1：シーケンシャルスキャンを確認した後にジェネリックプランを削除する

大きな結果セットに対してはシーケンシャルスキャンが正しい場合があります。まずは代表値に対する実際の行数、IO、テールレイテンシを比較してください。

ミス 2：コストを実際の時間として扱う

コストはプランナによる相対的な推定値です。ANALYZE の実際の時間、バッファ、および本番メトリクスを組み合わせて判断してください。

ミス 3：本番環境で書き込みEXPLAIN ANALYZEを直接実行する

ANALYZE はステートメントを実行します。書き込みはロールバックトランザクションまたは隔離されたレプリカで検証してください。

ミス 4：統計ターゲットの引き上げのみを行う

ターゲットを高くすると分析およびプランニングの作業が増加し、プールやパラメータ型の問題が解決しない場合があります。ベンチマークで効果を証明してください。

ミス 5：プールのセッション境界を無視する

セッションレベルの plan_cache_mode やプリペアドステートメントは、一部の接続にしか影響しない場合があります。リリース前にすべてのプール接続とリサイクルポリシーを網羅してください。