Что такое explain analyse?

EXPLAIN ANALYSE

EXPLAIN ANALYSE (или EXPLAIN ANALYZE в PostgreSQL) — это инструмент для анализа производительности SQL-запросов. Он показывает план выполнения (query plan) и фактические метрики выполнения, помогая понять, как СУБД выполняет запрос и где возможны узкие места.

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

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

EXPLAIN SELECT * FROM users WHERE id = 1;

-- Вывод:
-- Seq Scan on users  (cost=0.00..35.50 rows=1 width=100)
--   Filter: (id = 1)
-- Planning time: 0.042 ms

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

EXPLAIN ANALYSE SELECT * FROM users WHERE id = 1;

-- Вывод:
-- Seq Scan on users  (cost=0.00..35.50 rows=1 width=100)
--                    (actual time=0.015..0.025 rows=1 loops=1)
--   Filter: (id = 1)
-- Planning time: 0.042 ms
-- Execution time: 0.234 ms

Компоненты EXPLAIN ANALYSE вывода

1. Тип операции

-- Различные типы операций:
-- Seq Scan - полное сканирование таблицы
-- Index Scan - сканирование через индекс
-- Hash Join - соединение через хеширование
-- Nested Loop - вложенный цикл
-- Sort - сортировка
-- Aggregate - агрегация (COUNT, SUM, etc.)
-- Limit - ограничение количества строк

2. Стоимость (cost)

EXPLAIN ANALYSE
SELECT * FROM users WHERE age > 30;

-- Seq Scan on users  (cost=0.00..35.50 rows=200 width=100)
--                    (actual time=0.012..2.456 rows=180 loops=1)

-- cost=0.00..35.50 :
-- - 0.00 = стартовая стоимость (время до первой строки)
-- - 35.50 = полная стоимость (время до последней строки)

-- rows=200 :
-- - Предполагаемое количество строк

-- actual time=0.012..2.456 rows=180 :
-- - 0.012 = фактическое время до первой строки (мс)
-- - 2.456 = фактическое время до последней строки (мс)
-- - 180 = фактическое количество строк

3. Время выполнения

EXPLAIN (ANALYSE, TIMING ON)
SELECT COUNT(*) FROM users;

-- Aggregate  (cost=41.50..41.51 rows=1 width=8)
--            (actual time=0.203..0.204 rows=1 loops=1)
-- Planning time: 0.034 ms
-- Execution time: 0.240 ms  -- ВСЕГО времени на выполнение

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

Пример 1: Выявление полного сканирования таблицы

-- ❌ ПЛОХО: Полное сканирование (Seq Scan)
EXPLAIN ANALYSE
SELECT * FROM users WHERE name = 'John';

-- Seq Scan on users  (cost=0.00..1000.00 rows=100 width=100)
--                    (actual time=0.012..45.234 rows=100 loops=1)
-- Execution time: 45.456 ms

-- ✅ ХОРОШО: С индексом (Index Scan)
CREATE INDEX idx_users_name ON users(name);

EXPLAIN ANALYSE
SELECT * FROM users WHERE name = 'John';

-- Index Scan using idx_users_name on users
--                    (cost=0.29..12.34 rows=100 width=100)
--                    (actual time=0.012..0.456 rows=100 loops=1)
-- Execution time: 0.678 ms  -- 67x быстрее!

Пример 2: Анализ JOIN операций

-- Запрос с двумя таблицами
EXPLAIN ANALYSE
SELECT u.id, u.name, o.order_id
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.age > 30;

-- Вывод может показать:
-- Hash Join  (cost=100.00..500.00 rows=1000 width=16)
--            (actual time=5.234..12.456 rows=950 loops=1)
--   Hash Cond: (o.user_id = u.id)
--   ->  Index Scan on orders o
--              (cost=0.29..250.00 rows=5000 width=8)
--              (actual time=0.012..4.234 rows=5000 loops=1)
--   ->  Hash  (cost=50.00..50.00 rows=2000 width=8)
--        (actual time=0.456..0.678 rows=2000 loops=1)
--        ->  Seq Scan on users u
--                  (cost=0.00..50.00 rows=2000 width=8)
--                  (actual time=0.012..0.234 rows=2000 loops=1)
--              Filter: (age > 30)

Пример 3: Диагностика медленного запроса

# Python код для анализа запроса
import psycopg2

def analyze_query_performance(query: str):
    """Анализирует производительность SQL запроса"""
    conn = psycopg2.connect("dbname=mydb user=postgres")
    cursor = conn.cursor()
    
    # Выполняем EXPLAIN ANALYSE
    explain_query = f"EXPLAIN (ANALYSE, BUFFERS, FORMAT JSON) {query}"
    cursor.execute(explain_query)
    plan = cursor.fetchone()[0]
    
    # Парсим результат
    stats = plan[0]['Plan']
    print(f"Operation: {stats['Node Type']}")
    print(f"Actual time: {stats.get('Actual Total Time', 'N/A')} ms")
    print(f"Rows: {stats.get('Actual Rows', 'N/A')} (expected: {stats.get('Rows', 'N/A')})")
    print(f"Buffers: {stats.get('Shared Hit Blks', 0)} hits")
    
    cursor.close()
    conn.close()

# Использование
analyze_query_performance("SELECT * FROM users WHERE id > 100 LIMIT 10;")

Опции EXPLAIN ANALYSE

-- Стандартный вывод
EXPLAIN ANALYSE SELECT * FROM users;

-- Расширенная информация
EXPLAIN (ANALYSE, BUFFERS)
SELECT * FROM users;
-- Показывает использование блоков памяти (cache hits)

-- JSON формат (для программной обработки)
EXPLAIN (ANALYSE, FORMAT JSON)
SELECT * FROM users;

-- Со сроком выполнения
EXPLAIN (ANALYSE, TIMING)
SELECT * FROM users;

-- Все варианты
EXPLAIN (ANALYSE, BUFFERS, TIMING, VERBOSE, FORMAT JSON)
SELECT * FROM users;

Интерпретация результатов

EXPLAIN ANALYSE
SELECT * FROM products
WHERE price > 100
ORDER BY name
LIMIT 10;

-- Limit  (cost=123.45..123.50 rows=10 width=50)
--        (actual time=5.123..5.234 rows=10 loops=1)
--   ->  Sort  (cost=123.45..150.00 rows=1000 width=50)
--        (actual time=5.012..5.123 rows=10 loops=1)
--        Sort Key: name
--        Sort Space Used: 256 kB
--        ->  Index Scan using idx_price on products
--             (cost=0.29..50.00 rows=1000 width=50)
--             (actual time=0.012..2.456 rows=1000 loops=1)
--             Index Cond: (price > 100)

-- Анализ:
-- 1. Используется индекс - хорошо
-- 2. Сортируется 1000 строк, но возвращается только 10
--    -> добавить LIMIT в подзапрос или индекс по (price, name)
-- 3. Sort Space = 256 kB - умеренно

Оптимизация запросов на основе EXPLAIN ANALYSE

-- Проблема 1: Плохо распознано количество строк
EXPLAIN ANALYSE
SELECT * FROM users WHERE status = 'active';
-- rows=50 (predicted) vs rows=50000 (actual) - неправильная статистика

-- Решение: обновить статистику
ANALYZE users;
VACUUM ANALYZE users;

-- Проблема 2: Неправильный выбор алгоритма JOIN
EXPLAIN ANALYSE
SELECT * FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- Hash Join (плохо) вместо Nested Loop (хорошо)

-- Решение: подсказать оптимизатору
SET enable_hashjoin = OFF;
EXPLAIN ANALYSE SELECT ...;

-- Проблема 3: Full table scan вместо индекса
EXPLAIN ANALYSE
SELECT * FROM logs WHERE timestamp > NOW() - INTERVAL '1 day';
-- Seq Scan (медленно)

-- Решение: создать индекс
CREATE INDEX idx_logs_timestamp ON logs(timestamp DESC);
EXPLAIN ANALYSE SELECT ...; -- Теперь Index Scan

Практический чек-лист

# Аннотированный чек-лист для анализа EXPLAIN ANALYSE
def check_query_performance(explain_output: str) -> dict:
    """Проверяет качество плана выполнения"""
    issues = {}
    
    # Проверка 1: Полное сканирование таблицы?
    if 'Seq Scan' in explain_output:
        issues['full_scan'] = 'Используется полное сканирование таблицы'
    
    # Проверка 2: Различие между predicted и actual rows?
    # Если actual >> predicted, нужно ANALYZE
    
    # Проверка 3: Сортировка больших наборов?
    if 'Sort' in explain_output:
        issues['sort'] = 'Сортируется большой набор строк'
    
    # Проверка 4: Вложенные циклы слишком много раз?
    if 'loops=1000' in explain_output:
        issues['loops'] = 'Много итераций в цикле'
    
    return issues

Заключение

EXPLAIN ANALYSE — это незаменимый инструмент для оптимизации SQL-запросов. Он позволяет:

• Понять, как СУБД выполняет запрос
• Выявить неиспользованные индексы
• Обнаружить неправильную оптимизацию
• Оценить влияние изменений на производительность

Регулярное использование EXPLAIN ANALYSE при разработке больших приложений помогает избежать проблем с производительностью и дорогостоящей рефакторизации в будущем.