Для 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 - 4х быстрее!

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

EXPLAIN SELECT * FROM products WHERE category = 'electronics' AND price > 100;

Bitmap Index Scan on idx_products_category  (cost=12.54..1234.55 rows=5000 width=0)
  Index Cond: (category = 'electronics')
Filter: (price > 100)

База использует индекс на category, потом применяет фильтр price > 100 к результатам. Компромисс между Seq и Index.

Joins: как база соединяет таблицы

Ту же самую операцию JOIN база умеет выполнять тремя разными способами и выбирает сама:

Nested Loop

Nested Loop  (cost=... rows=...)
 -> Index Scan on products
 -> Index Scan on orders

Для каждой строки слева выполняет запрос справа. На малых наборах нормально, на больших медленно (O(n²)).

Hash Join

Hash Join  (cost=... rows=...)
 -> Seq Scan on products
 -> Seq Scan on orders

Полностью читает левую таблицу, создаёт 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 раз быстрее!

EXPLAIN FORMAT JSON

Для программной обработки:

EXPLAIN (FORMAT JSON) SELECT * FROM users WHERE id = 1;

Вывод:

[
  {
    "Plan": {
      "Node Type": "Index Scan",
      "Relation Name": "users",
      "Index Name": "users_pkey",
      "Rows": 1,
      "Total Cost": "0.29"
    },
    "Planning Time": 0.05,
    "Execution Time": 0.10
  }
]

Парсится в приложении для анализа.

Чек-лист оптимизации

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)

Показывает точные имена таблиц, какие колонки выводятся.

Практические задания

• Создай таблицу с 100k строк (можешь скрипт написать)
• Напиши SELECT по неиндексированному полю, посмотри Seq Scan и cost
• Добавь индекс, повтори EXPLAIN - cost должен упасть
• Напиши JOIN двух таблиц, посмотри тип join (Nested Loop vs Hash)
• Используй EXPLAIN ANALYZE, сравни rows vs actual rows
• Попробуй EXPLAIN FORMAT JSON и распечатай JSON
• Оптимизируй один медленный запрос (добавь индекс / переписи запрос)