Как прочитать план EXPLAIN ANALYZE и найти в нем узел, на который уходит время?

ЗнаниеMiddleЧасто

Проверила команда СопроводОбновлено

Короткий ответ

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

Почему этот вопрос задают

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

Как рассуждать

  1. Объясните разницу: EXPLAIN показывает план, EXPLAIN ANALYZE выполняет запрос и добавляет факт
  2. Читайте дерево снизу вверх: от чтения таблиц к соединениям и сортировке
  3. Считайте собственное время узла: его время минус время детей, с учетом loops
  4. Сравните ожидаемое и фактическое число строк: большой разрыв указывает на устаревшую статистику

Ответ с примером

EXPLAIN показывает план, который выбрал планировщик, с оценками стоимости и числа строк. EXPLAIN ANALYZE выполняет запрос и рядом с оценкой пишет факт: actual time=0.03..5980.12 — время до первой и до последней строки в миллисекундах, rows — сколько строк вернул узел, loops — сколько раз он запускался. Время узла включает его детей, а при loops больше 1 время и строки показаны на один запуск.

Каршеринг строит отчет по поездкам машин из Казани, и запрос идет 7,4 секунды. Код показывает дерево плана, таблица — цифры EXPLAIN ANALYZE с рабочей базы. Из 7 412 мс почти 6 000 уходят на Seq Scan по trips: запрос читает 12,3 миллиона поездок, чтобы после соединения оставить 4,1 миллиона. Дальше выбирают лечение: фильтр по периоду с индексом, секции по месяцам или витрину.

Узелactual time до последней строки, мсrowsloops
Sort7 41231
HashAggregate7 41031
Hash Join7 1054 100 0001
Seq Scan on trips t5 98012 300 0001
Hash0,91 1501
Seq Scan on cars c0,71 1501
SQL
EXPLAIN (COSTS OFF)
SELECT c.model, count(*) AS trips, sum(t.distance_km) AS km
FROM trips t
JOIN cars c ON c.id = t.car_id
WHERE c.city = 'Казань'
GROUP BY c.model
ORDER BY km DESC;
Вывод
                        QUERY PLAN
-----------------------------------------------------------
 Sort
   Sort Key: (sum(t.distance_km)) DESC
   ->  HashAggregate
         Group Key: c.model
         ->  Hash Join
               Hash Cond: (t.car_id = c.id)
               ->  Seq Scan on trips t
               ->  Hash
                     ->  Seq Scan on cars c
                           Filter: (city = 'Казань'::text)
(10 rows)

Как ответить сильнее

Что добавит опытный кандидат

Что спросят следом

Доступно по подписке

Приемы опытных и готовые ответы

Частые ошибки

  • «Самый медленный узел — тот, у которого больше всего actual time»

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

  • «Если в плане Seq Scan, запрос плохой»

    Скажите, что для маленьких таблиц и выборок большой доли строк Seq Scan быстрее индекса. Смотрите на время узла и число отброшенных строк

  • «Цифры cost — это миллисекунды»

    Уточните, что cost — условные единицы планировщика. Реальное время в миллисекундах показывает actual time после EXPLAIN ANALYZE

  • «Запущу EXPLAIN ANALYZE на DELETE, чтобы посмотреть план»

    Предупредите, что команда выполнится по-настоящему. Оберните ее в BEGIN и ROLLBACK или смотрите план через обычный EXPLAIN

Сигналы для интервьюера

Сильный ответ

Идет по дереву снизу вверх, считает собственное время узлов с учетом loops и замечает разрыв между оценкой и фактом

Тревожный сигнал

Знает, что Seq Scan бывает медленным, но смотрит только на верхний узел и не учитывает loops

Красный флаг

Делает выводы по плану с пустой тестовой базы и не сверяет его с планом на рабочем объеме

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

  • Чем EXPLAIN отличается от EXPLAIN ANALYZE?
  • Как найти в плане запроса PostgreSQL самую медленную операцию?
  • Что означают actual time, rows и loops в EXPLAIN ANALYZE?
  • Как понять по плану, почему запрос работает медленно?

Похожие вопросы

ЗнаниеJuniorЧасто

Что такое индекс и почему не стоит создавать его на каждый столбец?

Индекс — отдельная структура, в которой значения столбца хранятся упорядоченно вместе с адресами строк. По умолчанию в PostgreSQL это B-дерево: поиск по нему проходит несколько шагов вместо чтения всей таблицы. Индекс на каждый столбец вредит: вставка пишет во все индексы, обновление индексированного столбца тоже, индексы занимают место, а для условия на большую долю строк планировщик индекс даже не возьмет.

  • индексы
  • PostgreSQL
275
ЗнаниеMiddleИногда

Почему запрос с LIKE '%…' или функцией от столбца в WHERE не использует индекс?

B-tree хранит значения столбца отсортированными и находит строки по началу значения. У LIKE '%ова' начало неизвестно, а extract(year FROM birth_date) — другое значение, которого в индексе нет, поэтому планировщик читает таблицу целиком. Помогают условие по самому столбцу, индекс по выражению, класс text_pattern_ops для префикса или триграммы pg_trgm.

  • индексы
  • LIKE
177
ЗнаниеSeniorИногда

Чем материализованное представление отличается от обычного и когда оно ускорит отчет по таблице событий?

Обычное представление хранит только текст запроса: каждый раз, когда дашборд его открывает, PostgreSQL заново считает агрегат по всей таблице событий. Материализованное хранит готовый результат, и отчет читает тысячи строк вместо сотен миллионов. Цена — данные на момент последнего REFRESH. Такое представление окупается, когда тяжелый агрегат читают часто, а задержка в час или сутки допустима.

  • оптимизация запросов
  • PostgreSQL
195
ЗадачаMiddleЧасто

Выведите выручку по месяцам и ее изменение к предыдущему месяцу в процентах

Сначала сверните поездки в помесячную выручку, потом возьмите выручку прошлого месяца окном LAG(revenue) OVER (ORDER BY month). Изменение в процентах — 100.0 * (revenue - prev) / prev, знаменатель оберните в NULLIF(prev, 0). У первого месяца сравнивать не с чем, поэтому там остается NULL: ноль или 100% исказили бы график.

  • LAG
  • выручка
  • оконные функции
245

Частые вопросы по теме

Короткие ответы на частые вопросы по этой теме.

Достаточно основных: Seq Scan, Index Scan, Index Only Scan, Bitmap Heap Scan, три вида соединений — Nested Loop, Hash Join, Merge Join — и Sort с HashAggregate. Для каждого скажите, когда планировщик его выбирает и чем он плох на больших данных.

Создайте таблицу на миллион строк через generate_series и сравните планы одного запроса без индекса, с индексом и после ANALYZE. Проговаривайте вслух, какой узел сколько занял и почему планировщик выбрал именно его.

Посмотрите на блокировки и на объем результата. Иногда запрос ждет чужую блокировку или передает клиенту миллионы строк, и план тут ни при чем. Во время выполнения проверьте pg_stat_activity.

Общие вопросы на интервью

Общий вопросС опытомЧасто

Почему вы хотите работать у нас?

Назовите две-три причины, которые связывают вас именно с этой компанией: продукт, задачи, масштаб, команда. Каждую подкрепите фактом, который нашли сами: новость, проект, текст вакансии, свой опыт клиента. Потом покажите, что принесете со своей стороны. Слова о стабильности и известном бренде подходят любой компании, поэтому на них ответ не держится.

  • мотивация
  • о компании
290
Общий вопросБез опытаЧасто

Какие у вас сильные стороны?

Хватит двух-трех сильных сторон, которые нужны на этой работе, если к каждой есть пример: что сделали и какой был результат. Берите качества из текста вакансии, а не из списка «ответственный, коммуникабельный». Без опыта примеры ищите в учебе, практике и подработке. Говорите спокойно, без лишней скромности и без хвастовства.

  • сильные стороны
  • без опыта
276
Общий вопросБез опытаЧасто

Кем вы видите себя через 5 лет?

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

  • мотивация
  • карьерные планы
289
ИсторияС опытомЧасто

Расскажите о конфликте с коллегой: из-за чего он возник и как вы его решили?

Расскажите один реальный конфликт по рабочему вопросу: из-за чего разошлись, чего хотела каждая сторона и как вы договорились. Говорите спокойно, без оценок коллеги. Покажите, что вы искали решение, где-то уступили и сохранили рабочие отношения. Закончите результатом и тем, что теперь делаете, чтобы похожий спор не повторился.

  • конфликты
  • работа в команде
252
ИсторияС опытомЧасто

Каким профессиональным достижением вы гордитесь больше всего?

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

  • достижения
  • результат в цифрах
276
Общий вопросБез опытаЧасто

Опыта работы еще нет, а интервьюер просит рассказать о себе: что вы скажете?

Начните с того, кто вы сейчас и на какую работу пришли. Вместо стажа расскажите об учебе, практике и подработке, которые похожи на задачи вакансии, и назовите один результат с цифрой. Закончите тем, почему хотите начать карьеру именно здесь. Уложитесь в одну-две минуты и не пересказывайте резюме по порядку.

  • о себе
  • без опыта
244

Вопросы руководителю на интервью

ИсторияРуководительЧасто

Как вы делегируете задачи: что отдаете команде, а что оставляете себе?

Назовите свое правило: что вы отдаете команде и что оставляете себе, и покажите его на одной задаче. Расскажите, кому и почему передали, какие полномочия и рамки дали, как проверяли без мелкого контроля. Закончите честным итогом: что получилось хуже или медленнее, чем у вас, и что вы выиграли.

  • делегирование
  • команда
255
КейсРуководительЧасто

Два сотрудника вашей команды месяц не разговаривают друг с другом, и общий отчет уже сорвал срок — что сделаете?

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

  • конфликт
  • ответственность
242
ИсторияРуководительЧасто

Расскажите о сотруднике, которого вы вырастили: каким он пришел и чего добился?

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

  • развитие сотрудников
  • наставничество
303
ИсторияРуководительЧасто

Расскажите о цели, которую ваша команда не выполнила: когда вы это поняли и что сделали?

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

  • цели
  • провал
241
Общий вопросРуководительЧасто

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

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

  • показатели
  • цели
216
ИсторияРуководительЧасто

Расскажите, как вы разложили цель компании на задачи для своей команды

Возьмите одну цель компании с цифрой и сроком и покажите, как вы ее разбили: на какие части, по какому признаку, какие личные цели получил каждый сотрудник. Расскажите, как следили за ходом и что меняли по дороге. Закончите итогом план-факт честно, даже если цель выполнена не целиком.

  • цели
  • планирование
227