【テクニカル・上級編】インデックススキャン – PostgreSQL

インデックススキャンは「ただの検索」ではない —— PostgreSQLの深淵を覗く

データベースエンジニアとして長く現場に立っていると、ふと「インデックススキャン」という当たり前の操作に対して、どれだけ解像度を持って向き合えているかを自問することがあります。

多くの開発者は、`EXPLAIN` を叩いて `Index Scan` が出れば「よし、最適化された」と安心します。しかし、我々のようなプロフェッショナルにとって、それは物語の始まりに過ぎません。PostgreSQLがB-treeの階層をどう辿り、ヒープ（テーブルの実データ）とどう対話し、その結果としてどれだけのI/Oコストを支払っているのか。その「舞台裏」を理解することこそが、パフォーマンストラブルシューティングの要諦です。

B-treeの階層を駆け抜ける：その裏側にあるコスト

PostgreSQLの標準であるB-treeインデックスは、単なるツリー構造ではありません。ノードには「ページ」という単位があり、私たちはそのページをメモリ（Shared Buffers）に載せるために血眼になります。

インデックススキャンが発生したとき、エンジンはルートページからリーフページへと「下降」します。ここで重要になるのが、「ピン留め（Pinning）」の概念です。インデックスのページを読み込む際、PostgreSQLはバッファマネージャを介してそのページをピン留めし、アクセスが終わるまでロックを保持します。

もし、インデックスの階層が深すぎたり、インデックス自体が肥大化（Bloat）していたりすると、この「物理的な読み込み」の回数が指数関数的に跳ね上がります。特にインデックスがメモリに乗らない大規模なワークロードでは、インデックススキャンのコストは「CPUの比較回数」ではなく「いかにディスクI/Oを抑えるか」という、古くて新しい戦いに集約されるのです。

Index Scan vs Index Only Scan：理想と現実

最近のPostgreSQLでは、`Index Only Scan` が当たり前のように実行されます。必要なデータがすべてインデックス内に存在すれば、ヒープへのアクセス（Heap Fetch）を省略できる。この恩恵は計り知れません。

しかし、ここで罠が待っています。「Visibility Map」です。

`Index Only Scan` が機能するためには、そのデータが現在のトランザクションから「可視」である必要があります。PostgreSQLのMVCC（多版同時実行制御）の性質上、どれだけインデックスにデータがあっても、ヒープ側のVisibility Mapが更新されていなければ、結局はヒープまでデータを見に行かなければなりません。

教訓: インデックスを貼ったのに `Index Only Scan` にならない場合、それは単にクエリの問題ではなく、`VACUUM` の頻度や `autovacuum` の設定、あるいはヒープの更新頻度がVisibility Mapの更新を阻害している可能性があります。ここをチューニングできるかどうかが、シニアエンジニアの腕の見せ所です。

パフォーマンストラブルシューティング：統計情報の裏を読め

インデックススキャンが遅いと感じたとき、真っ先に `pg_stat_user_indexes` を見る人は多いでしょう。しかし、そこで見るべきは `idx_scan`（スキャン回数）だけではありません。

私が特に注目するのは、`idx_tup_fetch`（インデックス経由で読み取ったタプルの数）と、実際の実行計画上の `Rows Removed by Filter` の関係です。

1. インデックスの「不適合」: インデックスは引いているのに、実際にはインデックスの先で大量のタプルを捨てているケース。これはインデックスの設計がクエリの条件（WHERE句）と噛み合っていない証拠です。
2. ランダムアクセスの恐怖: `Index Scan` は、原則として「ランダムI/O」を多用します。もしインデックススキャンがメインのクエリで、かつデータが物理的にバラバラに配置されている場合、シーケンシャルスキャンよりも遥かに遅くなることがあります。

最後に：エンジニアとしての矜持

インデックススキャンを最適化するということは、単に処理時間を削ることではありません。それは、データベースという巨大な機械が、いかに無駄なく、いかに優雅にメモリとディスクの間でデータをやり取りするかを設計することです。

「とりあえずインデックスを貼る」のは簡単です。しかし、そのインデックスが、更新処理（INSERT/UPDATE/DELETE）のたびにどれだけのオーバーヘッドを生み、共有バッファをどれだけ汚染するのかを想像できること。それこそが、データベースエンジニアが持つべき「視点」だと私は信じています。

皆さんのシステムでも、一度 `EXPLAIN (ANALYZE, BUFFERS)` を取ってみてください。そこには、まだあなたが知らないPostgreSQLの「叫び」が記されているはずです。

さて、次はどのインデックスを深掘りしましょうか？データベースの旅に終わりはありません。