План выполнения – не заключение о причине медленного запроса, а описание решения, принятого оптимизатором. Поэтому Seq Scan, Nested Loop, высокий cost или дорогая сортировка сами по себе ничего не доказывают.

Надежная диагностика строится иначе:

  1. Получить план и runtime-метрики именно проблемного выполнения.
  2. Найти участок дерева, где резко вырос объем фактической работы.
  3. Проверить, есть ли до него существенное расхождение между оцененным и фактическим числом строк.
  4. Если такое расхождение есть, проверить, могло ли оно изменить порядок соединений, алгоритм join или способ доступа к данным.
  5. Если расхождения нет либо оно не объясняет задержку, проверить объем I/O, CPU, временные файлы, ожидания и работу вне плана.

Главный вопрос при чтении плана звучит не как «какой оператор выглядит плохо?», а как «почему СУБД решила выполнить именно столько работы именно этим способом?».

Что показывает execution plan

Execution plan – это дерево операторов, или узлов: отдельных действий вроде чтения таблицы, фильтрации, соединения, сортировки и агрегации. Листовые узлы получают строки из таблиц и индексов, промежуточные преобразуют или соединяют их, а корневой узел формирует результат. Визуальное направление дерева различается между инструментами, но поток данных логически идет от листьев к корню. SQL Server также описывает план как последовательность физических и логических операторов, выбранных для выполнения запроса (Execution Plan Overview).

Для чтения плана нужны четыре термина:

  • Кардинальность – число строк на некотором этапе плана.
  • Селективность – доля строк, прошедших условие. Чем меньше доля, тем селективнее предикат.
  • Оценка оптимизатора – прогноз количества строк и относительной стоимости операции, сделанный до выполнения.
  • Фактический план – план, дополненный runtime-данными конкретного запуска: фактическими строками, повторениями, временем и, в зависимости от СУБД, ресурсными показателями.

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

Особенно осторожно следует обращаться с Oracle EXPLAIN PLAN: Oracle прямо предупреждает, что такой план может не совпасть с планом, который фактически использует курсор, например из-за различий в окружении и bind-переменных (SQL Tuning Guide).

Оценки и измерения нельзя смешивать

Обычный EXPLAIN, estimated plan или EXPLAIN PLAN отвечает на вопрос: «Как оптимизатор предполагает выполнить запрос?». Он не выполняет запрос и не содержит достоверного фактического времени или количества строк.

Фактический план отвечает на другой вопрос: «Что произошло в этом запуске?». Даже он не устанавливает причину автоматически, но дает необходимые измерения.

Показатель Что он означает Чего он не доказывает
Estimated rows Сколько строк ожидал оптимизатор Сколько строк реально было прочитано или возвращено
Actual rows Сколько строк выдал оператор в конкретном формате плана Сколько строк он просмотрел, если отброшенные строки показаны отдельно
Loops / executions Сколько раз выполнялся оператор Что повторения были дорогими по отдельности
Estimated cost Внутренняя сравнительная оценка оптимизатора Миллисекунды, CPU time или физическое I/O
Actual time Измеренное время оператора или итератора в семантике конкретной СУБД Что это собственное время узла, что его можно напрямую сравнить с CPU time или что оно исключает работу дочерних операторов
Buffers / reads Объем работы с буферами или хранилищем Продолжительность I/O и наличие медленного диска
Temp read/write, spill Выход промежуточных данных во временное хранилище Что именно spill определил всю задержку

В PostgreSQL cost выражен в условных единицах модели планировщика, а rows обозначает ожидаемое число строк, выдаваемых узлом, а не обязательно число просмотренных строк (Using EXPLAIN). Следовательно, фраза «этот оператор занимает 80% стоимости» означает лишь, что модель приписала ему такую долю оценочной стоимости. Это не измерение времени.

Есть еще одна ловушка: время родительского узла нередко включает часть или всю работу дочерних узлов, а формат может показывать среднее на проход или значение по worker. Поэтому максимальное actual time не делает корень дерева узким местом, а вычитание времени соседних узлов без знания семантики формата ненадежно. Для причинного анализа сопоставляют иерархию операторов с числом проходов, объемом работы и метриками одного запуска; при параллельном плане отдельно уточняют, являются ли значения суммой, средним или показателем отдельного worker.

Получать фактический план нужно безопасно

В PostgreSQL EXPLAIN ANALYZE действительно выполняет анализируемый запрос; документация отдельно предупреждает о последствиях для изменяющих данные команд (EXPLAIN). MySQL 8.4 EXPLAIN ANALYZE также выполняет запрос и возвращает измерения итераторов, но поддерживается только для SELECT, TABLE и многостабличных UPDATE/DELETE, а не для любого DML, включая INSERT и однотабличные UPDATE/DELETE (MySQL 8.4 EXPLAIN Statement). В SQL Server и Oracle фактический план и runtime-статистику получают другими средствами, поэтому риск выполнения и условия сбора нужно проверять для конкретного инструмента и режима, а не выводить из слова ANALYZE.

Поэтому в PostgreSQL нельзя без проверки запускать инструментированный INSERT, UPDATE, DELETE или вызывающий побочные эффекты код в рабочей среде. То же правило относится к средствам других СУБД в тех режимах и для тех типов операторов, где инструментирование действительно выполняет команду. Транзакция с последующим откатом иногда защищает табличные изменения, но не является универсальной изоляцией от всех побочных эффектов. Безопасный способ зависит от СУБД, версии, типа оператора и устройства приложения.

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

Практический порядок расследования

1. Зафиксировать запрос, параметры и контекст

Сначала нужно удостовериться, что исследуется тот же SQL, то же значение параметров и по возможности тот же план, что использовались при медленном выполнении. Для параметризованных запросов это принципиально: распределение данных может делать разные планы подходящими для разных значений.

В SQL Server такое поведение рассматривается, в частности, механизмом Parameter Sensitive Plan optimization, который допускает несколько вариантов плана для разных диапазонов параметров (Parameter Sensitive Plan Optimization). Но общий диагностический принцип применим и к другим СУБД: один быстрый запуск с удобным параметром не опровергает проблему.

Стоит также отделить время планирования от времени выполнения. Если значительная задержка возникает при компиляции, перепланировании или hard parse, изменение оператора доступа не устранит ее. PostgreSQL, например, выводит Planning Time и Execution Time отдельно.

2. Найти объем фактической работы

План читают от источников строк к корню, отмечая:

  • сколько строк получил и выдал каждый оператор;
  • сколько строк было удалено фильтром;
  • сколько раз выполнялся узел;
  • где появились сортировка, хеширование, материализация или временные данные;
  • какие поддеревья прочитали особенно много страниц или строк;
  • где заметно выросло фактическое время.

Поле rows не имеет совершенно одинаковой семантики во всех форматах. Нужно различать строки, выданные оператором, строки, прочитанные из источника, и строки, отброшенные фильтром. Например, индексный оператор может вернуть одну строку, предварительно просмотрев значительно больше записей или страниц.

Отдельный случай – LIMIT и другие условия ранней остановки. Если верхнему оператору уже достаточно строк, дочерний узел может не пройти все строки, которые оптимизатор оценивал как потенциально доступные. Поэтому меньшее actual rows при LIMIT не является само по себе доказательством ошибки кардинальности: сначала нужно установить, была ли работа остановлена потребителем результата.

3. Обязательно учитывать повторения

Особенно часто недооценивают внутреннюю сторону Nested Loop. Быстрый индексный поиск становится дорогим, если запускается сотни тысяч раз.

Для PostgreSQL и иных форматов, где actual rows показано в среднем на один проход, ориентировочное общее число выданных строк вычисляют так:

[ \text{total rows} \approx \text{actual rows} \times \text{loops} ]

Допустим, в таком формате внутренний поиск возвращает в среднем 5 строк и имеет loops = 100000. Это не «оператор на пять строк», а примерно 500 000 строк результата за все проходы. Аналогично нужно интерпретировать время, если конкретный формат показывает его на одну итерацию. PostgreSQL усредняет значения времени и строк для многократно выполненного узла, чтобы итог после умножения на loops согласовывался с общим выполнением (Using EXPLAIN). В параллельном плане сначала нужно установить, относится ли значение к отдельному worker, среднему значению или суммарному узлу; умножение на loops допустимо только при подтвержденной семантике конкретного представления.

Для SQL Server и MySQL нужно отдельно проверять семантику Actual Rows, Actual Number of Executions и метрик итераторов в конкретном формате. Формулу нельзя автоматически переносить между СУБД и представлениями плана.

4. Сравнить estimated rows с actual rows, если это объясняет путь к задержке

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

Но это не обязательная причина медленного запроса. Оценки могут быть достаточно точными, а задержку всё равно создают большой, но ожидаемый объем данных, недостаток памяти, spill, блокировка, I/O, сеть или конкуренция за ресурсы. Если заметного расхождения нет, не нужно искусственно искать «ошибку кардинальности»: следующий шаг – проверить, какой измеренный ресурс или внешний фактор действительно объясняет время.

Универсального порога вроде «ошибка в десять раз всегда критична» нет. Оценка в 1 строку вместо 20 может не иметь значения. Оценка в 10 000 вместо 200 000 иногда тоже не меняет план. Важны одновременно:

  • абсолютное количество строк;
  • положение узла в дереве;
  • число повторений;
  • выбранный порядок соединений;
  • чувствительность алгоритма к размеру входа.

Модели оценки кардинальности опираются на статистические предположения, включая однородность, независимость и корреляцию данных; эти предположения не всегда соответствуют реальному распределению (Cardinality Estimation).

5. Проверить ресурсы и только затем формулировать причину

После локализации лишней работы нужно определить ее цену:

  • CPU – вычисления предикатов, агрегация, хеширование, сортировка;
  • логические чтения – обращения к страницам в буферном кэше;
  • физические чтения – получение данных из хранилища;
  • временные чтения и записи – spill сортировки или hash-операции;
  • память и параллелизм – с учетом инструментов конкретной СУБД.

Большое число buffer hits означает большой объем работы с буферным кэшем, но не медленный диск. И наоборот, сравнительно небольшой объем физических чтений может быть дорогим при высокой задержке хранилища. Счетчик блоков нужно сопоставлять со временем и системными метриками.

Пример: когда Nested Loop становится следствием, а не готовым диагнозом

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

Nested Loop  (actual time: 0.15..4200 ms, actual rows: 500000, loops: 1)
  buffers: shared hit=120000 read=4800

  -> Filtered Scan on orders
       estimated rows: 100
       actual rows: 100000
       actual time: 0.05..110 ms
       loops: 1

  -> Index Lookup on order_items
       estimated rows: 1
       actual rows: 5
       actual time: 0.01..0.03 ms
       loops: 100000

Это иллюстративные числа, а не фрагмент плана конкретной СУБД. Они предполагают формат, в котором actual rows и время повторяемого узла даны в среднем на одну итерацию, как в PostgreSQL. В таком формате около 0,03 ms на 100 000 проходов объясняют примерно три секунды суммарной работы внутренних lookup; при другом формате эту арифметику применять нельзя. В параллельном плане потребовалось бы еще установить, как агрегированы показатели worker.

Поверхностный вывод: «Nested Loop работает медленно, нужно заменить его на другой join».

Но дерево показывает более содержательную цепочку:

  1. Оптимизатор ожидал от orders 100 строк.
  2. Фактически оператор выдал 100 000 строк.
  3. Внутренний индексный поиск поэтому выполнился 100 000 раз.
  4. В сумме он вернул около 500 000 строк.
  5. Nested Loop является местом накопления работы; недооценка внешнего входа – правдоподобная гипотеза о том, почему выбранный путь оказался дорогим.

Такой профиль поддерживает гипотезу «неверная оценка кардинальности могла привести к неподходящему решению о соединении», но сам по себе не доказывает её и не доказывает, что другой алгоритм join был бы быстрее. Чтобы перейти от гипотезы к причинному выводу, нужно сопоставить тот же запрос и параметры с альтернативой, полученной контролируемым способом, и проверить, не объясняют ли задержку I/O, блокировки, cache state или внешний компонент. Только после этого имеет смысл рассматривать изменение статистики, индекса или запроса.

Следовательно, вывод имеет три уровня:

  • наблюдается: внешний оператор выдал намного больше строк, а внутренний lookup многократно повторялся;
  • поддерживается как гипотеза: недооценка внешнего входа могла повлиять на выбор соединения;
  • требует отдельной проверки: источник ошибки оценки и польза конкретного изменения.

Именно это различие удерживает анализ от преждевременного «исправления» join hint или индекса.

Типовые симптомы и проверяемые гипотезы

Видимый симптом Возможная гипотеза Что нужно проверить
Seq Scan или Full Table Scan Прочитано слишком много ненужных данных Размер отношения, actual rows, отброшенные строки, селективность, страницы и время чтения
Index Scan Много случайных обращений или lookup для большого результата Число выполнений, строки на проход, логические чтения, покрытие нужных столбцов
Высокий cost или процент на диаграмме Оптимизатор считает поддерево дорогим Фактическое время, CPU, I/O и различия estimated/actual rows
Nested Loop Внутренняя сторона выполняется слишком много раз loops или executions, размер внешнего входа, оценка его кардинальности
Sort или Hash с spill Промежуточный набор не поместился в выделенный ресурс Фактические строки и их ширина, temp read/write, объем spill и вклад во время
Большое число buffers Запрос обрабатывает много страниц Тип чтений, cache state, elapsed time, повторения и внешняя latency
Большая сортировка Сортируется слишком большой промежуточный результат Где строки могли быть отфильтрованы раньше и почему оптимизатор ожидал другой объем

Полное сканирование небольшой таблицы или таблицы, из которой нужна значительная доля строк, может быть рациональным. Индексный доступ к большой части таблицы, напротив, иногда выполняет больше работы из-за многочисленных обращений. Тип scan – это решение, которое нужно объяснить, а не готовый диагноз.

Откуда берутся ошибки кардинальности

Статистика планировщика представляет данные приближенно. В PostgreSQL она включает, среди прочего, частые значения, гистограммы и сведения о различимости; объем и качество этих данных ограничены настройками сбора статистики (Statistics Used by the Planner).

Расхождение estimated и actual rows может возникнуть из-за нескольких причин:

  • данные изменились после сбора статистики;
  • распределение сильно перекошено;
  • нужное значение редко и недостаточно представлено в статистике;
  • два предиката коррелируют, а модель считает их независимыми;
  • условие содержит выражение или функцию, которую трудно оценить;
  • конкретное значение параметра нетипично;
  • изменилась версия оптимизатора, compatibility level или среда компиляции.

В PostgreSQL многомерная статистика может улучшать оценки для связанных столбцов. Документация показывает, как статистика функциональных зависимостей и сочетаний значений исправляет некоторые ошибки, вызванные предположением о независимости условий (Multivariate Statistics Examples). Это целевое средство для определенного типа ошибки, а не универсальное лечение плохих планов.

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

Когда причины нет в плане

Execution plan хорошо описывает работу операторов, но не весь путь запроса. Задержка может возникать из-за:

  • ожидания блокировки;
  • конкуренции за CPU, память или I/O;
  • лимита ресурсов;
  • сетевой передачи большого результата;
  • медленного чтения результата клиентом;
  • фоновой нагрузки;
  • ожиданий внутри внешнего или удаленного компонента.

Если elapsed time значительно больше времени, которое удается объяснить измерениями операторов и ресурсной работой того же запуска, анализ нужно расширить до wait statistics, блокировок и системных метрик. Нельзя механически вычитать CPU time из elapsed time: при ожиданиях, параллелизме и различной семантике счетчиков эти величины не образуют простой баланс. Руководство Microsoft по медленным запросам также разделяет проблемы плана, ожидания блокировок, CPU, I/O и другие системные причины (Troubleshoot Slow-Running Queries).

Отсутствие предупреждения в плане не доказывает отсутствие внешней причины. Фактический план конкретного запуска также зависит от прогретости кэша и текущей нагрузки. Холодный и повторный запуск могут использовать одинаковое дерево, но иметь различное время.

Как получить доказательства в разных СУБД

Названия и семантика полей различаются, поэтому нельзя буквально переносить интерпретацию одного формата на другой.

СУБД План без выполнения Данные фактического выполнения Существенная оговорка
PostgreSQL EXPLAIN EXPLAIN (ANALYZE, BUFFERS) и дополнительные параметры ANALYZE выполняет оператор; значения повторяемых узлов нужно читать с учетом loops
SQL Server Estimated execution plan Actual execution plan Нужно анализировать план конкретного выполнения и actual rows/executions, а не только проценты estimated cost (Display and save execution plans)
Oracle Database EXPLAIN PLAN План реально использованного курсора через DBMS_XPLAN.DISPLAY_CURSOR, обычно с форматом вроде 'ALLSTATS LAST', после предварительного сбора runtime-статистики, например с /*+ GATHER_PLAN_STATISTICS */ или подходящим STATISTICS_LEVEL DBMS_XPLAN сам по себе не добавляет фактические строки и время к любому курсору. EXPLAIN PLAN может отличаться от реально использованного плана, а доступность runtime-полей зависит от способа сбора, версии и прав (DBMS_XPLAN)
MySQL 8.x EXPLAIN EXPLAIN ANALYZE для поддерживаемых типов операторов Вывод описывает итераторы; actual time, rows и loops нужно трактовать в терминах iterator execution. В MySQL 8.4 команда поддерживает SELECT, TABLE и multi-table UPDATE/DELETE, но не любой DML (MySQL 8.4 EXPLAIN Statement)

Команды из таблицы показывают направление анализа, но не заменяют проверку безопасности выполнения и документации конкретной версии.

Как сформулировать доказательный вывод

Хорошее заключение по плану связывает наблюдение, механизм и границы уверенности. Например:

В данном выполнении оператор фильтра вернул 100 000 строк вместо ожидаемых 100. Из-за этого внутренний индексный поиск был запущен 100 000 раз и сформировал около 500 000 строк. Это объясняет основную часть работы поддерева и указывает на ошибку оценки кардинальности перед выбором соединения. Причина самой ошибки оценки пока не установлена; нужно проверить распределение данных, статистику, корреляцию предикатов и чувствительность к параметру.

Такой вывод сильнее утверждения «виноват Nested Loop», потому что его можно проверить повторным измерением. Он также не обещает больше, чем показывают данные.

Перед изменением индекса, hint, запроса или конфигурации полезно проверить четыре пункта:

  • анализируется фактически использованный план с нужными параметрами;
  • учтены actual rows, повторения и иерархия времени;
  • найдено первое существенное расхождение перед ростом работы;
  • задержка сопоставлена с CPU, I/O, временными данными и внешними ожиданиями.

План становится причинным инструментом только при сравнении гипотез оптимизатора с результатами выполнения. Без этого любой заметный оператор остается симптомом, а предлагаемое исправление – догадкой.