PostgreSQL 基礎講座 #4 インデックス基礎 — いつ効いて、いつ効かないのか

インフラ PostgreSQL データベース
読了 4分

クエリが遅いときは、まずインデックスを検討します。インデックスとは何かという一般原理は別の記事で扱ったので、今回は PostgreSQL での実務、つまり作り方よりも、作ったのになぜ使われないのかを中心に固めます。判別の道具である EXPLAIN は次回に本格的に扱い、ここでは原理とルールを立てます。

B-tree: 既定のインデックスが変えるもの #

CREATE INDEX の既定タイプは B-tree です。カラムの値をソート済みのツリーとして保持し、全体を読む順次走査(Seq Scan)の代わりに、ツリーを下っていく検索を可能にします。数百万行で数秒かかっていた検索がミリ秒に下がる、あの劇的な改善の正体がこれです。

インデックス作成 
CREATE INDEX idx_orders_user_id ON orders (user_id);

ソートを保つ構造なので、= だけでなく範囲(<、>、BETWEEN)、ソート(ORDER BY)、そして LIKE 'abc%'(前方一致)まで扱えます。逆に LIKE '%abc'(後方一致)はソート構造では解けないので、基本の B-tree インデックスは使えません。PK と UNIQUE 制約にはインデックスが自動で作られますが、外部キーには自動で作られないというのがよくある誤解です。orders.user_id のような FK カラムは結合と親削除の確認にずっと使われるので、インデックスを自分で作っておくのが基本です。

効かない理由 1: 複合インデックスの先頭カラム #

複数カラムの複合インデックスは左から使われます。電話帳が(姓、名)の順でソートされていたら、姓なしで名だけでは探せないのと同じ構造です。

複合インデックスと先頭カラム 
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);

-- 効きます: user_id から始まる
WHERE user_id = 42 AND created_at >= '2026-12-01'
WHERE user_id = 42

-- 効きません: 先頭カラム(user_id)なしで created_at だけ
WHERE created_at >= '2026-12-01'

複合インデックスのカラム順は「等号で絞るカラムを前に、範囲で絞るカラムを後ろに」が基本ルールです。応用戦略(部分・カバリング・GIN など)は実践編で扱います。

効かない理由 2: カラムを加工すると効きません #

インデックスは保存された値そのままでソートされています。カラムに関数をかぶせると、そのソートが使えません。

カラム加工の落とし穴 
-- 効きません: カラムに関数を適用
WHERE lower(email) = 'tanaka@example.com';
WHERE created_at::date = '2026-12-25';

-- 効きます: カラムはそのままにして条件側を変える
WHERE email = 'tanaka@example.com';
WHERE created_at >= '2026-12-25' AND created_at < '2026-12-26';

日付の比較を範囲に書き換える 2 つ目の例は、実務で特によく登場します。どうしても加工した値で探す必要があるなら、その式自体にインデックスを作る式インデックス(CREATE INDEX ... ON users (lower(email)))が解決策です。同じ系統の罠として、型の不一致(text カラムを数値と比較するなど)も、暗黙の型変換がカラム側にかかってインデックスを無効にします。

効かない理由 3: 効かないほうが速い場合 #

インデックスがあってもプランナがあえて使わない場合があります。代表が選択度の低い条件です。status = 'done' が全体の 90% なら、インデックスで 90% の行を 1 つずつ辿るより、そのまま全体を順に読むほうが安上がりです。テーブルがとても小さいときも同じです。つまり「Seq Scan が出た = 問題」ではなく、その条件が行を十分に絞り込んでいるかが判断基準で、この判断を目で確かめる道具が次回の EXPLAIN です。

コスト: インデックスはタダではありません #

インデックス 1 つは「そのテーブルのもう 1 つのソート済みコピー」です。INSERT・UPDATE・DELETE のたびにすべてのインデックスが一緒に更新されるので、インデックスが増えるほど書き込みが遅くなり、ストレージも増えます。「念のため」に作ったインデックスは、純粋な書き込みコストです。だから実務の順序は明確です。先に想像して作るのではなく、実際に遅いクエリの WHERE・JOIN・ORDER BY のカラムを見て作ります。 何が遅いかを見つける仕組み(pg_stat_statements)と、使われていないインデックスの整理は実践編の主題です。

まとめ #

  • 既定の B-tree インデックスは等号、範囲、ソート、前方一致 LIKE を支えます。FK カラムには自動生成がないので自分で張ります。
  • 複合インデックスは左からです。等号カラムを前に、範囲カラムを後ろに置くのが順序の基本ルールです。
  • カラムを加工すると効きません。条件側を変えるか(日付は範囲比較に)、式インデックスを使います。型の不一致も同じ系統です。
  • 選択度が低ければ効かないのが正常です。Seq Scan は罪ではなく、判断の結果であることがあります。
  • インデックスは書き込み税です。想像ではなく実測された遅いクエリを見て作るのが順序で、その観察の道具(EXPLAIN)を次回扱います。
X