← назад к разделу

Запрос работает медленно, но непонятно почему. Добавить индекс? Переписать JOIN? Увеличить память? Ответ на все эти вопросы даёт одна команда — EXPLAIN ANALYZE. Она показывает, что именно PostgreSQL делал во время выполнения запроса и сколько времени потратил на каждый шаг.

Как запустить

Минимальный вариант — просто добавить EXPLAIN ANALYZE перед запросом:

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'PAID';

Лучший вариант — добавить BUFFERS, чтобы видеть, сколько данных шло из кеша, а сколько с диска:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'PAID';

Важный момент: EXPLAIN ANALYZE выполняет запрос по-настоящему. Это нужно для точного замера. Если вы хотите проверить план для UPDATE или DELETE, не применяя изменения, оберните в транзакцию:

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'X' WHERE id = 123;
ROLLBACK;

Как читать вывод

Вот пример вывода:

Hash Join  (cost=12.34..567.89 rows=1000 width=64) (actual time=1.234..56.789 rows=987 loops=1)
  Hash Cond: (a.id = b.a_id)
  Buffers: shared hit=1234 read=56
  ->  Seq Scan on a
  ->  Hash
        ->  Seq Scan on b

Что означает каждая часть:

  • cost=START..TOTAL — оценка PostgreSQL в условных единицах. Это не миллисекунды.
  • rows=N — сколько строк PostgreSQL ожидал получить.
  • actual time=START..TOTAL — реальное время в миллисекундах.
  • actual rows=N loops=M — сколько строк реально вернул узел и сколько раз он запускался.
  • Buffers: shared hit=X read=Y — hit это данные из кеша, read это обращения к диску.

Читать план нужно снизу вверх. Нижние узлы — это внутренние операции, которые выполняются первыми. Корень — финальный результат.

Первое на что смотреть — расхождение между rows= (оценка) и actual rows= (факт). Если оценка в десять и более раз меньше реального числа строк, PostgreSQL строит план на неверных данных. Помогает команда ANALYZE на таблице — она обновляет статистику.

Второй важный момент — loops. Если узел запускался много раз, реальное время нужно умножить: loops=10000 × actual time=0.5..1.2 означает 12 секунд внутри одного Nested Loop.

Узлы сканирования таблицы

PostgreSQL выбирает способ читать таблицу исходя из размера выборки, наличия индексов и настроек стоимости.

Seq Scan — полный проход

Seq Scan on orders
  Filter: (status = 'PAID')
  Rows Removed by Filter: 850000

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

Index Scan — проход по индексу

Index Scan using ix_orders_status on orders
  Index Cond: (status = 'PAID')

PostgreSQL идёт по B-tree индексу, потом читает нужные строки из таблицы. Обратите внимание на разницу между Index Cond: и Filter:. Если условие попало в Filter:, а не в Index Cond:, значит индекс по этому полю не используется как ключ поиска — и строки всё равно перебираются.

Index Only Scan — только индекс, без таблицы

Index Only Scan using ix_orders_status_created on orders
  Index Cond: (status = 'PAID')
  Heap Fetches: 12

Если все нужные колонки есть в индексе, PostgreSQL может не обращаться к таблице вообще. Это самый быстрый вариант.

Если Heap Fetches больше нуля, PostgreSQL всё-таки ходит в таблицу — карта видимости устарела и не позволяет обойтись без проверки. Помогает VACUUM на таблице.

Bitmap Scan — двухфазное чтение

Bitmap Heap Scan on orders
  Recheck Cond: (status = 'PAID')
  ->  Bitmap Index Scan on ix_orders_status

Двухфазный процесс: сначала строится битовая карта страниц, которые содержат нужные строки, затем эти страницы читаются по порядку. Это эффективнее случайного чтения при средней доле выборки. Bitmap также позволяет комбинировать несколько индексов (BitmapAnd, BitmapOr).

Recheck Cond: означает повторную проверку условия — это происходит, когда битовая карта хранит только страницы, а не конкретные строки (lossy bitmap из-за нехватки памяти).

Соединение таблиц

Nested Loop — цикл внутри цикла

Nested Loop  (rows=100 loops=1)
  ->  Index Scan on a  (rows=100)
  ->  Index Scan on b  (rows=1 loops=100)
        Index Cond: (b.a_id = a.id)

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

Частая ловушка: loops=1000000 при actual time=0.5 — это 500 секунд внутри одного узла. Если внутренняя таблица большая и без индекса, Nested Loop деградирует.

Hash Join — хеш-таблица в памяти

Hash Join
  Hash Cond: (a.id = b.a_id)
  ->  Seq Scan on a
  ->  Hash
        ->  Seq Scan on b

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

Если в выводе Batches: 2 и больше — хеш-таблица не уместилась в памяти и PostgreSQL спилился на диск. Помогает увеличить work_mem для сессии:

SET work_mem = '64MB';
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Merge Join — слияние двух сортированных потоков

Merge Join
  Merge Cond: (a.id = b.a_id)
  ->  Sort ...
  ->  Index Scan on ix_b_a_id

Оба потока отсортированы по ключу соединения и сливаются как зиппер. Эффективен при больших объёмах данных, когда оба потока уже отсортированы (есть подходящий индекс) или стоимость сортировки оправдана.

Сортировка и группировка

Sort — как понять, что данные идут с диска

Sort
  Sort Key: created_at DESC
  Sort Method: external merge  Disk: 8192kB

external merge Disk: означает, что сортировка не уместилась в памяти и ушла на диск. Это медленно. Решений два: увеличить work_mem или добавить индекс с нужным порядком сортировки, чтобы узел Sort исчез из плана.

Если сортировка в памяти — будет quicksort или top-N heapsort (для ORDER BY ... LIMIT N).

Группировка

  • Aggregate — простые агрегаты: count(*), sum().
  • HashAggregate — GROUP BY через хеш-таблицу в памяти. Быстро, пока данные помещаются.
  • GroupAggregate — GROUP BY после сортировки. Используется, когда HashAggregate не влезает в work_mem.

Параллельный план

Gather
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on orders

PostgreSQL может разбить работу между несколькими параллельными воркерами. Количество задаётся параметром max_parallel_workers_per_gather (по умолчанию 2). Маленькие таблицы не параллелятся — порог регулирует min_parallel_table_scan_size (по умолчанию 8 МБ).

Инструменты для сложных планов

Когда план состоит из пяти и более уровней, читать его в текстовом виде неудобно. Для этого есть специализированные инструменты:

  • explain.depesz.com — цветовая подсветка узлов, показывает самые дорогие шаги.
  • explain.dalibo.com (PEV2) — графическое дерево плана прямо в браузере.
  • pg_stat_statements — расширение для сбора статистики по запросам в продакшене, без ручного EXPLAIN.

Для PEV2 удобно получить план в формате JSON:

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;

Коротко

  • EXPLAIN (ANALYZE, BUFFERS) — стандартный вариант: видно реальное время и IO.
  • EXPLAIN ANALYZE выполняет запрос; для DML используй BEGIN ... ROLLBACK.
  • Читай план снизу вверх — листья выполняются первыми.
  • Реальное время узла = actual time × loops — не забывай умножать.
  • Расхождение rows= и actual rows= в 10 раз и более → запусти ANALYZE на таблице.
  • Buffers: read= большое → данные идут с диска, нужен индекс или больше кеша.
  • Условие в Filter: вместо Index Cond: → индекс не работает как ключ поиска.
  • Heap Fetches > 0 на Index Only Scan → запусти VACUUM.
  • Batches > 1 в Hash Join или external merge Disk: в Sort → увеличь work_mem.
  • Nested Loop с большим loops → проверь наличие индекса по ключу соединения.

Что почитать дальше

  • Типы индексов PostgreSQL — что должно появиться в плане при правильном индексе.
  • Составные индексы — как Index Only Scan зависит от состава индекса.
  • Селективность — почему rows= расходится с actual rows=.
  • VACUUM и autovacuum — как устранить Heap Fetches.
  • Мониторинг и медленные запросы — pg_stat_statements для продакшена.