Перейти к основному содержимому

Практика: Анализ производительности (EXPLAIN / ANALYZE)


Онлайн редактор кода для PostgreSQL

Здесь вы можете попробовать свои силы в программировании на языке SQL для работы с PostgreSQL. Для этого мы предоставляем вам онлайн редактор кода, в котором вы можете написать свой код и запустить его.


❗️ При обновлении страницы код пропадёт, по этому, сохраните свой код куда-нибудь, если он важный.

Задание 1 — Чтение простых планов EXPLAIN​

Задание 1: Простые планы EXPLAIN — что делает PostgreSQL?

⏱️ Примерное время: 15-25 минут

Запусти EXPLAIN на разных запросах и определи тип сканирования, стоимость и примерное время.


Требования:

  • выполни EXPLAIN (без ANALYZE) на каждом запросе
  • определи для каждого:
    • тип сканирования (Seq Scan, Index Scan, Bitmap Index Scan и т.д.)
    • примерную стоимость (cost=...)
    • оценочное количество строк (rows=...)
    • будет ли использован индекс и какой
  • напиши, почему PostgreSQL выбрал именно этот план

Пример ответа:

Запрос: SELECT * FROM users WHERE country = 'Russia'
План: Bitmap Heap Scan → Bitmap Index Scan на idx_users_country
cost=234..4567, rows≈20000
Объяснение: селективность country ≈ 20%, индекс есть → bitmap лучше seq scan

Задание 2 — EXPLAIN ANALYZE: реальность vs оценка​

Задание 2: Сравнение оценок и реальности (EXPLAIN ANALYZE)

⏱️ Примерное время: 25-40 минут

Запусти EXPLAIN ANALYZE и найди расхождения между планом и фактом.


Требования:

  • выполни EXPLAIN (ANALYZE, BUFFERS) на каждом запросе
  • сравни:
    • оценочные rows vs actual rows
    • actual time (особенно разница между startup и total time)
    • Buffers: много ли read (чтение с диска)?
    • есть ли Sort / Hash с Disk (внешняя сортировка)?
  • укажи 1–2 возможные причины расхождений (устаревшая статистика, плохая селективность, нехватка индексов)
  • предложи хотя бы одно улучшение (индекс, переписывание запроса, ANALYZE)

Пример:

Оценка: rows=5000, actual rows=45678 → расхождение в 9 раз
actual time=0.1..234 ms, Buffers: shared read=5678
Причина: устаревшая статистика или неверная оценка селективности status + total_amount
Улучшение: CREATE INDEX idx_orders_status_amount ON orders(status, total_amount);

Задание 3 — Поиск узких мест и оптимизация​

Задание 3: Найди и устрани узкие места

⏱️ Примерное время: 30-50 минут

Определи, почему запрос медленный, и предложи оптимизацию.


Требования:

  • выполни EXPLAIN ANALYZE (BUFFERS) на исходном запросе
  • определи главный узкий узел (Seq Scan, Sort Disk, Hash Join с большим объёмом, много read и т.д.)
  • предложи минимум 1–2 улучшения (новый индекс, переписанный запрос, ограничение фильтра, ANALYZE и т.д.)
  • покажи пример плана после оптимизации (можно описать словами или запустить)

Пример:

Узкое место: Seq Scan on orders + Filter → 500k строк
Решение: CREATE INDEX idx_orders_date ON orders(order_date);
Новый план: Index Scan using idx_orders_date

Задание 4 — Статистика, bloat, неиспользуемые индексы​

Задание 4: Анализ статистики, раздутия и мёртвых индексов

⏱️ Примерное время: 25-45 минут

Проверь здоровье таблиц и индексов.


Требования:

  • используй запросы из лекции (pg_stat_user_tables, pg_stats, pg_stat_user_indexes)
  • сделай 4–6 разных проверок
  • для каждого результата напиши вывод:
    • нормально / проблема / требует внимания
    • что делать (ANALYZE, VACUUM, удалить индекс, увеличить target и т.д.)

Пример:

n_dead_tup = 120000, dead_percentage ≈ 19% → таблица раздалась
Действие: VACUUM FULL orders; или настрой autovacuum агрессивнее

Задание 5 — Итоговый аудит производительности​

Задание 5: Полный аудит запросов и схемы (итоговое)

⏱️ Примерное время: 90-150 минут

Проведи аудит реальной нагрузки и предложи оптимизации.


Требования:

  • выбери 5–7 типичных запросов из сценария
  • для каждого:
    • EXPLAIN ANALYZE + BUFFERS
    • определи узкое место
    • предложи оптимизацию (индекс, переписывание, матвью, денормализация)
  • сделай 2–3 проверки статистики / bloat / неиспользуемых индексов
  • составь итоговый список рекомендаций (5–10 пунктов)

Это задание — полноценный кейс для собеседования или портфолио по оптимизации PostgreSQL.

подсказка

Хороший план — это когда actual rows ≈ estimated rows,
а Execution Time укладывается в твои SLA.
Главные инструменты:
EXPLAIN ANALYZE + BUFFERS — твой лучший друг
ANALYZE — когда статистика врёт
pg_stat_* — когда нужно понять, что происходит в проде