Зачем нужно ключевое слово EXPLAIN в запросе?

EXPLAIN в SQL: анализ плана выполнения запроса

EXPLAIN — это команда для просмотра плана выполнения SQL запроса БД. Она показывает, как база данных будет выполнять запрос, включая сканирование таблиц, использование индексов и затраты на операции.

Основная идея

EXPLAIN не выполняет запрос, а показывает план его выполнения. Это критически важно для оптимизации медленных запросов.

-- Просмотреть план выполнения (без выполнения)
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

-- Выполнить запрос и показать реальные метрики (PostgreSQL)
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'john@example.com';

Пример: неоптимизированный запрос

Представьте таблицу с миллионом пользователей без индекса на email:

-- БЕЗ индекса
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'john@example.com';

Seq Scan on users  (cost=0.00..35000.00 rows=1 width=128)
  Filter: (email = 'john@example.com')
  Rows: 1 (estimated 1)
  Planning Time: 0.123 ms
  Execution Time: 250.456 ms  -- МЕДЛЕННО!

Сканирование последовательное (Seq Scan) — БД проверяет каждую строку.

Афтер добавления индекса:

-- Создаём индекс
CREATE INDEX idx_users_email ON users(email);

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

Index Scan using idx_users_email on users  (cost=0.29..8.31 rows=1 width=128)
  Index Cond: (email = 'john@example.com')
  Rows: 1 (estimated 1)
  Planning Time: 0.098 ms
  Execution Time: 0.234 ms  -- БЫСТРО!

Далеко быстрее! Индекс позволяет прямому поиску.

Что показывает EXPLAIN

Основные метрики:

• Node Type (тип операции): Seq Scan (полное сканирование, медленно), Index Scan (поиск по индексу, быстро), Hash Join (соединение по хешу), Nested Loop Join (вложенные циклы)

• Cost (стоимость): формат (startup_cost..total_cost). Относительные единицы, lower is better.

• Rows (строки): estimated (сколько БД думает вернёт) и actual (сколько на самом деле)

• Execution Time: реальное время в миллисекундах

Пример сложного запроса

EXPLAIN ANALYZE
SELECT 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
LIMIT 10;

Чтение справа налево и снизу вверх:

1. Сканируем users с фильтром по дате
2. Сканируем posts
3. Соединяем по хешу
4. Группируем и агрегируем COUNT
5. Сортируем по count DESC
6. Берём top 10

Практические советы для оптимизации

1. Проверить использование индексов

-- Плохо: Seq Scan вместо Index
EXPLAIN SELECT * FROM users WHERE id = 5;
-- Seq Scan on users (cost=0.00..35000.00)  Почему не индекс?!

-- Решение: убедись что индекс существует
CREATE INDEX idx_users_id ON users(id);  -- Обычно есть PRIMARY KEY

2. Проверить JOIN стратегию

Хороший план использует индексы для соединения, плохой — Hash Join с полным сканированием.

3. Проверить фильтры (WHERE)

Фильтры должны применяться при использовании индекса, а не после сканирования всех строк.

EXPLAIN FORMAT

-- JSON формат (парсить программой)
EXPLAIN (FORMAT JSON) SELECT * FROM users WHERE id = 5;

-- XML формат
EXPLAIN (FORMAT XML) SELECT * FROM users WHERE id = 5;

-- YAML формат
EXPLAIN (FORMAT YAML) SELECT * FROM users WHERE id = 5;

Python: парсинг результатов EXPLAIN

import json
from sqlalchemy import text
from sqlalchemy.orm import Session

def analyze_query(db: Session, query_text: str):
    result = db.execute(
        text(f"EXPLAIN (FORMAT JSON) {query_text}")
    ).scalar()
    
    plan = json.loads(result)[0]
    print(f"Total cost: {plan['Plan']['Total Cost']}")
    print(f"Rows: {plan['Plan']['Actual Rows']}")
    print(f"Execution time: {plan['Execution Time']} ms")
    
    return plan

# Использование
query = "SELECT * FROM users WHERE id = 5"
analyze_query(db, query)

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

• Запрос выполняется медленнее 100ms
• Нужна оптимизация production запросов
• Нужно добавить индекс (проверить план БЕЗ индекса и С индексом)
• Сравнивать разные варианты JOIN

Итог

EXPLAIN — критический инструмент для оптимизации:

• Показывает план выполнения БЕЗ выполнения
• ANALYZE выполняет и показывает реальные метрики
• Помогает найти missing indexes
• Позволяет выбрать лучшую JOIN стратегию
• Экономит время на production