План выполнения: EXPLAIN и здравый смысл
План выполнения: EXPLAIN и здравый смысл
EXPLAIN - это рентген запроса. Он показывает, как база планирует его выполнять, без реального выполнения. EXPLAIN ANALYZE - это рентген с операцией: сначала выполняет запрос, потом показывает реальные числа.
Базовый EXPLAIN
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';
Вывод:
QUERY PLAN
--------------------------------------------------------------
Seq Scan on users (cost=0.00..35.50 rows=1 width=100)
Filter: (email = 'alice@example.com')
Что это значит:
Seq Scan- полный скан таблицы (плохо на больших таблицах)cost=0.00..35.50- оценочная стоимость (начало..конец). Выше число = медленнее.rows=1- ожидается 1 строкаwidth=100- каждая строка ~100 байт
EXPLAIN ANALYZE: реальные числа
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';
Вывод:
QUERY PLAN
--------------------------------------------------------------
Seq Scan on users (cost=0.00..35.50 rows=1 width=100) (actual time=0.150..0.200 rows=1 loops=1)
Filter: (email = 'alice@example.com')
Planning Time: 0.050 ms
Execution Time: 0.210 ms
Новая информация:
actual time=0.150..0.200 rows=1- реально заняло 0.2 мс, найдено 1 строкаloops=1- был один проход через операцию- Если
rows=1(оценка) иactual rows=1совпадают - оптимизатор (планировщик) угадал хорошо
⚠️ ANALYZE выполняет запрос реально
EXPLAIN ANALYZEзапускает твой запрос! На проде это может:
- Загрузить базу (если UPDATE/DELETE)
- Взять блокировки
- Спалить кэш
Для UPDATE/DELETE вместо ANALYZE используй простой
EXPLAINили оборачивай в транзакцию с ROLLBACK.
Типы сканов
Seq Scan (Sequential Scan)
EXPLAIN SELECT * FROM users WHERE email LIKE '%example%';
Seq Scan on users (cost=0.00..35.50 rows=1 width=100)
Filter: (email LIKE '%example%')
База читает всю таблицу строка за строкой и проверяет условие. На миллионе строк это медленно.
Index Scan
CREATE INDEX idx_users_email ON users(email);
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';
Index Scan using idx_users_email on users (cost=0.29..8.31 rows=1 width=100)
Index Cond: (email = 'alice@example.com')
База использует индекс, чтобы быстро найти строку. Оценочная стоимость упала с 35.50 до 8.31. Это условные единицы планировщика, а не миллисекунды: реальное ускорение покажет EXPLAIN ANALYZE.
Index Only Scan
EXPLAIN SELECT email FROM users WHERE email = 'alice@example.com';
Index Only Scan using idx_users_email on users (cost=0.29..8.30 rows=1 width=32)
Index Cond: (email = 'alice@example.com')
База вообще не открывает основную таблицу, только индекс. Самое быстрое.
Bitmap Index Scan + Bitmap Heap Scan
EXPLAIN SELECT * FROM products WHERE category = 'electronics' AND price > 100;
Bitmap Heap Scan on products (cost=113.76..2116.66 rows=8074 width=119)
Recheck Cond: (category = 'electronics'::text)
Filter: (price > '100'::numeric)
-> Bitmap Index Scan on idx_products_category (cost=0.00..111.74 rows=10060 width=0)
Index Cond: (category = 'electronics'::text)
Узлов два, и работают они в паре. Сначала Bitmap Index Scan проходит по индексу на category и собирает битовую карту страниц таблицы, где есть подходящие строки. Потом Bitmap Heap Scan читает эти страницы по порядку, перепроверяет условие (Recheck Cond) и применяет фильтр price > 100. Компромисс между Seq и Index: строк много для Index Scan, но мало для чтения всей таблицы.
Joins: как база соединяет таблицы
Ту же самую операцию JOIN база умеет выполнять тремя разными способами и выбирает сама:
Nested Loop
Nested Loop (cost=... rows=...)
-> Index Scan on products
-> Index Scan on orders
Для каждой строки слева выполняет запрос справа. На малых наборах нормально, на больших медленно (O(n²)).
Hash Join
Hash Join (cost=... rows=...)
Hash Cond: (o.user_id = u.id)
-> Seq Scan on orders o
-> Hash
-> Seq Scan on users u
Читает одну таблицу (в плане она под узлом Hash, обычно та, что меньше) и строит по ней hash table в памяти, потом проходит вторую и для каждой строки ищет совпадение в хеш-таблице. Быстро на больших наборах, использует память.
Merge Join
Merge Join (cost=... rows=...)
-> Sort on id
-> Index Scan on products
Сортирует обе таблицы по ключу, потом проходит одновременно. Быстро, если обе таблицы уже отсортированы (через индекс).
Примеры: оптимизация
Плохо: Seq Scan на большой таблице
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT,
created_at TIMESTAMP,
total DECIMAL
);
EXPLAIN SELECT * FROM orders WHERE user_id = 42;
Seq Scan on orders (cost=0.00..3500.00 rows=1000 width=100)
Filter: (user_id = 42)
Оценочная стоимость очень высокая - таблица большая, сканируем всё.
Хорошо: добавляем индекс
CREATE INDEX idx_orders_user_id ON orders(user_id);
EXPLAIN SELECT * FROM orders WHERE user_id = 42;
Index Scan using idx_orders_user_id on orders (cost=0.29..50.00 rows=1000 width=100)
Index Cond: (user_id = 42)
Оценочная стоимость упала с 3500 до 50 - в 70 раз. Это не значит, что запрос стал ровно в 70 раз быстрее: cost - условные единицы, реальное время покажет EXPLAIN ANALYZE.
EXPLAIN FORMAT JSON
Для программной обработки:
EXPLAIN (FORMAT JSON) SELECT * FROM users WHERE id = 1;
Вывод:
[
{
"Plan": {
"Node Type": "Index Scan",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "users_pkey",
"Relation Name": "users",
"Alias": "users",
"Startup Cost": 0.28,
"Total Cost": 8.29,
"Plan Rows": 1,
"Plan Width": 24,
"Index Cond": "(id = 1)"
}
}
]
Ключи Planning Time и Execution Time (и фактические Actual Rows) появятся только с EXPLAIN (ANALYZE, FORMAT JSON), когда запрос реально выполняется.
Парсится в приложении для анализа.
Чек-лист оптимизации
| Проблема | Решение |
|---|---|
| Seq Scan где Seq Scan плохо | Добавить индекс |
| Оценка rows != actual rows на 10x | Анализировать таблицу: ANALYZE table_name |
| Дорогой Hash Join на больших таблицах | Индексы на join колонки, или переписать запрос |
| Фильтр на неиндексированной колонке | Добавить индекс или денормализовать |
| Index Scan но медленно всё равно | Может быть индекс фрагментирован: REINDEX |
EXPLAIN VERBOSE для деталей
EXPLAIN VERBOSE SELECT * FROM orders WHERE user_id = 42;
Index Scan using idx_orders_user_id on public.orders (cost=0.29..50.00 rows=1000)
Output: id, user_id, created_at, total
Index Cond: (user_id = 42)
Показывает точные имена таблиц, какие колонки выводятся.
ℹ️ Как читать EXPLAIN
- Посмотри на самую верхнюю операцию (главный план)
- Проверь используется ли индекс или Seq Scan
- Посмотри cost - если > 10000, может быть проблема
- Используй ANALYZE, чтобы сравнить оценку с реальностью
- Если actual rows сильно расходится с rows, статистика устарела или неточна: запусти
ANALYZE, при необходимости увеличь statistics target
Практические задания
- Создай таблицу с 100k строк (можешь скрипт написать)
- Напиши SELECT по неиндексированному полю, посмотри Seq Scan и cost
- Добавь индекс, повтори EXPLAIN - cost должен упасть
- Напиши JOIN двух таблиц, посмотри тип join (Nested Loop vs Hash)
- Используй EXPLAIN ANALYZE, сравни rows vs actual rows
- Попробуй EXPLAIN FORMAT JSON и распечатай JSON
- Оптимизируй один медленный запрос (добавь индекс / переписи запрос)