Смотрел ли план запроса

Смотрел ли я EXPLAIN ANALYZE при оптимизации SQL запросов

Короткий ответ

Да, постоянно. EXPLAIN ANALYZE — это инструмент номер 1 для оптимизации slow queries.

Когда я впервые это понял

Я писал запрос:

SELECT user_id, email
FROM users
WHERE company_id = 123
AND created_at > '2024-01-01';

Этот запрос выполнялся 30 секунд! Я был шокирован. 30 сек для 1M rows? Что-то не так.

Тогда я запустил:

EXPLAIN ANALYZE
SELECT user_id, email
FROM users
WHERE company_id = 123
AND created_at > '2024-01-01';

Что показал EXPLAIN ANALYZE

Seq Scan on users  (cost=0.00..45234.00 rows=10000 width=42)
  Filter: (company_id = 123 AND created_at > '2024-01-01')
  Rows: 10000 (estimated 10000, actual 8234)
  Planning time: 0.231 ms
  Execution time: 28564.223 ms

Что это означает

Seq Scan (Sequential Scan): PostgreSQL смотрит на каждый row в таблице (1M rows)

cost=0.00..45234.00: Estimated cost (0 = first row, 45234 = all rows)

Execution time: 28 сек — это было узким местом!

Решение

Добавить index на (company_id, created_at):

CREATE INDEX idx_users_company_date 
ON users(company_id, created_at);

После index:

EXPLAIN ANALYZE
SELECT user_id, email
FROM users
WHERE company_id = 123
AND created_at > '2024-01-01';

Index Scan using idx_users_company_date on users
  Index Cond: (company_id = 123 AND created_at > '2024-01-01')
  Rows: 8234 (estimated 8400, actual 8234)
  Planning time: 0.145 ms
  Execution time: 23.456 ms  <-- 28 сек было, теперь 23 мс!

Razлика: 28,000 мс → 23 мс. 1000x быстрее!

Как я пользуюсь EXPLAIN ANALYZE

Шаг 1: Понять как PostgreSQL выполняет запрос

- Seq Scan (плохо, смотрит все rows)
- Index Scan (хорошо, пропускает ненужные rows)
- Hash Join vs Nested Loop (разные стратегии)
- Sort (может быть дорого)

Шаг 2: Смотреть на стоимость (cost)

• Если estimated cost >> actual rows: плохой estimate
• Пример: estimated 1M rows, actual 100 rows — нужен analyze

Шаг 3: Смотреть на Execution time

Если > 1 сек — это slow и нужна оптимизация.

Шаг 4: Смотреть на Rows (Planning)

Esimated vs Actual.

• Если estimate = actual → хорошо (optimizer знает данные)
• Если estimate >> actual → может быть плохой join order
• Если estimate << actual → может быть missing index

Практические примеры optimization

Problem 1: Missing Index

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE user_id = 456;

Результат: Seq Scan, 5 сек

Решение:

CREATE INDEX idx_orders_user_id ON orders(user_id);

Афтер: Index Scan, 50 мс

Problem 2: Bad Join Order

EXPLAIN ANALYZE
SELECT o.*, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.country = 'US';

Eсли это делает Nested Loop (slow), переписать:

SELECT o.*, u.name
FROM orders o
JOIN (
  SELECT id, name FROM users WHERE country = 'US'
) u ON o.user_id = u.id;

Это может помочь optimizer понять что нужен Hash Join (быстрее).

Problem 3: Too Many Rows in Memory

EXPLAIN ANALYZE
SELECT *
FROM large_table
ORDER BY created_at DESC
LIMIT 10;

Если это делает Sort entire table потом LIMIT — slow.

Чек: нужен ли Index на created_at DESC?

CREATE INDEX idx_created_at_desc ON large_table(created_at DESC);

Метод optimization

1. Запустить EXPLAIN ANALYZE на slow query
2. Identify bottleneck (Seq Scan? Hash Join? Sort?)
3. Add index or rewrite query
4. ANALYZE table (update stats)
5. Run EXPLAIN ANALYZE снова
6. Compare: старая vs новая

Когда я пользуюсь EXPLAIN ANALYZE

Ежедневно:

• Пишу query, заметил что медленная → EXPLAIN
• Хочу быть sure что мой index используется → EXPLAIN
• PR review: смотрю на сложный query → EXPLAIN

Еженедельно:

• Performance review дашборда: какие запросы slow?
• Analyzing slow_log в PostgreSQL
• Работа с инженерами: "Вот почему твой query медленный"

Ежемесячно:

• VACUUM и ANALYZE table (обновить statistics)
• Review unused indexes (drop them, они замедляют writes)

Советы

1. Всегда используй EXPLAIN ANALYZE, не просто EXPLAIN

• EXPLAIN показывает estimated plan
• EXPLAIN ANALYZE показывает actual execution (медленнее, но правдива)

2. Смотри на Filter vs Index Cond

Filter: (status = 'active')  <-- applied AFTER index, может быть медленно
Index Cond: (user_id = 123)  <-- used in index, быстро

3. Добавляй BUFFERS для понимания I/O

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...

Показывает: сколько блоков read из disk vs cache

4. Обнови STATS если indices не используются

ANALYZE users;

Оптимизатор может не знать что index поможет.

EXPLAIN ANALYZE в других БД

PostgreSQL:

EXPLAIN ANALYZE SELECT ...

MySQL/MariaDB:

EXPLAIN FORMAT=JSON SELECT ...

BigQuery:

-- Нет EXPLAIN, но есть query stats в UI

Snowflake:

EXPLAIN SELECT ...

Мой вывод

Если query медленная, EXPLAIN ANALYZE — first thing. Без этого ты гадаешь. С этим — видишь ровно что делает БД.

Это как X-ray для SQL. Показывает что не видно с первого взгляда.