PostgreSQL EXPLAIN

При работе с MySql’овским EXPLAIN’ОМ всё просто (а может просто привычно).
Postgres предоставляет больше информации, но с ним сложнее разобраться.
Далее — вольный перевод статьи про PostgreSQL EXPLAIN (ссылка на оригинал в конце статьи).

Разберём небольшой пример:

Это означает, что оптимизатор решил произвести последовательное чтение записей из таблицы. Он также оценил,что первая строка будет выведена через 0.00, а все — через 60.48. При этом будет возвращено 2048 строк средней длиной 107 байт.
EXPLAIN не запускает сам запрос, это лишь оценка. Для получения точных результатов нужно запустить EXPLAIN ANALYZE:

Теперь появился второй набор данных: реальное время, затраченное на выполнение последовательного чтения, реальное число строк и количество повторов чтения этих строк (про повторы позже). Также появилась информация о полном времени выполнения запроса.
Можно заметить, что числа в реальном времени не очень то соответствуют предсказанным. Это потому, что оценка измеряется не во времени, а в специальных единицах. Одна единица равна времени, затраченному на последовательное чтение одной страницы с диска.
Но это слишком скучный запрос, перейдём к более интересным.

Теперь план запроса включает 2 шага: сортировку и последовательное чтение. Хотя это и кажется нелогичным, запрос выполняется снизу вверх (если смотреть на план), т.е. результат последовательного чтения подаётся на вход оператору сортировки (оператор сортировки использует результаты последовательного чтения как входные данные).
Если взглянуть на шаг сортировки, можно заметить, что оптимизатор говорит, по какому полю проводится сортировка («Sort Key»). Разные виды обработки могут выводить дополнительную информацию вроде этой. Также стоит обратить внимание на то, что затраты на вывод первой строки оператором сортировки очень велики, они почти равны затратам на вывод всех столбцов. Это из-за того, что сначала он должен отсортировать все строки, что и занимает большую часть времени.
Если шагов выполнения запроса несколько, то показываемые затраты на шаг содержат не только затраты на выполнение этого шага, но и всех шагов ниже. В нашем примере затраты на шаг сортировки равны 173.12-60.48 для вывода первого результата, и 178.24-60.48 для вывода всех результатов. 60.48 вычитается из-за того, что сортировке необходимо получить все данные от последовательного чтения, прежде чем вернуть хоть что-то. В общем, когда видите очень похожие числа в затратах вывода первого и всех результатов, это означает, что операции требуются все данные предыдущих операций.

Теперь рассмотрим ещё более интересный пример:

Наконец-то что-то похожее на настоящий запрос. Обратите внимание на отступы. Они используются, чтобы показать, что результаты одних шагов используются в качестве входных данных для других. В нашем примере можно увидеть, что hash join принимает на вход результаты выполнения последовательного чтения и hash-операции. Последняя, в свою очередь, сама работает с результатами последовательного чтения. Заметьте, что hash-операция имеет одинаковые затраты на вывод первого и всех результатов — ей нужны все результаты до того, как вернуть что-либо. В этом плане интересно то, что затраты на вывод первого результата hash join зависит от суммарных затрат hash-операции, но начинается одновременно с последовательным чтением таблицы customer. Это потому, что hash join может возвращать результаты сразу, как только получит первый результат от обеих операций, дающих ему данные на вход.

Вернёмся к объяснению того, что такое повторы или циклы (loops), для этого рассмотрим пример:

Вложенный цикл — это то, что хорошо знакомо программистам, пишущим на процедурных языках; это что-то вроде:

Если в input_a 4 элемента, то внутренний цикл ( по input_b) будет выполнен 4 раза. Это именно то, о чём нам говорит оптимизатор в примере выше. Если умножить 0.055 на 4, то получится чуть меньше, чем разница между общим временем выполнения hash join’а и общим временем выполнения вложенного цикла (остаток времени скорее всего пошёл на накладные расходы, связанные с вычислением всего этого).

Что же это означает на практике? Обычно запрос запускают с EXPLAIN, пытаясь улучшить производительность. Ключом к этому является поиск шага, который занимает большую часть времени (а потом надо попытаться переписать медленную часть или весь запрос). Давайте взглянем на следующий пример и попытаемся найти проблемный шаг. (Этот запрос может запустить каждый и получит такой же результат).

Вложенный цикл имеет самое большое время выполнения — 20.035мс. Он, в свою очередь, использует результаты другого вложенного цикла и последовательного чтения, и уже этот вложенный цикл — самый долгий (19.481 мс).
Следовательно, сейчас «путь» к «дорогому» шагу следующий:

В этом примере эти шаги расположены последовательно, но это не всегда так.
Нижний вложенный цикл берёт данные отсюда:

Заметим, что большую часть времени занимает hash join. Он берёт данные из последовательного чтения и hash-операции. Эта hash-операция занимает большую часть времени:

Мы наконец нашли самую дорогую часть запроса: сканирование индекса pg_class_relname_nsp_index. К сожалению, практически невозможно ускорить сканирование индекса для поиска одной строки. Но и занимает оно всего 18.464 мс, вряд ли когда-нибудь понадобится настолько улучшать производительность.
Ещё одно замечание: затраты на EXPLAIN ANALYZE достаточно значительны. Они могут занимать до 30% времени выполнения запроса. Поэтому EXPLAIN — это инструмент для измерения относительной производительности, а не абсолютной.

Оригинал статьи — Introduction to VACUUM, ANALYZE, EXPLAIN, and COUNT

LEAVE A COMMENT