Оптимизация PostgreSQL: индексы и планы запросов

Сценарий знаком многим: сайт работал быстро, данных стало больше, страницы начали открываться по несколько секунд. Первый порыв — увеличить мощность сервера. Обычно это лечит симптом на пару месяцев. Разбираться стоит с запросами.

Сначала измерьте

Оптимизация без замеров — это гадание. PostgreSQL предоставляет всё необходимое, чтобы гадать не пришлось.

Начните с поиска медленных запросов через расширение pg_stat_statements. Оно накапливает статистику по всем выполненным запросам, и это честная картина нагрузки, а не догадки о том, какая страница тормозит:

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Обратите внимание: сортировка идёт по total_exec_time, а не по среднему времени. Запрос, выполняющийся 20 мс, но вызываемый сто тысяч раз, съедает больше ресурсов, чем разовый отчёт на две секунды. Именно такие запросы дают наибольший выигрыш при оптимизации.

Как читать EXPLAIN ANALYZE

Когда проблемный запрос найден, смотрим его план:

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Разница между EXPLAIN и EXPLAIN ANALYZE принципиальна: первый показывает предполагаемый план, второй реально выполняет запрос и показывает фактические цифры. Ориентироваться нужно на второй.

В выводе важны несколько вещей:

• Seq Scan по большой таблице — последовательное чтение. На таблице в сто строк это нормально, на миллионе — почти всегда проблема.
• Расхождение rows и actual rows. Если планировщик ожидал 10 строк, а получил 100 000, у него устаревшая статистика — выполните ANALYZE по таблице.
• Nested Loop с большим числом итераций. Соединение вложенными циклами хорошо на малых объёмах и катастрофично на больших.
• BUFFERS. Показывает, читались данные с диска или из кэша. Много read вместо hit — данные не помещаются в память.

Индексы: порядок колонок решает

Самое частое заблуждение — что составной индекс работает при любом сочетании колонок. Это не так. Индекс по (city, status, created_at) помогает запросу с условием по city, по city + status, по всем трём колонкам — но не поможет запросу, который фильтрует только по status.

Практическое правило построения составного индекса: сначала колонки с условием равенства, затем колонка с диапазоном или сортировкой.

Типы индексов стоит различать:

• B-tree — используется по умолчанию, подходит для равенства, диапазонов и сортировки. Покрывает большинство задач.
• GIN — для полнотекстового поиска, JSONB и массивов. Строится дольше, занимает больше места, но по составным типам данных незаменим.
• Частичный индекс — индексирует не всю таблицу, а подмножество строк: WHERE is_published = true. Заметно компактнее, если активных записей мало.

Почему PostgreSQL игнорирует индекс

Ситуация, которая ставит в тупик: индекс создан, а в плане по-прежнему Seq Scan. Причины обычно такие:

• Выборка слишком большая. Если запрос возвращает существенную долю таблицы, последовательное чтение дешевле похода по индексу. PostgreSQL считает это осознанно.
• Функция над колонкой. Условие WHERE lower(email) = ... не задействует обычный индекс по email — нужен функциональный индекс по lower(email).
• Несовпадение типов. Сравнение колонки одного типа с литералом другого может помешать использованию индекса.
• Устаревшая статистика. После массовой загрузки данных выполните ANALYZE.

Цена индекса

Индексы не бесплатны, и об этом легко забыть. Каждый индекс:

• занимает место на диске, иногда сопоставимое с самой таблицей;
• замедляет INSERT, UPDATE и DELETE — при записи обновляется каждый индекс;
• требует обслуживания и участвует в вакуумировании.

Таблица с пятнадцатью индексами, из которых используются четыре, — распространённая проблема. Найти неиспользуемые помогает системное представление:

SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

Что в итоге

Рабочий порядок такой: найти реально дорогие запросы через pg_stat_statements, посмотреть их план через EXPLAIN ANALYZE, добавить точечный индекс под конкретный запрос, проверить план ещё раз. Добавление индексов наугад по всем колонкам, которые встречаются в WHERE, — это не оптимизация, а перекладывание проблемы с чтения на запись.