PostgreSQLSQLоптимизацияEXPLAINиндексы

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

2026-06-02 13 мин
💡 Никогда не работал с SQL? Сначала пройди серию SQL с нуля для аналитика — 10 связанных частей от «что такое БД» до Window Functions.

«Запрос работал секунду, теперь работает 30 секунд». Если ты слышал это от DBA — пора учить EXPLAIN ANALYZE. Это команда PostgreSQL, которая показывает план выполнения запроса + реальное время на каждом этапе. Без неё ты гадаешь, с ней — лечишь bottleneck.

Этот гайд — практическое чтение EXPLAIN ANALYZE с 4 реальными примерами оптимизации.

Что такое EXPLAIN и EXPLAIN ANALYZE

EXPLAIN показывает планируемый план запроса. Без выполнения. Быстро.

EXPLAIN SELECT * FROM orders WHERE user_id = 12345;

EXPLAIN ANALYZE показывает реальный план с выполнением. Запрос реально запускается, ты получаешь actual rows / actual time.

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 12345;

Опции (полная команда):

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 12345;

Как читать plan tree

Plan читается снизу вверх (или изнутри-наружу). Самые вложенные nodes выполняются первыми.

Aggregate  (cost=8.45..8.46 rows=1 width=8) (actual time=0.156..0.157 rows=1 loops=1)
  ->  Index Scan using orders_user_id_idx on orders  (cost=0.43..8.42 rows=10 width=8) (actual time=0.041..0.142 rows=15 loops=1)
        Index Cond: (user_id = 12345)
Planning Time: 0.234 ms
Execution Time: 0.187 ms

Читаем снизу вверх:

Cost vs Actual: главные числа

Каждый node имеет 2 пары чисел:

(cost=8.45..8.46 rows=1 width=8) (actual time=0.156..0.157 rows=1 loops=1)
ЧислоЧто значит
cost=startup..totalОценка cost'а (planner's estimate). Безразмерное число
rows (planner)Сколько rows планер ожидал
actual time=startup..totalРеальное время (ms)
actual rowsСколько rows реально вернулось
loopsСколько раз выполнился node (важно для nested loop)

Главный сигнал — расхождение между rows (planner) и actual rows. Если planner думал 10 rows, а пришло 10000 — статистика устарела или query сложный для оптимизатора.

-- Обнови статистику если расхождение большое
ANALYZE orders;

Типы Scan: Seq Scan vs Index Scan

Seq Scan (Sequential Scan)

Читает всю таблицу последовательно. Хорошо для:

Seq Scan on orders  (cost=0.00..1234.56 rows=50000 width=8)
  Filter: (status = 'completed')
  Rows Removed by Filter: 30000

Bad sign: Seq Scan на большой таблице (>100k rows) с малым числом результатов. Fix: добавь индекс на колонку из Filter.

Index Scan

Использует индекс для поиска rows. Очень быстро при селективных условиях.

Index Scan using orders_status_idx on orders  (cost=0.43..8.42 rows=10 width=8)
  Index Cond: (status = 'cancelled')

Cost=0.43..8.42 — startup 0.43, total 8.42. Гораздо быстрее Seq Scan на той же таблице.

Bitmap Index Scan + Bitmap Heap Scan

Гибрид: индекс выдаёт bitmap rows, потом heap читается по bitmap. Хорошо для multiple conditions.

Bitmap Heap Scan on orders
  Recheck Cond: (status = 'completed' AND amount > 1000)
  ->  Bitmap Index Scan on orders_status_amount_idx
        Index Cond: (status = 'completed' AND amount > 1000)

Типы JOIN

Nested Loop

Для каждой row левой таблицы — ищет matching rows в правой через индекс. Хорошо для малых результатов.

Nested Loop  (cost=0.43..2456.78 rows=100 width=16) (actual time=0.05..15.3 rows=120 loops=1)
  ->  Index Scan on users  (rows=10)
  ->  Index Scan on orders  (loops=10)
loops=10 — orders index scan выполнялся 10 раз (по 1 на каждый user).

Hash Join

Строит hash table на правой таблице, для каждой row левой — lookup в hash. Хорошо для больших join'ов.

Hash Join  (cost=234.56..5678.90 rows=50000 width=16)
  Hash Cond: (orders.user_id = users.id)
  ->  Seq Scan on orders  (rows=50000)
  ->  Hash  (rows=1000)
        ->  Seq Scan on users

Merge Join

Сортирует обе таблицы, потом merge'ит. Хорошо когда обе уже sorted (через индекс по join key).

Пример 1. Тормозит COUNT с WHERE

SELECT COUNT(*) FROM orders WHERE created_at >= '2026-01-01';
-- 3.4 seconds (бывает раз в день в репорте)

EXPLAIN ANALYZE:

Aggregate  (cost=15234.56..15234.57 rows=1)
  ->  Seq Scan on orders  (rows=1500000)
        Filter: (created_at >= '2026-01-01')
        Rows Removed by Filter: 4500000

Проблема: Seq Scan по 6M rows, фильтрует 75%. Нет индекса по created_at.

Fix:

CREATE INDEX orders_created_at_idx ON orders (created_at);

После:

Aggregate  (cost=24.56..24.57 rows=1)
  ->  Index Only Scan on orders (rows=1500000)
        Index Cond: (created_at >= '2026-01-01')

Время с 3.4s → 0.08s. 40× быстрее.

Пример 2. JOIN тормозит на больших таблицах

SELECT u.name, SUM(o.amount)
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at >= '2026-01-01'
GROUP BY u.name;

EXPLAIN:

GroupAggregate
  ->  Nested Loop  (rows=10000, loops=1)
        ->  Seq Scan on orders  (rows=10000)
              Filter: (created_at >= '2026-01-01')
        ->  Index Scan on users  (rows=1, loops=10000)

Проблема: Nested loop делает 10k index scans. Может быть быстрее Hash Join.

Fix: скорее всего нужен индекс на orders(created_at, user_id) — composite + хороший selectivity.

CREATE INDEX orders_created_user_idx ON orders (created_at, user_id);

Или forced Hash Join (редко требуется):

SET enable_nestloop = off;
-- запрос
SET enable_nestloop = on;  -- сбросить

Пример 3. NULLs ломают индекс

SELECT * FROM users WHERE email IS NULL;
Seq Scan on users  (rows=1000)
  Filter: (email IS NULL)

Обычный B-tree индекс может игнорировать NULLs. Fix: partial index.

CREATE INDEX users_email_null_idx ON users (id) WHERE email IS NULL;

Пример 4. LIKE с wildcard не использует индекс

SELECT * FROM products WHERE name LIKE '%phone%';
LIKE '%phone%' (wildcard в начале) → Seq Scan, всегда.

Fix 1: trigram index (pg_trgm extension)

CREATE EXTENSION pg_trgm;
CREATE INDEX products_name_trgm_idx ON products USING GIN (name gin_trgm_ops);

Fix 2: full-text search

CREATE INDEX products_name_fts_idx ON products USING GIN (to_tsvector('russian', name));
SELECT * FROM products WHERE to_tsvector('russian', name) @@ to_tsquery('russian', 'phone');

Шаг 1: Воспроизведи slow query

SELECT pg_stat_statements_reset(); → подожди пока приложение генерит queries → SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10; — топ-10 самых медленных запросов.

Шаг 2: Запусти EXPLAIN ANALYZE

С опциями BUFFERS, FORMAT TEXT. Прочитай plan снизу вверх.

Шаг 3: Найди самый дорогой node

По actual time — то место, где выполняется больше всего ms. Это bottleneck.

Шаг 4: Определи причину

Anti-patterns

1. Не добавляй индекс на каждую колонку. Каждый индекс замедляет INSERT/UPDATE. 5 хороших индексов лучше 50 случайных.

2. Не игнорируй cost — у тебя dev DB маленькая, на prod может быть в 1000× больше. Тестируй на staging с realistic data volume.

3. Не лечи симптомы (OFFSET 100000 тормозит → добавь кэш). Лечи root cause (используй cursor / keyset pagination).

4. Не используй EXPLAIN без ANALYZE для performance investigation. Cost — оценка, actual time — реальность.

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

Что такое cost=startup..total?

Startup — cost до первой row (для запросов с ORDER BY включает sort). Total — cost до последней row. Если ты делаешь LIMIT 1 — смотри startup. LIMIT 1000 — total.

Что значит rows=10 width=8?

rows — оценка числа rows которые вернутся. width — средний размер row в bytes. Чем больше width, тем больше памяти / disk.

Когда статистика устаревает?

После большого INSERT / DELETE / UPDATE. PG автоматически запускает ANALYZE через auto-vacuum, но при больших batch loads вручную лучше: VACUUM ANALYZE table;.

Что лучше Hash Join или Nested Loop?

Nested Loop — для малых результатов (<1000 rows из правой таблицы). Hash Join — для больших (>10k). Merge Join — когда обе таблицы уже отсортированы (через index по join key).

Можно ли заставить PG использовать определённый индекс?

Через hints (через extension pg_hint_plan). Но обычно лучше** — проверь selectivity индекса через pg_stat_user_indexes и обновляй статистику. Если индекс не используется, обычно проблема в статистике или selectivity.

Связанные ресурсы

Источники

EXPLAIN ANALYZE — главный инструмент для query performance. Тренируйся на SQL-тренажёре — там настоящий PostgreSQL 16, ты можешь добавлять индексы и смотреть как меняется план.

PostgreSQL тренажёр
Настоящий PG 16 в браузере: пиши EXPLAIN ANALYZE, добавляй индексы, измеряй cost. 491 SQL-задача, бесплатные первые 5.
Открыть SQL-тренажёр →

Похожие статьи