🌲 Индексы
Индекс — отдельная структура рядом с таблицей, по которой база находит строки, не читая таблицу целиком. На работе от него зависит, ответит ручка за миллисекунду или положит базу под нагрузкой. На собеседовании про индексы спрашивают почти всегда: что такое B-дерево, важен ли порядок полей в составном индексе, почему индекс есть, а запрос его не использует. Все цифры ниже получены на PostgreSQL 16 на таблице заказов в 5 млн строк
🧠 Как это устроено
Что даёт индекс
Таблица orders занимает 534 МБ. Запрос where user_id = 4242 без индекса читает все 5 млн строк, чтобы найти 28 нужных: Parallel Seq Scan за 86 мс, и это при таблице, целиком лежащей в памяти. Индекс на user_id собрался за 1,2 секунды, занял 37 МБ, и тот же запрос выполнился за 0,3 мс
Разница в 300 раз выглядит не страшно, пока запрос один. Когда такой запрос приходит тысячу раз в секунду, без индекса база занята только им
B-дерево
B-tree — индекс по умолчанию: create index без using строит именно его. Это сбалансированное дерево из страниц по 8 КБ. В листьях лежат значения ключа в отсортированном порядке и ссылки на строки таблицы, листья связаны между собой в список. Верхние уровни хранят границы: «ключи до 1000 налево, остальные направо»
Поиск идёт от корня к листу, на каждом уровне выбирая одну страницу. Дерево низкое: у первичного ключа на 6,5 млн строк всего три уровня. Значит, чтобы найти одну строку, база читает три страницы индекса и одну страницу таблицы
Из устройства следует, что B-tree умеет:
- равенство и диапазоны:
=,<,>,between,in is nullиis not nullorder byпо ключу без сортировки, в обе стороныlike 'abc%', когда шаблон начинается с константы
И чего не умеет: like '%abc', поиск по элементу массива или ключу внутри jsonb. Для этого есть другие типы
| Тип | Для чего |
|---|---|
| B-tree | почти всё: равенство, диапазоны, сортировка, уникальность |
| Hash | только =, на практике его редко выбирают вместо B-tree |
| GIN | значения из многих частей: массивы, jsonb, полнотекстовый поиск, триграммы |
| GiST, SP-GiST | геометрия, диапазоны, поиск ближайших |
| BRIN | огромные таблицы, где значения идут по порядку записи, например время в логах |
Составной индекс и порядок полей
Индекс на (user_id, created_at) отсортирован сначала по user_id, а внутри одного пользователя по created_at. Как телефонная книга: по фамилии найти легко, по имени без фамилии — только перелистав всю книгу
Правило из документации Postgres: диапазон чтения индекса сужают равенства на первые поля и одно неравенство на первое поле без равенства. Условия на поля правее проверяются внутри индекса, но читать меньше не заставляют
| Запрос | Что вышло |
|---|---|
where user_id = 4242 order by created_at desc limit 10 | Index Scan Backward, без сортировки, 0,1 мс |
where user_id between 4000 and 4100 and created_at > now() - interval '1 day' | индекс используется, 0,2 мс |
where created_at > now() - interval '1 hour' | Parallel Seq Scan, 178 мс |
Первая строка — главный повод для составного индекса: «последние заказы пользователя» отдаются прямо из индекса в нужном порядке. Третья — то, о чём спрашивают на собеседовании: без условия на первое поле индекс не помог
В PostgreSQL 18 (вышел в сентябре 2025) у B-tree появился skip scan: база перебирает значения первого поля и для каждого ищет по второму. Документация оговаривает, что это выгодно, только когда разных значений первого поля мало. У нас 200 тысяч пользователей, и в такой ситуации, по той же документации, планировщик почти всегда предпочтёт прочитать таблицу. Так что правило порядка полей осталось: первым ставят поле, по которому ищут равенством во всех запросах
Покрывающий индекс
Обычный индексный поиск находит ссылку и идёт за строкой в таблицу. Если все нужные запросу столбцы есть в индексе, Postgres делает Index Only Scan и в таблицу не ходит. Чтобы добавить столбец только для чтения, без участия в сортировке, есть include:
create index orders_user_created_incl
on orders (user_id, created_at) include (amount);
explain (analyze, costs off, timing off)
select created_at, amount from orders where user_id = 4242;
-- Index Only Scan using orders_user_created_incl on orders (actual rows=28 loops=1)
-- Index Cond: (user_id = 4242)
-- Heap Fetches: 0Есть подвох. В индексе нет информации о видимости строки для транзакции, поэтому Postgres смотрит в карту видимости: если страница таблицы помечена как «все строки видны всем», в неё можно не ходить. После обновления 25 тысяч строк у тех же 28 заказов Heap Fetches выросло до 56, база снова читала таблицу. После vacuum вернулось к 0. На таблице, которую постоянно пишут, покрывающий индекс выигрывает мало
И он тяжелее: с include (amount) индекс занял 194 МБ вместо 151 МБ
Частичный индекс
Если запросы всегда фильтруют по одному значению, индексировать остальные строки незачем. Новых заказов в таблице 5 тысяч из 5 млн, а очередь на обработку читает только их:
create index orders_new on orders (created_at) where status = 'new';
select id from orders where status = 'new' order by created_at limit 20;
-- Index Scan using orders_new, 0,06 мсТакой индекс занял 80 КБ. Обычный индекс на status весит 33 МБ. Частичный индекс работает, только если условие запроса совпадает с условием индекса или следует из него: для status = 'paid' он бесполезен
Индекс по выражению и GIN
Индекс на email не помогает запросу where lower(email) = 'user42@mail.ru': в индексе лежит email, а не lower(email), и база читает таблицу. Решение — индекс по тому же выражению: create index on orders (lower(email)). После него план стал Index Scan. Выражение в запросе должно совпасть с индексом буквально
GIN — обратный индекс: для каждого элемента хранится список строк, где он встречается. Так работает поиск по тегам:
create index orders_tags on orders using gin (tags);
select count(*) from orders where tags @> array['vip'];
-- 500 строк: 0,6 мс с индексом, 169 мс без негоТот же индекс на теге gift, который есть у 100 тысяч заказов, проиграл чтению таблицы: 310 мс против 200. Эти 2% строк разбросаны по 61 тысяче страниц из 68, и база прочитала почти всю таблицу, только в случайном порядке
Цена индекса
Каждый индекс обновляется при каждой вставке. 500 тысяч строк в пустую таблицу:
| Индексов | Время вставки | Размер таблицы с индексами |
|---|---|---|
| 0 | 0,3 с | 45 МБ |
| 2 | 1,9 с | 98 МБ |
| 7 | 5,5 с | 193 МБ |
С обновлением тоньше. Если меняется столбец, которого нет ни в одном индексе, и на странице есть свободное место, Postgres делает HOT-обновление: новая версия строки ложится на ту же страницу, индексы не трогаются. В таблице с fillfactor = 90 обновление неиндексированного столбца у 5000 строк дало 5000 HOT-обновлений. Обновление индексированного столбца у других 5000 строк не дало ни одного. Лишний индекс на часто меняющемся поле ломает этот механизм
🛠️ На практике
Индекс на живой таблице создают так:
create index concurrently orders_user_created on orders (user_id, created_at);Обычный create index блокирует запись в таблицу на всё время построения: чтение идёт, а вставки и обновления ждут. На большой таблице построение идёт минуты, и всё это время запись стоит. concurrently не блокирует запись, но читает таблицу дважды, ждёт завершения текущих транзакций и не работает внутри транзакции. Последнее важно для миграций: многие инструменты оборачивают каждую миграцию в транзакцию, и такой шаг придётся вынести отдельно
Если построение упало, например на нарушении уникальности, остаётся индекс с пометкой INVALID. Запросы его не используют, а вставки продолжают обновлять. Его удаляют и строят заново или делают reindex index concurrently
SQL · Почему индекс не работает: три запроса из жизни
-- 1. Функция над столбцом: индекс на created_at не используется
select id from orders where created_at::date = '2026-11-01';
-- так используется
select id from orders where created_at >= '2026-11-01' and created_at < '2026-11-02';
-- 2. LIKE по префиксу в базе с локалью en_US.utf8: обычный индекс не годится
create index orders_email_pattern on orders (email text_pattern_ops);
select id from orders where email like 'User4242%';
-- Index Cond: ((email ~>=~ 'User4242'::text) AND (email ~<~ 'User4243'::text))
-- 3. LIKE с % в начале: B-tree не поможет никогда, нужен pg_trgm
create extension pg_trgm;
create index orders_email_trgm on orders using gin (email gin_trgm_ops);
select id from orders where email like '%4242@Mail.ru';Триграммный индекс на той же колонке занял 162 МБ против 54 МБ у обычного B-tree, так что его заводят под конкретный поиск, а не на всякий случай
⚠️ Где ошибаются
Индекс на каждый столбец. Запросы по нескольким полям это ускоряет плохо: два одиночных индекса Postgres умеет объединить через BitmapAnd, но это дороже одного составного и не даёт готового порядка для сортировки. Зато каждая вставка обновляет все индексы, и HOT-обновления перестают работать
Индекс на поле с тремя-четырьмя значениями. Индекс на status для status = 'paid' бесполезен: оплачена половина заказов, и Postgres читает таблицу целиком, даже когда индекс есть. Для редкого значения лучше частичный индекс, он в сотни раз меньше
Составной индекс в неправильном порядке. Индекс (created_at, user_id) не отдаст последние заказы пользователя без сортировки. Первым ставят поле, по которому ищут равенством, вторым то, по которому сортируют или ищут диапазон
create index без concurrently в миграции. На маленькой таблице незаметно, на большой запись встаёт на минуты, а сервис отваливается по таймаутам
Индекс по выражению без analyze. Статистика по lower(email) собирается при следующем analyze. До этого планировщик оценивает число строк наугад и может выбрать не тот план
🎯 Вопросы с собеседований
Что такое B-дерево и почему поиск по нему быстрый?
Сбалансированное дерево из страниц, где в листьях лежат отсортированные ключи и ссылки на строки, а на верхних уровнях границы диапазонов. Поиск идёт от корня к листу, на каждом уровне читается одна страница. Дерево низкое: на миллионы строк хватает трёх-четырёх уровней, поэтому одна строка находится за несколько чтений страниц вместо полного перебора таблицы
Важен ли порядок полей в составном индексе?
Да. Индекс на (a, b) отсортирован по a, а внутри по b, поэтому он ускоряет запросы с условием на a или на a и b, а запрос только по b обычно идёт мимо. В PostgreSQL 18 появился skip scan, который иногда спасает такой запрос, но выгоден он, только когда разных значений a мало. Первым ставят поле с равенством, затем поле для диапазона или сортировки
Индекс есть, а запрос его не использует. Почему?
Обычные причины: функция или приведение типа над столбцом (lower(email), created_at::date), like с процентом в начале, условие не на первое поле составного индекса. Ещё бывает, что запрос выбирает большую долю таблицы, и последовательное чтение дешевле. Или статистика устарела, и планировщик неверно оценил число строк. Проверяют через explain analyze
Чем покрывающий индекс отличается от обычного?
В нём есть все столбцы, которые нужны запросу, поэтому Postgres делает Index Only Scan и не читает таблицу. Неключевые столбцы добавляют через include. Выигрыш есть, только если большая часть страниц таблицы помечена в карте видимости, а это зависит от vacuum: на часто обновляемой таблице база всё равно ходит в таблицу проверять видимость
Что такое частичный индекс и когда он нужен?
Индекс с условием where, в него попадают только подходящие строки. Нужен, когда запросы всегда ищут по редкому значению: необработанные заказы, активные сессии. Он меньше и быстрее обновляется. Работает, только если условие запроса совпадает с условием индекса или следует из него
Почему не стоит создавать индекс на каждый столбец?
Каждый индекс обновляется при вставке и при изменении его столбцов, занимает место и память. Индексированные столбцы мешают HOT-обновлениям, и обновление начинает трогать все индексы. А пользы мало: запросу по нескольким полям нужен один составной индекс, а не набор одиночных
Когда нужен GIN, а когда B-tree?
B-tree работает со значением целиком: равенство, диапазоны, сортировка. GIN раскладывает значение на части и для каждой хранит список строк, поэтому он нужен для поиска элемента в массиве, ключа в jsonb, слова в тексте или подстроки через pg_trgm. GIN дороже обновлять и он не умеет сортировать
Как добавить индекс на большую таблицу в проде?
Через create index concurrently. Он не блокирует запись, но идёт дольше, читает таблицу дважды и не работает внутри транзакции, поэтому в миграции его выносят в отдельный шаг без транзакции. Если построение упало, остаётся невалидный индекс: его удаляют и строят заново
📚 Что почитать
- Составные индексы — точное правило про первые поля и как работает skip scan
- Index-only scans и покрывающие индексы — карта видимости и ограничения
include - CREATE INDEX — какие блокировки берёт построение и что делать с невалидным индексом
- Use The Index, Luke — бесплатная книга Маркуса Винанда про индексы для разработчиков, с примерами на Postgres