Индексы и партиции: когда помогают аналитике

Индекс это отдельная структура, отсортированная по значениям колонки, с указателями на строки. Он превращает поиск «строки с customer_id = 42» из полного чтения таблицы в несколько переходов по дереву. Но у аналитики другие запросы, и там индекс часто не работает. Урок про то, где граница.

Точечный запрос: индекс решает

На большой базе SELECT count(*) FROM orders WHERE customer_id = 42 без индекса читает все 944 страницы таблицы и выбрасывает 79 964 строки из 80 000: около 6 миллисекунд. С индексом ix_orders_customer план меняется на Bitmap Index Scan, читаются 38 страниц, время 0,1 миллисекунды. Разница в 60 раз, и она растёт с размером таблицы.

CREATE INDEX ix_orders_customer ON orders (customer_id);

Индекс занимает место, 900 килобайт на 12 мегабайт таблицы, и замедляет вставку: каждая новая строка обновляет ещё и дерево. Для таблиц, которые пишутся раз в сутки и читаются весь день, это выгодная сделка. Для staging, куда льют миллионы строк, индексы часто вообще не создают.

Аналитический запрос: индекс не нужен

SELECT count(*) FROM orders WHERE customer_id < 10000 возвращает 64 тысячи строк из 80. Индекс есть, но планировщик выбирает Seq Scan: прочитать таблицу подряд дешевле, чем прыгать по индексу к 80% её страниц. Правило такое: индекс помогает, когда условие отбирает малую долю строк, единицы процентов. Отчёт «выручка по месяцам за год» читает всю таблицу при любых индексах, и ускорять его нужно иначе.

Поэтому в аналитических системах индексы играют меньшую роль, чем в приложениях. ClickHouse хранит данные отсортированными по ключу и колоночно: запрос читает только нужные колонки и только нужный диапазон ключа. Greenplum распределяет строки по сегментам и партиционирует. Parquet в озере данных хранит статистику по блокам, чтобы пропускать ненужные. Все эти механизмы решают ту же задачу, что индекс: не читать лишнего, но для сканирования диапазонов, а не для точечного поиска.

Составной индекс и порядок колонок

CREATE INDEX ix_orders_customer_status ON orders (customer_id, status);

Составной индекс отсортирован сначала по первой колонке, внутри неё по второй. Он обслуживает условия по customer_id, по паре customer_id и status, но не по одному status: искать в телефонной книге по имени без фамилии бесполезно. Первой ставят колонку, по которой ищут всегда и которая отбирает меньше строк.

В плане это видно по строке Index Cond: если оба условия попали туда, индекс использован целиком. Если второе условие ушло в Filter под узлом Bitmap Heap Scan, база прочитала строки по первой колонке и отфильтровала вторую уже в таблице.

Дополнительный приём: INCLUDE (total_amount) добавляет колонку в листья индекса без участия в сортировке. Тогда запрос, которому нужны только customer_id и total_amount, выполняется как Index Only Scan без обращения к таблице.

Индекс по выражению и частичный индекс

Условие lower(status) = 'refunded' не использует индекс по status. Выход: индекс по самому выражению:

CREATE INDEX ix_orders_status_lower ON orders (lower(status));

Планировщик применит его, если в запросе стоит ровно то же выражение. Бонус: для выражения появляется статистика, и оценка строк становится честной вместо 0,5% по умолчанию.

Частичный индекс покрывает только часть таблицы: CREATE INDEX ... ON orders (created_at) WHERE status = 'new'. Он маленький и быстрый для запросов «новые заказы за сегодня», а для остальных строк не существует. Типичный случай в загрузках: индекс по признаку «ещё не обработано».

Партиционирование: отсечь лишнее целиком

Таблица событий растёт на сотни тысяч строк в день. Запрос за один день читает всю таблицу, потому что нужные строки рассыпаны по всем страницам. Партиционирование разрезает таблицу на физические части по значению колонки, обычно по дате:

CREATE TABLE events_p (LIKE events) PARTITION BY RANGE (event_ts);
CREATE TABLE events_p_2026_06 PARTITION OF events_p
  FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

Запрос WHERE event_ts >= '2026-06-01' AND event_ts < '2026-06-02' теперь читает только партицию июня: в плане одна строка Seq Scan on events_p_2026_06 вместо полного чтения. Это называется отсечением партиций, partition pruning. Условие должно быть по самой колонке партиционирования: date_trunc('day', event_ts) = ... отсечения не даёт, и в плане появляется Append со всеми партициями подряд.

Для инженера данных партиции важнее индексов по трём причинам:

• Загрузка за день это работа с одной партицией: её можно перезаписать целиком, как в уроке про идемпотентность, или удалить одной командой вместо DELETE по миллионам строк.
• Старые данные удаляются отсоединением партиции за секунду.
• Отчёт за период читает только нужные партиции, и это работает для сканирования диапазона, где индекс бесполезен.

Границы партиций пишутся полуинтервалом FROM ... TO ..., правая не включается. Та же логика, что и везде в этом модуле.

Что из этого переносится в другие системы

В Greenplum партиционирование по дате плюс распределение по ключу соединения это основа физической модели, а индексы используются редко. В ClickHouse партиция обычно месяц, а внутри партиции строки лежат по ключу сортировки, который заменяет индекс для диапазонов. В Spark с Parquet партиции это папки по дате в хранилище, и запрос без фильтра по партиции читает всё озеро. Везде один принцип: физическая раскладка данных под самые частые фильтры и соединения.

Практика