問題とコンテキスト
マルチテナントのイベントテーブルに、(tenant_id, created_at)に対するB-treeインデックスがあります。新しいクエリはcreated_atの範囲のみを指定しており、以前のバージョンではシーケンシャルスキャンが選択されることがよくありました。PostgreSQL 18のスキップスキャンを使用して、オプティマイザがサフィックス列をどのように利用できるか、その効果をどのように測定するか、そしてなぜこれが専用に構築されたインデックスの万能な代替手段にはならないのかを説明してください。
面接官が評価するポイント
重要な違いは、B-treeの最左プレフィックスルール（leftmost-prefix rule）とスキップスキャン戦略です。PostgreSQL 18は、先頭列の個別値を列挙し、それぞれに対してサフィックスの検索を実行できます。コストは、プレフィックスのカーディナリティ、サフィックスの選択性、テーブルとインデックスの相関関係、および統計情報に依存します。候補者はEXPLAIN (ANALYZE, BUFFERS)を用いて結果を証明できる必要があります。
最初に確認すべき明確化のための質問
データの分布
テナント数、テナントごとの行数、時間範囲の幅、およびデータが時間順にクラスタ化されているかどうかを確認します。個別プレフィックス値が多い場合、検索の繰り返しによりシーケンシャルスキャンよりもコストが高くなる可能性があります。
ワークロードとバージョン
サーバーがPostgreSQL 18であること、クエリの実行頻度、インデックス追加の権限、および同時書き込みの挙動を確認します。スキップスキャンはプランの選択肢の一つであり、SQLの保証ではありません。
測定のベースライン
既存のEXPLAINの出力、バッファヒット率、実行時間、およびコールドキャッシュのベースラインを確認します。推定コストやウォームキャッシュでの1回の実行結果ではなく、実際の実行プランを比較します。
30秒の回答フレームワーク
「(tenant_id, created_at)に対する従来のルールでは、まずtenantidの述語が必要でした。PostgreSQL 18では、コストが有利な場合、個別のtenantidごとに試行し、created_atの範囲を使用して無関係なインデックス範囲をスキップできます。私なら統計情報を更新し、EXPLAIN ANALYZE BUFFERSを使用してスキップスキャン、シーケンシャルスキャン、および専用の(created_at)インデックスを比較します。プレフィックスのカーディナリティが高い場合、範囲が広い場合、または相関が低い場合は、スキップスキャンの方が遅くなる可能性があります。」
詳細な解決手順
ステップ1: インデックス列を述語にマッピングする
インデックスの順序、等値述語、範囲述語、およびソート要件をリストアップします。スキップスキャンは、先行列に有用な制約がないものの後続列に選択性の高い述語がある場合に複数列B-treeに役立ちます。物理的なキーの並び順を変更するわけではありません。
ステップ2: プレフィックス列挙コストを説明する
オプティマイザは、先行列の各個別値を暗黙的な検索エントリとして扱い、サフィックスの範囲を検索できます。プローブの回数は、プレフィックスのカーディナリティと推定誤差の影響を受けます。カーディナリティが高いほど、ランダムアクセスと位置特定の繰り返しが増加します。
ステップ3: 統計情報を更新してプランを検査する
ANALYZEを実行し、個別値のカウント、ヒストグラム、相関統計に現在のデータが反映されるようにします。実際の行数、共有バッファヒット、読み取り、プランノード、および関連するオプティマイザ設定を含めてEXPLAIN (ANALYZE, BUFFERS, SETTINGS)を取得します。
ステップ4: 比較可能なベースラインを構築する
同一のデータスナップショット上で、既存のインデックスによるスキップスキャン、シーケンシャルスキャン、および新しいサフィックス列インデックスを比較します。1つのサンプルだけに依存せず、コールドキャッシュとウォームキャッシュ、狭い範囲と広い範囲、テナントの偏りなどをテストします。
ステップ5: カバリングとヒープのコストを考慮する
クエリがインデックスに含まれない多くの列をプロジェクション（射影）する場合、スキップスキャン後のヒープアクセスが支配的になる可能性があります。インデックスがプロジェクションをカバーしているか、可視性マップ（Visibility Map）によってIndex-Only Scanが可能か、そしてランダムなヒープアクセスがフィルタリングによる利益を打ち消していないかを確認します。
ステップ6: プランの安定性を管理する
データの増加に伴いプレフィックスのカーディナリティや選択性が変化するため、オプティマイザはスキップスキャン、シーケンシャルスキャン、および別のインデックスの間で選択を切り替える可能性があります。プランのフィンガープリントとp95レイテンシを記録し、必要に応じて統計ターゲットを調整するか、主要なアクセスパスに一致するインデックスを追加します。
ステップ7: アップグレードとロールバックを計画する
PostgreSQL 18にアップグレードした後、統計情報を再収集し、代表的なトラフィックをリプレイします。バッファ読み取り、CPU、ロック待機、テールレイテンシを監視します。プランのリグレッションが発生した場合は、スキップスキャンプランを維持するかどうかを判断する前に、安定したインデックスまたはクエリ形状に戻します。
質の高い模範解答
(tenant_id, created_at)に対して、私はスキップスキャンをコストベースのプランとして扱います。tenantidの値を列挙し、それぞれについてcreatedatの範囲を検索します。私ならANALYZEを実行し、コールドキャッシュとウォームキャッシュ、および異なる範囲幅の条件下で、EXPLAIN ANALYZE BUFFERSを使用してシーケンシャルスキャンおよび(created_at)インデックスと比較します。プレフィックスのカーディナリティ、ヒープアクセス、または範囲幅によってプローブの繰り返しが高コストになる場合は、サフィックスインデックスまたはカバリングインデックスを追加し、アップグレード後のプランの安定性を監視します。
よくある間違い
- 間違い: 任意の複数列インデックスがそのサフィックスを効率的にフィルタリングすると仮定すること。 → 理由: 最左プレフィックスルールは依然として重要であり、スキップスキャンはコストベースです。 → 修正: 実際のプランと分布を検証します。
- 間違い: スキップスキャンを新しいインデックスタイプとして扱うこと。 → 理由: これはオプティマイザのアクセス戦略です。 → 修正: 物理インデックスは変更されていないことを明記します。
- 間違い: 推定コストのみを比較すること。 → 理由: 統計情報が誤っている可能性があります。 → 修正: キャッシュ状態全体でANALYZE BUFFERSを測定します。
- 間違い: ヒープとカバリングのコストを無視すること。 → 理由: 高速なフィルタリングを行っても、依然として多くの行フェッチが必要になる場合があります。 → 修正: Index-Onlyの適用可否とヒープアクセスを評価します。
フォローアップの質問と回答
フォローアップ1: テナントが2つしかない場合、常にスキップスキャンが選択されますか？
いいえ。サフィックスの選択性、ページの相関関係、キャッシュの状態、および推定コストが依然として重要です。シーケンシャルスキャンのほうが安価な場合があります。
フォローアップ2: スキップスキャンは一致しないすべてのリーフページをジャンプしてスキップしますか？
個別のプレフィックス値を使用して複数の検索を実行し、無関係な範囲を回避しますが、各検索には依然として位置特定とヒープフェッチのコストが発生する可能性があります。ジャンプは無料ではありません。
フォローアップ3: ANALYZEの後でもプランが不適切になる場合があるのはなぜですか？
複数列の相関、偏り、パラメータ値、およびキャッシュ状態は、基本的な統計モデルの範囲を超えています。拡張統計、代表的なパラメータのリプレイ、および長期的なp95モニタリングを使用してください。
フォローアップ4: 直接の(created_at)インデックスの方が優れているのはどのような場合ですか？
サフィックスのみのクエリが安定した主要パスである場合、プレフィックスのカーディナリティが高い場合、範囲が広い場合、またはヒープフェッチが支配的である場合、専用インデックスを使用することでプレフィックスプローブの繰り返しを回避できます。これを書き込み増幅、ストレージ、および元のインデックスを使用する他のクエリと比較して検討してください。