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

Что проверяют

Умеете ли вы читать план и делать из него выводы, а не просто знаете команду.

Как отвечать

EXPLAIN показывает план с оценками, EXPLAIN ANALYZE выполняет запрос и добавляет фактические время и число строк. Добавляют ещё BUFFERS, чтобы увидеть, сколько страниц прочитано из кэша, а сколько с диска.

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

На что смотреть по порядку:

• узел с наибольшим фактическим временем: план читается снизу вверх и изнутри наружу, оптимизировать надо самое дорогое место, а не первое попавшееся;
• расхождение rows оценки и факта: разница в разы означает устаревшую статистику (лечится ANALYZE таблицы) или условие, которое планировщик не умеет оценивать. Из-за неверной оценки выбирается неудачный способ соединения, и отсюда большинство медленных запросов;
• Seq Scan на большой таблице: иногда правильный выбор (выбирается половина строк), иногда признак отсутствующего индекса;
• Nested Loop с большим числом итераций: часто следствие той же недооценки;
• Sort Method: external merge Disk: сортировка не поместилась в память, помогает увеличение work_mem или сокращение выборки;
• Rows Removed by Filter: прочитали много, выбросили почти всё, значит нужен более подходящий индекс.

Важное предупреждение: EXPLAIN ANALYZE реально выполняет запрос, включая UPDATE и DELETE. Для них оборачивают в транзакцию с откатом.

Где спотыкаются

Смотрят только итоговое время, не сравнивая оценку с фактом.

Запускают ANALYZE на изменяющем запросе в проде.

Правят индексы, не открыв план.

Делают выводы на тестовой базе из сотни строк.