В предыдущем уроке мы написали одну строку:

CREATE INDEX idx_orders_placed_status ON orders(placed_at, status);

И время запроса упало с 8 000 мс до 250 мс. Тридцатикратное ускорение. Простая команда, но за ней — все 15 модулей этого курса. Пройдёмся по каждому уровню.

Модуль 1: Storage layer

Без индекса Postgres делал Seq Scan. Это значит: открыть relfilenode файла orders, читать страницу за страницей (по 8 KiB), на каждой смотреть line pointers и tuples, применять фильтр в memory. На 500 000 строк (≈ 7 000-9 000 страниц = 56-72 MiB) — это всегда минимум 9 000 page reads.

С индексом картина другая. B-tree-индекс выводит из «диапазон placed_at» в посортированный список ctid, и Postgres читает только нужные страницы heap’а. Если в марте было 41 000 заказов, и они равномерно распределены по 9 000 страниц — мы прочитаем ~3 000-4 000 страниц (а не 9 000). Если же placed_at коррелирует с физическим порядком (новые заказы попадают на новые страницы — обычно так и есть), то ещё меньше — ~1 000 страниц.

Bottom line: индекс работает не потому что «быстрее, чем Seq Scan», а потому что читает меньше 8 KiB страниц.

Модули 2-3: B-tree и альтернативные индексы

Почему (placed_at, status), а не два отдельных индекса? Leftmost prefix rule.

Composite B-tree-индекс (A, B) отвечает на запросы:

• WHERE A = ?
• WHERE A = ? AND B = ?
• WHERE A > ? AND A < ? (range на старшем столбце)

Но не отвечает на WHERE B = ? без A. Поэтому при выборе порядка колонок в composite важно: сначала тот столбец, по которому делаем range, потом — equality (или сначала самый селективный equality).

В нашем случае placed_at — range (>= '2025-03-01' AND < '2025-04-01'), status — equality (IN (...)). Соответственно — (placed_at, status). И B-tree-страница для марта 2025 хранит ~150 (placed_at, status, ctid)-кортежей, плотно отсортированных.

Можно ли BRIN вместо B-tree? Только если placed_at физически отсортирован в heap (новые заказы всегда в конец). Если у тебя так — BRIN в 100 раз меньше по размеру и почти такой же быстрый.

Модуль 4-5: MVCC и VACUUM

Помнишь, что у нас 86% строк было отброшено фильтром (Rows Removed by Filter: 431766)? Что внутри Postgres делал на каждой из них?

Для каждой строки Seq Scan должен:

1. Прочитать tuple header (xmin, xmax, infomask, ctid, …).
2. Проверить visibility относительно snapshot транзакции (xmin committed + visible? xmax not committed?).
3. Если visible — применить фильтр.

На таблице с большим количеством dead tuples (после массовых UPDATE без VACUUM) Seq Scan ещё медленнее: он читает и dead tuples тоже, просто отбрасывает их по xmax. С индексом dead tuples не появляются в результате — потому что в индексе нет указателей на них (после VACUUM), а если есть (bloated index) — Postgres всё равно проверит heap.

Урок: bloat ускоряет регрессии. Запрос, который сегодня работает за 200 мс, через полгода без VACUUM может работать за 2 секунды на тех же данных — только потому, что между live tuples теперь много мёртвых.

Модуль 6: Planner и executor

Без индекса оптимизатор выбрал Seq Scan потому, что у него не было альтернативы. С индексом — он сравнил cost(Seq Scan) = 9000 * seq_page_cost = 9000 с cost(Index Scan) = ~200 * random_page_cost + cpu_cost ≈ 220 + .... И выбрал второе — потому что это в 40 раз дешевле.

Что могло пойти не так:

• Если бы наш диапазон placed_at покрывал 80% таблицы — Index Scan стал бы дороже, чем Seq Scan (random reads вместо sequential).
• Если бы random_page_cost = 4 (default для HDD) на SSD — оптимизатор недооценил бы выгоду индекса. Поэтому на NVMe-серверах ставят random_page_cost = 1.1.

Модуль 7: Статистика и costing

Selectivity для placed_at >= '2025-03-01' AND placed_at < '2025-04-01' оптимизатор посчитал на основе histogram’а placed_at. Если ANALYZE свежий — оценка близка к реальности (rows=68234 estimated vs 68000 actual). Если устаревший (последний ANALYZE неделю назад, а данные росли) — оценка может быть в 10 раз меньше, и оптимизатор выберет Nested Loop вместо Hash Join — что катастрофа на 100 000 правых строк.

Урок: autovacuum_analyze_scale_factor критически важен для query plans. На write-heavy таблицах с ростом >= 10% в день — настраивай агрессивнее (0.02 вместо default 0.1).

Модуль 8: Join algorithms

Hash Join был выбран потому что:

• Customers (50K) спокойно помещаются в work_mem (default 4 MiB → 50K × 100 байт ≈ 5 MiB, на грани, но укладывается).
• Equi-join по customer_id.

С увеличением customers до 1M Hash Join перестал бы помещаться в work_mem и начал batched-режим (multiple passes по orders). Это в 2-5 раз медленнее. Tuning: либо повысить work_mem (SET work_mem = '64MB'), либо перейти на Merge Join (если оба входа отсортированы).

Модуль 9: EXPLAIN deep dive

Метрики, которые мы смотрели:

• actual time на каждом узле — где время идёт.
• actual rows vs estimated rows — насколько оптимизатор ошибается.
• Buffers: shared hit=X read=Y — сколько страниц из кэша vs с диска. read высокий = bottleneck в I/O.
• Rows Removed by Filter — пища для индекса.

auto_explain extension в проде логирует EXPLAIN для всех медленных запросов автоматически — must-have на любой нагрузочной системе.

Модули 10-11: Locks и isolation

CREATE INDEX (без CONCURRENTLY) берёт SHARE lock на таблицу — блокирует write-операции на время создания. На 500K orders это секунды. На 100M — десятки минут. В production используй CREATE INDEX CONCURRENTLY (только без CONCURRENTLY transaction’ы, но он не блокирует writes).

Concurrent reads index создавать не мешает — SHARE lock совместим с ACCESS SHARE (SELECT).

Модули 12-13: Partitioning и WAL

Что было бы, если orders была партиционирована по месяцам? Запрос с WHERE placed_at >= '2025-03-01' AND < '2025-04-01' обошёл бы только партицию orders_2025_03 через partition pruning — даже без индекса. Это и есть архитектурное решение «индекс vs partition»: оба решают одну проблему (читать меньше данных), но partition хорош когда:

• Старые партиции можно archive/drop.
• Запросы стабильно фильтруют по partition key.
• Таблица — десятки миллионов строк и растёт.

CREATE INDEX пишется в WAL — поэтому появляется на replica почти мгновенно (асинхронно). На write-heavy master замедлится на ~5% от write amplification (каждый INSERT теперь дополнительно пишет в индекс + WAL).

Модули 14-15: Vectorized и performance patterns

Этот запрос — типичный аналитический: фильтр по диапазону + JOIN + GROUP BY + ORDER BY + LIMIT. На vectorized engine (DuckDB, ClickHouse) он бы отработал в 2-5 раз быстрее даже без индекса, потому что батчевая обработка лучше кешируется и вектор изуется через SIMD.

Урок: если у тебя в OLTP-PostgreSQL много таких аналитических запросов, и они становятся узким местом — рассмотри replica с TimescaleDB/Citus columnar, либо ETL в DuckDB/ClickHouse, и отдавай отчёты оттуда.

Чек-лист

• Один индекс — это операция на уровне B-tree, но эффект на уровне страниц heap’а.
• Composite индекс работает только пока запрос соответствует leftmost prefix.
• Selectivity, статистика и оценки оптимизатора — ключевые для правильного выбора плана.
• EXPLAIN ANALYZE с BUFFERS — главный инструмент диагностики.
• Архитектурные альтернативы (partitioning, vectorized engine) — на горизонте, когда индексов уже мало.