Индексы за 7 минут

Индекс это отдельная структура данных, которая позволяет найти строки, не читая всю таблицу. Как алфавитный указатель в книге: нашли слово, получили номера страниц.

B-tree: индекс по умолчанию

CREATE INDEX ON orders (customer_id); создаёт B-tree. Это сбалансированное дерево с отсортированными ключами, поиск за O(log n). B-tree умеет:

• равенство = и IN;
• диапазоны <, >, BETWEEN;
• ORDER BY по индексированной колонке без отдельной сортировки;
• LIKE 'abc%' (префикс), при подходящей collation или с text_pattern_ops.

Не умеет: LIKE '%abc', поиск внутри JSON или массива, полнотекстовый поиск.

Цена индекса

Индекс не бесплатный:

• каждая вставка, обновление индексированной колонки и удаление обновляет все индексы таблицы;
• индекс занимает место на диске и в памяти;
• лишние индексы замедляют запись и VACUUM.

Поэтому «повесить индекс на каждую колонку» это плохой совет.

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

Индекс (customer_id, created_at) отсортирован сначала по customer_id, внутри по created_at. Он эффективно работает для:

• WHERE customer_id = 5;
• WHERE customer_id = 5 AND created_at > '2026-01-01';
• WHERE customer_id = 5 ORDER BY created_at.

Для WHERE created_at > ... без customer_id такой индекс эффективно не поможет: нужный диапазон размазан по всему дереву. Порядок колонок важен: сначала колонки с равенством, потом с диапазоном или сортировкой.

Когда индекс не используется

• Функция над колонкой: WHERE lower(email) = 'a@b.ru' не использует индекс по email. Решение: индекс по выражению CREATE INDEX ON users (lower(email)).
• Низкая селективность: если условие подходит под 40% строк, последовательное чтение дешевле, чем прыгать по индексу. Планировщик решает по статистике.
• Несовпадение типов или неявное приведение.
• Маленькая таблица: прочитать 3 страницы быстрее любого индекса.
• OR по разным колонкам иногда даёт Bitmap OR, а иногда seq scan.

Особые виды индексов в PostgreSQL

• Уникальный: CREATE UNIQUE INDEX гарантирует уникальность. Первичный ключ и UNIQUE создают его автоматически. Внешний ключ индекс не создаёт, его на дочерней таблице добавляют сами.
• Частичный: CREATE INDEX ON orders (created_at) WHERE status = 'new'; индексирует только часть строк, меньше и быстрее.
• Покрывающий: CREATE INDEX ON orders (customer_id) INCLUDE (total); позволяет Index Only Scan: всё нужное есть в индексе, в таблицу ходить не надо (если страницы отмечены в visibility map как полностью видимые).
• GIN: для jsonb, массивов и полнотекстового поиска. Внутри «значение → список строк».
• GiST: геометрия, диапазоны, ближайшие соседи.
• BRIN: крошечный индекс для огромных таблиц, где значения коррелируют с физическим порядком (время вставки в логах).
• Hash: только равенство, используется редко.

Кластеризация

В PostgreSQL таблица это куча (heap): строки лежат не в порядке индекса. CLUSTER пересортирует таблицу один раз, но порядок не поддерживается при новых вставках. В MySQL InnoDB таблица сама хранится в B-tree по первичному ключу (кластерный индекс).

Создание на проде

CREATE INDEX блокирует запись в таблицу на всё время построения. На живой базе используют CREATE INDEX CONCURRENTLY: дольше, нельзя внутри транзакции, но запись не блокирует. Если упал, остаётся невалидный индекс, его надо удалить и создать снова.

Частые ошибки

• Индекс на каждую колонку «на всякий случай».
• Составной индекс с колонками в неудачном порядке.
• Функция над колонкой в WHERE.
• Забытый индекс на внешнем ключе: медленные JOIN и каскадные удаления.

Что спросят на собесе

• Как устроен B-tree и почему поиск логарифмический.
• Почему индекс может не использоваться.
• Правило левого префикса для составного индекса.
• Чем платим за индексы.
• Что такое покрывающий и частичный индекс, когда нужен GIN.