ЯдроКодаподготовка к экзаменам
Учебная платформа

Загружаем материалы

Подготавливаем материалы и навигацию по разделу.

EXPLAIN ANALYZE и B-tree индексы PostgreSQL: безопасная оптимизация

Автор: · Обновлено

Оптимизация PostgreSQL начинается не с команды CREATE INDEX, а с проверяемой гипотезы. EXPLAIN показывает выбранный план и оценки планировщика, а EXPLAIN ANALYZE действительно…

Оптимизация PostgreSQL начинается не с команды CREATE INDEX, а с проверяемой гипотезы. EXPLAIN показывает выбранный план и оценки планировщика, а EXPLAIN ANALYZE действительно выполняет запрос и добавляет фактическое время и число строк. B-tree — индекс по умолчанию, хорошо подходящий для равенства, диапазонов и упорядоченного доступа, но не обязанный выигрывать в каждом запросе.

Безопасная лаборатория

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

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  status text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  total numeric(12, 2) NOT NULL CHECK (total >= 0)
);

SELECT id, created_at, total
FROM orders
WHERE customer_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Сохраните исходный SQL, значения параметров и снимок данных. Иначе «до» и «после» будут разными экспериментами. На production сначала используйте обычный EXPLAIN или безопасную копию нагрузки.

EXPLAIN и EXPLAIN ANALYZE — не одно и то же

EXPLAIN SELECT ... строит план, но не выполняет сам SELECT. EXPLAIN (ANALYZE) SELECT ... исполняет запрос и показывает фактические показатели каждого узла. Для изменяющих команд это критично: EXPLAIN ANALYZE над INSERT, UPDATE или DELETE внесёт изменения.

EXPLAIN (FORMAT TEXT)
SELECT id, created_at, total
FROM orders
WHERE customer_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, created_at, total
FROM orders
WHERE customer_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

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

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders SET status = 'archived' WHERE created_at < now() - interval '2 years';
ROLLBACK;

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

План состоит из узлов. Читайте его от нижних источников данных к верхнему результату, сохраняя структуру вложенности. Seq Scan просматривает таблицу, Index Scan идёт через индекс и получает строки таблицы, Index Only Scan может при подходящих условиях получить нужные данные из индекса, а Sort, Hash, Nested Loop и другие узлы выполняют последующие операции.

В оценочной части важны cost=startup..total и rows. Стоимость — внутренняя сравнительная единица планировщика, а не миллисекунды. В фактической части смотрите actual time, rows и loops. Время узла накоплено по исполнениям; при большом loops нельзя воспринимать одну строку вывода как однократную работу.

Главный сигнал — расхождение estimated rows и actual rows

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

После крупной загрузки данных проверьте актуальность статистики и при необходимости выполните ANALYZE orders;. Не лечите ошибку оценки увеличением случайных параметров стоимости: сначала поймите распределение и условие запроса.

BUFFERS отделяет вычисление от ввода-вывода

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

EXPLAIN ANALYZE сам добавляет измерительную нагрузку. Для пользовательской задержки дополнительно нужны метрики приложения и репрезентативный профиль запросов.

Что умеет B-tree

PostgreSQL создаёт B-tree, если в CREATE INDEX не указан другой метод. Планировщик может рассматривать его для сравнений =, <, <=, >=, >, а также эквивалентных диапазонов вроде BETWEEN и IN. B-tree хранит упорядоченные ключи, поэтому иногда помогает одновременно фильтровать и выдавать строки в нужном ORDER BY.

CREATE INDEX orders_customer_status_created_idx
  ON orders (customer_id, status, created_at DESC);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total
FROM orders
WHERE customer_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Такой индекс соответствует форме конкретного запроса: равенство по клиенту и статусу, затем требуемый порядок времени. Это не рецепт для всех таблиц orders.

Порядок колонок составного индекса

Составной B-tree наиболее эффективен, когда условия ограничивают ведущие, то есть левые, колонки. Индекс (customer_id, status, created_at) естественно поддерживает запрос по customer_id и запрос по customer_id вместе со status. Запрос только по status не получает того же свойства автоматически.

Формула «самая селективная колонка всегда первая» слишком груба. Порядок выводят из реально частых условий равенства, диапазона и сортировки. Один широкий индекс также не заменяет анализ разных форм запросов.

Почему Seq Scan может быть правильным

Если запрос читает значительную часть таблицы, последовательный просмотр может быть дешевле множества обращений через индекс. То же верно для маленькой таблицы. Цель оптимизации — не убрать слова Seq Scan из плана, а уменьшить стоимость важного пользовательского сценария без неприемлемого ухудшения записей.

Каждый индекс занимает место и должен обновляться при изменениях данных. Дублирующие и редко используемые индексы делают запись дороже. Поэтому список индексов — часть модели нагрузки, а не коллекция «ускорителей».

Цикл безопасной оптимизации

  1. Зафиксируйте медленный запрос, параметры и метрику пользователя.
  2. Получите исходный план на репрезентативных данных.
  3. Сформулируйте одну причину: неверная оценка, лишняя сортировка, чтение слишком многих строк.
  4. Сделайте одно изменение — индекс, статистику или переписывание условия.
  5. Повторите тот же план и тест нагрузки.
  6. Проверьте влияние на INSERT/UPDATE, размер индекса и соседние запросы.
  7. Оставьте изменение только при измеримом выигрыше и понятной эксплуатационной цене.

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

Практика

Сгенерируйте достаточно строк orders с разными клиентами и статусами. Получите EXPLAIN (ANALYZE, BUFFERS) исходного запроса, сохраните план, создайте предложенный индекс и повторите измерение. Ответьте письменно:

Затем выполните запрос только по status и объясните, почему составной индекс может оказаться для него менее полезным.

Что важно запомнить

EXPLAIN объясняет решение планировщика, EXPLAIN ANALYZE проверяет его реальным выполнением, а B-tree является инструментом под определённую форму доступа. Хорошая оптимизация сохраняет исходный замер, меняет одну причину, повторяет эксперимент и учитывает цену индекса для записей и эксплуатации.

Источники