Индексы, EXPLAIN и VACUUM

Ускоряем запросы индексами, читаем план выполнения и узнаём, зачем нужен VACUUM.

Индекс — вспомогательная структура данных, которая позволяет базе находить нужные строки быстро, не просматривая всю таблицу.

Зачем нужны индексы

Без индекса, чтобы найти строку по условию, база читает всю таблицу подряд — это называется последовательное сканирование (Seq Scan). На миллионе строк это медленно. Индекс — как алфавитный указатель в книге: вместо перелистывания всех страниц вы сразу прыгаете в нужное место.

Плата за скорость чтения — замедление записи (индекс надо обновлять) и дополнительное место на диске. Поэтому индексируют не всё подряд, а столбцы, по которым часто ищут и соединяют.

Создание индекса

Индекс по умолчанию — типа B-tree, он годится для =, диапазонов < > и сортировки.

-- Ускорить поиск пользователей по email
CREATE INDEX idx_users_email ON users (email);

-- Составной индекс по двум столбцам (важен порядок!)
CREATE INDEX idx_orders_client_date ON orders (client_id, created_at);

-- Уникальный индекс — заодно гарантирует уникальность
CREATE UNIQUE INDEX uq_users_email ON users (email);

Важно: PRIMARY KEY и UNIQUE автоматически создают индекс — отдельно его делать не нужно. Внешние ключи индексируйте сами: PostgreSQL не делает это автоматически, а JOIN-ы по ним очень частые.

Частичные индексы

PostgreSQL умеет индексировать не всю таблицу, а только строки по условию. Такой индекс меньше и быстрее, если запросы всегда касаются этого подмножества.

-- Индексируем только активные заказы — их ищут чаще всего
CREATE INDEX idx_active_orders ON orders (created_at)
WHERE status = 'active';

EXPLAIN ANALYZE — смотрим план

Как понять, использует ли запрос индекс? Команда EXPLAIN показывает план выполнения, а EXPLAIN ANALYZE ещё и реально выполняет запрос, замеряя время.

EXPLAIN ANALYZE
SELECT * FROM users WHERE email = '[email protected]';

В выводе ищите ключевые слова. Index Scan — отлично, индекс используется. Seq Scan на большой таблице по условию — сигнал, что индекса не хватает.

Index Scan using idx_users_email on users
  (cost=0.29..8.30 rows=1 width=64)
  (actual time=0.024..0.025 rows=1 loops=1)
Planning Time: 0.1 ms
Execution Time: 0.05 ms

VACUUM — уборка мусора

PostgreSQL при UPDATE и DELETE не стирает старые версии строк сразу, а помечает их как «мёртвые» (это нужно для изоляции транзакций). Со временем мёртвые строки накапливаются и раздувают таблицу. VACUUM их вычищает.

# Очистить мёртвые строки и обновить статистику
VACUUM ANALYZE users;

В большинстве случаев об этом заботится фоновый процесс autovacuum — он запускается сам. Знать о VACUUM важно, чтобы понимать, почему таблица растёт даже без новых данных, и почему статистика для планировщика иногда устаревает.

Итог

Индекс ускоряет поиск и JOIN ценой замедления записи и места; индексируйте часто искомые столбцы и внешние ключи.
PRIMARY KEY/UNIQUE создают индекс автоматически; частичный индекс покрывает только нужное подмножество строк.
EXPLAIN ANALYZE показывает план (Index Scan vs Seq Scan); VACUUM убирает мёртвые версии строк.