Индексы в SQL: что это, как работают и когда помогают

Коротко

• Индекс — отдельная упорядоченная структура, по которой база находит строки, не читая всю таблицу.
• Стандартный индекс PostgreSQL — B-tree: подходит для =, <, >, between, диапазонов дат и сортировки.
• Составной индекс (a, b) помогает условиям на a и на a вместе с b, но почти бесполезен для условия только на b.
• Функция над столбцом в where (date(event_time), lower(name)) отключает обычный индекс. Помогает индекс по выражению или переписанное условие.
• Если условию соответствует большая доля строк, база честно читает таблицу целиком: так быстрее.
• Индекс занимает место и замедляет каждую вставку и обновление.

Что такое индекс в базе данных

Индекс в базе данных — отдельная структура, в которой значения одного или нескольких столбцов хранятся в упорядоченном виде вместе с адресами строк. По ней база находит нужные строки за несколько шагов вместо чтения всей таблицы. Индекс ускоряет поиск и сортировку, но занимает место и замедляет запись.

Ближайшая аналогия — алфавитный указатель в конце книги. Чтобы найти все упоминания «когорты», не нужно листать 400 страниц: вы открываете указатель, находите слово по алфавиту и видите номера страниц. Без указателя остаётся читать подряд — в терминах базы это Seq Scan, последовательное чтение таблицы.

Как работает индекс: B-tree

B-tree — дерево из страниц. На нижнем уровне лежат все значения столбца по порядку, у каждого — адрес строки в таблице. Верхние уровни — оглавление: «значения до 100 000 — налево, дальше — направо». Индекс по user_id на тестовой таблице из следующего раздела занимает 3 694 страницы и имеет три уровня. Чтобы найти user_id = 250042, база читает по одной странице на каждом уровне, а не 25 425 страниц таблицы.

Нижние страницы связаны друг с другом по порядку. Поэтому B-tree подходит не только для равенства, но и для диапазонов: база находит начало интервала и идёт вправо до его конца. Отсюда же польза для order by — данные в индексе уже отсортированы.

Кроме B-tree в PostgreSQL есть и другие типы: GIN для jsonb и массивов, BRIN для огромных таблиц, где значения растут вместе с физическим порядком строк, GiST для геоданных. Для большинства аналитических фильтров по id, датам и статусам хватает B-tree, и create index без уточнений создаёт именно его.

Синтаксис CREATE INDEX в PostgreSQL

Индекс создаётся одной командой и дальше работает сам: в запросах его не упоминают, планировщик решает, использовать ли его. Имя можно не задавать — PostgreSQL придумает его из имени таблицы и столбцов, — но с понятным именем проще разбираться в планах.

Обычный create index на время построения блокирует запись в таблицу. На рабочей базе используют create index concurrently: он строится дольше, зато не останавливает вставки. Выполнить его внутри транзакции нельзя.

create index events_big_user_id_idx on events_big (user_id);

-- составной
create index events_big_user_time_idx on events_big (user_id, event_time);

-- по выражению
create index events_big_event_date_idx on events_big ((date(event_time)));

-- частичный
create index events_big_exports_time_idx on events_big (event_time)
where event_name = 'export_completed';

-- уникальный
create unique index events_big_event_id_key on events_big (event_id);

-- на рабочей базе, без блокировки записи
create index concurrently events_big_event_name_idx on events_big (event_name);

drop index events_big_event_name_idx;

На каких данных проверяли

В учебной базе 35 341 событие: на таком объёме любой запрос выполняется за миллисекунды, и разницу не видно. Поэтому события размножены в 85 раз через generate_series со сдвигом user_id: получилась таблица events_big на 3 003 985 строк и 199 МБ. Распределения остались прежними: те же пять типов событий в тех же долях.

Все замеры сделаны на ноутбуке в PostgreSQL 14 с настройками по умолчанию, по два-три прогона на запрос. Абсолютные миллисекунды на вашей машине будут другими — важны тип узла в плане и порядок разницы. Читать план подробно помогает статья про EXPLAIN ANALYZE.

create table events_big as
select
  row_number() over (order by k, e.event_id)::bigint as event_id,
  e.user_id + k * 10000                              as user_id,
  e.event_time,
  e.event_name
from events e
cross join generate_series(0, 84) as k;

analyze events_big;

Seq Scan против Index Scan: что меняет индекс

Без индекса поиск 15 событий одного пользователя читает всю таблицу тремя параллельными процессами и отбрасывает по миллиону строк на каждый. С индексом по user_id, который строился около секунды, план сводится к одному узлу Index Scan.

-- без индекса
Gather  (cost=1000.00..42071.66 rows=9 width=30) (actual time=18.871..65.936 rows=15 loops=1)
  ->  Parallel Seq Scan on events_big  (actual time=17.465..63.366 rows=5 loops=3)
        Filter: (user_id = 250042)
        Rows Removed by Filter: 1001323
Execution Time: 65.946 ms

-- с индексом
Index Scan using events_big_user_id_idx on events_big  (cost=0.43..8.59 rows=9 width=30) (actual time=0.018..0.056 rows=15 loops=1)
  Index Cond: (user_id = 250042)
Execution Time: 0.063 ms

Index Scan и Bitmap Index Scan

Когда строк немного, база идёт по индексу и за каждой строкой сразу обращается в таблицу — это Index Scan. Когда строк тысячи, так выходит много случайных чтений. Тогда PostgreSQL сначала собирает по индексу карту нужных страниц (Bitmap Index Scan), а потом читает эти страницы по порядку (Bitmap Heap Scan).

События за 15 июля — 40 460 строк. С индексом по event_time план такой: индекс отдал карту, база прочитала 425 страниц из 25 425 и уложилась в 3 мс. Условие записано полуоткрытым интервалом event_time >= '2026-07-15' and event_time < '2026-07-16' — почему так надёжнее, чем BETWEEN, разобрано в статье про BETWEEN.

Составной индекс: почему важен порядок столбцов

Составной индекс (user_id, event_time) отсортирован сначала по пользователю, внутри пользователя — по времени. Как телефонный справочник: по фамилии, внутри фамилии по имени. Найти «Иванов Пётр» легко, всех «Ивановых» — тоже, а всех «Петров» с любой фамилией справочник не поможет.

Планы это подтверждают. Условие «пользователь 250042 в июле» и условие только на пользователя дают Index Scan using events_big_user_time_idx. Условие только на дату при этом индексе снова превращается в Parallel Seq Scan на 57–60 мс. Правило называют правилом левого префикса: индекс работает для первых столбцов по порядку.

Отсюда порядок при создании: первым ставьте столбец, по которому фильтруют через равенство и который есть почти во всех запросах, за ним — столбец для диапазона или сортировки. Для отчётов «события пользователя за период» подходит (user_id, event_time), для отчётов «все события за день» нужен отдельный индекс по event_time.

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

Функция над столбцом. Индекс по event_time хранит моменты времени, а не даты, поэтому where date(event_time) = '2026-07-15' его не использует: 73 мс последовательного чтения. То же с lower(event_name) при индексе по event_name — Seq Scan на 116 мс. Выхода два: переписать условие диапазоном по самому столбцу или создать индекс по выражению. С индексом по date(event_time) тот же запрос выполнился за 3 мс, причём планировщик узнал выражение и в записи event_time::date.

Низкая селективность. Событий app_open — 81,2 % таблицы. С индексом по event_name запрос по ним всё равно идёт через Seq Scan: прыгать по индексу к четырём пятым строк дольше, чем прочитать таблицу подряд. Даже для export_completed с долей 2,8 % выигрыш почти исчез: битовая карта указала на 20 387 страниц из 25 425, потому что редкие экспорты разбросаны по всей таблице. Запрос занял 64–106 мс, а принудительное последовательное чтение — 70–80 мс.

Маленькая таблица. В справочнике тарифов три строки, и поиск по первичному ключу plan = 'pro' выполняется через Seq Scan: одна страница таблицы читается быстрее, чем индекс плюс таблица. На исходных 35 341 событии индекс по user_id планировщик ещё использует — граница зависит от размера и селективности, а не от круглого числа строк.

Несовпадение типов и устаревшая статистика тоже мешают, но это уже тема чтения планов: если оценка строк в плане сильно расходится с фактом, начните с analyze имя_таблицы.

select
  event_name,
  count(*) as events,
  round(100.0 * count(*) / sum(count(*)) over (), 1) as pct
from events
group by event_name
order by events desc;
-- app_open 81.2, workspace_created 7.8, report_created 5,
-- invite_sent 3.2, export_completed 2.8

Частичный индекс

Если почти все запросы смотрят на небольшую часть таблицы, индекс можно построить только по ней. Частичный индекс по event_time для строк event_name = 'export_completed' занял 584 КБ против 20 МБ у полного индекса по времени. Подсчёт экспортов за июль по нему — 28 135 строк за 7–14 мс.

Планировщик выбирает частичный индекс, только если условие запроса включает условие индекса. Запрос без event_name = 'export_completed' этот индекс не увидит.

Сколько стоит индекс: место и скорость записи

Индекс — копия значений столбцов с адресами строк, и он занимает диск. На таблице в 199 МБ составной индекс (user_id, event_time) занял 90 МБ, уникальный индекс по event_id — 64 МБ. Индексы по event_name и event_time вышли по 20 МБ: в синтетике каждое время повторяется 85 раз, а B-tree в PostgreSQL 13 и новее сжимает повторы. На настоящих данных индекс по времени будет крупнее. Размеры смотрят через pg_relation_size('имя_индекса') и pg_indexes_size('таблица').

Каждая вставка и каждое обновление индексированного столбца пишут не только в таблицу, но и во все её индексы. Вставка миллиона строк в пустую таблицу без индексов заняла 0,7–1,4 с, в такую же таблицу с тремя индексами — 4,7–7,4 с в трёх прогонах. Поэтому индексы не ставят «на всякий случай»: неиспользуемый индекс только замедляет загрузку.

Чем UNIQUE-индекс отличается от UNIQUE-ограничения

Ограничение unique PostgreSQL реализует через уникальный индекс, поэтому дубли ловятся одинаково: «ERROR: duplicate key value violates unique constraint "u1_code_idx"» — даже когда это просто индекс. В описании таблицы разница видна: UNIQUE, btree (code) у индекса и UNIQUE CONSTRAINT, btree (code) у ограничения. Внешний ключ PostgreSQL принял в обоих случаях.

Ограничение — часть модели данных: его видно в описании таблицы, его понимают инструменты проектирования, а индекс под ним нельзя удалить отдельно: «cannot drop index u2_code_key because constraint u2_code_key on table u2 requires it». Уникальный индекс гибче: он бывает по выражению — unique (lower(code)) не пустит A рядом с a — и частичным. Правило простое: уникальность столбцов объявляйте ограничением, а уникальность выражения или части строк — индексом.

Нужны ли индексы аналитику в DWH и колоночных базах

Индексы B-tree — инструмент транзакционных баз вроде PostgreSQL и MySQL, где запросы ищут немного строк по ключу. Аналитические хранилища устроены иначе: они хранят данные по столбцам и читают большие объёмы целиком, поэтому привычный create index там либо отсутствует, либо играет второстепенную роль. В ClickHouse главное решение — ключ сортировки таблицы, заданный при её создании: по нему база пропускает целые блоки данных. В BigQuery ту же задачу решают партиционирование и кластеризация таблицы. Аналитику в хранилище полезнее знать, по каким столбцам таблица разбита и отсортирована, и фильтровать именно по ним. Индексы в PostgreSQL становятся вашей заботой, когда вы работаете с репликой продуктовой базы или ведёте небольшую витрину в ней. Разница между этими классами баз разобрана в статье про OLAP и OLTP.

Чеклист: нужен ли индекс

• Запрос повторяется регулярно, а не один раз для разовой выгрузки.
• explain analyze показывает Seq Scan с большим Rows Removed by Filter.
• Условие отбирает небольшую долю строк — проверьте долю запросом с count(*) по значениям.
• В where стоит сам столбец, а не функция от него, либо индекс строится по той же функции.
• Для составного индекса первым идёт столбец, который есть почти во всех запросах с равенством.
• Таблица больше нескольких страниц, и на неё не идёт интенсивная запись, которую индекс замедлит.
• На рабочей базе индекс создаётся через concurrently и по согласованию с владельцем базы.

Что почитать дальше

В песочнице курса explain недоступен: она принимает только select и with. Но проверить селективность можно и там — запустите запрос с долями значений для users.channel и users.country и прикиньте, по какому столбцу индекс имел бы смысл. Сами индексы и планы пробуйте в своём PostgreSQL на таблице, размноженной через generate_series.