🔍 Как читать план запроса: EXPLAIN, оценки и почему оптимизатор врёт #SQLWednesday

В посте про последнюю транзакцию я обещал показать план выполнения. Обещание закрываю, только это будет не разбор одной задачи, а навык, который окупается каждый день.

Смысл EXPLAIN в одной фразе: ты перестаёшь гадать, почему запрос медленный, и просто спрашиваешь у движка, что он собирается делать. Ответ бывает обидным.

Первое, что надо запомнить - EXPLAIN и EXPLAIN ANALYZE это разные вещи. Первый показывает намерение: план и предположения оптимизатора, запрос не выполняется. Второй реально выполняет и показывает, что получилось на самом деле. Почти вся диагностика живёт в разнице между этими двумя картинками.

Второе - план читается снизу вверх. Внизу источники данных, наверху результат.

И третье: смотреть надо всего на три вещи.

Где время. Не на весь запрос, а по операторам. Обычно 90% висит на одном, и это не то место, куда ты смотрел. Вот мой прогон на 5 млн транзакций, дедуп через ROW_NUMBER:

WINDOW 5,000,000 rows 3.51s
TABLE_SCAN 5,000,000 rows 0.06s

Чтение таблицы — шесть сотых секунды. Всё остальное время съела сортировка внутри оконной функции. Оптимизировать тут «чтение с диска» бессмысленно, лечится только раскладкой данных по ключу партиционирования.

Сколько строк реально прошло через каждый оператор. Если между двумя соседними шагами строк стало в сто раз больше - у тебя размножение на join. Если фильтр стоит наверху и отсекает 99% строк, значит вся эта гора тащилась через весь план впустую, и его надо опустить вниз.

Оценка против факта. Вот это самое интересное. Оптимизатор не знает данные, он знает статистику: сколько строк в таблице, сколько уникальных значений в колонке, как они распределены по гистограмме. Из этого он гадает, сколько строк вернёт каждый шаг, и по этой догадке выбирает алгоритм.

Проверил на своей таблице. Условие status = 'refund', оптимизатор говорит:

SEQ_SCAN Filters: status='refund' ~2,500,000 rows

Два с половиной миллиона. По факту — 5029 строк. Ошибка в пятьсот раз, просто потому что статистики по этой колонке нет и движок честно поделил таблицу пополам.

Само по себе это не страшно, страшны последствия. На оценке в пять тысяч строк движок возьмёт nested loop и индекс. На оценке в два с половиной миллиона — построит хеш-таблицу и пойдёт сканом. Ошибся на входе - выбрал не тот алгоритм для всего дерева выше.
Так запрос, который вчера отрабатывал за две секунды, сегодня висит сорок минут, хотя ты в нём ничего не менял. - ! Менялись данные.

Отсюда типовые причины, почему оценки врут: статистика устарела после массовой заливки; в WHERE несколько условий, которые движок считает независимыми, а они жёстко связаны (город и почтовый индекс); на колонку навешана функция — DATE(created_at) = ... прячет от оптимизатора и статистику, и индекс; данные перекошены, и «средний» клиент существует только на бумаге.

Что с этим делать? : обновить статистику (ANALYZE в Postgres, UPDATE STATISTICS в SQL Server), убрать функции с колонок в предикатах, разбить монстра на шаги с материализацией, а не надеяться, что оптимизатор разрулит семиэтажный CTE.

И архитектурный угол. В лейкхаусе всё то же самое, только статистика живёт в метаданных: min/max по row groups в Parquet, счётчики в манифестах Iceberg. Поэтому Spark умеет adaptive query execution - начинает выполнять, видит реальные объёмы и на лету перестраивает план, потому что доверять оценкам на распределёнке уже никто не хочет.

Открываете план, когда что-то тормозит, или сразу лезете вешать индексы наугад? И какая была самая дикая разница между оценкой и фактом? 👇

#SQL #SQLWednesday #performance #ETL #DataJungle