Menu

CREATE INDEX в PostgreSQL: типы индексов и примеры

CREATE INDEX по умолчанию строит индекс B-tree, чтобы PostgreSQL находил строки, не читая всю таблицу. Другие типы (GIN, GiST, BRIN, hash) подходят для JSONB, массивов, полнотекстового поиска, диапазонов и очень больших таблиц.

На этой странице есть исполняемые редакторы: меняйте, запускайте и сразу видите результат.

CREATE INDEX name ON table (column); строит индекс, с которым PostgreSQL находит подходящие строки, не читая всю таблицу. Тип по умолчанию B-tree, он обслуживает =, <, >, BETWEEN, IN, IS NULL и ORDER BY. Другой тип выбирают через USING: gin, gist, spgist, brin или hash.

Первая строка плана: Index Scan using users_email_idx on users. PostgreSQL ищет значение в индексе и читает только подходящую строку. Уберите строку CREATE INDEX и запустите снова: появится Seq Scan on users, который читает все 10 000 строк.

На таблице из нескольких десятков строк планировщик часто выбирает последовательное сканирование даже при наличии индекса: прочитать несколько страниц дешевле. Как читать такие планы, объясняет страница EXPLAIN ANALYZE.

Выбор типа индекса

ТипДля чего
B-tree (по умолчанию)равенство, диапазоны, сортировка, LIKE 'prefix%' (с collation C или text_pattern_ops)
GINзначения из многих частей: jsonb (@>, ?), массивы (@>, &&), полнотекстовый tsvector
GiSTгеометрия, диапазоны (&&, пересечение), полнотекстовый поиск, ближайшие соседи ORDER BY ... <->
SP-GiSTданные, которые делятся на непересекающиеся области: точки, IP-адреса, префиксы текста
BRINочень большие таблицы, где значения идут в физическом порядке, например created_at в таблице, куда только добавляют
Hashтолько равенство; B-tree умеет то же и больше, поэтому hash выбирают редко

Индекс GIN на столбце jsonb позволяет запросам на вхождение использовать индекс:

План показывает Bitmap Index Scan по индексу GIN, который передаёт данные в Bitmap Heap Scan, а тот читает подходящие строки. Операторы разобраны на странице о JSONB.

Индекс по нескольким столбцам

Составной индекс B-tree сортирует по первому столбцу, а внутри каждого его значения по второму. Он обслуживает запросы с фильтром по ведущему столбцу или по ведущему и следующим. Фильтр только по второму столбцу почти ничего от него не выигрывает, поэтому первым ставьте столбец, который вы всегда фильтруете через =.

CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at);

-- served well by the index above
SELECT * FROM orders WHERE customer_id = 7 AND created_at > '2026-01-01';
SELECT * FROM orders WHERE customer_id = 7 ORDER BY created_at DESC LIMIT 10;

INCLUDE (PostgreSQL 11+) хранит в индексе дополнительные столбцы, не сортируя по ним: CREATE INDEX ON orders (customer_id) INCLUDE (total);. Запрос, который читает только эти столбцы, тогда получает ответ из одного индекса, это Index Only Scan.

Уникальные и частичные индексы: правило для части строк

CREATE UNIQUE INDEX отклоняет повторяющиеся значения. Частичный индекс добавляет WHERE и индексирует только подходящие под условие строки. Он меньше, и с ним правило уникальности можно применить к подмножеству строк:

Обе строки принимаются, потому что неудалённая из них только одна. Третья живая строка с тем же email падает с ошибкой duplicate key value violates unique constraint "accounts_email_live". Запрос использует частичный индекс, только если его WHERE влечёт условие индекса, здесь WHERE NOT deleted.

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

Индекс может хранить результат выражения. Чтобы им воспользоваться, запрос должен повторить то же выражение, а индекс на email не помогает WHERE lower(email) = ...:

План использует users_email_lower_idx, потому что WHERE в точности повторяет lower(email). Выражение должно быть immutable, поэтому now() или функция, зависящая от настройки, не принимаются.

Список, создание и удаление индексов на рабочей базе

pg_indexes выводит каждый индекс вместе с командой, которая создала бы его заново:

Первичный ключ и ограничение UNIQUE создали собственные индексы, users_pkey и users_email_key. В psql \d users показывает тот же список под столбцами таблицы.

На таблице, в которую идёт запись, создавайте и удаляйте индексы с CONCURRENTLY, чтобы запись не блокировалась. Эти формы нельзя выполнять внутри блока транзакции. Неудачное параллельное построение оставляет индекс INVALID, его нужно удалить и создать заново:

CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);
DROP INDEX CONCURRENTLY orders_status_idx;
REINDEX INDEX CONCURRENTLY orders_status_idx;  -- rebuild a bloated index

Часто задаваемые вопросы

Создаёт ли PostgreSQL индексы автоматически?

Только для PRIMARY KEY, UNIQUE и ограничений исключения. Столбец внешнего ключа индекса не получает, поэтому добавьте его сами, если соединяете по нему таблицы или удаляете строки из таблицы, на которую он ссылается.

Почему PostgreSQL не использует мой индекс?

Частые причины: таблица маленькая, и прочитать её целиком дешевле; запрос возвращает большую долю строк; WHERE оборачивает столбец в функцию или приведение типа, которых нет в индексе; статистика устарела (выполните ANALYZE). Какой план выбран, показывает EXPLAIN.

Сколько индексов уже слишком много?

Фиксированного числа нет. Каждый индекс ускоряет часть чтений и замедляет каждый INSERT и большинство UPDATE на таблице, а ещё занимает место на диске. pg_stat_user_indexes.idx_scan показывает, как часто используется каждый индекс, и те, что остаются на нуле, можно удалить.

Блокирует ли CREATE INDEX таблицу?

Обычный CREATE INDEX блокирует INSERT, UPDATE и DELETE на таблице до завершения, но чтение продолжается. CREATE INDEX CONCURRENTLY разрешает запись во время построения, работает дольше и не может выполняться внутри блока транзакции.

Иллюстрация языков программирования Coddy

Учитесь программировать с Coddy

Начать