План выполнения: 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 база умеет выполнять тремя разными способами и выбирает сама:

План EXPLAIN как дерево: листья читают данные, корень отдаёт результат

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

  1. Посмотри на самую верхнюю операцию (главный план)
  2. Проверь используется ли индекс или Seq Scan
  3. Посмотри cost - если > 10000, может быть проблема
  4. Используй ANALYZE, чтобы сравнить оценку с реальностью
  5. Если 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
  • Оптимизируй один медленный запрос (добавь индекс / переписи запрос)

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