В чем разница между EXPLAIN и EXPLAIN ANALYZE в PostgreSQL?

# Разница между EXPLAIN и EXPLAIN ANALYZE в PostgreSQL

Коротко

• EXPLAIN - показывает план выполнения, который PostgreSQL планирует использовать
• EXPLAIN ANALYZE - на самом деле выполняет запрос и показывает реальные метрики

Разница: планирование vs реальная работа.

EXPLAIN - Теоретический план

Пример:

Seq Scan on users (cost=0.00..35.50 rows=1 width=100)
  Filter: (email = 'john@example.com')

Что видишь:

• Seq Scan - тип операции (полное сканирование таблицы)
• cost=0.00..35.50 - оцененная стоимость (от start до end)
• rows=1 - ожидаемое количество строк
• width=100 - средний размер строки в байтах

Это предположение! БД не выполняет запрос, а только говорит "я думаю, что это будет стоить столько".

Проблема EXPLAIN без ANALYZE

Проблема: План может быть неправильным! Статистика БД может быть устаревшей. Реальное количество строк может отличаться. Индекс может быть неиспользован из-за ошибочной оценки.

EXPLAIN ANALYZE - Реальные метрики

Пример:

Seq Scan on users (cost=0.00..35.50 rows=1 width=100)
          (actual time=0.023..0.045 rows=1 loops=1)
Planning Time: 0.087 ms
Execution Time: 0.156 ms

ANALYZE действительно выполняет запрос и показывает:

• actual time=0.023..0.045 - реальное время (от первой строки до последней)
• actual rows=1 - сколько строк реально вернулось
• loops=1 - сколько раз операция была выполнена
• Execution Time - общее время выполнения

Пример 1: Неправильный план без ANALYZE

Индекс есть, но план показывает Seq Scan:

С EXPLAIN:

Seq Scan on users (cost=0.00..35.50 rows=1 width=100)
  Filter: (email = 'john@example.com')

С EXPLAIN ANALYZE:

Seq Scan on users (cost=0.00..35.50 rows=1 width=100)
          (actual time=1234.567..1234.890 rows=1 loops=1)

Реальное время: 1234 мс! Это медленно. Индекс был бы в 100x быстрее.

Причина: БД не знала статистику, поэтому выбрала неправильный план.

Решение:

ANALYZE users;
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

Index Scan using idx_email on users (cost=0.29..8.30 rows=1 width=100)
  Index Cond: (email = 'john@example.com')

Теперь БД правильно оценила и будет использовать индекс.

Пример 2: Оценка строк неправильная

БД ожидает 10 строк:

Seq Scan on orders (cost=0.00..100.00 rows=10 width=50)
  Filter: (status = 'pending')

А реально вернулось 50,000 строк:

Seq Scan on orders (cost=0.00..100.00 rows=10 width=50)
          (actual time=0.1..50.5 rows=50000 loops=1)

Это очень плохо! Алгоритм был неправильным. Может быть, нужен индекс или денормализация.

Подробный пример с JOIN'ом

Nested Loop (cost=1.41..3.16 rows=1 width=108)
          (actual time=0.023..0.045 rows=1 loops=1)
  -> Index Scan using pk_users on users u (cost=0.29..2.01 rows=1 width=100)
             (actual time=0.012..0.015 rows=1 loops=1)
        Index Cond: (id = 1)
  -> Index Scan using idx_orders_user_id on orders o (cost=0.29..1.12 rows=5 width=8)
             (actual time=0.007..0.010 rows=1 loops=1)
        Index Cond: (user_id = 1)
Planning Time: 0.156 ms
Execution Time: 0.089 ms

Анализ:

• Nested Loop - внешний цикл
• Первый Index Scan нашёл 1 строку
• Для каждой строки выполнен второй Index Scan (1 раз)
• Estimated rows совпадают с actual rows - отлично!

Когда использовать что

EXPLAIN (без ANALYZE)

Успользовать:

• Быстрая оценка плана
• На production (безопасно, не меняет данные)

Не использовать:

• Для диагностики медленных запросов (может быть неправильный план)
• Когда нужны реальные метрики

EXPLAIN ANALYZE

Использовать:

• Диагностика медленных запросов
• Проверка правильности плана
• Тестирование индексов
• Сравнение different query approaches

Осторожно:

• Выполняет запрос! Для UPDATE/DELETE может быть опасно
• Для длительных операций может занять долго

Совет: Используй EXPLAIN ANALYZE DELETE ... RETURNING чтобы не удалять данные

Полезные опции

EXPLAIN (FORMAT JSON)

Выводит JSON, удобно парсить программно.

EXPLAIN (BUFFERS TRUE)

Показывает какая память использована (shared buffers, disk I/O).

EXPLAIN (VERBOSE TRUE)

Подробный вывод со всеми деталями.

Практический процесс оптимизации

1. Получаешь жалобу: "Запрос медленный!"

2. Запускаешь EXPLAIN ANALYZE

3. Смотришь estimated vs actual:

   • Если rows (estimated: 10, actual: 10000) - проблема в статистике
   • Если time (0.001..0.002, actual: 5000.000) - проблема в алгоритме

4. Оптимизируешь:

   • Добавь индекс
   • ANALYZE таблицу чтобы обновить статистику
   • Переписать запрос
   • Денормализовать данные

5. Проверяешь снова EXPLAIN ANALYZE

Резюме

Правило: При оптимизации всегда используй EXPLAIN ANALYZE, это единственный способ узнать реальность.