> Как использовать EXPLAIN и ANALYZE для оптимизации SQL запросов (Go)

Короткий ответ

EXPLAIN показывает план выполнения запроса: порядок чтения таблиц, типы join, использование индексов и оценку стоимости. ANALYZE дополнительно выполняет запрос и возвращает фактическое время операций. Для оптимизации смотрите на Seq Scan против Index Scan, rows против фактического числа строк, actual time и узлы с наибольшей стоимостью. Главное - сравнивать план до и после изменений, а не полагаться на абсолютные цифры.

Подробное объяснение

EXPLAIN без ANALYZE выводит планы на основе статистики планировщика - это оценка, а не факт. С ANALYZE запрос реально выполняется, поэтому вы видите actual time, actual rows и loops. Разница между оценкой и фактом - главный сигнал проблемы: если rows сильно расходится с actual rows, статистика устарела или запрос написан так, что планировщик не может корректно оценить селективность.

Ключевые узлы плана, которые нужно читать:

• Seq Scan - полное сканирование таблицы. Оправдано только для маленьких таблиц или когда нужно прочитать большую часть строк.
• Index Scan - чтение по индексу с последующим обращением к таблице. Хорошо для точечных выборок.
• Index Only Scan - данные полностью покрыты индексом, обращение к таблице не нужно. Самый быстрый вариант.
• Bitmap Index Scan + Bitmap Heap Scan - используется при комбинации условий или когда индекс возвращает много строк.
• Nested Loop - для маленьких наборов, обычно с индексом во внутреннем цикле.
• Hash Join - для средних наборов, когда одна сторона помещается в память.
• Merge Join - для отсортированных больших наборов.
• Sort - часто узкое место, если требует work_mem больше доступного.
• Materialize - заморозка промежуточного результата, иногда полезна, иногда признак неоптимального join.

Порядок чтения плана - снизу вверх: сначала читаются самые глубокие узлы (доступ к таблицам), затем они соединяются. Узлы с наибольшей cost или actual time - кандидаты на оптимизацию.

ANALYZE имеет важный побочный эффект - запрос выполняется. Для INSERT, UPDATE, DELETE нужно оборачивать в транзакцию с ROLLBACK, чтобы не менять данные. Для тяжелых запросов на продакшене используйте EXPLAIN (ANALYZE, BUFFERS) с осторожностью, лучше на реплике.

Дополнительные опции:

• BUFFERS - показывает чтение из кэша и с диска, помогает выявить проблемы с shared_buffers.
• FORMAT JSON - удобен для программного анализа.
• VERBOSE - показывает полные имена колонок и функции.
• SETTINGS - показывает измененные параметры планировщика.

На практике

Типичный процесс оптимизации:

1. Найдите медленный запрос через логирование или мониторинг.
2. Снимите EXPLAIN (ANALYZE, BUFFERS) на тестовой копии данных.
3. Определите самый дорогой узел - обычно это Seq Scan на большой таблице или Sort с большим объемом.
4. Проверьте, есть ли подходящий индекс. Если нет - создайте и сравните план.
5. Если индекс есть, но не используется - проверьте типы данных, обертки функций, LIKE с wildcard в начале, неявные приведения.
6. Если проблема в join - проверьте порядок соединения, статистику, work_mem.
7. После изменений обязательно сравните фактическое время выполнения, а не только план.

Для Go-разработчика важно помнить: ORM (GORM, sqlx) часто генерирует неоптимальные запросы. Смотрите реальный SQL через логирование запросов, а не то, что написано в коде. Также проверяйте, не выполняются ли запросы в цикле - это часто хуже любого плана.

Пример кода

SQL
-- Базовый план
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

-- С фактическим выполнением и буферами
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 42;

-- Для модифицирующих запросов - в транзакции
BEGIN;
EXPLAIN (ANALYZE) UPDATE orders SET status = 'paid' WHERE id = 123;
ROLLBACK;

-- JSON формат для автоматического анализа
EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at > now() - interval '1 day';

GO
// В Go - логирование реальных запросов для анализа
sqlDB, _ := db.DB()
sqlDB.SetMaxOpenConns(20)

// Включить логирование в GORM
db = db.Debug()

// Или через middleware для sql.DB
sqlDB.Stats() // для мониторинга пула соединений

Как отвечать на собеседовании

Начните с того, что EXPLAIN - это план, а ANALYZE - факт. Подчеркните разницу между оценкой и реальностью. Затем расскажите, как читаете план: снизу вверх, ищете самый дорогой узел, сравниваете rows и actual rows. Приведите пример из практики: была проблема с Seq Scan, добавили составной индекс, время упало с секунд до миллисекунд.

Если спросят про конкретный узел - объясните, когда Nested Loop лучше Hash Join, а когда наоборот. Упомяните, что work_mem влияет на выбор между сортировкой в памяти и на диске. Для Go-разработчика добавьте, что часто проблема не в плане, а в N+1 запросах из ORM.

Не углубляйтесь в теорию cost calculation, если не просят. Покажите, что умеете находить узкое место и проверять гипотезы через сравнение планов.

Что проверяет интервьюер

• Понимаете ли вы, что EXPLAIN без ANALYZE - это оценка, а не факт.
• Умеете ли читать план и находить проблемные узлы.
• Знаете ли, когда индекс помогает, а когда мешает.
• Понимаете ли влияние статистики и work_mem.
• Умеете ли применять это на практике, а не только в теории.
• Для senior - способность объяснить trade-off между разными планами и методами доступа.

Типичные ошибки

• Смотреть только на cost, игнорируя actual time и actual rows.
• Не использовать BUFFERS - пропускаете проблемы с кэшированием.
• Запускать ANALYZE на продакшене для тяжелых запросов без необходимости.
• Забывать про ROLLBACK для модифицирующих запросов.
• Считать, что Index Scan всегда лучше Seq Scan - для маленьких таблиц полное сканирование быстрее.
• Не проверять расхождение rows и actual rows - главный индикатор проблем со статистикой.
• Оптимизировать запрос, не измерив baseline - без сравнения до/после невозможно оценить эффект.
• В Go - игнорировать запросы, которые генерирует ORM, и оптимизировать несуществующий SQL.