Что делает EXPLAIN ANALYZE в БД?

EXPLAIN ANALYZE: практическое применение

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

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

EXPLAIN (без ANALYZE) — только предполагаемый план:

# PostgreSQL
EXPLAIN SELECT * FROM users WHERE status = 'active';

# Результат:
# Seq Scan on users  (cost=0.00..35.50 rows=500 width=64)
# Filter: (status = 'active')
# Planning Time: 0.089 ms

Здесь:

• cost=0.00..35.50 — предполагаемая стоимость
• rows=500 — предполагаемое количество строк
• Запрос НЕ выполняется, только планируется

EXPLAIN ANALYZE — реальный план с фактическими метриками:

EXPLAIN ANALYZE SELECT * FROM users WHERE status = 'active';

# Результат:
# Seq Scan on users  (cost=0.00..35.50 rows=500 width=64)
#                    (actual time=0.456..12.345 rows=487 loops=1)
# Filter: (status = 'active')
# Planning Time: 0.089 ms
# Execution Time: 12.456 ms

Здесь:

• actual time=0.456..12.345 — реальное время выполнения
• rows=487 — реальное количество возвращённых строк
• Запрос выполняется, и собираются точные метрики

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

1. Диагностика медленных запросов

# Подозреваем, что запрос медленный
EXPLAIN ANALYZE
SELECT u.id, u.name, COUNT(p.id) as post_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
ORDER BY post_count DESC;

Вывод покажет:

• Какая операция занимает больше всего времени?
• Индекс используется эффективно?
• Плана оптимизатора совпадают ли с реальностью?

2. Валидация оптимизации

# До оптимизации
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;
# Seq Scan, 5 seconds

# Добавили индекс
CREATE INDEX idx_orders_user_id ON orders(user_id);

# После оптимизации
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;
# Index Scan, 0.5 ms — 10000x быстрее!

3. Выявление неправильно спланированных запросов

EXPLAIN ANALYZE
SELECT *
FROM products p
JOIN orders o ON p.id = o.product_id
WHERE p.price > 1000 AND o.created_at > NOW() - INTERVAL '1 day';

# Результат может показать:
# Index Scan using idx_products_price (cost=0.42..1200.50 rows=50 width=100)
#                                       (actual time=0.123..45.678 rows=47 loops=1)
# Nested Loop  (cost=0.42..5000.00 rows=5000 width=200)
#              (actual time=0.234..78.901 rows=4987 loops=1)
#   Filter: (o.created_at > (NOW - '1 day'::interval))

Здесь видно:

• Оптимизатор предполагал 5000 rows, на самом деле 4987 (ошибка 0.26%)
• Nested Loop может быть не оптимальным, возможно нужен Hash Join
• Фильтр по created_at работает после объединения (неэффективно)

Как читать EXPLAIN ANALYZE

Ключевые метрики:

Index Scan using idx_name on table_name
  (cost=0.29..10.50 rows=5 width=32)
  (actual time=0.123..2.456 rows=5 loops=1)

# cost=start..end
#   start — стоимость получения первой строки
#   end — стоимость получения всех строк

# rows=X (expected vs actual)
#   5 = 5 — точный прогноз (хорошо)
#   100 = 5 — переоценка (ANALYZE должен помочь)
#   5 = 100 — недооценка (может быть проблема)

# actual time=start..end (ms)
#   Реальное время выполнения этого шага

# loops=X
#   Сколько раз выполнился этот узел
#   loops > 1 обычно означает nested loop

Практические примеры

Пример 1: Хороший индекс

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

# Index Scan using idx_users_email on users
#   (cost=0.29..8.30 rows=1 width=64)
#   (actual time=0.023..0.024 rows=1 loops=1)
# Planning Time: 0.078 ms
# Execution Time: 0.042 ms

# ✅ ХОРОШО: быстро, использует индекс, точный прогноз

Пример 2: Проблемный запрос

EXPLAIN ANALYZE
SELECT u.*, COUNT(p.id)
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.name, u.email;

# Seq Scan on users u  (cost=0.00..35.50 rows=1000 width=64)
#   (actual time=0.456..50.789 rows=1000 loops=1)
# HashAggregate  (cost=0.00..100.00 rows=1000 width=32)
#   (actual time=51.234..75.678 rows=1000 loops=1)
# Planning Time: 0.134 ms
# Execution Time: 75.812 ms

# ❌ ПРОБЛЕМЫ:
# 1. Seq Scan на users (полное сканирование)
# 2. HashAggregate медленный (25ms на 1000 rows)
# 3. Может понадобиться индекс на (user_id, created_at)

Использование в Python/Django

from django.db import connection
from django.core.management.base import BaseCommand

class Command(BaseCommand):
    def handle(self, *args, **options):
        # Проанализировать query
        query = str(
            User.objects.select_related('profile')
            .filter(status='active')
            .query
        )
        
        with connection.cursor() as cursor:
            cursor.execute(f"EXPLAIN ANALYZE {query}")
            results = cursor.fetchall()
            
            for row in results:
                self.stdout.write(row[0])

Инструменты для анализа

1. Online analyzer — explain.depesz.com

# Скопировать результат EXPLAIN ANALYZE
# Вставить на сайт
# Получить красивый граф и рекомендации

2. pgAdmin встроенный анализ

# В pgAdmin запустить EXPLAIN ANALYZE
# Увидеть visual план с цветовой кодировкой

3. pgBadger для логов

# Включить slow query logging в PostgreSQL
log_min_duration_statement = 100  # логировать > 100ms

# Анализировать логи
pgbadger /var/log/postgresql/postgresql.log

Когда НЕ использовать EXPLAIN ANALYZE

❌ На production базе:

# ОПАСНО: запрос выполняется!
EXPLAIN ANALYZE DELETE FROM orders WHERE id > 1000000;

# ПРАВИЛЬНО: используй просто EXPLAIN
EXPLAIN DELETE FROM orders WHERE id > 1000000;

# Или скопируй данные в тестовую БД и анализируй там

Заключение

EXPLAIN ANALYZE — это мощный инструмент для:

• Нахождения узких мест в queries
• Выявления неправильного использования индексов
• Проверки эффективности оптимизаций
• Выявления ошибок в статистике оптимизатора (нужен ANALYZE на таблице)

Основное правило: если query медленный, первое, что делаю — запускаю EXPLAIN ANALYZE, не полагаясь на интуицию.