外部キーにインデックスは自動で作られない。PostgreSQL と SQLite で足りないインデックスを 1 クエリで見つける

外部キー制約を付けると、子テーブル側の列にもインデックスができると思われがちです。実際には、自動で作るのは主要な DB の中では MySQL（InnoDB）だけです。PostgreSQL・SQLite・SQL Server では、自分で作らない限りインデックスはありません。

この記事では、インデックスがないと何が起きるかを実際の実行計画で確かめ、既存のスキーマから「インデックスのない外部キー」を一覧するクエリを紹介します。

結論

DB	外部キー列（子テーブル側）のインデックス
PostgreSQL	自動では作られない
MySQL（InnoDB）	なければ自動で作られる
SQLite	自動では作られない
SQL Server	自動では作られない

参照される側（親テーブルの主キーや UNIQUE 列）には、どの DB でもインデックスがあります。足りなくなるのは参照する側です。

インデックスがないと何が起きるか

子テーブルの外部キー列にインデックスがないと、次の 2 つの場面で子テーブルを全件読むことになります。

• 親のキーで子を探す検索：「この顧客の受注一覧」のような、日常的なクエリ
• 親の削除・更新：親の行を消すとき、DB は参照している子の行がないか（ON DELETE CASCADE なら削除対象）を探す

2 つ目は見落とされがちです。アプリのコードには子テーブルを読むクエリが出てこないので、遅くなっても原因がすぐにはわかりません。

PostgreSQL で確かめる

顧客 2,000 件・受注 40 万件のテーブルで、顧客を 1 件削除してみます。

CREATE TABLE customers (id bigint PRIMARY KEY);
CREATE TABLE orders (
  id bigint PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(id) ON DELETE CASCADE
);
INSERT INTO customers SELECT g FROM generate_series(1, 2000) g;
INSERT INTO orders SELECT g, (g % 2000) + 1 FROM generate_series(1, 400000) g;
ANALYZE;

EXPLAIN (ANALYZE, COSTS OFF) DELETE FROM customers WHERE id = 1;

外部キーの検査は EXPLAIN の木には出てこず、最後の Trigger for constraint の行に時間が出ます。

-- インデックスなし
Trigger for constraint orders_customer_id_fkey on customers: time=73.599 calls=1
Execution Time: 95.479 ms

orders.customer_id にインデックスを作ってから同じ削除を実行すると、こうなります。

CREATE INDEX orders_customer_id_idx ON orders (customer_id);

-- インデックスあり
Trigger for constraint orders_customer_id_fkey on customers: time=1.958 calls=1
Execution Time: 10.504 ms

外部キーの検査にかかった時間が約 74 ms から約 2 ms になりました。40 万行でこの差なので、行数が増えるほど開きます。

計測は PGlite（PostgreSQL 18.3 の WebAssembly 版）で行いました。絶対値は環境によって変わりますが、子テーブルを全件読むかどうかの差は同じです。

インデックスのない外部キーを一覧する

PostgreSQL

SELECT c.conrelid::regclass         AS table_name,
       c.conname                    AS foreign_key,
       pg_get_constraintdef(c.oid)  AS definition
FROM pg_constraint c
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1
    FROM pg_index i
    WHERE i.indrelid = c.conrelid
      AND (string_to_array(i.indkey::text, ' ')::int2[])[1:array_length(c.conkey, 1)] @> c.conkey
  )
ORDER BY 1, 2;

やっていることは次のとおりです。

• pg_constraint から外部キー（contype = 'f'）を取り出す。conkey は外部キーを構成する列番号の配列
• 同じテーブルのインデックス（pg_index）について、indkey（インデックスの列番号）の先頭から外部キーの列数分を取り出す
• その中に外部キーの列がすべて含まれるインデックスが 1 つもなければ、一覧に出す

先頭の列だけを見ているのがポイントです。たとえば (order_id, line_no) の主キーは order_id の外部キーに使えますが、(id, order_id) のインデックスは使えません。

CREATE TABLE order_items (
  order_id bigint NOT NULL REFERENCES orders(id),
  line_no int NOT NULL,
  PRIMARY KEY (order_id, line_no)          -- 先頭が order_id なので OK
);
CREATE TABLE shipments (
  id bigint PRIMARY KEY,
  order_id bigint REFERENCES orders(id)
);
CREATE INDEX shipments_x ON shipments (id, order_id);  -- 先頭が id なので使えない

このスキーマで先ほどのクエリを実行すると、order_items は出てこず、shipments だけが出てきます。

 table_name |       foreign_key       |               definition
------------+-------------------------+-----------------------------------------
 shipments  | shipments_order_id_fkey | FOREIGN KEY (order_id) REFERENCES orders(id)

部分インデックスや式インデックスも「ある」と判定されますが、条件によっては検査に使われません。結果は候補の一覧として扱い、気になるものは EXPLAIN で確かめてください。

SQLite

SQLite では、pragma_foreign_key_list と pragma_index_list / pragma_index_info をテーブル値関数として結合できます（SQLite 3.16 以降）。

SELECT m.name    AS table_name,
       f."from"  AS column_name,
       f."table" AS parent_table
FROM sqlite_master AS m
JOIN pragma_foreign_key_list(m.name) AS f
WHERE m.type = 'table'
  AND NOT EXISTS (
    SELECT 1
    FROM pragma_index_list(m.name) AS il
    JOIN pragma_index_info(il.name) AS ii
    WHERE ii.seqno = 0 AND ii.name = f."from"
  )
ORDER BY 1, 2;

インデックスの先頭列（seqno = 0）が外部キーの列と一致するかを見ています。複合の外部キーは列ごとに 1 行ずつ出てくるので、先頭の列で判断してください。

実行計画でも確認できます。shipments.order_id にインデックスがない状態で受注を消すと、shipments が全件スキャン（SCAN）になっています。

EXPLAIN QUERY PLAN DELETE FROM orders WHERE id = 1;

SEARCH orders USING INTEGER PRIMARY KEY (rowid=?)
SCAN shipments
SEARCH order_items USING COVERING INDEX sqlite_autoindex_order_items_1 (order_id=?)

なお SQLite は、PRAGMA foreign_keys = ON; を接続ごとに実行しないと外部キーの検査そのものが無効です。

MySQL（InnoDB）

MySQL は外部キーの列を先頭に持つインデックスを必須としていて、なければ外部キーの作成時に自動で作ります。そのため、このチェックは基本的に不要です。外部キーが使っているインデックスを DROP INDEX しようとするとエラーになります。

SQL Server

SQL Server も自動では作りません。sys.foreign_key_columns と sys.index_columns（key_ordinal が先頭からの順番）を突き合わせれば、同じ考え方で一覧できます。

付けなくてよい場合

• 子テーブルが常に小さく、親を消すことも、親のキーで子を探すこともない
• 書き込みが非常に多く、インデックスの更新コストのほうが問題になる（実測してから判断する）

迷ったら付けておき、不要とわかったら外すほうが安全です。

まとめ

• 外部キー列のインデックスを自動で作るのは MySQL（InnoDB）だけ
• ないと、親の削除・更新のたびに子テーブルを全件読む。EXPLAIN ANALYZE の Trigger for constraint の行で確認できる
• 既存のスキーマは、この記事のクエリで「インデックスのない外部キー」を一覧できる
• 複合インデックスは、外部キーの列が先頭にあるときだけ代わりになる

---

筆者は、DBML でスキーマを書いて ER 図・DDL・テーブル定義書を生成する DB 設計ツール「Joinery」を開発しています。Joinery の Lint は、この記事で扱った「先頭にインデックスのない外部キー」を設計の段階で警告します（MySQL は対象外）。DB 設計の手順や ER 図の書き方は DB 設計ガイド にもまとめています。