Postgres: 外部キーの列には自動でインデックスが付かない。子テーブル側に自分で張る

REFERENCES を書いてもインデックスが付くのは参照される側（主キー・ユニーク）だけで、参照する側の列には付かない。親の削除や更新、JOIN のたびに子テーブルを全件走査することになるので、FK 列にインデックスを張る。

何が遅くなるか

sql

CREATE TABLE orders (
  id      bigint PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES users(id)  -- ここにはインデックスがない
);

DELETE FROM users WHERE id = 1 のとき、Postgres は「この user を参照する orders がないか」を確かめるため orders を走査する。ON DELETE CASCADE でも同じ。
WHERE user_id = $1 や JOIN orders ON orders.user_id = users.id も遅い。
親の削除中は子テーブル側のチェックに時間がかかり、その分ロックも長く持つ。

対処

sql

CREATE INDEX CONCURRENTLY orders_user_id_idx ON orders (user_id);

インデックスのない FK 列を探すクエリ（単一列 FK の簡易版）:

sql

SELECT c.conrelid::regclass AS table_name,
       a.attname            AS fk_column
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
WHERE c.contype = 'f'
  AND array_length(c.conkey, 1) = 1
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid AND i.indkey[0] = c.conkey[1]
  );

注意点

複合インデックスの先頭列が FK 列ならそれで足りる。
本番の大きなテーブルでは CONCURRENTLY を付け、失敗したら INVALID なインデックスが残っていないか確認する（PostgreSQL: 失敗した CREATE INDEX CONCURRENTLY は INVALID なインデックスを残す）。
子テーブルがごく小さい、親を消さない、などの場合は不要なこともある。書き込みコストとの兼ね合い。

出典

@amitsinha__07 · 2026-10-22
PostgreSQL ドキュメント「外部キー」: postgresql.org