設問と前提条件
orders テーブルには2億行のレコードが存在し、毎秒3,000件の挿入またはステータス更新が発生しています。全注文の約2%が pending ですが、その割合はテナント間で大きく異なります。運用ダッシュボードは次のクエリを毎秒40回実行します。このクエリは、特定のテナントにおける直近30日間の保留中注文の最新50件を要求します。
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = $1
AND status = 'pending'
AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;テーブルにはすでに2つの単一列B-treeインデックス、orders(tenant_id) と orders(created_at) が存在します。アプリケーションのモニタリングによると、PostgreSQLのCPU、メモリ、接続数に余裕があるにもかかわらず、クエリのp95が120ミリ秒から2.8秒に悪化しました。本番規模のレプリカで取得した実行計画では、推定行数が8,000行であるのに対し、ソート前に420,000行が生成され、約120,000の共有バッファ（shared buffers）にアクセスし、最終的に50行を返すためにtop-Nソートが適用されています。
この設問はPostgreSQL 18を対象としています。テーブルサイズ、スループット、実行計画の数値は、推論を検証可能にするための面接上の想定です。目的は、インデックス構築リスク、ストレージ、書き込み増幅を制御しながら、この高頻度な読み取りを改善することです。シャーディング、キャッシュ、ハードウェア拡張は初期検討の対象外とします。
面接官が評価するポイント
第一の評価基準は、SQLを変更する前にワークロードを確認しているかどうかです。優れた回答は、「単一実行で最も遅いクエリ」と「累積コストが最大のクエリ」を区別します。毎秒1,000回呼び出される平均80ミリ秒のクエリは、たまに発生する5秒のクエリよりも先に対処すべき場合があります。pg_stat_statements は呼び出し回数、総実行時間、平均実行時間を提供します。テナント固有のパーセンタイルやp95を把握するには、依然としてアプリケーション監視やトレーシングが必要です。
第二の評価基準は、実行計画を因果関係の連鎖として読み解けるかです。重要な根拠には、8,000行と420,000行のカーディナリティの乖離、スキャンノードから出力される行数、各ノードの loops、バッファのアクティビティ、ソート手法、そして述語がインデックス条件かスキャン後のFilterのどちらに適用されているかが含まれます。Seq Scan が表示されていることやインデックスが使用されたという事実だけでは、実行計画が適切であるかは判断できません。
第三の評価基準は、クエリの構造からキーの順序を導出できるかです。tenant_id は等価述語（equality predicate）です。created_at は範囲述語（range predicate）であると同時に要求されたソート順でもあります。status='pending' は固定された低頻度のビジネス状態です。適切なインデックスは、特定テナントの保留中範囲に直接アクセスし、タイムスタンプ順に読み取り、50行見つかった時点で即座に停止する必要があります。
最後に、面接官は検証とロールアウトの規範を重視します。インデックスはストレージと構築I/Oを消費し、挿入やステータス遷移のオーバーヘッドを増加させます。完全な回答には、本番規模のデータ、コールドキャッシュとウォームキャッシュ、異なるテナントサイズ、同時書き込み、明確なロールバック閾値に基づく実行計画の比較が含まれます。ローカル環境での1回の高速な実行結果だけでは十分な証拠とは言えません。
回答前に確認すべき質問
- どのレイテンシ指標が悪化したか？ テナントごとのp95、全体のp95、平均レイテンシ、合計データベース時間は、それぞれ異なる優先順位を示唆します。悪化が始まった時期と、それがデータ量の増加、パラメータ分布、デプロイ、または統計情報の変更と一致しているかを確認します。
- 保留中注文の割合とテナントの分布はどうなっているか？
pendingが全体の1%〜2%にとどまる場合、部分インデックス（partial index）が非常に有効です。テーブルの半分が保留中の場合、サイズ面の利点は小さくなります。また、平均値だけでは超大規模テナントと小規模テナント間の偏りが見落とされます。 - クエリには常にリテラル
status='pending'が含まれるか？ 部分インデックスは、クエリ条件がインデックス述語を包含しているとプランナが証明できる場合にのみ使用可能です。汎用的なステータスパラメータを使用していると、その証明ができない場合があります。 - どの列と一貫性保証が必要か？ 大きなテキスト、JSON、または10個の結合テーブルを返すと、カバリングインデックス（covering index）が急速に肥大化します。この一覧エンドポイントが実際に必要とする列を確認します。
- テーブルの書き込み頻度はどの程度で、どのようなロールアウト手順が許容されるか？ 毎秒3,000件の書き込みがある場合、インデックス幅と遷移コストを測定する必要があります。本番環境では
CREATE INDEX CONCURRENTLYが必要となる場合があり、これにはより長い構築時間、追加のスキャン、失敗後の無効なインデックスのクリーンアップ手順が伴います。 - 実際の実行計画を本番規模のレプリカで実行できるか？
EXPLAIN ANALYZEはステートメントを実際に実行します。SELECTであっても重大な負荷を生じさせる可能性があり、データ変更ステートメントは副作用を及ぼします。まずはレプリカ、範囲を限定したパラメータ、またはプレーンなEXPLAINを使用します。
30秒の回答フレームワーク
「まず、ロック待ちや外部依存関係を排除しつつ、アプリケーションのp95を pg_stat_statements の呼び出し回数、総データベース時間、低速テナントと関連付けます。次に、本番規模のレプリカで代表的なパラメータを用いて EXPLAIN (ANALYZE, BUFFERS) を実行し、推定行数と実測行数、ループ回数、バッファ、ソートノードを検証します。このケースでは、2つの単一列インデックスを使用してもソート前に420,000行の候補が生成されています。クエリが常に希少な保留状態を対象としているため、(tenant_id, created_at DESC) INCLUDE (id, total_cents) WHERE status='pending' に対する部分カバリングインデックスをテストし、最初の50行を順序通りに読み取れるようにします。ステータスのパラメータ化が必要な場合は、フル構成の (tenant_id, status, created_at DESC) インデックスと比較します。ロールバック閾値を設定した上で同時構築（CONCURRENTLY）を行う前に、テナントサイズ、コールド/ウォームキャッシュ、同時書き込みの条件下でp95、バッファ処理量、インデックスサイズ、書き込みレイテンシを検証します。」
ステップバイステップの詳細解説
ステップ1: 実際のワークロードから優先順位を決定する
ルート、テナント、パラメータ範囲、およびアプリケーションのp95を正規化されたデータベースクエリにマッピングします。pg_stat_statements が有効な場合は、まず累積リソース消費量を確認します。
SELECT queryid, calls, total_exec_time, mean_exec_time, rows, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;total_exec_time は頻繁な呼び出しによって蓄積されたデータベース時間を特定し、mean_exec_time は個別の高コストな実行を浮き彫りにし、calls はその乗数を示します。このビューではp95が確認できず、特定のテナントやパラメータが遅い理由も説明されないため、アプリケーション側のパーセンタイルとパラメータ群の保持が必要です。レイテンシの主な要因がロック待ち、接続キュー、ネットワーク、またはダウンストリームの呼び出しである場合、クエリプランの変更だけではエンドツーエンドのレイテンシは解消されません。
ステップ2: 実際の実行証拠を安全に収集する
まず通常の EXPLAIN でクエリの構造を確認します。その後、本番規模のレプリカまたは制御された環境で以下を実行します。
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = 42
AND status = 'pending'
AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;実際の所要時間が長い最も深いノードから順に上に読み進めます。actual rows × loops はノードの総作業量の一部として扱います。Buffers: shared read はストレージから読み取る必要があったブロックを記録します。shared hit は共有バッファにすでに存在していたことを意味しますが、これらのヒットもCPUとメモリ帯域幅を消費します。ディスクを使用したソートは外部ソートと一時ブロックI/Oを報告するため、入力サイズとメモリ割り当ての調査が必要です。
推定8,000行に対して実際には420,000行という数値は、52.5倍の誤差です。統計情報が古いか、単一列の統計情報では tenant_id と status の相関関係を表現できていない可能性があります。適切なスコープで ANALYZE を実行し、再測定します。安定した相関関係がプランに大きな影響を与えている場合は、それらの列に対して拡張統計（extended statistics）をテストします。拡張統計には収集コストとプランニングコストが伴うため、重要な推定を改善する強く関連した列にのみ作成します。
ステップ3: クエリ構造からインデックスを導出する
プランナは既存の単一列インデックスをBitmapAndで結合する可能性がありますが、ビットマップの結果はB-treeの順序を保持しません。そのため、依然として多数のヒープページを走査してソートを行う可能性があります。あるいは、プランナが一方のインデックスを選択し、もう一方の述語を後からフィルタリングする場合もあります。両方のインデックスが存在しても候補パスが作られるだけであり、WHERE + ORDER BY + LIMIT に最適化されたパスは生成されません。
固定された希少な保留状態に対しては、まず部分インデックスを検討します。
CREATE INDEX CONCURRENTLY orders_pending_tenant_created_idx
ON orders (tenant_id, created_at DESC)
INCLUDE (id, total_cents)
WHERE status = 'pending';
CREATE INDEX CONCURRENTLY orders_tenant_status_created_idx
ON orders (tenant_id, status, created_at DESC)
INCLUDE (id, total_cents);最初のインデックスは保留中の注文のみを格納するため、前述の分布下ではサイズが小さくなります。テナントの等価検索を行った後、created_at が30日間の範囲を制限すると同時に降順の順序を提供し、スキャンは50行に達した時点で終了できます。id と total_cents は INCLUDE のペイロード列です。これらは検索やソートには参加せず、単にインデックスオンリースキャン（Index Only Scan）を可能にするためのものです。
完全な複合インデックスは、パラメータ化されたステータスや、同じクエリパターンを使用する複数のステータスに適しています。B-treeの先頭に等価条件の列を配置することで、時間範囲とソート順を評価する前に対象を絞り込みます。「常に最も選択性の高い列を先頭にする」というのは単純化しすぎです。等価条件、範囲条件、ソート順、および実際のクエリテンプレート間での再利用性を総合してキー順序を決定します。
ステップ4: 部分インデックスとカバリングインデックスの制限事項を把握する
部分インデックスは、クエリ条件に status='pending' が含まれていることをプランナが証明できる場合にのみ使用されます。status = $2 のように記述された汎用プリペアドステートメントでは、そのパラメータがあらゆる可能な値に対して述語を満たすとは限らないため、プランナは部分インデックスを無視する可能性があります。専用の運用クエリでリテラルを保持するか、完全な複合インデックスを採用します。インデックス定義から推測するのではなく、実際のクエリテンプレートで挙動を検証してください。
INCLUDE を指定しても、すべての実行でIndex Only Scanが保証されるわけではありません。PostgreSQLは依然としてMVCCの可視性を確認する必要があります。ヒープページにall-visibleビットが立っていない場合、スキャンはヒープにアクセスします。高頻度で挿入やステータス変更が行われるテーブルでは、ヒープアクセスが発生しやすくなります。プランで依然として多数の Heap Fetches が報告される場合は、ペイロード列を含まないスリムなインデックスと比較検証します。幅の広いインデックスはディスクとキャッシュの使用量を増やし、影響を受けるすべての書き込みのメンテナンスコストを増加させます。
ステップ5: メリットとコストを検証する
同じ代表的なパラメータを使用して、変更前後のプランを比較します。超大規模・中規模・小規模テナント、保留行が大量にあるテナントとほぼないテナント、コールドキャッシュとウォームキャッシュの各条件で測定します。レイテンシ分布、実際の行数、バッファ、ソートの挙動、一時I/O、Heap Fetches、インデックスサイズを記録します。単一の経過時間はキャッシュや同時実行性の影響を受けやすいのに対し、プランの作業量は結果が変化した理由を論理的に説明します。
次に、本番比率で挿入と pending → paid の遷移に対する負荷テストを実施します。注文が完了すると部分インデックスからエントリが削除され、完全インデックスではステータスキーが更新されます。どちらも書き込み処理の負荷となります。合格基準としては、読み取りp95が目標値内に収まりp99に重大な悪化がないこと、候補行と共有ブロック作業量の大幅な削減、および書き込みp95、WAL量、ストレージ、レプリケーション遅延が許容範囲内に収まることなどを設定します。
本番ロールアウトの前に、ディスクの空き容量と同時構築のモニタリングを確認します。CREATE INDEX CONCURRENTLY は挿入、更新、削除の継続を可能にしますが、所要時間が長くなり、失敗時に無効なインデックスが残る可能性があります。構築完了後は、本番のクエリテンプレートが実際に新しいパスを選択していることを確認し、完全なピークサイクルを監視します。書き込みやレプリケーションのレイテンシが閾値を超えた場合は、運用手順に従って新しいクエリパスを取り消し、新しいインデックスを削除します。移行後のインデックスが安定期間を過ぎ、他のワークロードが依存していないことが確認できるまで、古いインデックスは保持します。
質の高い模範解答
「まず、このSQLが優先的に対処すべきものであるかを判断します。アプリケーション監視からp95と遅延テナントを把握し、pg_stat_statements から呼び出し回数、総実行時間、平均実行時間を取得します。毎秒40回実行され、累積データベース時間の上位を占めている場合、実際のクエリテンプレートと代表的なテナントを抽出し、本番規模のレプリカで EXPLAIN (ANALYZE, BUFFERS) を実行します。
このプランの根本的な問題はスキャン範囲の広さです。プランナの推定は8,000行ですが、実際には420,000行がtop-Nソートに到達し、クエリは約120,000のバッファにアクセスしています。単一列インデックスはフィルタを結合できるものの、tenant_id + pending + created_at DESC に最適化されたソート済み範囲を直接提供することはできません。統計情報を更新して再測定します。テナントとステータスの相関関係が推定誤差の原因であり続ける場合は、拡張統計を検証します。
運用クエリが常に希少な保留状態を要求することから、(tenant_id, created_at DESC) INCLUDE (id, total_cents) WHERE status='pending' に対する部分インデックスを検証します。これによりインデックス対象のデータ量が制限され、特定テナントのソート済み時間範囲に直接アクセスして50行で処理を停止できます。アプリケーションがステータスをパラメータ化し複数の値をクエリする場合は、完全な (tenant_id, status, created_at DESC) インデックスと比較します。INCLUDE はIndex Only Scanを可能にするものに過ぎず、ホットなページではヒープアクセスが必要になる場合があるため、Heap Fetches の数値を確認します。
検証では、大規模・中規模・小規模テナント、コールドおよびウォームキャッシュ、同時書き込み環境を対象とします。p95とp99、候補行数、バッファ、一時I/O、インデックスサイズ、WAL、書き込みレイテンシ、レプリケーション遅延を比較します。本番環境ではCONCURRENTLYでインデックスを構築し、実際のテンプレートが新しいプランを採用していることを確認した上で、ピークサイクル全体を監視します。読み取りの改善効果が薄い場合や書き込みパスが許容値を超える場合は、安易にハードウェア増強で根本原因を曖昧にせず、実行パスを元に戻して新しいインデックスを削除します。」
よくある間違い
- SQLが遅いと見なした瞬間にインデックスを追加する → 頻度、パラメータ、待機タイプが不明なままでは、優先度の低いクエリを最適化してしまうリスクがあります → まずアプリケーションのパーセンタイル、
pg_stat_statements、実際のパラメータを照合します。 - すべての
Seq Scanを欠陥とみなす → 小さなテーブルや大部分の行を返すクエリでは、シーケンシャルスキャンのほうが低コストな場合があります → 実際の行数、バッファ、総コストを他の選択肢と比較します。 - プランにIndex Scanと表示されていることだけを確認する → インデックススキャンであっても数十万件のエントリを読み取り、ヒープへ頻繁にアクセスしている場合があります →
actual rows × loops、Filter、Buffers、およびHeap Fetchesを検証します。 - 推定行数と実測行数の乖離を無視する → 不正確なカーディナリティは、不適切な結合、スキャン、ソートを引き起こします → 統計情報を更新し、安定した相関関係を持つ列に対して拡張統計をテストします。
- 複数の単一列インデックスが1つの複合インデックスと同等であると誤認する → ビットマップ結合は通常、要求されたソート順を失い、多数のヒープページにアクセスする可能性があります → 等価条件、範囲条件、ソート順、LIMITから最適なキーを導出します。
- 返されるすべての列を
INCLUDEに含める → インデックスの肥大化はキャッシュ効率を低下させ、書き込みを増幅させます → 重要度の高いクエリで必要とされる最小限の列のみをカバーします。 - クエリテンプレートを検証せずに部分インデックスを作成する → プランニング時に、パラメータ化された述語がインデックス述語を満たすと判断されない場合があります → 本番で使用されるプリペアドステートメントと同じ形式でEXPLAINを実行します。
- プライマリデータベース上で任意のSQLに対して
EXPLAIN ANALYZEを実行する → 実際に文が実行されるため、重い読み取りは負荷を生み、書き込みは副作用を発生させます → まずは通常のEXPLAINを使用し、実際の証拠はレプリカまたは制御されたトランザクション内で取得します。 - 実行時間が2.8秒から短縮された1回限りの測定値を根拠にする → キャッシュ、パラメータ、同時実行性によって偶発的に良好な結果が出ている可能性があります → レイテンシ分布、プランの処理量、ピークサイクル全体を通じて比較します。
フォローアップ質問と回答
フォローアップ1: シーケンシャルスキャンがインデックススキャンより高速になる場合があるのはなぜですか？
クエリがテーブルの大部分の行を読み取る場合、シーケンシャルアクセスを使用すると、インデックスのトラバースや分散したヒープページの取得に伴う大量のランダムアクセスを回避できます。また、小さなテーブルでは全体がわずか数ページに収まるため、直接スキャンする方が低コストになります。ノード名だけでプランを判断せず、実際のデータでバッファと総所要時間を比較します。
フォローアップ2: (tenant_id) と (created_at) の個別インデックスではなぜ不十分なのですか？
プランナは一方のインデックスを選択して後からフィルタリングするか、BitmapAndで両方を結合します。ビットマップは対象タプルの物理位置を収集するだけであり、created_at のB-tree順序を保持しないため、多くのヒープページを読み取った後にソートを実行することになります。複合インデックスは、テナントの等価検索、時間範囲、およびソート順を単一の順序付きアクセスパスに統合するため、LIMIT 50 による早期終了が可能になります。
フォローアップ3: PostgreSQLが部分インデックスを使用しない場合があるのはなぜですか？
プランナはプラン作成時に、クエリ条件がインデックス述語を包含していると判断する必要があります。status='pending' は直接一致しますが、status=$2 ではあらゆるパラメータで一致するとは保証されません。また、異なる構文の式、データ分布の変化、またはヒープアクセスが高コストに見積もられるコスト計算によって、別のパスが選択されることもあります。実際にプリペアド化されたテンプレートでEXPLAINを実行し、ステータスを汎用的に扱う必要がある場合は完全な複合インデックスを採用します。
フォローアップ4: ANALYZEを実行しても推定値が50倍ずれている場合はどう対処しますか？
サンプリングカバレッジ、列ごとの統計ターゲット（statistics target）、およびデータが急激に変化していないかを確認します。tenant_id と status に強い相関がある場合、単一列統計では述語が独立しているものとして概算されます。その列グループに対して依存関係（dependency）またはMCV（最頻値）の拡張統計をテストします。拡張統計は推定を改善しますが、不足しているアクセスパスを作成するわけではないため、インデックスとクエリ構造は個別に検証します。
フォローアップ5: 読み取りは改善したものの、書き込みのp95が悪化しました。どう判断しますか？
SLOとワークロード全体に立ち返ります。読み取りで節約されたデータベース時間、書き込み性能低下の影響を受けるユーザー数、およびWAL、ストレージ、レプリケーション遅延の変化を定量化します。特定の希少な固定ステータスのみを高速化すればよい場合は、スリムな部分インデックスの方が完全なカバリングインデックスより優れている可能性があります。ペイロード列が肥大化の原因となっている場合は、INCLUDE を削除してある程度のヒープアクセスを許容します。書き込みの許容上限を超える場合は、インデックス計画を取り下げ、クエリのスコープ、ページネーションの保証、またはデータモデルの見直しを通じて、より負荷の小さいアクセスパスを検討します。