Ты прошёл пятнадцать модулей. Знаешь как лежит heap, что внутри B-tree, как MVCC решает конкуренцию, что планировщик считает в страницах, как выглядят все три алгоритма JOIN. Самое время взять реальную задачу и провести её от диагноза до фикса.

Brief

Пятница 18:00. Slack:

PM: Привет. У нас «топ клиенты за месяц» в дашборде стал тормозить — был 200 мс, стал 8 секунд. Пользователи матерятся, я его временно выключил. Можешь глянуть? Запрос ниже, БД — postgres-replica, 500K orders, 50K customers. Никаких миграций мы не делали, но за последние пару недель orders в 5 раз выросли.

SELECT
  c.country,
  c.full_name,
  COUNT(o.id)            AS orders_count,
  SUM(o.total_cents)     AS revenue_cents
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.placed_at >= '2025-03-01'
  AND o.placed_at <  '2025-04-01'
  AND o.status   IN ('paid', 'shipped', 'delivered')
GROUP BY c.country, c.full_name
ORDER BY revenue_cents DESC
LIMIT 20;

PM: Ничего экзотического, я знаю. Просто стал тормозить.

Шаг 1: воспроизвести и измерить

Первое правило перформанса: не оптимизируй на угадывание. Сначала — EXPLAIN ANALYZE. У тебя есть тот же датасет (dataset="large" в нашей вселенной): 50 000 клиентов, 500 000 заказов, без индексов кроме PK.

На реальной production-БД мы бы увидели примерно такой план:

Limit  (cost=...) (actual time=8123.4 .. 8123.5 rows=20 loops=1)
  Buffers: shared hit=15234 read=8902
  ->  Sort  (cost=...) (actual time=8123.4 .. 8123.5 rows=20 ...)
        Sort Key: (sum(o.total_cents)) DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  HashAggregate ...
              ->  Hash Join  (cost=...) (actual time=312.4 .. 7891.2 rows=68234 loops=1)
                    Hash Cond: (o.customer_id = c.id)
                    ->  Seq Scan on orders o  (actual time=0.043 .. 5234.1 rows=68234 loops=1)
                          Filter: ((placed_at >= ...) AND (placed_at < ...) AND (status IN ...))
                          Rows Removed by Filter: 431766
                          Buffers: shared read=8500
                    ->  Hash  (actual time=312.2 .. 312.2 rows=50000 loops=1)
                          ->  Seq Scan on customers c ...
Planning Time: 0.234 ms
Execution Time: 8123.987 ms

Что ты видишь?

1. Seq Scan on orders прочитал 500 000 строк (rows=68234 + Rows Removed=431766). 86% работы выкинуто фильтром.
2. Buffers: shared read=8500 — половина страниц orders читалась с диска, не из кэша. Это потому что orders — большая таблица, не вся в shared_buffers.
3. Hash Join делает hash-таблицу по customers (50K строк), потом probe’ит 68K строк — это OK.
4. Top-N heapsort на 27 KiB — копейки, не виновник.

Главный виновник: Seq Scan на 500K строк, из которых нужно только 13%. Если бы у нас был индекс на (placed_at, status) — Postgres мог бы прочитать только нужный диапазон.

Шаг 2: гипотеза + индекс

Гипотеза: добавить composite index orders(placed_at, status). Это даст Index Range Scan по месяцу + фильтрацию на статус прямо в индексе.

Что должно поменяться: Seq Scan on orders превращается в Bitmap Index Scan + Bitmap Heap Scan (или Index Scan, если оптимизатор решит, что bitmap не нужен). Время Seq Scan (~5 секунд) уходит в Index Scan (~50-200 мс). Buffers падают значительно.

Ожидаемое суммарное время: с 8 секунд до 200-400 мс. Это 20-40x ускорение от одного индекса.

Шаг 3: проверка плана на регрессии

Прежде чем выкатывать в прод — три вопроса:

1. Влияет ли индекс на INSERT/UPDATE? Да, на ~3-5% медленнее (каждый INSERT теперь обновляет ещё одну B-tree). На write-heavy системе это значимо, на read-heavy — копейки. У нас аналитический отчёт читается каждый день, заказы пишут реже — индекс окупится.
2. Не мешает ли индекс другим запросам? Не должен — он на новой паре колонок. Но проверь через pg_stat_user_indexes.idx_scan после релиза — если индекс не используется ни в одном запросе через неделю, удали его.
3. Использует ли индекс существующий запрос-сосед? Да, на placed_at >= фильтре сработает leftmost prefix rule — все запросы со старшим столбцом placed_at теперь могут им пользоваться.

Шаг 4: можно ли ещё лучше?

Дальнейшая оптимизация — выбор:

• Covering index с INCLUDE: CREATE INDEX ... ON orders(placed_at, status) INCLUDE (customer_id, total_cents). Тогда Index Only Scan — heap не читаем вообще. Это ещё 30-50% времени.
• Partial index: ... WHERE status IN ('paid', 'shipped', 'delivered'). Меньше размер, такая же скорость на нашем запросе. Хорошо если эти три статуса — стабильный фильтр.
• Partitioning: разрезать orders на партиции по месяцу. Полезно, если таблица растёт до десятков миллионов и кроме отчётов есть OLTP-нагрузка.

Для нашей задачи первого индекса достаточно. Не оптимизируй дальше без явного запроса от менеджмента — это потенциальный over-engineering.

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

1. Не оптимизируй на угадайку. Сначала EXPLAIN ANALYZE с BUFFERS — иначе ты не знаешь, что чинишь.
2. Найди bottleneck: где actual time максимально, где rows проходит больше, чем нужно, где buffers read высок.
3. Сформулируй гипотезу: один индекс, один refactor — и проверь EXPLAIN-ом до и после.
4. Подумай о side effects: что замедлится, кому ещё может повредить или помочь.
5. Не оптимизируй дальше без причины. Хороший фикс — минимальный фикс, который решает проблему.

В следующем уроке — глубокий разбор: что было не так в этом запросе с точки зрения каждого модуля курса (storage layer, B-tree, planner, statistics).