EXPLAIN ANALYZE действительно выполняет UPDATE: как исследовать запись

Нужно понять, почему массовое обновление работает медленно. Разработчик привык добавлять EXPLAIN ANALYZE перед SELECT и делает то же перед UPDATE в рабочей базе. Команда выводит красивый план, но данные уже изменились: слово EXPLAIN не сделало операцию предварительным просмотром.

Без ANALYZE PostgreSQL строит план, а с ANALYZE запускает запрос и измеряет исполнение. Это относится к записывающим командам тоже. Для исследования транзакционных изменений удобно использовать отдельный тестовый стенд; ROLLBACK помогает вернуть обычные изменения таблиц при локальном опыте.

Как изменить решение

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE order_stage
SET processed = true
WHERE processed = false;
ROLLBACK;

План покажет реальные строки и обращения к буферам, а откат отменит изменения processed в таблице. Команда всё равно выполняла работу, брала блокировки и создавала нагрузку. Это принципиально отличается от EXPLAIN без ANALYZE.

Если UPDATE вызывает триггеры, изучите их до запуска. Побочные действия вне транзакционной базы и продвижение sequence нельзя считать автоматически отменёнными. Для плана потенциально тяжёлой записи сначала используйте обычный EXPLAIN, затем воспроизводимый набор данных в отдельной среде.

Что проверить на своих данных

Запомните количество строк с processed = false, выполните опыт и после ROLLBACK посчитайте снова. Для чтения плана сравните оценку rows с actual rows, проверьте loops и время узлов. Числа actual rows при нескольких loops показываются в расчёте на один цикл; без этого легко недооценить работу вложенного цикла.

Не складывайте времена всех узлов: родительское время включает работу дочерних узлов. И не делайте вывод по одной строке Seq Scan. Для обновления значительной части таблицы последовательное чтение может быть разумным. Ищите место, где объём или повторение работы расходится с ожиданием, затем меняйте запрос и повторяйте замер на сопоставимом состоянии данных.

Практика дальше

Следующая практика — запросы, где правильность зависит от нескольких источников и граничных случаев. В курсе «Продвинутый SQL» вы решаете 109 задач на JOIN, окна и CTE, сверяете отчёты и собираете витрину интернет-магазина. Запросы выполняются в браузере на SQLite с автоматической проверкой.

Посмотреть программу и практику курса

Источник: EXPLAIN ANALYZE действительно выполняет UPDATE — официальная документация.

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