インデックス — 作ったのに使われない理由

一言でいうと

インデックスは、値で並べ替えられた別のデータ構造であり、オプティマイザーがそれを使わないのはたいてい正しい判断で、正しくない少数のケースには、原因が決まっています。

なぜ必要なのか

条件に合う行を探すには、原則として表をすべて読まなければなりません。100万行から1行を探すために100万行を読むのは無駄です。本の索引のように、値と位置を並べて保存した補助構造があれば、数回の比較で位置を割り出せます。これがB-treeインデックスです。

どう動くのか

B-treeは、値で並べ替えられた平衡木です。並べ替えられているので、等号での探索だけでなく、範囲探索や並べ替えの要求も処理できます。逆に言えば、カラムに何かをかぶせた瞬間、その並び順は役に立たなくなります。

-- 안 탄다: 컬럼에 함수가 걸렸다
SELECT * FROM orders WHERE date(created_at) = '2026-07-26';

-- 탄다: 범위 조건으로 바꿔 컬럼을 그대로 둔다
SELECT * FROM orders
WHERE created_at >= '2026-07-26' AND created_at < '2026-07-27';

同じ種類のものとして、型が合わずにカラム側が変換される暗黙のキャスト、そして開始点を固定できないLIKE '%kim%'があります。実行計画で、カラム名にキャストの表記が付いていたり、Filterの行に関数呼び出しが見えたりしたら、この問題です。

複合インデックスには、先頭カラムのルールがあります。(a, b, c)のインデックスは、aで並べ替え、aが同じもの同士をbで、bも同じならcで並べ替えた構造です。電話帳を姓で並べ替え、同じ姓の中で名前順に並べたものと同じで、姓を知らないまま名前だけでは探せません。カラム順序の原則は、等号条件のカラムを前に、範囲条件のカラムを後ろに置くことです。範囲条件がかかったカラムの後ろのカラムは、探索に使えず、フィルターとしてだけ動作するからです。

ここで、最も重要な前提を押さえておく必要があります。オプティマイザーがインデックスを使わないのは、たいてい正しいのです。インデックススキャンは、インデックスページを読んだ後、一致した行ごとにヒープページをランダムに訪問しなければなりません。返す行が全体の相当な割合なら、結局、表全体をランダムな順序で読む形になり、シーケンシャルスキャンより遅くなります。実際にenable_seqscan = offで強制してみると、231msだったクエリが1094msと、4倍遅くなることがよくあります。

境界を決める変数は3つあります。ランダムアクセスの相対コストの設定(random_page_cost)、インデックスの順序とヒープの順序の一致度(相関)、そして返すカラムがインデックスにすべて入っているかどうかです。最後のものを満たすために、あえてカラムを載せたのがカバリングインデックスで、このときヒープを訪問しないIndex Only Scanが可能になります。

現場での姿

部分インデックスは、複数の問題を一度に解く道具です。全注文のうち数パーセントにすぎないpending状態だけを照会するワークロードなら、WHERE status = 'pending'を付けたインデックスは、42MBから312KBに減ります。サイズが減るだけでなく、条件に合わない行のINSERTとUPDATEがこのインデックスに触れないので、書き込みコストも減ります。

そして、インデックスの反対側のコストを忘れてはいけません。すべてのINSERTとDELETEは、その表のすべてのインデックスを更新します。インデックスが増えると計画の候補も増え、計画の策定時間まで増えます。使っていないインデックスを定期的に取り除く必要がある理由です。

実行計画の読み方

インデックスを作った後、「速くなっただろう」で済ませてはいけません。実際にそのインデックスを使っているか、そしてどれだけかかったかは、実行計画が教えてくれます。初めて見ると圧倒されますが、実際に読むべきものは、いくつかだけです。

推定と実測の差。計画には、オプティマイザーが予想した行数と、実際に出た行数が一緒に出ます。この2つが大きくずれていたら、それが問題の出発点です。オプティマイザーは統計を見て判断するので、統計が古いか、条件が統計で表現できない形だと、誤った計画を選びます。10倍以上ずれたノードを先に探すのが、計画を読む最初の動作です。

時間がどこで使われたか。計画は木であり、各ノードの時間には子の時間が含まれています。そのため、全体で最も遅いノードではなく、自分の時間が大きいノードを探す必要があります。そして、繰り返し実行されるノードは、1回の時間に実行回数を掛けて初めて、実際のコストが出ます。

どこで絞り込まれたか。インデックスで探した後、条件に合わず捨てられた行が多いなら、その条件をインデックスに入れてみる余地があります。逆に、捨てられた行がほとんどなければ、インデックスがすでによく合っているということです。

ディスクから読んだか。キャッシュにあるものを読んだ場合と、ディスクから読んだ場合では、速度が大きく違います。最初の1回は遅く、2回目から速いクエリを見て「インデックスのおかげ」と勘違いしやすいので、比較するときは、同じ条件で何回も測る必要があります。

最後に1つ。計画は、その瞬間のデータと統計に対する答えです。開発環境の小さな表ではシーケンシャルスキャンが勝ち、本番の大きな表ではインデックスが勝つのが正常なので、計画を見るときは、本番と同じくらいの大きさのデータで見る必要があります。

次のラボですること

実際の表にさまざまな種類のインデックスを作ってみて、実行計画がどう変わるかをファイルに残します。特に、強制的にインデックスを使わせたときに、かえって遅くなるケースを自分で測定します。