Какие редфлаги есть при использовании EXPLAIN ANALYZE?

Редфлаги при использовании EXPLAIN ANALYZE в PostgreSQL

EXPLAIN ANALYZE — мощный инструмент для анализа производительности запросов в PostgreSQL, но его вывод содержит множество показателей, которые могут указывать на серьёзные проблемы. Вот ключевые редфлаги, на которые стоит обращать внимание:

1. Значительное расхождение между планируемым и фактическим временем

Самая частая проблема — когда фактические затраты (actual time) сильно превышают планируемые (planning time) или когда разница между EXPLAIN и EXPLAIN ANALYZE огромна. Это указывает на неверные оценки планировщика.

-- Пример вывода с расхождением
->  Seq Scan on users  (cost=0.00..10.00 rows=100 width=4) 
    (actual time=0.005..50.123 rows=100000 loops=1)

Проблема: планировщик ожидал 100 строк, но получил 100000. Причины: устаревшая статистика, отсутствие индексов, сложные предикаты.

2. Огромное количество циклов (loops) в Nested Loop

->  Nested Loop  (cost=0.15..24.20 rows=10 width=8)
    (actual time=0.020..125.300 rows=1000 loops=1000)

Редфлаг: 1000 циклов при соединении таблиц. Если внутренняя таблица сканируется многократно без эффективного индекса, производительность деградирует. Решение: проверить индексы на join-ключах, рассмотреть Hash Join или Merge Join.

3. Buffer usage: high read operations

Buffers: shared hit=5 read=15000

Критический показатель: read=15000 означает 15000 операций чтения с диска. Высокое значение read указывает на недостаточный размер shared_buffers или на то, что данные не кэшируются. shared hit — чтение из кэша, что гораздо быстрее.

4. Sort Method: external merge Disk

Sort Method: external merge  Disk: 10240kB

Редфлаг: сортировка выполняется на диске, а не в памяти. Это происходит, когда work_mem недостаточно для операции. Влияет на производительность в разы. Решение: увеличить work_mem для сессии или глобально.

5. Seq Scan на больших таблицах

->  Seq Scan on orders  (cost=0.00..15000.00 rows=1000000 width=40)
    (actual time=0.005..250.000 rows=1000000 loops=1)

Проблема: полное сканирование таблицы с миллионом строк. Хотя иногда это оптимально, часто указывает на:

• Отсутствие индекса для WHERE
• Неселективные условия, где индекс всё равно не поможет
• Необходимость переписать запрос

6. Высокое actual time для отдельных узлов

Даже если общее время приемлемо, отдельные узлы могут быть узким местом:

->  Hash Join  (cost=100.00..200.00 rows=500 width=8)
    (actual time=50.000..150.000 rows=500 loops=1)
    Hash Cond: (orders.user_id = users.id)

Если Hash Join занимает 150ms из 200ms общего времени — это узкое место. Возможно, нужно увеличить work_mem для хеш-таблицы.

7. Параллельные планы с низкой эффективностью

->  Gather  (cost=1000.00..12000.00 rows=100000 width=8)
    (actual time=10.000..300.000 rows=50000 loops=1)
    Workers Planned: 4
    Workers Launched: 4

Редфлаги:

• Workers Launched: 4, но общее время не уменьшилось в 4 раза — накладные расходы на параллелизацию
• Малое количество строк на worker — параллелизация неэффективна
• Решение: настроить max_parallel_workers_per_gather, проверить parallel_setup_cost

8. Index Scan с высоким actual time

->  Index Scan using idx_user_email on users  
    (cost=0.15..8.17 rows=1 width=4)
    (actual time=1.000..2.500 rows=1000 loops=1)
    Index Cond: (email LIKE '%@example.com%')

Проблема: индекс используется неэффективно из-за:

• Неселективного условия (LIKE с % в начале)
• Функции вокруг колонки (WHERE lower(email) = ...)
• Неоптимального индекса (отсутствие покрывающего индекса)

9. Огромные значения в rows x width

->  Seq Scan on log_data  
    (cost=0.00..10000.00 rows=500000 width=1024)

Расчёт: 500000 строк × 1024 байт ≈ 500MB данных. Если это промежуточный результат, может исчерпать память. Указание на необходимость:

• Агрегации на более ранней стадии
• Фильтрации лишних колонок
• Пагинации или ограничения выборки

10. CTE (WITH) как optimization fence

CTE scan on cte_data  (cost=100.00..200.00 rows=100 width=8)

Важно: в PostgreSQL CTE до версии 12 является "барьером оптимизации" — материализуется отдельно. Это может быть неочевидным редфлагом, если CTE большой.

Как анализировать вывод EXPLAIN ANALYZE

1. Сравнивайте планы — используйте EXPLAIN (ANALYZE, BUFFERS, VERBOSE) для полной картины
2. Проверяйте статистику — ANALYZE table_name для обновления статистики
3. Используйте расширения — pg_stat_statements для выявления проблемных запросов
4. Тестируйте на реалистичных данных — на пустых таблицах планировщик даёт неверные оценки

Главный принцип: редфлаг — не приговор, а указание на область для оптимизации. Часто достаточно добавить индекс, увеличить параметр памяти или переписать запрос, чтобы устранить проблему.