プロンプトとコンテキスト
同じクエリがテスト環境では高速ですが、本番環境では低速です。EXPLAIN (ANALYZE, BUFFERS) と、新たに追加されたメモリ、ディスク、I/O の詳細情報を利用して、PostgreSQL 18 における診断計画を策定し、不正な実行プラン、ソートのスピル（ディスク溢れ）、キャッシュミス、ストレージレイテンシを切り分ける方法を設計してください。
これはデータエンジニアリング、バックエンド、およびデータベース運用の職種に適した内容です。PostgreSQL 18 では、より多くのノードに対してメモリとディスクの詳細を提供するよう EXPLAIN が拡張され、実行バッファへのアクセス詳細が表示されます。公式の EXPLAIN ガイドでは、推定値、実際の行数、ループ数、BUFFERS、および ANALYZE が定義されています。本記事は公開ドキュメントに基づいており、特定企業の面接設問集に関する主張ではありません。
面接官が評価するポイント
面接官は、単にインデックスの追加を自動的に提案するのではなく、プランから得られる証拠の連鎖を求めています。優れた回答とは、推定誤差とリソース枯渇を区別し、shared hit/read/dirtied/written、ソートまたはハッシュのメモリ、I/O タイミングについて説明し、さらに本番環境でのサンプリング、権限管理、ロールバック手順を含んでいるものです。
確認のための質問
- クエリをリードレプリカや匿名化データ上で再現・再実行できますか？
- 性能低下（リグレッション）は平均レイテンシ、テールレイテンシ、または特定のパラメータに応じたプラン選択のいずれで発生していますか？
- 本番環境において
EXPLAIN ANALYZEに許容される実行オーバーヘッドはどの程度ですか？ - 相関分析のために、クエリフィンガープリント、統計情報の更新履歴、ディスク/キャッシュのメトリクスは利用可能ですか？
30秒での回答
「まず本番環境のパラメータとクエリフィンガープリントを保持します。レプリカ上で EXPLAIN (ANALYZE, BUFFERS, VERBOSE) を実行し、推定行数と実際の行数、ループ数、バッファのヒット/リード数、および PostgreSQL 18 のメモリ/ディスク項目を比較します。大きな推定誤差は統計情報に問題があることを示唆し、ソートやハッシュのスピルは作業メモリ、並行性、またはデータの偏り（スキュー）を示唆します。リード数が多い場合はキャッシュとストレージの証拠が必要です。インデックス、統計情報、パラメータの変更はレプリカで検証した上で、テールレイテンシを監視しながらカナリアリリースを行います。」
段階的な解決策
まずサンプルを確定させます。SQL、バインドパラメータ、計画時間、実行時間、行数、データベースのバージョンを記録します。パラメータが異なると異なるプランが選択される可能性があります。EXPLAIN は実行せずに見積もりを行い、ANALYZE は文を実際に実行します。書き込み処理の場合は、診断によってデータが変更されないよう、リードレプリカ、該当する場合は読み取り専用トランザクション、または安全なロールバックを使用します。
推定行数と実測行数の桁数を比較してプランを読み解き、次に loops を考慮に入れます。BUFFERS は、共有バッファのヒット（shared hit）、読み取り（read）、変更（dirtied）、書き込み（written）の各ページ数を分離して表示します。ヒット数が多いからといってクエリが高速であるとは限りません。読み取り（read）については、データ量とストレージレイテンシのコンテキストが必要です。PostgreSQL 18 ではメモリとディスクの使用量の詳細がより多くのノードに追加され、ソート、ウィンドウ集約、CTE、Materialize ノードのワーキングセットの特定に役立ちます。
ソートやハッシュでディスクが使用されている場合、work_mem が小さすぎるのか、並行性が高いのか、データが偏っているのかを判断します。各オペレータや並行セッションごとにメモリが消費されるため、グローバルで一律に引き上げることは避けてください。バッファ読み取りと I/O レイテンシが高い場合は、キャッシュ容量、テーブルの肥大化（bloat）、インデックスの選択性、ストレージを調査します。レイテンシが低い読み取りは単にコールドキャッシュを反映しているだけの場合もあります。安定した再生実行とサンプルの反復採取で確認します。
推定誤差は多くの場合、統計情報の陳腐化、相関列に対する拡張統計の不足、パラメータ感度、またはデータ分布の変化を示しています。結合順序を強制する前に、ANALYZE、拡張統計、またはクエリの書き換えを試してください。インデックスの変更は、書き込み増幅（write amplification）、保守コスト、カバレッジを評価する必要があります。プランが改善されても、全体のスループットが向上するとは限りません。
本番環境の診断には、サンプリングと権限の境界設定が必要です。EXPLAIN ANALYZE の頻度と並行度を制限し、リテラルと結果を匿名化し、pg_stat_statements でフィンガープリントを集約します。プラン、メモリ、バッファ、I/O メトリクスを p95/p99 レイテンシと相関させます。変更はカナリアリリースで行い、ロック待機、メモリ逼迫、またはテールレイテンシが悪化した場合は即座にロールバックします。
模範回答
固定されたパラメータをリードレプリカ上で再現し、推定行数/実測行数、ループ数、バッファの hit/read/dirtied/written、および PostgreSQL 18 のノード別メモリ/ディスク項目を収集します。推定の大きな乖離は統計情報の対応につながり、ソート/ハッシュのスピルは work_mem、並行性、データスキューの分析につながり、高い読み取り数はキャッシュとストレージの証拠確認につながります。レプリカ上でインデックス、統計情報、またはパラメータの変更を検証した後、カナリアリリースを実施し、p99、ロック待機、メモリ、I/O を監視します。
よくある間違い
- 間違い → プライマリノードで
EXPLAIN ANALYZEを直接実行する；失敗する理由 → 実際のステートメントが実行され、負荷や副作用が生じる；修正方法 → レプリカ、読み取り専用トランザクション、または安全なロールバックを使用する。 - 間違い → バッファ読み取りを確認した後、すぐにインデックスを追加する；失敗する理由 → 読み取りの原因がコールドキャッシュ、統計情報の誤差、またはストレージレイテンシである可能性がある；修正方法 → 反復サンプルと I/O メトリクスを相関させる。
- 間違い → グローバルの
work_memを極端に高く設定する；失敗する理由 → すべてのオペレータおよび並行セッションがそれを消費する；修正方法 → 並行性の予算を計算し、セッション/クエリ単位の設定でカナリア検証する。 - 間違い → 実行時間のみを比較する；失敗する理由 → テールレイテンシ、書き込み増幅、プランの安定性が隠れてしまう；修正方法 → p99、リソースメトリクス、リグレッションサンプルを総合的に評価する。
フォローアップ質問
なぜ shared hit のカウントが高くてもクエリが遅いことがあるのですか？
ヒットとはページが共有バッファから取得されたことを意味するだけであり、CPU 処理、ソート、ロック待機、オペレータ処理のコストが低くなるわけではありません。ループ数、ノードのメモリ/ディスク、実行時間の分布、待機イベントを組み合わせてボトルネックを特定します。
work_mem を物理メモリの半分に設定してはいけない理由は何ですか？
1 つのクエリに複数のオペレータが存在する場合があり、また多数の並行セッションが存在し、それぞれが work_mem を消費します。単純な割合で設定するとピーク時の予算を超過し、OOM（メモリ不足）を引き起こす可能性があります。並行性、オペレータ数、プールサイズ、ノード予算から算出し、監視を通じて検証してください。
SQL を書き換えるのではなく、統計情報を更新すべきなのはどのような場合ですか？
データの変更や相関列の統計情報の欠如により、推定値が実際の分布から大きく乖離している場合は、まず更新を行うか拡張統計を追加します。推定値が信頼できる状態になり、それでもオペレータの選択に失敗する場合にのみ、書き換えやインデックス追加を行います。