В прошлом уроке мы научились снимать план через EXPLAIN. Но голый текстовый план на сотню строк пугает, и глаз цепляется не за то. Этот урок про два навыка сразу: как загнать план в визуализатор explain.tensor.ru и за минуту увидеть проблемное место, и как читать сам EXPLAIN ANALYZE руками - чтобы понимать, что именно подсветил сервис и почему. Разберём cost против реального времени, узлы плана, буферы и пройдём живой сценарий ускорения запроса на демобазе Авиаперевозки.
Как это работает
explain.tensor.ru - это бесплатный русскоязычный веб-сервис анализа планов postgresql от компании Tensor (та самая, что делает СБИС). Идея простая: вы вставляете текст плана, а сервис строит наглядное дерево узлов, считает долю времени по каждому узлу, подсвечивает самый тяжёлый и дописывает рекомендации. То, на что вручную уходит десять минут разглядывания, тут видно сразу.
Важно понимать: сервис ничего не выполняет и к вашей базе не подключается. Он разбирает уже готовый текст плана. Значит, качество разбора целиком зависит от того, насколько полный план вы сняли. Снимете без буферов - не будет анализа ввода-вывода. Снимете без ANALYZE - не будет реального времени, только оценки планировщика.
Ключевая развилка в чтении любого плана: cost против actual time. Cost - это абстрактная оценка планировщика в условных единицах (примерно сколько страниц и строк он ждёт обработать), она нужна оптимизатору, чтобы выбрать план. Actual time - реальные миллисекунды выполнения. Ускоряем мы запрос по фактическому времени, а не по cost. Большое расхождение между ожидаемыми и фактическими строками (rows) - первый сигнал, что у планировщика устаревшая статистика и пора делать ANALYZE таблицы.
SQL и примеры
Сначала правильно снимаем план. Три настройки делают его пригодным для глубокого explain analyze разбора:
Код: Выделить всё
SET track_io_timing = TRUE;
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM flights WHERE flight_no = 'PG0001';
Осторожно с пишущими запросами. ANALYZE действительно выполняет INSERT, UPDATE, DELETE. Чтобы снять план и не менять данные, оборачивайте в транзакцию с откатом:
Код: Выделить всё
BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
DELETE FROM boarding_passes WHERE ticket_no = '0005432000284';
ROLLBACK;
Теперь главное - какие узлы вы увидите в дереве и что они значат:
- Seq Scan - последовательное чтение всей таблицы. По большой таблице без индекса это частый виновник торможения.
- Index Scan - проход по индексу плюс обращение к таблице за остальными колонками.
- Index Only Scan - данные взяты только из индекса, в таблицу не ходим. Самый дешёвый доступ.
- Bitmap Index Scan + Bitmap Heap Scan - пара для выборки многих строк: сначала строим битовую карту страниц по индексу, потом читаем страницы пачкой.
- Nested Loop - для каждой строки внешнего набора ищем совпадения во внутреннем. Хорош на малых объёмах, опасен при большом числе loops.
- Hash Join - строим хэш по меньшей таблице, прогоняем большую. Рабочая лошадка для крупных соединений.
- Merge Join - слияние двух отсортированных потоков.
- Sort, Aggregate, Gather - сортировка, агрегация и сбор результатов параллельных воркеров.
Про BUFFERS детально, это часто недооценивают. shared hit - страницы взяты из кэша shared_buffers, это быстро. shared read - страницы читались мимо кэша, с диска или из кэша ОС, вот оно реальное I/O. local - буферы временных таблиц. temp - временные данные на диске для сортировок, хэшей и Materialize. Если у узла Sort стоит external merge Disk и большой temp written - значит не хватило work_mem и сортировка ушла на диск. Если у скана огромный shared read - узел упирается в чтение с диска.
Частые грабли
- Снимают EXPLAIN без ANALYZE и спорят про оценки планировщика как про реальность. Без ANALYZE нет фактического времени вообще.
- Ищут узкое место по самому большому cost. Ориентир - фактическое время узла, сервис не зря подсвечивает долю времени.
- Забывают про loops в Nested Loop и пугаются маленького rows, не заметив тысячи итераций.
- Не сделали SET track_io_timing = TRUE и удивляются, что нет реального времени I/O в плане.
- Гонят EXPLAIN ANALYZE на боевом UPDATE без BEGIN/ROLLBACK и меняют данные.
- Видят расхождение оценки и факта в сотни раз и лезут крутить индексы, хотя сначала надо ANALYZE таблицы - устаревшая статистика или коррелированные условия врут планировщику.
- Меряют первый прогон холодного запроса: shared read большой просто потому, что кэш пуст, второй прогон даёт shared hit. Сравнивайте сопоставимое.
- Подключитесь к демобазе Авиаперевозки в psql и выполните SET track_io_timing = TRUE;
- Снимите план соединения без индекса: EXPLAIN (ANALYZE, BUFFERS) SELECT t.passenger_name, f.flight_no FROM tickets t JOIN ticket_flights tf ON tf.ticket_no = t.ticket_no JOIN flights f ON f.flight_id = tf.flight_id WHERE f.flight_no = 'PG0007';
- Скопируйте весь вывод, откройте explain.tensor.ru и вставьте план. Найдите подсвеченный самый тяжёлый узел и его долю времени.
- Посмотрите, есть ли Seq Scan по flights и каков у него shared read.
- Создайте индекс: CREATE INDEX ON flights (flight_no); затем выполните ANALYZE flights;
- Снимите план повторно и снова загрузите в сервис.
- Сравните total actual time, тип скана по flights и буферы до и после. Запишите, во сколько раз ускорилось.
- Чем cost отличается от actual time и по какому из них искать узкое место?
- Как из rows и loops получить реальное число обработанных строк на узле?
- Что означают shared hit и shared read и как по ним найти I/O-проблему?
- Почему EXPLAIN ANALYZE на INSERT/UPDATE/DELETE надо оборачивать в транзакцию с ROLLBACK?
- О чём говорит сильное расхождение оценки rows и факта, и что делать в первую очередь?
- Что изменилось в поведении BUFFERS в PostgreSQL 18?
