Глубокий разговор про индексы будет в модуле 13 — там мы пройдём по B-tree, EXPLAIN, селективности и плану запроса. Этот урок — короткий, прагматичный: какой индекс ставить для типов из этого модуля, чтобы запросы из 30 секунд превращались в миллисекунды.

Почему B-tree не подходит для всего

Обычный B-tree индекс — это сортированное дерево, оптимизированное под операторы =, <, >, BETWEEN, LIKE 'prefix%'. Если запрос использует что-то другое (@>, &&, @@, <<), B-tree не помогает: оптимизатор делает sequential scan и проверяет каждую строку.

Для специальных операций существуют другие типы индексов:

• GIN (Generalized Inverted Index) — обратный индекс. Хорош, когда внутри ячейки много мелких значений, по которым ищут.
• GiST (Generalized Search Tree) — обобщённое поисковое дерево. Хорошо для диапазонов, геометрии, EXCLUDE constraint.
• BRIN — block range index. Для очень больших таблиц с естественной локальностью (например, лог-таблица по дате).
• SP-GiST и hash — реже используются.

Сейчас разберём первые два и две частые техники для B-tree.

GIN — для массивов, JSONB и FTS

GIN строит обратный индекс: для каждой «элементарной части» значения он хранит список строк, в которых эта часть встречается.

• Для TEXT[] элементарная часть — это элемент массива.
• Для JSONB — пары ключ-значение (зависит от operator class).
• Для tsvector — каждая лексема.

GIN для массивов

CREATE INDEX customers_tags_idx ON customers USING GIN (tags);

После этого запросы вида WHERE tags @> ARRAY['vip'] или WHERE tags && ARRAY['vip','frequent'] идут через индекс, не через seq scan.

GIN для JSONB

CREATE INDEX products_attrs_idx ON products USING GIN (attrs);

Это индексирует все ключи и значения JSONB. Поддерживает @>, ?, ?|, ?&. Стоит много места на диске.

Если ты знаешь, что фильтруешь почти всегда через @>, можно сэкономить место через специальный operator class:

CREATE INDEX products_attrs_idx ON products USING GIN (attrs jsonb_path_ops);

jsonb_path_ops индексирует только хэш пути+значения, не ключи отдельно. В два-три раза компактнее, быстрее на @>, но не поддерживает ? и его друзей.

GIN для FTS

CREATE INDEX articles_search_idx ON articles USING GIN (search_vec);

Где search_vec — это tsvector, обычно хранимый в GENERATED ALWAYS AS (...) STORED колонке. Без этого индекса to_tsvector(...) @@ to_tsquery(...) идёт по всей таблице.

Цена GIN

• Медленная вставка/обновление. GIN строит много элементов из одной строки — каждый INSERT/UPDATE дороже B-tree.
• Больше места на диске.
• fastupdate накапливает изменения в pending list; пока не VACUUM, поиск делает дополнительную работу.

Поэтому: GIN на read-heavy таблицы — отлично. На очень write-heavy — задумайся, нужен ли.

GiST — для диапазонов, геометрии и EXCLUDE

GiST мы уже встречали в EXCLUDE constraint. Это дерево, в котором каждый узел знает «диапазон/область, покрываемую его потомками». Поиск идёт сверху вниз: если запрос не пересекается с областью узла, целая ветка отсекается.

CREATE INDEX bookings_period_idx ON bookings USING GIST (period);

После этого WHERE period && '[2025-05-12 10:00, 2025-05-12 12:00)'::tstzrange идёт через индекс. Без него — seq scan.

GiST также используется для:

• Геометрии (point, polygon, PostGIS).
• Расстояний (<-> оператор, kNN-поиск).
• tsvector — альтернатива GIN. GiST мельче на диске, но проигрывает на больших словарях.

Expression index — индекс по выражению

Бывает, что часто фильтруют по результату функции от колонки:

SELECT * FROM users WHERE lower(email) = '[email protected]';
SELECT * FROM products WHERE (attrs->>'color') = 'black';
SELECT * FROM customers WHERE date_trunc('month', signup_date) = '2025-01-01';

Обычный B-tree на email не поможет: индекс знает '[email protected]', а запрос ищет lower(...). Решение — индекс по выражению:

CREATE INDEX users_lower_email_idx ON users (lower(email));
CREATE INDEX products_color_idx    ON products ((attrs->>'color'));

Двойные скобки во втором примере — синтаксическая необходимость (Postgres требует выражение в скобках, кроме одного-имени-колонки).

Запросы должны использовать точно такое же выражение, как в индексе — иначе оптимизатор не поймёт, что индекс подходит.

Partial index — индекс на подмножестве строк

Если 99% строк имеют статус deleted и тебе их искать не нужно — нет смысла включать их в индекс. Partial index фильтрует, что в него попадает:

CREATE INDEX orders_active_idx
  ON orders (placed_at)
  WHERE status NOT IN ('cancelled', 'refunded');

Запрос WHERE placed_at > ... AND status NOT IN ('cancelled','refunded') будет использовать индекс, и сам индекс будет в несколько раз меньше полного.

Это особенно мощно с GIN или GiST на «горячем» подмножестве — например, бронирования будущих периодов.

Где это всё видно: цифры

Точных цифр без EXPLAIN ANALYZE не назвать, но порядок такой:

• Seq scan на 10М строк JSONB с фильтром attrs @> '{...}' — десятки секунд.
• С GIN-индексом — миллисекунды.

Разрыв растёт линейно с размером таблицы: на 100М строк seq scan становится минутами. Поэтому правило: если запрос на JSONB/массив/FTS вылетает в seq scan на таблице больше нескольких сотен тысяч строк, нужен индекс.

EXPLAIN — как увидеть, что индекс работает

В модуле 13 мы погрузимся в EXPLAIN глубоко, но даже сейчас полезно посмотреть, как выглядит «индекс работает» против «индекса нет».

В выводе ищи строки Seq Scan on products vs Bitmap Index Scan on products_attrs_idx. На таблице из 20 строк seq scan выигрывает (потому что чтение страницы целиком быстрее, чем дополнительный hop через индекс). Но на 10M строк такой же запрос без индекса — секунды, с индексом — миллисекунды.

Множественные индексы и Bitmap AND

Postgres умеет объединять результаты нескольких индексов через bitmap operations. Если у тебя есть два индекса — на attrs и на category_id — и запрос фильтрует по обоим, оптимизатор может:

1. Получить bitmap row-id из первого индекса (по attrs).
2. Получить bitmap из второго (по category_id).
3. Сделать AND между bitmaps.
4. Прочитать только пересечение.

Это часто эффективнее, чем составной (multi-column) индекс, особенно когда фильтры приходят в разных комбинациях. На read-heavy таблицах с гибкими фильтрами правило большого пальца — много простых индексов, не один большой составной.

Когда индекс не нужен

Это контр-интуитивно, но иногда индекс вредит:

• Маленькая таблица (тысячи строк). Seq scan всё равно быстрее — индекс просто занимает место и замедляет вставки.
• Очень частые UPDATE колонки. Каждое UPDATE пересчитывает индекс — на горячих write-таблицах это видно.
• Низкая селективность. Если запрос возвращает 60% таблицы, индекс не поможет — оптимизатор сам выберет seq scan.

Правило: индексы ставятся под конкретные запросы, не «на всякий случай». Запусти EXPLAIN ANALYZE, увидь bottleneck, поставь индекс — снова EXPLAIN. Это итеративный процесс, мы будем им заниматься плотнее в модуле 13.

Чек-лист

• GIN для TEXT[], JSONB, tsvector. Идеален на read-heavy, дорог на write.
• Для JSONB-фильтров через @> — jsonb_path_ops компактнее обычного GIN.
• GiST для range-типов и геометрии. Тот же индекс держит EXCLUDE constraint.
• Expression index (CREATE INDEX ON t (lower(email))) — когда запросы используют функцию от колонки.
• Partial index (... WHERE status='active') — индексировать только «горячее» подмножество.
• На больших таблицах правильный спец-индекс — это разница «30 секунд vs миллисекунды».
• Индексы ставятся под конкретные запросы, а не на всякий случай. Сначала — EXPLAIN, потом — индекс.