EXPLAIN ANALYZE: как читать план запроса Postgres

Поставьте EXPLAIN (ANALYZE, BUFFERS) перед запросом, выполните его и читайте вывод снизу вверх. Вы ищете три вещи: узел, в котором на самом деле уходит большая часть времени; узел, у которого оценка числа строк отличается от фактической на порядок; и последовательное сканирование большой таблицы, возвращающее горстку строк. Почти каждый медленный запрос в небольшой или средней базе - это одно из трёх, а лечение - индекс, более свежая статистика или переписанное условие WHERE. Остальная часть статьи о том, как понять, с чем именно вы имеете дело.

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

EXPLAIN, EXPLAIN ANALYZE и опции, которые стоит использовать#

Один EXPLAIN показывает план, который выбрал планировщик, не выполняя запрос. Он мгновенный и безопасный и даёт только оценки. EXPLAIN ANALYZE действительно выполняет запрос и печатает то, что произошло на самом деле, рядом с предсказанным. В этой разнице весь смысл, поэтому используйте ANALYZE, если нет причин этого не делать.

Два предупреждения, прежде чем вы начнёте. EXPLAIN ANALYZE на UPDATE, DELETE или INSERT выполняет запись. Оберните его, если этого не хотите:

BEGIN;EXPLAIN (ANALYZE, BUFFERS)DELETE FROM sessions WHERE expires_at < now() - interval '30 days';ROLLBACK;

И ANALYZE добавляет накладные расходы на измерение времени. На большинстве машин это несколько процентов; на системе с медленным источником часов он может удвоить измеренное время короткого запроса. Если числа выглядят невозможными, запустите снова с TIMING OFF: он сохраняет счётчики строк и убирает тайминги по узлам.

Опции, которые заслуживают места:

• BUFFERS - сколько блоков по 8 КБ каждый узел прочитал из общих буферов (shared hit), с диска или из кэша операционной системы (shared read) и записал (shared written). Это самое близкое к физическому числу операций ввода-вывода, что вы получите, и оно гораздо стабильнее реального времени на общей машине. Начиная с PostgreSQL 18 оно включается вместе с ANALYZE по умолчанию; в более ранних версиях его нужно запрашивать.
• VERBOSE - печатает список выходных столбцов и имена с указанием схемы. Полезно для планов с несколькими соединениями и повторяющимися именами столбцов.
• SETTINGS - печатает любую настройку планировщика, значение которой не по умолчанию. Стоит добавить один раз, когда план на одном сервере отличается от того же плана на другом.
• WAL - записи журнала упреждающей записи и число сгенерированных байтов. Имеет смысл только с запросом на запись.
• FORMAT JSON - машиночитаемый вывод, который и потребляют веб-визуализаторы планов.
• GENERIC_PLAN (PostgreSQL 16 и новее) - позволяет выполнить EXPLAIN для выражения с плейсхолдерами $1, не подставляя значения, и именно так вы смотрите, что на самом деле отправляет подготовленное выражение или ORM.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)SELECT ...;

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

Как читать дерево#

Каждая строка, начинающаяся с ->, - это узел. Отступ - это вложенность: дочерние узлы сдвинуты под родителем, а выполнение идёт от самого внутреннего узла наружу. Читайте от самого глубокого отступа вверх, и вы следуете за строками.

Строка узла выглядит так:

->  Index Scan using orders_pkey on orders  (cost=0.43..8.45 rows=1 width=36)                                            (actual time=0.021..0.023 rows=1 loops=1)

Шесть чисел, и каждое означает что-то конкретное:

• cost=0.43..8.45 - оценочные стоимость запуска и полная стоимость в условных единицах, где 1,0 примерно равна стоимости чтения одной последовательной страницы. Первое число - это то, что нужно, прежде чем можно будет вернуть первую строку, поэтому у сортировки большая стоимость запуска, а у сканирования по индексу почти нулевая. Числа важны только относительно друг друга.
• rows=1 - оценочное число строк, которые узел произведёт за одно выполнение.
• width=36 - оценочная средняя ширина строки в байтах. Полезно, когда план перемещает куда больше данных, чем вы ожидали.
• actual time=0.021..0.023 - реальные миллисекунды до первой строки и до последней, усреднённые по циклам.
• loops=1 - сколько раз этот узел выполнялся. Это число, о котором забывают: на внутренней стороне nested loop actual time и rows указаны на один цикл. Узел с actual time=0.4..0.5 rows=3 loops=9000 стоил примерно 4,5 секунды и произвёл 27 000 строк, а не полмиллисекунды и три строки.

Время включает дочерние узлы, поэтому собственная стоимость узла - это его actual time минус время всего, что под ним. Две строки в самом низу, Planning Time и Execution Time, отдельные: время планирования, составляющее заметную долю общего, обычно означает секционированную таблицу с сотнями секций или огромное число соединений.

Дополнительные строки под узлом - это место, где живут подробности. Filter с Rows Removed by Filter, Index Cond, Heap Fetches, Sort Method, Buckets и Buffers - всё это говорит то, чего не говорят заголовочные числа.

Узлы, которые вы действительно увидите#

Последовательное сканирование - не обязательно плохо. Прочитать таблицу-справочник из 500 строк от начала до конца дешевле любого индекса, и планировщик это знает. Оно становится проблемой, когда таблица большая, фильтр селективный, а Rows Removed by Filter - число из шести цифр.

Две строки стоит научиться замечать сразу. Sort Method: external merge Disk: 48312kB означает, что сортировка не поместилась в work_mem и ушла во временные файлы, и это обычно самая большая из доступных побед: увеличение work_mem для этой сессии или этого запроса превращает её в Sort Method: quicksort Memory: 51000kB. А Buckets: 65536 Batches: 8 Memory Usage: 3841kB у hash join означает, что хеш-таблица по той же причине была разбита на восемь проходов по данным. В статье настройка PostgreSQL для небольших серверов описано, какими должны быть эти настройки на машине с 1-8 ГБ и почему повышение work_mem глобально - это способ исчерпать память.

Медленный запрос, исправленный#

Вот план для таблицы из 2,4 миллиона заказов, отвечающий на вопрос «последние двадцать заказов одного клиента».

EXPLAIN (ANALYZE, BUFFERS)SELECT id, created_at, totalFROM ordersWHERE customer_id = 4821  AND created_at >= now() - interval '90 days'ORDER BY created_at DESCLIMIT 20;

Limit  (cost=48231.44..48231.49 rows=20 width=20)       (actual time=812.334..812.339 rows=20 loops=1)  Buffers: shared hit=1204 read=41988  ->  Sort  (cost=48231.44..48232.07 rows=252 width=20)            (actual time=812.332..812.334 rows=20 loops=1)        Sort Key: created_at DESC        Sort Method: top-N heapsort  Memory: 27kB        ->  Seq Scan on orders  (cost=0.00..48224.72 rows=252 width=20)                                (actual time=0.412..811.903 rows=238 loops=1)              Filter: ((customer_id = 4821) AND (created_at >= (now() - '90 days'::interval)))              Rows Removed by Filter: 2399762              Buffers: shared hit=1204 read=41988Planning Time: 0.214 msExecution Time: 812.381 ms

Всё, что нужно, там есть. Сканирование прочитало 43 192 блока, около 337 МБ, чтобы вернуть 238 строк и выбросить 2,4 миллиона. Оценка в 252 строки была близка к фактическим 238, так что статистика в порядке. Просто нет индекса, соответствующего фильтру.

CREATE INDEX CONCURRENTLY orders_customer_created_idx    ON orders (customer_id, created_at DESC);

Limit  (cost=0.43..38.72 rows=20 width=20) (actual time=0.041..0.088 rows=20 loops=1)  Buffers: shared hit=23  ->  Index Scan using orders_customer_created_idx on orders        (cost=0.43..482.90 rows=252 width=20) (actual time=0.039..0.084 rows=20 loops=1)        Index Cond: ((customer_id = 4821) AND (created_at >= (now() - '90 days'::interval)))        Buffers: shared hit=23Planning Time: 0.302 msExecution Time: 0.114 ms

Двадцать три блока вместо сорока трёх тысяч, а узел Sort исчез совсем: поскольку индекс хранит created_at по убыванию внутри каждого customer_id, строки приходят в порядке, которого просил запрос, и LIMIT останавливается после двадцати. Порядок столбцов в этом индексе не произвольный, как и DESC. Почему столбцы с равенством идут первыми, а столбцы с диапазоном последними, разбирает статья индексы PostgreSQL простыми словами.

Пять причин медленного плана#

Неверная оценка строк. Сравнивайте rows= с actual ... rows= на каждом узле. Разница вдвое - ничто. Разница в сто раз означает, что планировщик выбрал стратегию по плохой информации, а узел над ним, вероятно, nested loop, который должен был стать hash join. Причины: таблицу не анализировали после массовой загрузки, default_statistics_target в 100 слишком груб для перекошенного столбца или два столбца коррелируют, а планировщик перемножает их селективности так, будто они независимы. Исправления в порядке возрастания усилий:

ANALYZE orders;ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;CREATE STATISTICS orders_city_region (dependencies, ndistinct)    ON city, region FROM orders;ANALYZE orders;

Отсутствующий или непригодный индекс. Индекс есть, но запрос не может его использовать: функция над столбцом (WHERE lower(email) = ... требует индекса по выражению lower(email)), несовпадение типов, вынуждающее приведение, ведущий подстановочный знак в LIKE или OR по двум столбцам, который не покрывает ни один индекс.

Сортировка или хеширование на диске. Описано выше. Ищите Disk: где угодно в плане.

Слишком много циклов. Nested loop из 40 000 итераций сканирования индекса по 0,2 мс - это восемь секунд, в которых не признаётся ни один узел. Умножайте actual time на loops, прежде чем верить, что узел дешёвый.

Работа, которая запросу не нужна. SELECT всех столбцов, когда нужны три, ORDER BY без LIMIT, DISTINCT, компенсирующий дублирующее соединение, COUNT по всей таблице при каждой загрузке страницы. Самый дешёвый запрос - тот, который вы удалили.

Мёртвые строки - шестая причина, и они хорошо прячутся: таблица, раздувшаяся оттого, что autovacuum не справляется, читается как гораздо большая таблица, а Index Only Scan с тысячами Heap Fetches - та же история с другой стороны. Если запрос замедлился без роста данных, читайте vacuum и bloat в Postgres.

Как найти медленные запросы#

Чтение плана предполагает, что вы знаете, какой запрос читать. Три инструмента в порядке возрастания настройки:

1. pg_stat_activity для того, что происходит прямо сейчас. SELECT pid, now() - query_start AS runtime, state, left(query, 80) FROM pg_stat_activity WHERE state <> 'idle' ORDER BY runtime DESC; показывает, что выполняется и как долго. pg_cancel_backend(pid) останавливает запрос вежливо, pg_terminate_backend(pid) - грубо.
2. log_min_duration_statement для журнала. Задайте 500, и каждое выражение, выполняющееся дольше полусекунды, записывается в журнал сервера вместе с длительностью и параметрами. Когда ничего не тормозит, это ничего не стоит.
3. pg_stat_statements для закономерностей. Именно он меняет то, как вы работаете. Его нужно указать в shared_preload_libraries, потребуется перезапуск, затем CREATE EXTENSION pg_stat_statements;.

SELECT calls,       round(total_exec_time::numeric, 1) AS total_ms,       round(mean_exec_time::numeric, 2)  AS mean_ms,       rows,       left(query, 70) AS queryFROM pg_stat_statementsORDER BY total_exec_time DESCLIMIT 15;

Сортируйте по total_exec_time, а не по mean_exec_time. Запрос, который выполняется 4 мс и запускается два миллиона раз в день, обходится вам дороже ночного отчёта на девять секунд, и исправить его обычно проще. pg_stat_statements_reset() обнуляет счётчики, чтобы вы могли измерить окно.

Четвёртый вариант, auto_explain, записывает в журнал полный план любого выражения дольше порога, что ловит план, который портится только в 3 часа ночи при особых параметрах. Его загрузка немного тормозит каждый запрос, поэтому включайте его, чтобы поймать что-то конкретное, и снова выключайте.

Все четыре требуют суперпользователя или роли с подходящими привилегиями, а также возможности править postgresql.conf и перезапускать сервер. На RE:NODE линейка PostgreSQL выдаёт вам пароль суперпользователя, сгенерированный для этого сервера, файловый менеджер и SFTP для файлов конфигурации и консоль для перезапуска, так что shared_preload_libraries - это настройка, которую вы действительно можете изменить, а не обращение в поддержку. Где появляются эти учётные данные, описано в руководствах по панели.

Чего план не показывает#

План описывает работу, которую сервер проделал для этого выражения. Он ничего не говорит о:

• Ожидании блокировки. Запрос, заблокированный за ALTER TABLE, показывает совершенно здоровый план и выполняется девяносто секунд. Смотрите pg_locks вместе с pg_stat_activity или столбец wait_event_type.
• Накладных расходах на соединение. Открытие нового соединения PostgreSQL порождает процесс и стоит несколько миллисекунд плюс несколько мегабайт. Приложение, открывающее по одному на запрос, тратит на подключение больше времени, чем на запросы. Это работа пулера, а не планировщика - см. пулы соединений и лимиты.
• Сетевых обращениях. Двести мелких запросов в цикле, каждый быстрый, - это проблема N+1, и каждый ORM порождает их случайно. Все планы выглядят идеально.
• Времени на стороне клиента. Выборка 400 000 строк в приложение, которому нужно было количество, медленна там, куда EXPLAIN не заглядывает.
• Остальной машине. План, читающий 40 000 блоков, приемлем на простаивающем сервере и ужасен, когда шесть других соединений делают то же самое. Графики памяти, CPU и диска в панели - это проверка того, что план и машина согласуются.

И ещё одно честное ограничение: EXPLAIN без ANALYZE показывает то, что планировщик намерен делать при текущей статистике и текущих настройках, а подготовленные выражения могут переключаться на обобщённый план после пяти выполнений, так что то, что выполняет ваш драйвер, - не всегда то, что вы проверили вручную. EXPLAIN (GENERIC_PLAN) в PostgreSQL 16 и новее существует именно из-за этого разрыва.

FAQ#

Почему Postgres игнорирует мой индекс?

Обычно потому, что считает, что запрос возвращает большую долю таблицы, и тогда последовательное сканирование действительно дешевле. Сначала сравните оценку с реальностью. Если оценка верна, а вы всё равно ожидаете сканирование по индексу, причина часто в random_page_cost, который по умолчанию равен 4 и предполагает вращающиеся диски. На NVMe значение 1.1 отражает оборудование и смещает планировщик в сторону сканирования по индексу повсюду.

Всегда ли Seq Scan - это плохо?

Нет. На маленькой таблице это самый быстрый вариант, и планировщик выберет его сознательно. Плохо, когда таблица большая, а фильтр отбрасывает большую часть прочитанного, о чём вам точно говорит строка Rows Removed by Filter.

В чём разница между cost и actual time?

Cost - это безразмерная оценка, которую планировщик использует для сравнения планов-кандидатов; за единицу принято чтение одной последовательной страницы. Actual time - миллисекунды, измеренные во время выполнения. Одно в другое не пересчитать, и пытаться не стоит: полезно сравнивать только оценочное и фактическое число строк.

Не добавить ли индекс на каждый столбец?

Нет. Каждый индекс нужно обновлять при каждой вставке, обновлении и удалении индексируемых столбцов, он занимает место на диске и добавляет работу для vacuum. Три хорошо подобранных составных индекса обычно лучше пятнадцати одностолбцовых. Загляните в pg_stat_user_indexes и найдите idx_scan = 0, чтобы увидеть индексы, которыми никто не пользовался с последнего перезапуска.

Запрос быстрый в psql и медленный из приложения. Почему?

Чаще всего приложение выполняет не тот же запрос: другие параметры, обобщённый план, обёртывающая транзакция с другим уровнем изоляции или ORM, добавляющий LIMIT и ORDER BY, которых вы не писали. Включите log_min_duration_statement, перехватите точное выражение, полученное сервером, и посмотрите его план.

Как часто нужно запускать ANALYZE вручную?

Обычный случай обрабатывает autovacuum. Запускайте вручную после массовой загрузки, после восстановления и после миграции, меняющей распределение значений в столбце, потому что пока он не выполнится, планировщик работает со статистикой, описывающей таблицу, которой больше нет. Дамп не несёт статистику с собой, поэтому свежевосстановленная база часто необъяснимо медленна; остальное об этом есть в статье pg_dump и pg_restore.