middle

Как анализировать производительность запросов? Что такое EXPLAIN ANALYZE?

EXPLAIN — команда, которая показывает план выполнения запроса — последовательность операций, которые СУБД планирует выполнить для получения результата. EXPLAIN ANALYZE дополнительно выполняет запрос и показывает фактическое время и количество обработанных строк.

EXPLAIN vs EXPLAIN ANALYZE

Пример

-- Только план (запрос НЕ выполняется)
EXPLAIN SELECT * FROM employees WHERE department = 'IT';

-- План + фактическое выполнение
EXPLAIN ANALYZE SELECT * FROM employees WHERE department = 'IT';

-- Расширенный вывод с буферами
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM employees WHERE department = 'IT';

Чтение плана выполнения

Пример вывода EXPLAIN ANALYZE

Sort  (cost=1234.56..1256.78 rows=8888 width=64) (actual time=12.345..15.678 rows=8500 loops=1)
  Sort Key: salary DESC
  Sort Method: quicksort  Memory: 1024kB
  ->  Hash Join  (cost=100.00..900.00 rows=8888 width=64) (actual time=1.234..8.901 rows=8500 loops=1)
        Hash Cond: (e.department_id = d.id)
        ->  Seq Scan on employees e  (cost=0.00..500.00 rows=10000 width=48) (actual time=0.010..3.456 rows=10000 loops=1)
        ->  Hash  (cost=80.00..80.00 rows=50 width=16) (actual time=0.123..0.124 rows=50 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 9kB
              ->  Seq Scan on departments d  (cost=0.00..80.00 rows=50 width=16) (actual time=0.005..0.100 rows=50 loops=1)
Planning Time: 0.234 ms
Execution Time: 16.789 ms

Ключевые понятия:

Метрика	Описание
`cost`	Оценочная стоимость (начальная…общая) в условных единицах (НЕ миллисекунды)
`rows`	Предполагаемое (estimated) и фактическое (actual) количество строк
`actual time`	Реальное время выполнения (мс) для первой строки…всех строк
`loops`	Сколько раз операция была выполнена (actual time надо умножить на loops)
`width`	Средний размер строки в байтах

Типы сканирования

Тип	Когда используется	Скорость
Seq Scan	Нет подходящего индекса или таблица маленькая	Медленный на больших таблицах
Index Scan	Есть индекс, высокая селективность	Быстрый
Index Only Scan	Все нужные данные есть в индексе	Самый быстрый
Bitmap Index Scan	Средняя селективность	Средний

Типы соединений

Тип	Когда используется
Nested Loop	Малый внешний набор + индекс на внутреннем
Hash Join	Средние/большие таблицы, нет индекса по ключу
Merge Join	Данные уже отсортированы (по индексу)

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

Исходный медленный запрос:

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.created_at >= '2026-01-01'
GROUP BY u.name
ORDER BY order_count DESC;

План показывает Seq Scan on orders с actual time=500ms.

Шаги оптимизации:

Создать индекс:

CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

Проверить план заново — должен появиться Index Scan или Bitmap Index Scan.
Если запрос фильтрует по диапазону дат, может помочь частичный индекс:

CREATE INDEX idx_orders_2026 ON orders(user_id)
WHERE created_at >= '2026-01-01';

Важное

EXPLAIN ANALYZE реально выполняет запрос — для DELETE/UPDATE используйте внутри транзакции с ROLLBACK
BUFFERS показывает количество обращений к кэшу и диску
Если estimated rows сильно отличается от actual rows — нужно выполнить ANALYZE table_name
Инструменты визуализации: explain.dalibo.com, pgAdmin, DBeaver

Частые ошибки

Запускают EXPLAIN ANALYZE DELETE FROM ... без транзакции — данные удаляются
Путают cost с реальным временем — cost это условные единицы планировщика
Игнорируют loops — если операция выполняется 1000 раз, actual time нужно умножить на loops
Оптимизируют запрос на тестовой базе с малым количеством данных — планы будут другими на production

На собеседовании: объясните разницу между EXPLAIN (только план) и EXPLAIN ANALYZE (план + выполнение). Покажите, что умеете читать план: Seq Scan -> нужен индекс. Частая ошибка — путать cost с миллисекундами или забыть про loops.

#sql