Индексы PostgreSQL: типы, порядок столбцов и цена

Индекс - это вторая, отсортированная копия части вашей таблицы, которая обновляется при каждой записи и позволяет PostgreSQL находить строки, не читая все. В этой фразе обе половины сделки: чтение ускоряется, запись замедляется, а диск расходуется. Чаще всего база медленна оттого, что в ней не хватает двух-трёх индексов; а база, медленная на запись, - оттого, что на таблице, которой хватило бы трёх, висит восемь.

Практический итог, если запомнить только одно: для запроса вроде WHERE tenant_id = $1 AND status = 'open' ORDER BY created_at DESC LIMIT 20 нужен один составной B-tree по (tenant_id, status, created_at DESC), а не три отдельных индекса по трём столбцам. Порядок столбцов в составном индексе - не украшение: он решает, можно ли индекс использовать вообще.

Что такое индекс и на что способен B-tree#

Индекс по умолчанию, и правильный ответ, пожалуй, в девяти случаях из десяти, - B-tree. Он хранит индексируемые значения в отсортированном виде в сбалансированном дереве, так что PostgreSQL может дойти от корня до листа за несколько чтений страниц, а затем пройти вбок по подходящим записям.

Поскольку записи отсортированы, один B-tree отвечает на широкий набор вопросов об одном столбце:

• Равенство: WHERE email = 'a@example.com'
• Диапазоны: WHERE created_at >= now() - interval '7 days'
• Списки BETWEEN и IN
• IS NULL и IS NOT NULL - PostgreSQL индексирует null
• Сортировка: ORDER BY created_at DESC с LIMIT, чтение в обратном порядке
• Поиск по префиксу через LIKE 'inv-2026%', но только если collation базы - C или индекс объявлен с text_pattern_ops

Последний пункт подводит людей в любой локали, кроме C. Если поиск по префиксу важен, добавьте класс операторов явно:

CREATE INDEX orders_ref_prefix ON app.orders (reference text_pattern_ops);

B-tree не помогает при подстановочном знаке в начале (LIKE '%carrot%'), при функции, применённой к столбцу в запросе, и при сравнении, тип которого не совпадает с индексом. Второй случай - самый частый с большим отрывом:

-- Does not use an index on created_at: the column is wrapped in a castWHERE created_at::date = '2026-09-01'-- Does, because the column is left aloneWHERE created_at >= '2026-09-01' AND created_at < '2026-09-02'

Каждый раз, когда запрос медленный, а у столбца «очевидно есть индекс», проверьте, не применяет ли запрос к этому столбцу функцию или приведение типа. Если без этого никак, проиндексируйте выражение - об этом ниже.

Составные индексы и порядок столбцов#

Составной индекс отсортирован сначала по первому столбцу, затем по второму внутри него и так далее - так же, как телефонная книга отсортирована по фамилии, а потом по имени. Отсюда правило левого префикса: индекс по (a, b, c) может обслуживать запросы с фильтром по a, по a и b или по всем трём. Для запроса, фильтрующего только по b, он почти бесполезен.

Большинство реальных случаев покрывают два правила:

1. Сначала столбцы равенства, последним - столбец диапазона или сортировки. С индексом по (tenant_id, created_at) запрос с фильтром tenant_id = 7 и сортировкой по created_at идёт прямо к нужному участку и читает его по порядку. Поменяйте столбцы местами - и так уже не получится.
2. Один составной лучше нескольких индексов по отдельным столбцам, когда столбцы встречаются вместе. PostgreSQL умеет объединять два индекса через bitmap scan, но это лишний шаг и перепроверка строк в heap; один индекс, подходящий запросу, всегда дешевле.

Разобранный пример:

-- The query the application runs a thousand times an hourSELECT id, total, created_atFROM app.ordersWHERE tenant_id = $1 AND status = 'open'ORDER BY created_at DESCLIMIT 20;-- The index it wantsCREATE INDEX orders_tenant_status_created  ON app.orders (tenant_id, status, created_at DESC);

С таким индексом план - index scan, который останавливается после двадцати строк. Без него PostgreSQL читает все заказы этого тенанта, сортирует их все и выбрасывает всё после двадцатого - работа растёт вместе с таблицей, хотя результат остаётся того же размера. DESC в определении необязателен для сортировки по одному столбцу (B-tree можно читать с конца), но важен, когда направления по столбцам смешиваются.

Типы индексов и когда какой уместен#

Два нестандартных типа, которые стоит изучить, - GIN и BRIN.

GIN индексирует значения внутри столбца, а не столбец целиком. Так ускоряют запросы на вхождение по jsonb, так работает полнотекстовый поиск, и - с расширением pg_trgm - так делают пригодным для индекса LIKE с подстановочным знаком в начале:

CREATE EXTENSION IF NOT EXISTS pg_trgm;CREATE INDEX customers_name_trgm ON app.customers USING gin (name gin_trgm_ops);-- now this can use an indexSELECT * FROM app.customers WHERE name ILIKE '%anders%';-- jsonb containmentCREATE INDEX events_payload ON app.events USING gin (payload jsonb_path_ops);SELECT * FROM app.events WHERE payload @> '{"type":"signup"}';

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

BRIN хранит только сводку по каждому диапазону блоков - минимальное и максимальное значение в каждом куске таблицы. Поэтому он крошечный (килобайты там, где B-tree занял бы гигабайты) и полезен только тогда, когда физический порядок таблицы совпадает с индексируемым столбцом, то есть на практике для таблицы, куда только добавляют, с индексом по метке времени. На таблице, где строки приходят в случайном порядке, он хуже, чем ничего.

Частичные индексы и индексы по выражениям#

Две возможности, которые превращают большой индекс в маленький.

Частичный индекс покрывает только строки, удовлетворяющие условию WHERE. Классический случай - столбец статуса, по которому вы всегда запрашиваете только одно значение:

CREATE INDEX orders_open ON app.orders (tenant_id, created_at)  WHERE status = 'open';

Если открыто 2% заказов, этот индекс составляет 2% размера, помещается в кэш и дешевле в обслуживании. Планировщик использует его только тогда, когда может доказать, что условия запроса влекут условие WHERE индекса, поэтому предикат должен быть записан в узнаваемом виде - делайте его простым и буквальным.

Индекс по выражению индексирует результат функции, и так ускоряют поиск без учёта регистра:

CREATE UNIQUE INDEX customers_email_lower ON app.customers (lower(email));SELECT * FROM app.customers WHERE lower(email) = lower($1);

Запрос должен использовать в точности то же выражение, что и индекс. lower(email) в индексе и email ILIKE $1 в запросе не совпадают. Функция должна быть также помечена как immutable, что исключает всё, где участвует текущее время или часовой пояс сессии.

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

CREATE UNIQUE INDEX one_active_sub ON app.subscriptions (customer_id)  WHERE status = 'active';

Индексы, которые вы получаете бесплатно, и тот, который нет#

Первичный ключ создаёт уникальный B-tree индекс. Так же и любое ограничение UNIQUE. Поэтому поиск строки по id быстр без всяких усилий с вашей стороны.

Внешние ключи - нет. orders.customer_id REFERENCES customers(id) создаёт индекс по customers.id (это первичный ключ) и ничего по orders.customer_id. На растущей таблице после этого ломаются две вещи: SELECT ... WHERE customer_id = $1 сканирует всю таблицу, а удаление клиента сканирует orders, чтобы проверить ограничение, удерживая при этом блокировку. Индексируйте столбцы внешних ключей, если только не уверены, что дочерняя таблица останется маленькой.

Два уточнения об уникальности. По умолчанию null отличны друг от друга, так что уникальный столбец может содержать много null; в PostgreSQL 15 добавили UNIQUE NULLS NOT DISTINCT, если нужно другое поведение. А внешнему ключу нужен обычный уникальный индекс по столбцам, на которые он ссылается, - частичный уникальный индекс не подходит, что удивляет тех, кто построил такой для приёма с ограничением выше.

Наконец, INCLUDE (PostgreSQL 11 и новее) добавляет в B-tree полезные столбцы, не делая их частью ключа сортировки. Он существует ради index-only scan:

CREATE INDEX orders_lookup ON app.orders (tenant_id, created_at) INCLUDE (total, status);

Чтение плана: используется ли индекс на самом деле?#

Узнать это можно только через EXPLAIN (ANALYZE, BUFFERS). Названия узлов говорят, что произошло:

• Seq Scan - прочитана каждая страница таблицы. Правильно для маленьких таблиц и для запросов, которым нужна большая часть строк.
• Index Scan - пройден индекс, каждая подходящая строка достаётся из таблицы.
• Bitmap Index Scan, за которым идёт Bitmap Heap Scan - совпадений много, поэтому PostgreSQL собрал их и прочитал таблицу в физическом порядке. Часто это правильный план и намёк на то, что может существовать более селективный индекс.
• Index Only Scan - всё, что нужно запросу, нашлось в индексе. Самый быстрый из трёх, и единственный, зависящий от vacuum.

Эта последняя зависимость неочевидна. Индекс не записывает, видна ли версия строки вашей транзакции, поэтому index-only scan всё равно должен заглянуть в таблицу, если карта видимости не говорит, что вся страница видна всем. Эти биты выставляет vacuum. Таблица, с которой autovacuum не справляется, незаметно теряет свои index-only scan, и это один из способов, которыми vacuum и bloat превращаются в проблему скорости запросов. Если в плане написано Heap Fetches: 84213, вы смотрите именно на это.

Seq scan - не всегда ошибка. На таблице в 500 строк он быстрее любого индекса, а на запросе, возвращающем 40% таблицы, индекс означал бы больше случайных чтений, чем прямой проход. Планировщик решает по константам стоимости и статистике, так что если он выбирает плохо, проверьте, что ANALYZE запускался недавно и что random_page_cost соответствует вашему хранилищу - обе темы разобраны в статье настройка PostgreSQL для маленьких серверов. Само чтение плана - отдельное умение: в статье EXPLAIN ANALYZE на медленном запросе оно разобрано построчно.

Когда индекс вредит#

Издержки реальны, и на маленьком сервере они заметны.

Запись. Каждый INSERT и DELETE обновляет каждый индекс таблицы. UPDATE - тоже, если только PostgreSQL не может выполнить heap-only tuple update, а это возможно лишь когда ни один индексируемый столбец не менялся и на странице есть свободное место. Индекс по столбцу, который приложение постоянно обновляет, убирает эту оптимизацию и умножает стоимость записи.

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

Планирование. Каждый лишний индекс - ещё один вариант, который оценивает планировщик, а набор перекрывающихся индексов делает плохой выбор вероятнее, а не менее вероятным.

Раздувание. Индексы раздуваются так же, как таблицы, а раздутый индекс медленнее.

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

Создание и удаление индексов без блокировки таблицы#

Обычный CREATE INDEX берёт блокировку, которая блокирует запись в таблицу на всё время построения. На большой таблице в рабочее время это простой.

CREATE INDEX CONCURRENTLY orders_tenant_created  ON app.orders (tenant_id, created_at);

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

SELECT indexrelid::regclass AS index, indrelid::regclass AS tableFROM pg_index WHERE NOT indisvalid;DROP INDEX CONCURRENTLY orders_tenant_created;

Перестроение следует тому же шаблону через REINDEX INDEX CONCURRENTLY (PostgreSQL 12 и новее). Увеличение maintenance_work_mem на время сессии ускоряет любую из этих операций. Добавление индекса - такое же изменение схемы, как любое другое, поэтому совет о порядке шагов из статьи миграции схемы без простоя применим: добавляйте его отдельным шагом, до кода, которому он нужен.

Как найти неиспользуемые и недостающие#

PostgreSQL ведёт счётчики. Пользуйтесь ими, а не мнениями.

-- Indexes nobody has used, largest firstSELECT s.relname AS table, s.indexrelname AS index, s.idx_scan AS scans,       pg_size_pretty(pg_relation_size(s.indexrelid)) AS sizeFROM pg_stat_user_indexes sJOIN pg_index i ON i.indexrelid = s.indexrelidWHERE s.idx_scan = 0 AND NOT i.indisuniqueORDER BY pg_relation_size(s.indexrelid) DESC;-- Tables being read sequentially the mostSELECT relname, seq_scan, seq_tup_read, idx_scan,       seq_tup_read / GREATEST(seq_scan, 1) AS rows_per_scanFROM pg_stat_user_tablesWHERE seq_scan > 0ORDER BY seq_tup_read DESCLIMIT 10;

Две оговорки по первому запросу. Счётчики сбрасываются при сбросе статистики или пересоздании сервера, так что ноль на сервере, перезапущенном вчера, ничего не значит; в PostgreSQL 16 и новее добавлен last_idx_scan, которому проще доверять. И никогда не удаляйте уникальный индекс на том основании, что он не используется, - он обеспечивает ограничение, запрашивает его кто-нибудь или нет.

Второй запрос находит кандидатов, а не ответы. Таблица с миллионами последовательно прочитанных строк говорит, что какой-то запрос сканирует её снова и снова; pg_stat_statements подскажет, какой именно, а EXPLAIN - какой индекс это исправит. Когда оценки планировщика сильно расходятся по двум коррелирующим столбцам, расширенная статистика (CREATE STATISTICS) может исправить оценку вообще без нового индекса.

FAQ#

Сколько индексов на одной таблице - это слишком много?

Фиксированного числа нет, но если на таблице с постоянной записью их больше пяти-шести, вы должны уметь назвать запрос, которому служит каждый. Если не можете, посмотрите idx_scan и удалите те, которыми никто не пользуется.

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

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

Нужно ли индексировать каждый столбец внешнего ключа?

Индексируйте те, по которым вы фильтруете или соединяете, и те, чьи родительские строки удаляются или обновляются. На маленькой дочерней таблице это не важно; на большой неиндексированный внешний ключ превращает каждое удаление родителя в полное сканирование под блокировкой.

Нужно ли обслуживать индексы?

Они поддерживаются в актуальном состоянии автоматически, но раздуваются по мере обновления и удаления строк. REINDEX INDEX CONCURRENTLY перестраивает индекс, не блокируя запись. Большинству баз этого не требуется; таблице с сильной текучестью - требуется.

Что такое index-only scan и почему мой перестал использоваться?

Это план, в котором все нужные запросу столбцы есть в индексе, так что к таблице обращения нет. Ему нужно, чтобы карта видимости пометила страницы как полностью видимые, а это делает только vacuum. Если autovacuum отстал, план незаметно деградирует до чтения строк из heap.

Что лучше: составной индекс или несколько индексов по одному столбцу?

Составной, когда столбцы используются вместе в одном запросе и в правильном порядке. Индексы по одному столбцу лучше, когда столбцы независимо используются разными запросами. PostgreSQL умеет объединять два индекса в bitmap scan, но это запасной вариант, а не план, к которому стоит стремиться.