EXPLAIN ANALYZE в PostgreSQL — план запроса, оптимизация

EXPLAIN ANALYZE показывает как PostgreSQL выполняет запрос: какие индексы используются, сколько строк обрабатывается, где теряется время. Разбираем чтение плана, типы узлов, выявление узких мест и диагностику deadlock.

EXPLAIN vs EXPLAIN ANALYZE

-- EXPLAIN — только план (запрос НЕ выполняется):
EXPLAIN SELECT * FROM users WHERE email = 'a@b.com';

-- EXPLAIN ANALYZE — план + реальное выполнение (запрос ВЫПОЛНЯЕТСЯ):
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'a@b.com';

-- EXPLAIN с полными опциями (рекомендуется для серьёзного анализа):
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.email, COUNT(o.id) AS orders
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.active = TRUE
GROUP BY u.email
ORDER BY orders DESC
LIMIT 10;

-- FORMAT JSON — для программной обработки или explain.depesz.com:
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT ...;

-- ВНИМАНИЕ: EXPLAIN ANALYZE реально выполняет запрос!
-- Для INSERT/UPDATE/DELETE — оберните в транзакцию:
BEGIN;
EXPLAIN ANALYZE UPDATE users SET score = score + 1 WHERE active = TRUE;
ROLLBACK;  -- откатить реальные изменения

Чтение плана запроса

-- Пример вывода EXPLAIN ANALYZE:
Gather  (cost=1000.00..18293.43 rows=1 width=120)
        (actual time=0.193..12.543 rows=1 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on users
      (cost=0.00..17293.33 rows=1 width=120)
      (actual time=3.456..8.234 rows=0 loops=3)
        Filter: (email = 'alice@example.com'::text)
        Rows Removed by Filter: 333333
Planning Time: 0.156 ms
Execution Time: 12.601 ms

-- Как читать:
-- cost=1000.00..18293.43  — оценочная стоимость (start..total)
-- rows=1                  — планировщик ожидал 1 строку
-- actual time=0.193..12.543 — реальное время (ms) первой..последней строки
-- rows=1                  — реально вернулось строк
-- loops=1                 — сколько раз узел вызывался

-- Дерево читается снизу-вверх:
-- 1. Самый вложенный узел — выполняется первым
-- 2. Результат передаётся родительскому узлу

Типы узлов плана

Выявление узких мест

-- 1. Расхождение оценки и реальности (плохая статистика):
-- Плохо:
Seq Scan on orders  (rows=1 estimated, rows=150000 actual)
-- Планировщик ошибся в 150000 раз — нужен ANALYZE

-- Исправить:
ANALYZE orders;
-- Или для всей базы:
ANALYZE;

-- 2. Seq Scan на большой таблице в точечном запросе → нужен индекс:
-- Плохо:
Seq Scan on users  (actual rows=1 loops=1)
  Filter: (email = 'a@b.com')
  Rows Removed by Filter: 999999  -- прочитал миллион, нашёл одну!

-- Исправить:
CREATE INDEX ON users(email);

-- 3. Sort Method: external merge (сортировка на диске):
Sort  (Sort Method: external merge  Disk: 45612kB)
-- Нужно увеличить work_mem для сессии:
SET work_mem = '256MB';
EXPLAIN ANALYZE SELECT ...;  -- проверить улучшение

-- 4. Hash Batches > 1 (hash join на диске):
Hash  (Batches: 4  Memory Usage: 4096kB)
-- Тоже решается увеличением work_mem

-- 5. Медленные вложенные циклы без индекса:
Nested Loop (actual time=0.001..25432.456 rows=100 loops=1)
  ->  Seq Scan on big_table  (actual rows=1000000 loops=100)
-- Нужен индекс на колонке JOIN или переписать запрос

Диагностика дедлоков и блокировок

-- Посмотреть медленные запросы (требует pg_stat_statements):
SELECT query, calls, total_exec_time, mean_exec_time,
  rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

-- Активные запросы и их длительность:
SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- Найти блокирующие запросы:
SELECT
  blocked.pid, blocked.query AS blocked_query,
  blocking.pid AS blocking_pid, blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

-- Принудительно завершить зависший запрос:
SELECT pg_cancel_backend(pid);    -- мягко (SIGINT)
SELECT pg_terminate_backend(pid); -- жёстко (SIGTERM)

-- Включить логирование дедлоков в postgresql.conf:
-- deadlock_timeout = 1s    -- сколько ждать перед проверкой дедлока
-- log_lock_waits = on      -- логировать ожидание блокировок > deadlock_timeout

Часто задаваемые вопросы

Как правильно читать EXPLAIN ANALYZE в PostgreSQL?

Читайте снизу-вверх — самый вложенный узел выполняется первым. Ищите расхождения между rows=N (оценка планировщика) и actual rows=M (реальность): большое расхождение означает устаревшую статистику, запустите ANALYZE. Смотрите на actual time — где больше всего времени. Ищите Seq Scan с большим Rows Removed by Filter — кандидат на индекс. Используйте онлайн-инструмент explain.depesz.com для визуализации — вставьте вывод EXPLAIN и получите таблицу с цветовой подсветкой медленных узлов.

Почему PostgreSQL не использует индекс и делает Seq Scan?

Планировщик выбирает Seq Scan когда считает его быстрее. Типичные причины: 1) Запрос вернёт много строк (>10% таблицы) — чтение всей таблицы быстрее чем случайный доступ через индекс. 2) Устаревшая статистика — запустите ANALYZE. 3) Маленькая таблица — накладные расходы индекса не оправданы. 4) Функция в WHERE без функционального индекса: WHERE upper(email) = 'A@B.COM'. 5) Нестандартная кардинальность — SET enable_seqscan = off; принудительно заставит использовать индекс (только для отладки).

Как найти самые медленные запросы в PostgreSQL?

Включите расширение pg_stat_statements: добавьте shared_preload_libraries = 'pg_stat_statements' в postgresql.conf, перезапустите PostgreSQL, выполните CREATE EXTENSION pg_stat_statements;. Затем: SELECT query, calls, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 20;. Для логирования медленных запросов: установите log_min_duration_statement = 1000 в postgresql.conf — запросы дольше 1 секунды будут записаны в лог. Посмотреть лог: tail -f /var/log/postgresql/postgresql-*.log | grep duration.