EXPLAIN ANALYZE в PostgreSQL: как найти причину медленного запроса

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

Как получить полезный план

Обычный EXPLAIN показывает, как PostgreSQL собирается выполнить запрос. То есть план запроса. Опция ANALYZE реально исполняет запрос, собирает и показывает измерения работы запроса. Например, получим план запроса последних заказов:

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT id, created_at, total_cents
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

BUFFERS показывает обращения к блокам таблиц и индексов. SETTINGS выводит настройки планирования, которые отличаются от встроенных значений. Они помогают сравнивать планы с разных серверов. В PostgreSQL 18 опция ANALYZE уже включает BUFFERS по умолчанию. В рамках этой статьи указываю её явно, чтобы команда запускалась корректно на более ранних версиях.

При работе с базой запускайте эти команды на тестовой отдельной базе. Потому что EXPLAIN ANALYZE создаёт ту же нагрузку, что и сам запрос. Если поставить его перед UPDATE, данные изменятся. ROLLBACK отменит транзакционные изменения, но не нагрузку и ожидания блокировок. Значения последовательности, выданные nextval, сохранятся и после отката.

Не путайте две команды. ANALYZE orders собирает статистику для планировщика. EXPLAIN ANALYZE SELECT ... измеряет выполнение конкретного запроса. Сам по себе второй вариант не обновляет статистику таблицы. Поведение опций описано в справке EXPLAIN, сбор статистики в справке ANALYZE.

Как читать дерево плана

Каждая операция представлена узлом. Отступы показывают, кто получает строки от кого. В нашем примере дерево может выглядеть так:

Limit
  -> Sort
       -> Seq Scan on orders

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

1. Seq Scan просматривает таблицу последовательно и выбирает подходящие заказы клиента.
2. Sort упорядочивает найденные заказы по времени.
3. Limit возвращает только первые двадцать строк.

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

Время родителя включает в себя работу потомков. Если сканирование заняло 60 мс, а сортировка вместе со сканированием 61 мс, нельзя складывать их и получать 121 мс. Разница помогает искать дополнительную работу в простом последовательном плане. Для параллельных планов такое вычитание уже не даёт надёжного распределения времени.

Что такое cost, rows и width?

Возьмём строку из нашего эксперимента:

Seq Scan on orders  (cost=0.00..44758.97 rows=992 width=20)
                    (actual time=0.200..60.141 rows=1000.00 loops=1)

Первая скобка содержит прогноз:

cost измеряется в условных единицах модели планировщика. Это не миллисекунды. Значение 44758.97 нельзя сравнивать напрямую с фактическими 60 мс. Оно нужно планировщику для выбора между способами выполнения одного запроса.

rows описывает строки, переданные родителю, а не все просмотренные записи. Таблица в нашем примере содержит миллион заказов, но узел выдаёт примерно тысячу. Остальные строки отсеиваются условием WHERE. И width описывает выходные столбцы узла, а не размер всей записи на диске.

Что показывают actual time и loops?

Во второй скобке находится результат выполнения. actual time=0.200..60.141 означает время до первой строки и до завершения узла в миллисекундах. rows=1000 показывает фактический выход.

Если loops больше единицы, время и число строк показаны в среднем за одно выполнение узла. Например, внутренний поиск в Nested Loop:

(actual time=0.003..0.020 rows=2 loops=10000)

В этом примере узел выполнился десять тысяч раз. Он вернул в среднем две строки за вызов, всего около 20 тысяч. На один вызов ушло 0.020 мс, а на все вызовы соответственно 0.020 мс * 10000 = 200 мс. Результат приблизительный: значения в плане округлены. В параллельном плане суммарное время работы также отличается от времени ожидания пользователя.

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

Какие узлы встречаются чаще

Seq Scan сам по себе не ошибка. Для небольшой таблицы или запроса большой доли строк последовательное чтение может быть дешевле множества обращений через индекс. А наличие Index Scan ещё не доказывает, что запрос эффективен.

Различайте Index Cond и Filter. Первое условие проверяется средствами индекса. Второе проверяется на полученных строках. При этом Index Cond не всегда сужает читаемый диапазон: в составном B-tree условие по более правому столбцу может лишь отсеивать записи внутри индекса, сокращая обращения к таблице. Это различие описано в документации по составным индексам. Если индекс выдаёт сотни тысяч кандидатов, а фильтр оставляет двадцать, стоит разобраться, можно ли сузить доступ раньше.

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

Что проверять в плане

Начните с Execution Time: это общее время выполнения запроса в плане. Затем ищите операции, которые просматривают много строк, повторяются тысячи раз или записывают промежуточные данные во временные файлы.

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

После массового изменения данных проверьте актуальность статистики. Если обычного ANALYZE недостаточно, исследуйте распределение значений и зависимость условий. Для коррелирующих столбцов одной таблицы могут помочь расширенные статистики. Проверьте эту гипотезу повторным планом. Механизм описан в документации по статистике планировщика.

Если оценки близки к факту, ищите лишнюю работу. В нашем примере PostgreSQL верно ожидает около тысячи заказов клиента. Проблема в том, что для их поиска приходится проверять миллион строк.

BUFFERS: сколько блоков затронул запрос

Время меняется из-за нагрузки и состояния кешей. Счётчики буферов помогают увидеть, какой объём работы выполняет план. Они особенно полезны, когда повторный запуск ускорился, но причина улучшения непонятна.

shared hit и shared read

Buffers: shared hit=15204 read=17055 written=94

shared относится к блокам обычных таблиц и индексов. hit означает, что нужный блок найден в буферном кеше PostgreSQL. read означает, что его пришлось прочитать в этот кеш.

Из read нельзя заключить, что PostgreSQL каждый раз ждал физический диск. Данные могли находиться в файловом кеше операционной системы. Поэтому высокий read важно анализировать только вместе с метриками операционной системы и нагрузки на диски.

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

Это счётчики обращений, а не уникальных страниц. При повторных индексных поисках одна страница может учитываться много раз. Сумма hit + read, умноженная на размер блока, даёт условный объём обращений. Она не показывает ни размер рабочего набора, ни объём физического чтения с диска.

Обычно размер блока равен 8 КиБ. Для своего сервера проверьте:

SHOW block_size;

В строке выше сумма равна 32 259 блокам. При размере блока 8192 байта это около 252 МиБ обращений. В этом конкретном плане сканируется вся таблица. Для вложенного цикла с повторными поисками такую сумму уже нельзя трактовать как размер просмотренной таблицы.

Почему нельзя складывать строки BUFFERS

Родитель включает обращения потомков. Если у Limit, Sort и Seq Scan одинаковые значения, это одна работа, отражённая на трёх уровнях дерева. Сложив строки, вы утроите результат.

Для общего объёма выполнения смотрите корневой узел. Для локализации спускайтесь по веткам. Отдельный раздел Planning: Buffers относится к планированию, а не к выполнению запроса.

В отличие от времени и rows, буферные счётчики уже накоплены за выполнения узла. Умножать их на loops ещё раз не нужно. В параллельном плане учитывайте также статистику работников и не суммируйте её повторно с уже включающими её значениями.

dirtied, written, local и temp

local и temp обозначают разные вещи. Временная таблица имеет собственные блоки, а сортировка может создавать временные рабочие файлы без CREATE TEMP TABLE.

written не обязательно означает, что SQL изменяет бизнес-данные. Читающий процесс тоже может вытеснять ранее изменённые страницы. Поэтому written=94 в нашем SELECT не доказывает, что запрос обновил 94 страницы заказов. И это не полный объём всех записей на диск, связанных с запросом: фоновые процессы СУБД PostgreSQL работают и учитываются отдельно.

Если у Sort указано Sort Method: external merge и Disk: ..., сортировка использовала диск. При нескольких пакетах хеширования обращайте внимание на Batches и temp read/written. Тогда имеет смысл проверить объём входных данных и доступную память. Если сократить число строк перед сортировкой, ей может хватить памяти. Это позволит избежать использования диска и ускорить запрос.

Увеличение work_mem может уменьшить сброс промежуточных данных. Но это лимит для отдельных операций, а не общий бюджет на соединение или сервер. Несколько узлов и параллельные работники могут потреблять память одновременно. Для хеширования учитывается также hash_mem_multiplier. Настраивать это безопаснее для конкретного эксперимента и с оценкой конкурентной нагрузки. Подробнее в разделе о ресурсах PostgreSQL.

Как проверить гипотезу об ожидании чтения и записи

При включённом track_io_timing план может показать время операций чтения и записи:

-- Нужны соответствующие права. Включайте в диагностической сессии.
SET track_io_timing = on;
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM orders;

Измерение тоже имеет накладные расходы. Полученное время отражает операции чтения и записи, но не раскрывает само по себе, какие чтения обслужил кеш ОС. Настройка описана в документации по сбору статистики.

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

Проверяем план на миллионе заказов

Нужно получить двадцать последних заказов одного клиента. Создадим миллион заказов, равномерно распределённых по тысяче клиентов. Поле payload расширяет запись, чтобы полное сканирование читало заметный объём страниц памяти. В этом примере запрос данное поле не возвращает.

Пример проверен на PostgreSQL 18.6, с shared_buffers=128MB, work_mem=4MB, блоком 8192 байта. Для понятного последовательного плана в тестовой сессии отключены параллельное выполнение этого запроса и JIT. На вашем сервере время, оценки и распределение hit/read будут другими.

Подготовка данных

Запускайте в отдельной тестовой базе. Имя схемы должно быть свободно:

SET max_parallel_workers_per_gather = 0;
SET jit = off;
CREATE SCHEMA explain_demo;
SET search_path = explain_demo;

CREATE TABLE orders (
    id bigint PRIMARY KEY,
    customer_id integer NOT NULL,
    created_at timestamp NOT NULL,
    total_cents integer NOT NULL,
    payload text NOT NULL
);

INSERT INTO orders
SELECT g,
       (g % 1000)::integer,
       timestamp '2026-01-01' + g * interval '1 second',
       (g % 10000)::integer,
       repeat('x', 200)
FROM generate_series(1, 1000000) AS g;

VACUUM (ANALYZE) orders;

Запускайте VACUUM вне явного блока транзакции. Опция ANALYZE здесь также запускает сбор статистики. Оба правила указаны в справке VACUUM. Первичный ключ уже создал индекс по id, но он не обеспечивает поиск по клиенту и нужный порядок времени.

Исходный план

В той же сессии выполните дважды:

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT id, created_at, total_cents
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Ниже сокращённый вывод второго запуска. Поля width, настройки и время планирования опущены, существенные измерения сохранены:

Limit (cost=44785.37..44785.42 rows=20)
      (actual time=60.338..60.341 rows=20.00 loops=1)
  Buffers: shared hit=15204 read=17055 written=94
  -> Sort (cost=44785.37..44787.85 rows=992)
          (actual time=60.337..60.338 rows=20.00 loops=1)
       Sort Key: created_at DESC
       Sort Method: top-N heapsort  Memory: 27kB
       Buffers: shared hit=15204 read=17055 written=94
       -> Seq Scan on orders (cost=0.00..44758.97 rows=992)
             (actual time=0.200..60.141 rows=1000.00 loops=1)
            Filter: (customer_id = 42)
            Rows Removed by Filter: 999000
            Buffers: shared hit=15204 read=17055 written=94
Execution Time: 60.352 ms

Планировщик ожидал 992 строки и получил тысячу. Оценка достаточно точна. Сбор статистики здесь не решит проблему: без подходящего индекса запросу всё равно придётся читать таблицу.

Причину задержки видно в Seq Scan: он проверил миллион строк, отбросил 999 тысяч и занял почти всё время запроса. Сортировка использовала top-N heapsort и всего 27 КиБ памяти. Временных блоков нет. Увеличивать память для сортировки по этому плану незачем.

LIMIT 20 уменьшил результат, но не объём сканирования. Без индекса PostgreSQL должен найти все подходящие заказы, прежде чем определить последние двадцать.

Обратите внимание: повторный запуск всё ещё имеет read=17055. Полный прогрев не гарантирован одним повтором. Таблица здесь больше shared_buffers, страницы могут вытесняться, а последовательное сканирование использует специальные стратегии работы с кешем. Называть второй запуск «полностью из памяти» по одному факту повторения нельзя.

Добавляем индекс по клиенту и времени

Хотим сначала ограничить доступ одним клиентом, затем получать его заказы в нужном порядке. Для этого подходит составной B-tree:

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

Равенство по первому столбцу выделяет диапазон клиента. Второй столбец задаёт порядок внутри этого диапазона. После двадцати строк выполнение можно остановить. Правила ограничения диапазона описаны в разделе о составных индексах, а получение первых строк без отдельной сортировки в разделе об индексах и ORDER BY. Для этого запроса подошёл бы и индекс (customer_id, created_at) с обратным сканированием: DESC в определении индекса здесь не обязателен.

Индекс специально не покрывает все выходные столбцы. Нам достаточно обычного Index Scan с небольшим числом обращений к таблице. Добавлять в индекс все поля запроса без измерения здесь незачем.

Проверяем, что изменилось

После создания индекса снова выполните тот же запрос дважды. Сокращённый второй план:

Limit (cost=0.42..79.89 rows=20)
      (actual time=0.004..0.011 rows=20.00 loops=1)
  Buffers: shared hit=23
  -> Index Scan using orders_customer_created_idx on orders
       (cost=0.42..3941.64 rows=992)
       (actual time=0.004..0.009 rows=20.00 loops=1)
       Index Cond: (customer_id = 42)
       Index Searches: 1
       Buffers: shared hit=23
Execution Time: 0.016 ms

Исчезла отдельная сортировка. Сканирование отдало двадцать строк, после чего родитель остановил чтение. Оценка rows=992 у индекса относится к выполнению узла до конца; фактические двадцать строк под Limit не означают ошибку статистики.

Счётчик read здесь равен нулю, поэтому в текстовом плане он не напечатан. Индекс и нужные страницы уже были в кеше после предыдущего запуска.

Главный эффект от добавления индекса в том, что число обращений к общим буферам сократилось с 32 259 до 23. PostgreSQL прочитал двадцать заказов через индекс вместо сканирования всей таблицы. Время тоже уменьшилось, но по двум запускам нельзя предсказать ускорение под рабочей нагрузкой. Особенно когда измерение занимает сотые доли миллисекунды.

На реальных данных проверьте разных клиентов, включая самого крупного, другие значения LIMIT и конкурентную нагрузку. Индекс требует места и дополнительной работы при записи. Цена может быть оправдана частыми чтениями, но это тоже нужно измерять.

Полный сценарий и исходный вывод сохранены в репозитории: benchmarks/explain-analyze/demo.sql и benchmarks/explain-analyze/result.txt. Сценарий рассчитан на запуск один раз в чистой тестовой базе:

psql -X -v ON_ERROR_STOP=1 -d explain_lab \
  -f benchmarks/explain-analyze/demo.sql

Что план не объясняет целиком

Execution Time не включает планирование, ожидание свободного соединения в приложении и передачу результата по сети. По умолчанию EXPLAIN ANALYZE также не измеряет обычную сериализацию выходных строк для отправки клиенту. В PostgreSQL 18 её можно отдельно исследовать опцией SERIALIZE TEXT или SERIALIZE BINARY, но сетевую передачу она не измеряет. Границы измерения описаны в справке EXPLAIN.

Если SQL в плане быстрый, а обработчик отвечает секунду, разбейте задержку приложения на получение соединения, выполнение SQL, чтение результата и остальную обработку. Один план не покажет, сколько времени запрос провёл в очереди пула соединений.

Для параметризованного SQL используйте реальные проблемные значения. Редкий и массовый клиент могут требовать разных планов. Если приложение выполняет подготовленный запрос, сравнивайте также его выполнение через EXPLAIN ... EXECUTE: план отдельного SQL с литералом может отличаться. Причины и правила выбора общего или зависящего от параметров плана описаны в справке PREPARE.

Но измерение не бесплатное и добавляет накладные расходы. Для большого числа быстрых операций можно использовать EXPLAIN (ANALYZE, BUFFERS, TIMING OFF). Время отдельных узлов исчезнет, но число строк, буферы и общее время выполнения останутся. Это полезный дополнительный замер, когда нужно проверить, насколько подробный сбор времени влияет на результат.

Итог

В запросе последних заказов основное время занимало сканирование миллиона строк. Планировщик почти точно оценил число заказов клиента, а сортировке хватало памяти. Индекс по клиенту и времени позволил получить двадцать строк и остановить чтение. Это подтвердилось новым планом: отдельная сортировка исчезла, число обращений к буферам сократилось с 32 259 до 23.

Для своего запроса пройдите тот же путь:

1. Найдите операцию, которая читает много данных или часто повторяется. Учитывайте loops и вложенность времени.
2. Сравните ожидаемые и фактические rows. Отделите ошибку оценки от дорогого способа получить строки.
3. По BUFFERS проверьте объём обращений, по временным блокам — сброс промежуточных данных на диск.
4. Измените запрос, индекс или настройки под найденную причину. Повторите замер в сопоставимых условиях и проверьте, уменьшилась ли работа.