POSTGRESQL
#postgresql#explain#optimization#performance#index

PostgreSQL: EXPLAIN ANALYZE — как найти медленный запрос

Как включить и читать план выполнения запроса: EXPLAIN ANALYZE, BUFFERS, Seq Scan vs Index Scan. По официальной документации PostgreSQL.

Как использовать

  1. Перед своим запросом выполни: EXPLAIN (ANALYZE, BUFFERS) SELECT ...
  2. Seq Scan по большой таблице — добавь индекс по полям в WHERE/ORDER BY.
  3. Если rows в плане сильно расходится с actual rows — выполни ANALYZE table_name;

Медленный SELECT в PostgreSQL проще всего разбирать по плану выполнения: видно, по каким таблицам идёт полное сканирование, используются ли индексы, сколько строк реально обработано. EXPLAIN показывает план без выполнения; EXPLAIN ANALYZE выполняет запрос и добавляет фактические время и число строк. Проблема: без плана непонятно, почему запрос тормозит — полное сканирование большой таблицы (Seq Scan) или устаревшая статистика. Симптомы: долгий ответ на выборке, высокая нагрузка на БД. Ниже — как вызывать EXPLAIN (ANALYZE, BUFFERS), как читать вывод (Seq Scan vs Index Scan) и что делать по результату по документации Using EXPLAIN.

Решение

Поиск узких мест через план выполнения. EXPLAIN — только план; EXPLAIN ANALYZE — запрос выполняется, в выводе actual time, actual rows. BUFFERS — использование кэша (shared/hit read).

Базовый вызов

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE created_at > now() - interval '7 days';

Форматы вывода: TEXT (по умолчанию), JSON, YAML, XML. Для консоли достаточно TEXT.

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

Плохо — последовательное сканирование большой таблицы:

Seq Scan on orders  (cost=0.00..1234.00 rows=5000 width=80) (actual time=0.05..89.22 rows=4823 loops=1)
  Filter: (created_at > (now() - '7 days'::interval))
  Rows Removed by Filter: 95177
Planning Time: 0.12 ms
Execution Time: 91.45 ms
  • Seq Scan — чтение всей таблицы.
  • Rows Removed by Filter — сколько строк отброшено; при большом числе запрос тяжёлый.

Хорошо — использование индекса:

Index Scan using idx_orders_created_at on orders  (cost=0.42..312.18 rows=5000 width=80) (actual time=0.03..8.11 rows=4823 loops=1)
  Index Cond: (created_at > (now() - '7 days'::interval))
Planning Time: 0.08 ms
Execution Time: 9.02 ms
  • Index Scan — планёр использует индекс; время выполнения обычно сильно меньше.

Что делать по результату

СитуацияДействие
Seq Scan по большой таблицеДобавить индекс по полям в WHERE / ORDER BY
rows в плане сильно ≠ actual rowsВыполнить ANALYZE table_name;
Нужна только статистика, без выполненияИспользовать EXPLAIN без ANALYZE

Пример индекса по дате:

CREATE INDEX idx_orders_created_at ON orders(created_at);

После создания индекса снова выполните EXPLAIN (ANALYZE, BUFFERS) SELECT ... и сравните Execution Time.

Минимальный пример

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

-- Если Seq Scan по orders — добавь индекс, например:
-- CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);
-- Затем снова EXPLAIN (ANALYZE, BUFFERS) ...

Проверка

  1. Запуск EXPLAIN — выполните ваш медленный запрос с префиксом EXPLAIN (ANALYZE, BUFFERS) в psql или клиенте. Убедитесь, что запрос реально выполняется (ANALYZE) и в выводе есть actual time и actual rows.

  2. Сравнение до и после индекса — сохраните вывод EXPLAIN до создания индекса, создайте индекс, выполните EXPLAIN снова. Execution Time и тип сканирования (Seq Scan → Index Scan) должны измениться в пользу индекса.

  3. Статистика — если rows в плане сильно отличается от actual rows, обновите статистику: ANALYZE orders; (или ваша таблица), затем снова EXPLAIN.

  4. Без выполнения запроса — для тяжёлых запросов, которые не хотите выполнять, используйте только EXPLAIN (без ANALYZE). План будет оценочным, без actual time/rows.

Типичные ошибки

  • Не смотреть на Execution Time — главный показатель «медленности» в выводе ANALYZE — Execution Time. Сравнивайте его до и после изменений (индекс, ANALYZE, переписывание запроса).
  • Индекс по полю, не участвующему в WHERE/ORDER BY — индекс должен покрывать условия и сортировку. Иначе планёр может не использовать его. Для составного условия создайте составной индекс (например status, created_at).
  • Забыть BUFFERS — BUFFERS показывает, сколько блоков прочитано из кэша и с диска. Помогает понять, упирается ли запрос в I/O. Для первичной оценки достаточно ANALYZE; BUFFERS добавляйте при углублённой оптимизации.

Где применять

  • Prod / dev: любой медленный SELECT в PostgreSQL. Запускайте EXPLAIN (ANALYZE, BUFFERS) в psql, из приложения (например node-pg) или из админки БД.
  • После добавления индекса — всегда перепроверяйте план и Execution Time; при необходимости обновите статистику (ANALYZE).

Связанные сниппеты: JSONB и GIN-индекс, Запросы через node-pg.

Документация: Using EXPLAIN, EXPLAIN.

Похожие сниппеты