re:re:actable
Урок 043 минуты чтения

Почему внешние ключи часто нужно индексировать

На этой странице
Кратко

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.

Документация

PostgreSQL: Foreign Keys.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам