30.1 Оценка против факта
EXPLAIN показывает план и оценки. EXPLAIN ANALYZE вдобавок
выполняет запрос и показывает факт:
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tickets WHERE flight_id = 7;
Index Scan on tickets (cost=0.29..8.31 rows=1 width=...)
(actual time=0.018..0.020 rows=1 loops=1)
Index Cond: (flight_id = 7)
Buffers: shared hit=3
Planning Time: 0.1 ms
Execution Time: 0.04 ms
Два набора чисел в скобках: cost/rows - оценка планировщика,
actual time/rows - что вышло на самом деле. Расследование -
это всегда поиск узла, где эти два набора расходятся сильнее всего.
Важно: EXPLAIN ANALYZE действительно выполняет запрос. Для
SELECT это безопасно, для UPDATE/DELETE/INSERT - нет:
изменения применятся. Чтобы посмотреть план таких команд без
побочного эффекта, оберни в транзакцию и откати:
BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK;.
30.2 Поля узла: time, rows, loops
У каждого узла в actual три ключевых поля:
actual time=0.018..0.020- время до первой строки и до последней, в миллисекундах, на один проход узла;rows=1- сколько строк узел реально вернул за один проход;loops=1- сколько раз узел был выполнен.
loops коварен. У внутренней стороны nested loop он равен числу
внешних строк. Время и строки в actual даны за один loop -
чтобы получить полное, надо умножить на loops:
-> Index Scan on tickets (actual time=0.005..0.006 rows=2 loops=5000)
Здесь не 2 строки и 0.006 мс, а 2 × 5000 = 10000 строк и
0.006 × 5000 = 30 мс суммарно. Не учтёшь loops - решишь, что
узел дешёвый, хотя он съел всё время.
30.3 Расхождение rows - главный след
Самый ценный сигнал - расхождение rows (оценка) и rows (факт).
Маленькое расхождение нормально. Расхождение в десятки и сотни раз
означает, что планировщик строил план под неверный размер - и почти
наверняка выбрал неподходящий алгоритм.
Hash Join (... rows=10 ...) (actual ... rows=48000 ...)
Планировщик ждал 10 строк, получил 48 000. Под «10» он мог выбрать nested loop, который на 48 000 строк катастрофичен. Корень - не в hash join, а в оценке: либо устаревшая статистика (см. planner-statistics), либо коррелированные колонки, либо условие, которое планировщик не умеет оценивать точно. Чинить надо источник расхождения, а не симптом наверху.
30.4 BUFFERS: где уходит ввод-вывод
EXPLAIN (ANALYZE, BUFFERS) добавляет к каждому узлу, сколько
страниц он трогал:
Buffers: shared hit=128 read=4096
shared hit - страницы нашлись в буферном кеше (дёшево). shared read - пришлось читать с диска (дорого). Большой read на узле
означает, что данные не в кеше и упор в диск. Это отделяет «медленно,
потому что много вычислений» от «медленно, потому что бьём по диску»
- разные болезни, разное лечение. По
BUFFERSвидно и пользу Index-Only Scan: если он не трогает страницы таблицы, в его поддереве не будет лишних чтений heap.
30.5 Типовые виновники
Несколько шаблонов, которые встречаются снова и снова.
Каст слева от оператора. WHERE created::date = '2024-01-01'
оборачивает индексированную колонку в функцию - индекс по created
перестаёт подходить, и план уходит в Seq Scan. Лечение - переписать
условие на диапазон, не трогая колонку:
created >= '2024-01-01' AND created < '2024-01-02'. Это свойство
называется sargability, ему посвящена sargability.
Функция над колонкой. WHERE lower(email) = 'a@b.c' - то же
самое: индекс по email не подходит. Либо индекс по выражению
lower(email), либо хранить уже нормализованное значение.
Устаревшая статистика. Оценка rows далека от факта, после
ANALYZE план чинится. Первое, что проверяют.
Оптимизационный барьер. OFFSET 0 в подзапросе или
материализованный CTE (WITH ... AS MATERIALIZED) запрещают
планировщику сплющить подзапрос - иногда это спасает от плохого
плана, иногда мешает. Полезно знать как инструмент в обе стороны.
30.5.1 Подводный камень: время в actual - на один loop
Повторим отдельно, потому что на этом спотыкаются чаще всего.
Когда видишь «дешёвый» узел глубоко внутри nested loop, проверь
loops. Узел с actual time=0.01 ... loops=100000 - это не 0.01 мс,
а потенциально секунда суммарно.
Многие инструменты визуализации планов уже умножают за тебя и
показывают «total time per node». Но читая голый текстовый
EXPLAIN ANALYZE, держи в голове: время и строки в строке узла -
это среднее на один проход, а проходов может быть много. Узел,
съедающий запрос, нередко выглядит безобидно именно из-за этого.
30.6 Алгоритм расследования
Сводим всё в порядок действий.
- Возьми
EXPLAIN (ANALYZE, BUFFERS), а не голыйEXPLAIN- нужен факт, не только оценка. - Найди узел с наибольшим расхождением
rowsоценка/факт. Это кандидат в корень. - Проверь, нет ли там убивающего индекс шаблона: каст или функция над колонкой, неявное приведение типа.
- Запусти
ANALYZEзадействованных таблиц и посмотри, не сошлась ли оценка с фактом - устаревшая статистика лидер по частоте. - Если оценка верна, а план всё равно плох - смотри на
loopsиBUFFERS: где реально уходит время. - Меняй одно за раз и сравнивай планы до/после. Финальный чеклист оптимизации соберём в капстоуне.
Главный принцип - не угадывать, а читать. План говорит, где проблема; задача - научиться его слушать.
Уроки в sandbox
lab-30.1. Расследовать плохой запрос и починить до Index Scan
Возьмём запрос с ::date-кастом, который убивает индекс, прочитаем EXPLAIN ANALYZE и починим. Перед фиксом предскажи, сменится ли тип узла с Seq Scan на Index Scan.
Подготовь:
CREATE TABLE ev AS SELECT g id, now() - (g || ' minutes')::interval AS at FROM generate_series(1,200000) g; CREATE INDEX ON ev(at); ANALYZE ev;.Плохой запрос:
EXPLAIN ANALYZE SELECT * FROM ev WHERE at::date = current_date;. Заметь Seq Scan - индекс по at не подошёл из-за каста.Найди расхождение estimated/actual rows и объясни, почему индекс не использован (каст оборачивает колонку).
Перепиши на диапазон:
EXPLAIN ANALYZE SELECT * FROM ev WHERE at >= current_date AND at < current_date + 1;. Предскажи и проверь смену на Index/Bitmap Scan.Сравни Execution Time двух вариантов и Buffers (shared read) - sargable-условие читает меньше.
Сломай статистику: вставь много строк без ANALYZE, посмотри расхождение rows, затем
ANALYZE ev;и убедись, что оценка сошлась с фактом.
sandbox с автопроверкой - открыть в песочнице
Резюме
- EXPLAIN ANALYZE выполняет запрос и кладёт рядом оценку (cost/rows) и факт (actual time/rows); расследование - поиск узла с наибольшим расхождением.
- Для SELECT это безопасно, для UPDATE/DELETE/INSERT - выполняется по-настоящему; смотри план таких команд внутри BEGIN ... ROLLBACK.
- actual time и rows даны на один проход узла; чтобы получить полное, умножай на loops - иначе «дешёвый» внутренний узел обманет.
- Главный след плохого плана - расхождение estimated и actual rows в десятки/сотни раз: планировщик строил план под неверный размер.
- BUFFERS отделяет упор в диск (shared read) от упора в вычисления (shared hit) - это разные болезни с разным лечением.
- Типовые виновники: каст или функция над индексированной колонкой (убивают индекс), устаревшая статистика, неявное приведение типа, оптимизационные барьеры.
- Алгоритм: взять ANALYZE+BUFFERS, найти расхождение rows, проверить sargability, обновить статистику, посмотреть loops/BUFFERS, менять по одному.
Контрольные вопросы
Почему `WHERE created::date = '2024-01-01'` уводит запрос в Seq Scan, даже если есть индекс по `created`?
Показать ответ
Потому что
created::date- это функция над колонкой, а не сама колонка. Индекс построен по значениямcreated(timestamptz), а условие сравнивает результат приведенияcreatedк date. Чтобы использовать индекс, PostgreSQL должен был бы вычислить выражение для каждой строки - то есть индекс не подходит, и план уходит в Seq Scan. Лечение - переписать на диапазон по самой колонке:created >= '2024-01-01' AND created < '2024-01-02'. Тогда условие sargable и индекс работает.В плане узел показывает `actual time=0.01 rows=2 loops=50000`. Сколько времени и строк это на самом деле?
Показать ответ
Время и строки в actual даны на один проход (loop), а проходов здесь 50000. Значит суммарно узел вернул 2 × 50000 = 100000 строк и потратил примерно 0.01 × 50000 = 500 мс. Сам по себе он выглядит мизерным (0.01 мс), но с учётом loops может оказаться самым дорогим в плане. Поэтому, читая EXPLAIN ANALYZE, всегда умножай на loops, особенно у внутренней стороны nested loop.
Какой признак в EXPLAIN ANALYZE точнее всего указывает на корень проблемы?
Показать ответ
Наибольшее расхождение между estimated rows (оценка) и actual rows (факт). Маленькое расхождение нормально, но разница в десятки-сотни раз означает, что планировщик строил план под неверный размер входа и почти наверняка выбрал неподходящий алгоритм соединения или метод доступа. Узел с таким расхождением - кандидат в корень, и чинить надо его источник (статистику, корреляцию, sargability), а не симптом, который виден выше по дереву.
Чем различаются `shared hit` и `shared read` в выводе BUFFERS?
Показать ответ
shared hit- страницы, которые нашлись в буферном кеше; их чтение почти бесплатно.shared read- страницы, которых в кеше не было и пришлось читать с диска; это дорого. Большойreadна узле говорит, что запрос упирается в диск, а не в процессор, - значит, либо данных больше, чем кеша, либо запрос читает лишнее. Это отделяет проблему ввода-вывода от проблемы вычислений и направляет, что чинить.Безопасно ли запускать `EXPLAIN ANALYZE` на UPDATE?
Показать ответ
Нет, без предосторожности. EXPLAIN ANALYZE реально выполняет запрос, поэтому для UPDATE/DELETE/INSERT изменения применятся к данным. Чтобы увидеть план и фактические числа без побочного эффекта, оберни команду в транзакцию и откати её:
BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK;. Тогда запрос выполнится для измерения, но изменения не зафиксируются. Для чистого SELECT таких предосторожностей не нужно.