EXPLAIN ANALYZE в PostgreSQL: как читать план и выбирать индекс

Что именно измеряет команда

EXPLAIN показывает предполагаемый план. Опция ANALYZE выполняет оператор и собирает статистику. Оценка cost выражается в условных единицах планировщика, actual time — в миллисекундах. BUFFERS добавляет обращения к блокам, включая чтения и попадания в кеш. Это разные показатели: cost 200 не означает прогноз в 200 миллисекунд.

Поскольку ANALYZE выполняет оператор, начните упражнение в одноразовой базе. Анализируемый UPDATE по-прежнему изменяет строки: слово EXPLAIN не превращает запись в чтение.

• PostgreSQL 17: EXPLAIN

Воспроизведите запрос последних задач

В нашем примере выбираются двадцать последних готовых задач одного клиента. Набор содержит 100 000 строк; готовые задачи распределены между клиентами. Один раз выполните подготовку в пустой базе PostgreSQL 17. EXPLAIN показывает план, а не результат: отдельно запустите SELECT без EXPLAIN и сохраните идентификаторы. Первые три — 99041, 98041 и 97041.

У запроса два условия равенства и сортировка по убыванию. Поле id разрешает совпадения времени. Наряду со скоростью важно получить именно нужные двадцать строк.

CREATE TABLE jobs (
  id bigint PRIMARY KEY,
  tenant_id integer NOT NULL,
  state text NOT NULL,
  created_at timestamptz NOT NULL
);

INSERT INTO jobs
SELECT g, (g % 100) + 1,
       CASE WHEN (g / 100) % 10 = 0 THEN 'ready' ELSE 'done' END,
       TIMESTAMPTZ '2026-01-01 00:00:00+00' + g * INTERVAL '1 second'
FROM generate_series(1, 100000) AS g;

ANALYZE jobs;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM jobs
WHERE tenant_id = 42 AND state = 'ready'
ORDER BY created_at DESC, id DESC
LIMIT 20;

Найдите работу, не попадающую в результат

Проследите строки от чтения таблицы к верхней части плана. Rows Removed by Filter показывает отброшенные строки. Sort перед Limit помогает заметить работу до получения страницы. Сравните ожидаемое и фактическое число строк: большое расхождение может указывать на проблему статистики. Если узел запускается многократно, показанные строки и время — средние значения за цикл; учитывайте loops.

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

• PostgreSQL 17: Using EXPLAIN

Согласуйте индекс с фильтрами и сортировкой

Для этого запроса проверим B-tree с первыми ключами tenant_id и state, затем created_at и id в нужном порядке. Условия равенства сужают ведущие ключи, оставшиеся задают упорядоченный диапазон. Это гипотеза для конкретного обращения к данным, а не совет индексировать каждый столбец из WHERE.

В нашей изолированной проверке на PostgreSQL 17.9 последовательное чтение с сортировкой сменилось на Index Only Scan под Limit. Двадцать строк остались прежними. Точное время и выбранный способ чтения зависят от среды и состояния таблицы; чтение только индекса не гарантировано. Сохраните новый план рядом с исходным, чтобы объяснить изменение.

CREATE INDEX jobs_tenant_state_order_idx
ON jobs (tenant_id, state, created_at DESC, id DESC);

ANALYZE jobs;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM jobs
WHERE tenant_id = 42 AND state = 'ready'
ORDER BY created_at DESC, id DESC
LIMIT 20;

• PostgreSQL 17: Multicolumn Indexes

Выберите следующую проверку по наблюдениям

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

Проверьте нагрузку, а не один удачный запуск

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

• Сверьте строки и порядок до и после изменения.
• Сравните отбрасывание строк, сортировку, буферы и время выполнения.
• Измерьте размер индекса и влияние на вставки и обновления.
• Объясните, какой найденный источник затрат устраняет индекс.

• PostgreSQL 17: Examining Index Usage
• RecallDeck: вопросы SQL, PostgreSQL и баз данных

Коротко

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