Введение: когда «магия» индексов перестает работать

Знакомая ситуация: вчера ваш SQL-запрос летал, а сегодня он вешает продакшн, вызывая таймауты и гневные сообщения в Slack. В такие моменты легко поддаться панике и начать лепить индексы на все поля подряд (в надежде, что база сама поймет ваш замысел), но в PostgreSQL «магия» не работает — здесь правит холодный расчет планировщика. Если вы хотите перестать играть в угадайку и начать лечить базу как хирург, пора освоить инструмент, который превращает хаос в понятную карту: EXPLAIN ANALYZE.

Заглядываем «под капот» базы

Планировщик PostgreSQL — это мозг системы. Он решает, какой путь выбрать: прочитать всю таблицу (Sequential Scan) или довериться индексу (Index Scan). Планировщик — как опытный тимлид: он всегда пытается выбрать путь наименьшего сопротивления, но иногда его «опыт» заставляет его выбирать самый длинный маршрут. Чтобы понять, почему этот «мозг» выбрал неоптимальный путь, нам нужно увидеть его логику.

Между EXPLAIN и EXPLAIN ANALYZE есть принципиальная разница. Первый показывает лишь предположения планировщика, основанные на статистике. Второй же — это «боевой» отчет: он реально выполняет запрос и фиксирует каждое действие. Это ваш главный диагностический прибор, но помните о технике безопасности: если вы тестируете INSERT, UPDATE или DELETE, всегда используйте транзакции:

BEGIN;
EXPLAIN ANALYZE DELETE FROM logs WHERE created_at < '2023-01-01';
ROLLBACK;

Так вы увидите план выполнения, не превратив свои данные в мусор (и не вызвав коллег на внеплановое восстановление из бэкапа).

Читаем план как профи

Чтобы выжать из анализа максимум, всегда используйте EXPLAIN (ANALYZE, BUFFERS). Флаг BUFFERS — это ваш лучший друг: он покажет, сколько данных база достала из оперативной памяти (shared hit), а сколько — вынуждена была «выгрызать» с медленного диска (read). Именно здесь обычно прячутся ответы на вопросы о тормозах.

На что смотреть в первую очередь:

  • Cost: Абстрактная цена «усилий» БД. Не зацикливайтесь на ней, но используйте для сравнения разных вариантов запроса.
  • Actual time: Реальное время выполнения узла в миллисекундах. Ищите «тяжеловесов», которые съедают большую часть времени.
  • Rows: Сколько строк реально обработано. Если планировщик ожидал 10, а получил 1 000 000 — статистика безнадежно устарела.
  • Loops: Количество повторений узла. Если число подозрительно высокое, значит, ваш запрос попал в ловушку вложенных циклов.

Почему база вас игнорирует?

Вы добавили индекс, но запрос всё равно тормозит? Скорее всего, планировщик считает его бесполезным. Вот три сценария, когда это случается чаще всего:

  1. Статистика протухла: Если таблица обновилась на 30%, а ANALYZE не запускался, планировщик «слеп». Решение: ANALYZE table_name;.
  2. Индекс — это лишняя трата ресурсов: Если запрос возвращает 80% таблицы, проще прочитать её целиком, чем прыгать по индексу.
  3. Функции-убийцы: Запрос WHERE upper(email) = 'TEST@EXAMPLE.COM' сбрасывает все настройки — база не может использовать индекс, пока не применит функцию к каждой строке. Используйте функциональные индексы.

Пора действовать

Оптимизация PostgreSQL — это не искусство догадок, а инженерная работа с фактами. Теперь, когда вы знаете, как заглянуть в план выполнения, попробуйте проанализировать самый медленный запрос в вашем проекте прямо сейчас. Найдите узел с максимальным actual time и высоким потреблением buffers — скорее всего, именно там кроется корень всех бед. Удачи в настройке!