Добавил индекс - запрос всё равно тормозит. Знакомо? Проблема не в том, что индекс «не работает». Проблема в том, что выбран не тот тип.
В этой статье разберём каждый тип индекса PostgreSQL на реальных примерах: когда B-Tree помогает, когда нужен GIN, BRIN или partial - и как не перегрузить базу лишними структурами.
Как PostgreSQL выбирает индекс - и когда игнорирует
Планировщик PostgreSQL не просто «ищет индекс» - он считает стоимость каждого варианта выполнения запроса. Побеждает самый дешёвый план.
Стоимость vs. селективность
Два ключевых фактора:
- Селективность - доля строк, которую вернёт условие. Если
WHERE status = 'active'возвращает 40% таблицы, индекс, скорее всего, проиграет обычному Seq Scan. - Стоимость доступа - Index Scan читает индекс, затем по указателям обращается к heap. Если таблица большая, а условие неселективное, прыжки по heap дороже одного последовательного прохода.
Пороговое значение зависит от random_page_cost и effective_cache_size. На SSD стоит снизить random_page_cost до 1.1–1.5, чтобы планировщик чаще выбирал Index Scan.
Три причины, почему индекс игнорируется
- Устаревшая статистика. После массовой загрузки данных вызовите
ANALYZE. Планировщик опирается наpg_statistic- если там устаревшие данные, он недооценит количество строк и возьмёт неоптимальный план. - Функция в
WHERE. ЗапросWHERE lower(email) = 'test@mail.com'не задействует обычный индекс поemail. Нужен функциональный:CREATE INDEX ON users (lower(email)). - Несовместимый тип оператора.
LIKE '%text%'(поиск в середине строки) обычный B-Tree не индексирует. Нужен триграммный индекс черезpg_trgm.
Как читать EXPLAIN (ANALYZE, BUFFERS)
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE created_at > '2026-01-01';
Смотрите на три строки:
| Строка в выводе | Что означает |
|---|---|
Seq Scan / Index Scan |
Метод доступа |
buffers: shared hit=X read=Y |
X — из кэша, Y — с диска. Много Y → индекс не помещается в shared_buffers |
actual rows=N loops=1 |
Сколько строк реально вернул план |
Если видите много read при наличии индекса - индекс слишком большой или данные физически разбросаны по heap. Это сигнал пересмотреть тип индекса.
Авторская ремарка: Я всегда запускаю
EXPLAIN (ANALYZE, BUFFERS)перед созданием нового индекса и после - только так понятно, что реально изменилось.
B-Tree: универсальный, но не всесильный
B-Tree - индекс по умолчанию. CREATE INDEX без USING создаёт именно его. Он покрывает большинство задач, но у него есть слепые зоны.
Когда B-Tree - лучший выбор
Используйте B-Tree, если в WHERE стоит один из операторов: =, <, <=, >, >=, BETWEEN, IN, IS NULL, LIKE 'foo%'.
-- Стандартный B-Tree на дату
CREATE INDEX idx_orders_created ON orders (created_at);
-- Уникальный B-Tree
CREATE UNIQUE INDEX idx_users_email ON users (email);
B-Tree также ускоряет ORDER BY - если сортировка совпадает с направлением индекса, PostgreSQL пропустит шаг сортировки целиком.
Составной и покрывающий (INCLUDE) индекс
Составной индекс по (a, b) эффективнее двух отдельных, если фильтрация часто идёт по обоим полям. Правило: в начале ставьте столбец с наивысшей селективностью.
С PostgreSQL 11 появился INCLUDE - добавляет столбцы в листовые узлы индекса без участия в поиске. Это позволяет выполнять Index Only Scan: PostgreSQL берёт все нужные данные прямо из индекса, не обращаясь к heap.
-- Покрывающий индекс: ищем по user_id, возвращаем status без heap
CREATE INDEX idx_orders_cover
ON orders (user_id)
INCLUDE (status, amount);
Когда B-Tree не справляется
LIKE '%text%'- поиск в середине строки.jsonb @> '{"role": "admin"}'- проверка вхождения в JSON.tags @> ARRAY['sale']- поиск по элементу массива.- Таблица на 500 млн строк с монотонно растущим
timestamp- индекс займёт десятки гигабайт и не поместится в кэш.
Для этих случаев - GIN, BRIN или pg_trgm.
GIN: когда в одной строке много значений
GIN (Generalized Inverted Index, обобщённый инвертированный индекс) хранит отдельный элемент для каждого «компонента» внутри значения столбца. Это как словарный указатель: слово → список строк, где оно встречается.
GIN для jsonb и массивов
-- GIN на jsonb
CREATE INDEX idx_products_attrs
ON products USING GIN (attributes);
-- Запрос, использующий этот индекс
SELECT * FROM products
WHERE attributes @> '{"color": "black", "size": "L"}';
-- GIN на массив тегов
CREATE INDEX idx_articles_tags
ON articles USING GIN (tags);
SELECT * FROM articles WHERE tags @> ARRAY['postgresql', 'indexes'];
GIN поддерживает операторы @>, <@, &&, = для массивов и jsonb. B-Tree на те же запросы сделает последовательный скан - потому что не умеет «заглядывать» внутрь составного значения.
GIN для полнотекстового поиска
-- Добавляем tsvector-столбец и индекс
ALTER TABLE articles ADD COLUMN search_vec tsvector;
UPDATE articles SET search_vec = to_tsvector('russian', title || ' ' || body);
CREATE INDEX idx_articles_fts
ON articles USING GIN (search_vec);
-- Полнотекстовый запрос
SELECT title FROM articles
WHERE search_vec @@ to_tsquery('russian', 'PostgreSQL & индекс');
Для поиска по произвольным подстрокам (LIKE '%text%') используйте расширение pg_trgm + GIN:
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_name_trgm
ON users USING GIN (name gin_trgm_ops);
-- Теперь LIKE '%ivan%' задействует индекс
SELECT * FROM users WHERE name ILIKE '%ivan%';
GIN vs GiST: что выбрать
GiST (Generalized Search Tree) - инфраструктура для нестандартных структур данных: геометрия, геоданные (PostGIS), диапазонные типы (tsrange, int4range).
| Критерий | GIN | GiST |
|---|---|---|
| Скорость чтения | Быстрее | Медленнее |
| Скорость вставки | Медленнее | Быстрее |
| jsonb, массивы | ✅ | ❌ |
| PostGIS, геометрия | ❌ (частично) | ✅ |
| Диапазонные типы | ❌ | ✅ |
| Поиск «ближайшего соседа» | ❌ | ✅ |
Для полнотекстового поиска GIN и GiST оба работают - но GIN быстрее на чтение, GiST быстрее обновляется при частых INSERT/UPDATE.
Авторская ремарка: Когда в SaaS-проекте нужна была поддержка фильтрации по jsonb-атрибутам товаров, GIN дал ускорение запросов примерно в 50–80 раз по сравнению с Seq Scan на таблице 10 млн строк. B-Tree на те же поля был бессилен - он просто не поддерживает оператор
@>.
Параметр fastupdate: По умолчанию GIN откладывает запись изменений в отдельный список ожидания (gin_pending_list_limit, по умолчанию 4 МБ). Это ускоряет INSERT, но замедляет первый SELECT после массовой загрузки. Если нужна предсказуемая latency - отключите: CREATE INDEX ... WITH (fastupdate = off).
BRIN: индекс для таблиц-гигантов
BRIN (Block Range INdex, индекс зон блоков) работает принципиально иначе чем B-Tree. Он не хранит ссылку на каждую строку - он хранит минимум и максимум значений для группы физически соседних блоков (зоны).
Как работает BRIN изнутри
По умолчанию одна зона = 128 страниц (≈ 1 МБ данных). Для каждой зоны BRIN знает: «здесь значения created_at от 2026-01-01 до 2026-01-31». Запрос за февраль пропустит эту зону целиком - и так для большинства зон таблицы.
Отсюда главное условие: данные должны быть физически упорядочены по индексируемому столбцу. Для таблицы логов, куда строки пишутся в хронологическом порядке, это выполняется автоматически. Для таблицы, в которую вставляют строки в случайном порядке - BRIN бесполезен.
-- BRIN на timestamp в таблице событий
CREATE INDEX idx_events_brin
ON events USING BRIN (created_at)
WITH (pages_per_range = 64); -- уменьшаем зону для точности
Настройка pages_per_range: меньше значение → точнее фильтрация, но больше размер индекса. Начните с 64–128 и замеряйте через EXPLAIN ANALYZE.
BRIN для временных рядов и IoT
Реальный кейс (воспроизведён по материалам Habr OTUS, март 2026): система сбора метрик с тысяч серверов, ~100 млн строк в сутки. B-Tree по (server_id, timestamp) занимал 40 ГБ и не помещался в shared_buffers - каждый запрос шёл на диск. После замены на BRIN по timestamp индекс занял 120 МБ, запросы по временным диапазонам стали выполняться за миллисекунды [web:1].
-- До
CREATE INDEX idx_metrics_btree ON metrics (server_id, timestamp);
-- Размер: ~40 ГБ на 100 млрд строк ⚠️ Предположительно, масштаб зависит от данных
-- После
CREATE INDEX idx_metrics_brin ON metrics USING BRIN (timestamp);
-- Размер: ~120 МБ
Когда BRIN не поможет
- Таблицы, куда строки вставляются в произвольном порядке (нет физической корреляции).
UPDATE-heavy workload: обновления нарушают физический порядок данных.- Запросы с высокой точностью фильтрации по узкому диапазону - BRIN может прочитать лишние блоки, и Seq Scan окажется сопоставим по стоимости.
Проверить корреляцию столбца с физическим порядком:
SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'your_table'
ORDER BY correlation DESC;
Если correlation близко к 1.0 или -1.0 - BRIN подойдёт.
Partial index: маленький, но меткий
Частичный (partial) индекс строится только по строкам, которые удовлетворяют условию WHERE. Остальные строки в индекс не попадают совсем.
Как создать partial index
Классический пример - таблица задач, где 95% строк имеет status = 'done' и только 5% - status = 'pending'. Индекс по всей таблице на status почти бесполезен (низкая селективность). Partial index - другое дело:
-- Индексируем только активные задачи
CREATE INDEX idx_tasks_pending
ON tasks (created_at)
WHERE status = 'pending';
-- Запрос использует индекс, потому что условия совпадают
SELECT * FROM tasks
WHERE status = 'pending' AND created_at < NOW() - INTERVAL '1 day';
Результат: индекс охватывает 5% строк → он в 20 раз меньше полного B-Tree. Он почти всегда помещается в кэш и работает быстрее.
Другие типичные случаи:
- Мягкое удаление:
WHERE deleted_at IS NULL - Очередь задач:
WHERE processed = false - Активные пользователи:
WHERE last_login > NOW() - INTERVAL '90 days'
Частичный + функциональный: комбо
Можно комбинировать: функциональный индекс по выражению с partial-условием.
-- Регистронезависимый поиск только по активным пользователям
CREATE INDEX idx_users_email_active
ON users (lower(email))
WHERE deleted_at IS NULL;
-- Этот запрос использует индекс
SELECT * FROM users
WHERE lower(email) = 'user@example.com' AND deleted_at IS NULL;
Как найти и удалить лишние индексы
Каждый индекс - это накладные расходы на INSERT/UPDATE/DELETE. Найти неиспользуемые:
SELECT
schemaname,
tablename,
indexname,
idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE 'pg_%'
ORDER BY pg_relation_size(indexrelid) DESC;
Индексы с idx_scan = 0 за последние 2 недели - кандидаты на удаление. Перед удалением: DROP INDEX CONCURRENTLY idx_name - без блокировки таблицы.
Шпаргалка: какой индекс выбрать
| Задача | Тип данных / оператор | Рекомендация |
|---|---|---|
| Равенство, диапазон, сортировка | любой скалярный, = < > BETWEEN |
B-Tree (дефолт) |
| Уникальность | любой | B-Tree UNIQUE |
LIKE 'foo%' (префикс) |
text | B-Tree |
LIKE '%foo%' (подстрока) |
text | GIN + pg_trgm |
| Полнотекстовый поиск | tsvector, @@ |
GIN |
jsonb, оператор @> ? |
jsonb | GIN |
Массив, оператор @> && |
array | GIN |
| Иерархия, ltree | ltree | GIN или GiST |
| Геоданные, PostGIS | geometry, && <-> |
GiST |
| Диапазонные типы | tsrange, int4range | GiST |
Только =, очень быстро |
скалярный | Hash (редко нужен) |
| Таблица-гигант, физический порядок | timestamp, id (autoincrement) | BRIN |
| Подмножество строк | любой + условие WHERE | Partial index |
| Покрывающий запрос (без heap) | скалярный | B-Tree + INCLUDE |
| Функция в WHERE | lower(col), date_trunc(...) |
Функциональный |
Bonus: создание индекса в продакшене без блокировок
Обычный CREATE INDEX блокирует запись в таблицу на время построения. На большой таблице это недопустимо. Используйте:
CREATE INDEX CONCURRENTLY idx_orders_user_id
ON orders (user_id);
CONCURRENTLY строит индекс в два прохода - это медленнее, но не блокирует INSERT/UPDATE. Ограничение: нельзя выполнять внутри транзакции и нельзя создавать уникальные индексы одновременно с операциями записи в случае конфликтов.
Если индекс «раздулся» из-за большого числа удалений - перестройте:
REINDEX INDEX CONCURRENTLY idx_orders_user_id;
Альтернативное мнение
Некоторые DBA считают, что лучшая стратегия индексирования - «минимум индексов, максимум партиционирования». Аргумент: в highload-системах партиционирование по диапазону дат уже даёт прунинг партиций (planner пропускает ненужные), а индексы внутри каждой партиции остаются маленькими и всегда помещаются в кэш. Это справедливо для OLAP-нагрузки. Для OLTP, где запросы точечные и нужна предсказуемая latency, правильно подобранный индекс незаменим.
Нетривиальный факт
BRIN на монотонно растущем id (serial, bigserial) работает почти так же хорошо, как на timestamp - потому что id физически коррелирован с порядком вставки строк. Если вы используете UUID v4 вместо serial, BRIN по id окажется бесполезным: UUID v4 случаен, корреляция близка к нулю. UUID v7 (упорядоченный по времени) решает эту проблему и позволяет использовать BRIN.
FAQ
Какой индекс PostgreSQL использовать по умолчанию?
B-Tree - индекс по умолчанию. Подходит для равенства, диапазонов, ORDER BY и уникальности. Если не знаете, какой тип выбрать - берите B-Tree.
Когда нужен GIN индекс?
GIN нужен, когда в одной строке хранится несколько значений: jsonb (оператор @>), массивы (@>, &&), полнотекстовый поиск (tsvector + @@), ltree. Для LIKE-поиска по подстроке - GIN + pg_trgm.
Чем BRIN отличается от B-Tree?
BRIN хранит мин/макс значений для зон блоков, а не запись для каждой строки. Занимает в сотни раз меньше места, но работает только если данные физически упорядочены по индексируемому столбцу (например, timestamp в таблице логов).
Что такое partial index и зачем он нужен?
Partial index строится только по строкам, которые удовлетворяют условию WHERE. CREATE INDEX ... WHERE status = 'active' охватывает только активные записи - индекс меньше, быстрее помещается в кэш, ускоряет именно нужные запросы.
Почему PostgreSQL не использует мой индекс?
Три причины: (1) Низкая селективность - условие возвращает >20–30% строк, Seq Scan дешевле. (2) Устаревшая статистика - запустите ANALYZE. (3) Несовместимый оператор - LIKE '%text%' не работает с B-Tree, нужен pg_trgm.
Как создать индекс в продакшене без блокировки?
Используйте CREATE INDEX CONCURRENTLY. Строит индекс в два прохода, не блокирует INSERT/UPDATE/DELETE. Нельзя выполнять внутри транзакции.
Как найти неиспользуемые индексы PostgreSQL?
Запрос к pg_stat_user_indexes: WHERE idx_scan = 0. Индексы с нулевым числом обращений за 2 недели - кандидаты на удаление через DROP INDEX CONCURRENTLY.
.svg.webp){kind=logo}
