Что такое EXPLAIN ANALYZE в PostgreSQL?

EXPLAIN ANALYZE в PostgreSQL

EXPLAIN ANALYZE — это команда в PostgreSQL для анализа выполнения SQL запросов. Она показывает не только план выполнения (как обычная команда EXPLAIN), но и реальные данные о том, как запрос был выполнен: время выполнения, количество обработанных строк, использованные индексы и затраты.

Основное назначение

EXPLAIN ANALYZE используется для профилирования и оптимизации SQL запросов. Команда выполняет запрос и собирает статистику о:

• Типе операций (Seq Scan, Index Scan, Hash Join и т.д.)
• Затратах на выполнение (Cost)
• Реальном времени выполнения (Actual Time)
• Количестве обработанных и возвращённых строк
• Памяти и буферах

Различие между EXPLAIN и EXPLAIN ANALYZE

EXPLAIN — показывает плановый путь выполнения запроса (как оптимизатор думает его выполнять):

EXPLAIN SELECT * FROM users WHERE age > 30;

EXPLAIN ANALYZE — реально выполняет запрос и показывает фактические метрики:

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;

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

Seq Scan on users  (cost=0.00..35.50 rows=500 width=32) (actual time=0.045..1.234 rows=487 loops=1)
  Filter: (age > 30)
  Rows Removed by Filter: 13
Planning Time: 0.123 ms
Execution Time: 1.456 ms

Здесь:

• cost=0.00..35.50 — предполагаемая стоимость (в условных единицах)
• rows=500 — ожидаемое количество строк
• actual time=0.045..1.234 — реальное время (start..end)
• rows=487 — фактически обработано строк
• Rows Removed by Filter — отсечено фильтром

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

1. Медленные запросы — найти узкие места
2. Проверка индексов — используется ли индекс или происходит Full Table Scan
3. Оптимизация JOIN — какой тип соединения выбран (Nested Loop, Hash Join, Merge Join)
4. Анализ N+1 проблем — сколько раз выполняется запрос

Практический пример на Python

import psycopg2

conn = psycopg2.connect("dbname=mydb user=postgres")
cur = conn.cursor()

# Анализ запроса
cur.execute("""
    EXPLAIN ANALYZE
    SELECT u.id, u.name, COUNT(p.id) as posts_count
    FROM users u
    LEFT JOIN posts p ON u.id = p.user_id
    WHERE u.created_at > 2024-01-01
    GROUP BY u.id, u.name
""")

for row in cur.fetchall():
    print(row[0])

conn.close()

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

EXPLAIN ANALYZE VERBOSE SELECT ...;  -- подробный вывод
EXPLAIN (ANALYZE true, BUFFERS true) SELECT ...;  -- с информацией о буферах
EXPLAIN (FORMAT JSON) SELECT ...;  -- JSON формат для парсинга

Важные метрики

• Seq Scan vs Index Scan — если много Seq Scan на больших таблицах, нужны индексы
• Actual vs Planned rows — большое расхождение означает неточную статистику
• Hash Join vs Nested Loop — влияет на производительность
• Execution Time — общее время выполнения

Экспертный совет: EXPLAIN ANALYZE выполняет запрос, поэтому используй с осторожностью для UPDATE/DELETE запросов в боевом окружении. Лучше обернуть в транзакцию и откатить изменения.