ブログ一覧

複合インデックスの順番はどう決める？PostgreSQLのEXPLAINで効かない原因を調べる

· 約10分で読めます

インデックスを作ったのにSQLが遅いとき、見るべきはWHERE・ORDER BY・実行計画の組み合わせ。等価条件と範囲条件、列の加工、深いOFFSET、外部キーの索引を、注文履歴APIの例で解説します。

複合インデックスとは？まず速くしたいSQLを1本決める

複合インデックスは複数列をまとめた索引です。ここではPostgreSQL 18のB木を使い、「あるテナントの、指定期間の注文を新しい順に50件返す」APIを考えます。idは主キー、tenant_idとcreated_atはNOT NULL、created_atはtimestamptzとします。SQLは説明用で、$1などにはアプリから値をバインドします。

注文履歴の検索と、その条件に合わせた索引の候補
SELECT id, created_at, total_minor
FROM orders
WHERE tenant_id = $1
  AND created_at >= $2
  AND created_at < $3
ORDER BY created_at DESC, id DESC
LIMIT 50;

CREATE INDEX orders_tenant_created_id_idx
ON orders (tenant_id, created_at DESC, id DESC);

tenant_idを1つに固定し、その中のcreated_atの範囲を探します。同時刻の注文はidで順序を確定させます。このクエリでは、テナントを先頭に、並び順に合う日時とIDを後ろへ置くのが出発点です。WHEREに書いた条件の見た目の順番を変えることと、索引の列順を変えることは別です。

列の順番：等価条件を先に、範囲と並び順を続ける理由

(tenant_id, created_at)はテナントごとに並び、その中が日時順になります。逆の(created_at, tenant_id)は日時ごとのまとまりが先です。最初の列を固定できると、欲しい範囲が連続しやすくなります。複数の等価条件がある場合は、その検索だけでなく、どの条件を省く別クエリも支えたいかを考えて順番を決めます。

索引の候補今回の検索での見方別途確かめること
(tenant_id, created_at DESC, id DESC)テナントを固定し、期間内を必要な順序で探す候補実際の行数、追加のフィルタ、テーブル参照の量
(created_at, tenant_id)日時の範囲を先に読む計画なら他テナントも候補になる別の走査方法やソートを選んでいないか
tenant_idとcreated_atの単列索引複数索引を組み合わせる計画などが候補になる注文順に返すためのソートが残るか

候補行数を計算する：返す50件と、探す範囲は違う

仮定として、100テナントに均等な注文が全体で1日100万件、365日分あるとします。1テナントの1日は100万 ÷ 100 = 1万行。テナントだけで絞ると1万 × 365 = 365万行が候補です。日付だけの範囲なら全テナントの100万件が候補になります。

仮定の候補集合：100テナント均等、全体で100万件/日、365日分。実際の走査行数や時間の測定ではない
  • 1テナント・1日: 1万行
  • 全テナント・1日: 100万行
  • 1テナント・365日: 365万行

これは条件が定める候補集合の大きさです。LIMITと索引順が合えば途中で読み終えられるため、この全数を必ず走査するわけではありません。逆に、並べ替えや別の条件で多くを落とすなら、50件を返すまでに広く読む場合があります。候補が100分の1でも、実行時間が必ず100分の1になるとは言えません。

インデックスが効かない原因：DATEなどで列を加工していないか

「日本時間の10月24日」という条件を作るとき、日時列を日付へ変換すると、created_atそのものの索引とは異なる式になります。次の例では同じ業務日の条件を、始点を含み終点を含まない半開区間へ書き換えています。境界の時刻は対象のタイムゾーンで計算し、夏時間のある地域で常に24時間を足す実装にはしません。

条件の比較：日本時間の1日を同じ範囲で指定する例
-- Business day in Asia/Tokyo; created_at is timestamptz.
WHERE tenant_id = $1
  AND (created_at AT TIME ZONE 'Asia/Tokyo')::date = DATE '2026-10-24'

-- Equivalent interval for this date, without transforming the column.
WHERE tenant_id = $1
  AND created_at >= TIMESTAMPTZ '2026-10-24 00:00:00+09'
  AND created_at <  TIMESTAMPTZ '2026-10-25 00:00:00+09'

通常のB木で範囲を絞りにくいことと、索引が一切使われないことは違います。式に合う索引や全体走査が選ばれることもあります。大文字小文字を区別しない検索ならlower(email)の式の索引も候補です。LIKEの前方一致は照合順序や演算子クラスにも依存するため、文字列の検索条件も計画で確認します。

EXPLAINの見方：Index Scanという名前だけで判断しない

まずEXPLAINで計画を確認し、実行可能な検証環境でEXPLAIN (ANALYZE, BUFFERS)を使って実際の処理を比べます。ANALYZE付きはSQLを本当に実行します。更新系SQLの副作用も発生するので、観察だけのコマンドとして本番の書き込みに使わないようにします。

表示見るポイント
推定rowsとactual rows大きく外れるなら統計情報や値の偏りを調べる。actual rowsは通常ノードの出力行数で、索引で調べた全エントリ数ではない
loops繰り返した回数。実測の行数・時間は1ループあたりの平均表示なので、全体の仕事量と区別する
Index Cond / Filter索引へ渡す条件と、取得後に落とす条件を見る。Index Condに載るだけで走査が十分狭いとは限らない
Buffersバッファのヒット・読み込みを比較する。親ノードが子の分を含むため、単純に全ノードを合計しない
Sort / Execution Timeソートの方法と全体時間を見る。全件に近い取得では順次走査とソートが適切なこともある

Seq Scanを見つけたこと自体を不具合にしません。小さなテーブルや多くの行を返す検索なら自然な選択です。同じ条件とデータで、読んだバッファや待ち時間、最終的なAPI遅延がどう変わるかを確認します。複数ノードの時間を足して全体時間とする読み方も避けます。

索引を作っても深いページが遅いなら、OFFSETを調べる

LIMIT 50 OFFSET 100000は、先頭の10万件を飛ばしてから50件を返します。条件に合う行が十分ある単純な順序走査なら、10万50行を取り出す仕事が残ります。次へ進むだけのUIなら、最後のcreated_atとidをカーソルにして、索引の続きから読む方式を検討します。

次のページ：前ページの末尾の日時とIDを$4・$5へバインドする
SELECT id, created_at, total_minor
FROM orders
WHERE tenant_id = $1
  AND created_at >= $2 AND created_at < $3
  AND (created_at, id) < ($4, $5)
ORDER BY created_at DESC, id DESC
LIMIT 50;

この例はNULLを含まない、一意で不変なソートキーと適切な索引が前提です。created_atだけでは同時刻の行を取りこぼしうるため、主キーのidも使います。行比較で判定に使う値がNULLだと結果が不明になりうるため、カーソルの日時とIDも必須の値として検証します。カーソルはテナント・検索条件と結び付け、次ページでも同じ認可を適用します。任意のページへ飛ぶ要件や、更新をまたいだ同一時点の一覧には別の設計が必要です。

親の削除が遅いときは、外部キーの子側も確認する

PostgreSQLは、外部キーを宣言しても参照する子側の列の索引を自動作成しません。顧客を削除するときはorders.customer_idを探す必要があります。今回のtenant_id先頭の索引だけでその探索を十分に支えられるとは限らず、外部キーと実際の検索条件に合う索引を別に検討します。

外部キーがorders.customer_id → customers.idの場合の索引候補
CREATE INDEX orders_customer_id_idx ON orders (customer_id);

子側にcustomer_idを先頭とする既存の複合索引があれば、同じ用途を満たす可能性があります。重複する索引を追加する前に確認します。子を大量に削除するCASCADEの仕事やロック待ちは、索引があっても残ります。

索引を増やす前に確認する5項目

  1. 頻度と遅延が問題のSQL、パラメーターの型、WHERE・ORDER BY・LIMITを特定する。
  2. テナントの偏り、日時の範囲、NULLの有無を代表データで再現する。
  3. 列の加工や深いOFFSETなど、クエリを書き換える余地を確かめる。
  4. 既存索引と候補を比べ、実行計画・バッファ・API遅延を同じ条件で測る。
  5. 索引の容量と書き込みの負担、作成時のロックと運用手順も確認する。

よくある疑問：選択度の高い列を必ず先頭にすればよい？

値の種類が多い列を先頭にする、という規則だけでは決まりません。今回の検索ではテナントを固定して日時順に50件ほしいため、その条件を支える順番を選びます。日時だけの横断集計が重要なら、別の列順や別索引が役立つ場合があります。検索の組み合わせと書き込みの負担を合わせて判断します。

列順・実行計画・制約を確認する一次資料

この記事で深掘りしたTips