Медленные SQL-запросы: как аналитику читать EXPLAIN ANALYZE и ускорить выборку без помощи DBA

Запрос крутится минуту, дашборд не открывается, коллеги ждут. Первый импульс - написать в чат DBA с просьбой «посмотреть, почему всё медленно». Но DBA заняты, очередь на несколько дней, а отчёт нужен сегодня. Хорошая новость: базовую диагностику аналитик может провести самостоятельно. PostgreSQL показывает, как именно он выполняет запрос, - нужно только уметь это читать.

Эта статья про оптимизацию SQL-запросов для аналитика: не теория про индексы вообще, а конкретный пошаговый разбор. Как запустить EXPLAIN ANALYZE, что значат строки в выводе, где искать узкое место и как его устранить. После этого материала вы сможете самостоятельно разобраться с большинством типичных проблем производительности.

Что делает EXPLAIN ANALYZE и зачем его запускать

Когда PostgreSQL получает запрос, он сначала строит план выполнения - последовательность операций: какие таблицы сканировать, в каком порядке соединять, как сортировать. Команда EXPLAIN показывает этот план без реального выполнения. EXPLAIN ANALYZE идёт дальше: она запрос выполняет, а рядом с каждым шагом плана выводит фактические данные - реальное время и количество строк.

Именно сочетание оценки и факта делает EXPLAIN ANALYZE полезным. Планировщик мог думать, что в таблице 500 строк, а там их 5 миллионов - и тогда он выбрал неоптимальный путь. Это видно сразу.

Как запустить и что получить

Синтаксис простой. Добавьте перед запросом:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ...

Параметр BUFFERS показывает, сколько данных прочитано из кэша и с диска - это помогает понять, почему запрос медленный даже при наличии индекса. FORMAT TEXT - читаемый вывод по умолчанию, можно опустить.

Результат выглядит примерно так:

Seq Scan on orders  (cost=0.00..45231.00 rows=1200000 width=64)
                   (actual time=0.042..3821.334 rows=1198432 loops=1)
  Filter: (status = 'completed')
  Rows Removed by Filter: 14231
Planning Time: 1.2 ms
Execution Time: 4102.8 ms

Давайте разберём каждую часть.

Как читать строки вывода: разбор по частям

Каждый узел плана содержит две строки: оценку планировщика и фактические значения.

Строка Execution Time в конце - это итоговое время выполнения всего запроса. Именно на неё смотрите в первую очередь.

Seq Scan: почему это часто главный виновник

Seq Scan означает последовательное сканирование: PostgreSQL читает таблицу с первой строки до последней и проверяет каждую под условие фильтра. На таблице из тысячи строк это нормально. На таблице из десяти миллионов - катастрофа.

Посмотрите на строку Rows Removed by Filter. Если там миллионы, а actual rows - сотни, значит база прочитала гигабайты данных, чтобы вернуть вам несколько строк. Именно здесь обычно живёт главная потеря времени.

SELECT * FROM orders WHERE created_at = '2024-11-01';

Альтернатива Seq Scan - это Index Scan или Bitmap Index Scan. Первый хорош для точечных запросов с небольшим результатом. Второй PostgreSQL выбирает, когда нужно вернуть много строк через индекс - он сначала собирает битовую карту страниц, потом читает их разом, избегая случайных чтений.

Где ещё теряется время: JOIN, сортировка, агрегация

Seq Scan - не единственная причина медленной работы. Вот другие узлы, на которые стоит обращать внимание.

Hash Join и Nested Loop

При соединении таблиц PostgreSQL выбирает между несколькими стратегиями. Hash Join строит хеш-таблицу из меньшей таблицы и проверяет по ней строки большой. Это эффективно, если результирующая хеш-таблица помещается в память. Если нет - начинается сброс на диск, и запрос замедляется резко.

Nested Loop - для каждой строки внешней таблицы ищет совпадения во внутренней. При loops=50000 и actual time=0.1..0.5 на узел это 50000 × 0.5 мс = 25 секунд только на этот шаг. Смотрите на произведение loops × actual time.

Sort

Сортировка в плане выглядит как узел Sort. Если рядом написано Sort Method: external merge Disk - данные не влезли в память (work_mem), PostgreSQL использует диск. Это медленно. Решение - либо увеличить work_mem для сессии (SET work_mem = '256MB';), либо добавить индекс, который уже хранит данные в нужном порядке.

HashAggregate и GroupAggregate

При агрегации с GROUP BY план покажет один из этих узлов. HashAggregate быстрее, но требует памяти. Если видите сброс на диск - та же история с work_mem.

Расхождение оценки и реальности: что делать

Планировщик строит план на основе статистики - распределения значений в таблице. Статистика обновляется автоматически при AUTOVACUUM, но если таблица меняется быстро или только что заполнена, оценки могут быть сильно неверными.

Признак: rows=100 в оценке, rows=2000000 в фактических данных. Планировщик думал, что JOIN отфильтрует почти всё, а в итоге обрабатывает миллионы строк не тем методом.

Решение простое: запустите ANALYZE table_name; на нужной таблице. Это обновит статистику без блокировок - безопасно в рабочей базе. После этого перезапустите EXPLAIN ANALYZE и посмотрите, изменился ли план.

Как добавить индекс: практика без DBA

Если вы убедились, что Seq Scan на большой таблице - главная проблема, следующий шаг - создать индекс. В PostgreSQL это делается командой:

CREATE INDEX CONCURRENTLY idx_orders_created_at
  ON orders (created_at);

Слово CONCURRENTLY критично: оно позволяет создать индекс без блокировки таблицы. Без него таблица заблокируется на время создания, что на больших таблицах неприемлемо в рабочей среде. С CONCURRENTLY создание занимает дольше, но не мешает другим запросам.

Составные индексы

Если в запросе несколько условий, простой индекс по одному полю может не помочь:

SELECT * FROM orders
WHERE user_id = 123 AND status = 'completed';

Здесь лучше составной индекс: CREATE INDEX CONCURRENTLY ON orders (user_id, status);. Порядок полей важен: первым ставьте поле с условием равенства (=), вторым - с диапазоном или другим оператором. PostgreSQL использует индекс слева направо.

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

Если запрос всегда фильтрует по одному значению, индекс можно сделать частичным - он будет меньше и быстрее:

CREATE INDEX CONCURRENTLY idx_orders_active
  ON orders (created_at)
  WHERE status = 'active';

Это особенно полезно, когда активных записей мало относительно общего объёма таблицы.

Пошаговый разбор реального случая

Представим: отчёт по заказам за последние 30 дней выполняется 45 секунд. Запрос такой:

SELECT u.email, COUNT(o.id) as order_count, SUM(o.amount) as total
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at >= NOW() - INTERVAL '30 days'
  AND o.status = 'completed'
GROUP BY u.email
ORDER BY total DESC;

Шаг 1. Запускаем EXPLAIN (ANALYZE, BUFFERS) и смотрим на самый дорогой узел. Видим:

Seq Scan on orders  (actual time=0.1..38200.0 rows=847293 loops=1)
  Filter: ((status = 'completed') AND (created_at >= ...))
  Rows Removed by Filter: 14152707

Из 15 миллионов строк прошли фильтр только 847 тысяч. PostgreSQL перебрал всё.

Шаг 2. Смотрим на Buffers: shared read: 180000 - большинство данных читается с диска, не из кэша.

Шаг 3. Создаём составной индекс под оба условия:

CREATE INDEX CONCURRENTLY idx_orders_status_date
  ON orders (status, created_at);

Шаг 4. Повторяем EXPLAIN ANALYZE. Теперь в плане:

Bitmap Index Scan on idx_orders_status_date
  (actual time=180.0..180.0 rows=847293 loops=1)

Время выполнения: 1.8 секунды вместо 45. Без DBA, без деплоя.

Когда индекс не поможет

Иногда аналитик создаёт индекс, запускает запрос - и PostgreSQL всё равно делает Seq Scan. Это не баг. Планировщик умеет считать: если запрос возвращает больше 10-20% строк таблицы, последовательное чтение часто быстрее, чем прыгать по индексу. Индекс оправдывает себя при высокой селективности - когда условие отбирает малую долю данных.

Ещё одна ловушка: функции и приведение типов в условии WHERE ломают использование индекса.

То же касается неявного приведения типов: если в таблице user_id это integer, а в условии вы пишете WHERE user_id = '123' (строка), PostgreSQL может не использовать индекс из-за несоответствия типов.

Чеклист диагностики медленного запроса

1. Запустите EXPLAIN (ANALYZE, BUFFERS) и найдите узел с наибольшим actual time.
2. Проверьте: это Seq Scan на большой таблице? Сколько строк отфильтровано?
3. Сравните оценку rows с фактическим значением. Расхождение в 10 раз и больше - признак устаревшей статистики.
4. Если статистика устарела - запустите ANALYZE table_name;.
5. Проверьте условия WHERE: нет ли функций, оборачивающих индексируемую колонку?
6. Проверьте типы данных в условиях - совпадают ли они с типами в таблице?
7. Если нужен индекс - создайте с CONCURRENTLY.
8. Повторите EXPLAIN ANALYZE и сравните время до и после.
9. Если проблема в сортировке или агрегации с диском - попробуйте SET work_mem = '256MB'; для текущей сессии.
10. Если запрос всё ещё медленный и план выглядит странно - тогда привлекайте DBA с готовым анализом на руках.

Инструменты, которые упрощают работу с планами

Текстовый вывод EXPLAIN ANALYZE читается нормально для небольших запросов, но сложный план с десятками узлов превращается в стену текста. Есть удобные альтернативы:

• explain.dalibo.com - вставляете вывод в формате JSON (FORMAT JSON), получаете интерактивное дерево с подсветкой медленных узлов. Бесплатно, работает в браузере.
• pgAdmin - встроенный визуальный просмотрщик планов, если вы уже работаете в этом клиенте.
• DataGrip / DBeaver - оба умеют строить графическое представление плана прямо из IDE.

Для получения вывода в JSON используйте: EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON). Скопируйте весь вывод и вставьте в explain.dalibo.com - самые дорогие узлы сразу выделяются цветом.