Почему внешние ключи часто нужно индексировать
На этой странице
PostgreSQL автоматически создаёт индекс для PRIMARY KEY и UNIQUE, но не для столбца внешнего ключа в дочерней таблице (MySQL InnoDB, наоборот, создаёт его сам). Индекс на FK нужен в трёх случаях: (1) JOIN родителя с детьми и выборка детей по родителю (WHERE order_id = $1) — самые частые запросы; (2) удаление или изменение ключа родителя — база должна найти ссылающиеся строки, и без индекса каждая такая операция делает Seq Scan по всей дочерней таблице; (3) ON DELETE CASCADE/SET NULL — то же самое плюс изменение найденных строк. Если FK начинает составной индекс ((user_id, created_at)), отдельный индекс не нужен. Исключение — маленькие справочники или таблицы, где по FK никогда не ищут и родителей не удаляют.
Вопрос
Почему столбцы внешних ключей обычно нужно индексировать?
Пример
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id bigint NOT NULL REFERENCES users (id) ON DELETE CASCADE,
created_at timestamptz NOT NULL DEFAULT now()
);
-- индекса на orders.user_id нет!
CREATE INDEX CONCURRENTLY orders_user_id_idx ON orders (user_id);Что ускоряет индекс на FK
-- 1. выборка детей по родителю
SELECT * FROM orders WHERE user_id = $1 ORDER BY created_at DESC;
-- 2. соединение (Nested Loop с индексом вместо Hash Join всей таблицы)
SELECT u.email, o.id FROM users u JOIN orders o ON o.user_id = u.id WHERE u.id = $1;
-- 3. удаление родителя: проверка/каскад
DELETE FROM users WHERE id = $1; -- без индекса — Seq Scan по orders на каждое удалениеУдаление 1000 пользователей без индекса = 1000 полных сканирований orders. При этом удаление держит блокировки, пока идёт сканирование.
Составной индекс покрывает FK
CREATE INDEX orders_user_created_idx ON orders (user_id, created_at DESC);
-- подходит и для WHERE user_id = $1, и для проверки FK
-- отдельный индекс (user_id) не нуженИндекс (created_at, user_id) для FK не подходит — FK должен быть первым столбцом.
Как найти FK без индекса
SELECT c.conrelid::regclass AS table_name,
a.attname AS column_name,
c.conname AS fk_name
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
WHERE c.contype = 'f'
AND NOT EXISTS (
SELECT 1 FROM pg_index i
WHERE i.indrelid = c.conrelid AND i.indkey[0] = c.conkey[1]
);Запрос упрощён для одностолбцовых FK.
Когда можно не индексировать
- Таблица маленькая (сотни строк).
- По FK никогда не ищут, а родителей не удаляют и не меняют им PK (например,
created_byв журнале аудита). - Индекс сильно замедлит интенсивную запись, а запросы по FK редки.
Частые ошибки
- Думать, что FK создаёт индекс автоматически (как в MySQL).
ON DELETE CASCADEбез индекса на большой дочерней таблице.- Составной индекс, где FK не на первом месте, считать покрывающим.
Что важно помнить
- PostgreSQL не индексирует FK сам.
- Индекс нужен для JOIN, выборки детей и удаления родителей.
- FK первым столбцом составного индекса — тоже подходит.
Мини-шпаргалка
CREATE INDEX CONCURRENTLY child_fk_idx ON child (parent_id).- Составной:
(parent_id, sort_col). - Проверка:
pg_constraint+pg_index.