Введение: зачем разработчику разбираться в индексах PostgreSQL
Если вы уже интуитивно понимаете, что индекс ускоряет чтение и замедляет запись, то в PostgreSQL быстро возникает следующий слой вопросов: почему один и тот же индекс иногда даёт ускорение в разы, а иногда вообще не используется; какие реальные издержки вы платите за каждый CREATE INDEX; какие типы индексов есть в Postgres и когда выбирать B-tree, GIN, GiST или BRIN.
Эта статья — для разработчиков, которым важно понимать внутреннюю механику индексов, trade-off’ы и набор практических приёмов, чтобы индексация соответствовала вашему реальному профилю запросов (OLTP, API, аналитика, поиск, события).
Как PostgreSQL хранит данные: heap, страницы и ctid
Таблица в PostgreSQL физически хранится как heap (куча кортежей/строк), разбитая на страницы (обычно 8KB). У каждой строки есть физический адрес — системное поле ctid в формате (номер_страницы, смещение_внутри_страницы). Важно: ctid — это не бизнес-идентификатор, он может меняться (например, при UPDATE), но он помогает понять, как именно индекс «приводит» вас к строкам.
Быстрая демонстрация (только для понимания, не как «паттерн на прод»):
CREATE TABLE foo (id int, name text);
INSERT INTO foo VALUES (1, 'Ronaldo'), (2, 'Romario');
SELECT ctid, * FROM foo;
В упрощённом виде индекс хранит структуру «ключ → ссылка на строки (TID/ctid)», чтобы не читать всю таблицу подряд. Но в реальности он ещё влияет на план выполнения, на стоимость записи и на обслуживание MVCC-версий строк.
Почему планировщик иногда игнорирует индекс и выбирает Seq Scan
Индекс не обязателен к использованию: PostgreSQL выбирает план, который считает дешевле по стоимости (cost). Есть несколько типовых причин, почему индекс может быть проигнорирован:
- Низкая селективность: запрос возвращает слишком большой процент строк, и последовательное чтение оказывается дешевле.
- Статистика неточная: planner ошибается в оценках кардинальности (особенно если данные сильно скошены или статистика устарела).
- Выражение в WHERE не соответствует индексу: например, WHERE lower(email)=… без индекса на lower(email).
- Сильная конкуренция за cache: чтение через индекс приводит к множеству случайных обращений к страницам (random I/O).
- Параллельный Seq Scan может «побить» индекс на больших выборках.
Проверять реальность важно через анализ плана с фактическим выполнением и буферами:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM foo WHERE name = 'Ronaldo';
Смотрите не только на “Index Scan/Seq Scan”, но и на фактические строки, время и количество прочитанных страниц (BUFFERS).
Цена индексов: что вы платите за ускорение чтения
Индекс — это ресурсный контракт. Он ускоряет чтение, но требует оплаты в нескольких валютах:
- Диск: индекс занимает место (иногда сопоставимо с таблицей, иногда больше).
- Запись: INSERT/UPDATE/DELETE дорожают — нужно поддерживать структуру индекса.
- VACUUM/maintenance: индексы тоже обслуживаются (reindex, bloat, анализ).
- Планирование запросов: больше индексов — больше вариантов, сложнее выбор плана.
- Кеш: страницы индексов конкурируют за память с данными таблицы.
Практическая мысль: индекс — не «всё индексируем», а «индексируем то, что реально ускоряет ключевые запросы и окупается по стоимости записи».
Основные типы индексов в PostgreSQL и когда какой выбирать
1) B-tree (по умолчанию)
Самый универсальный индекс: равенство, диапазоны, сортировка, join’ы. Идеален для большинства OLTP-запросов и типичных фильтров по датам, id, статусам, внешним ключам.
Примеры запросов, где B-tree почти всегда уместен:
-- точный поиск
SELECT * FROM users WHERE email = 'a@b.com';
-- диапазон по времени
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01';
-- быстрый top-N с сортировкой
SELECT * FROM logs
ORDER BY created_at DESC
LIMIT 100;
Составные индексы и порядок колонок
Составной индекс (a, b) обычно хорошо работает, когда условия/сортировки начинаются с a (принцип “левого префикса”). Поэтому порядок колонок нужно выбирать по вашим частым паттернам запросов.
2) Hash
Нишевый вариант для равенства (=) по одному столбцу. Не поддерживает диапазоны и сортировку. Иногда может быть выгоден на длинных ключах (например, очень длинные строки), но чаще B-tree остаётся дефолтным выбором.
3) GIN (Generalized Inverted Index)
GIN нужен, когда в одном поле «много элементов», а вы ищете по элементам: jsonb, массивы, полнотекст (tsvector), триграммы (через расширение). Это один из самых практичных “must know” типов индексов для современных приложений.
Типовые кейсы:
- Поиск по ключам/значениям в jsonb
- Поиск по элементам массива
- Полнотекстовый поиск
- LIKE/ILIKE по подстроке через pg_trgm
4) GiST и SP-GiST
Подходят для пространственных структур, диапазонов, специфичных типов и операторов. Часто используются в геоданных, range-типах, некоторых сценариях полнотекста/триграмм и нестандартных сравнений.
5) BRIN (Block Range Index)
BRIN — экономичный по месту индекс для огромных таблиц, особенно когда данные записываются «по порядку» и их значения хорошо совпадают с физическим расположением строк (например, по времени вставки). Он хранит агрегированную информацию по диапазонам страниц и помогает быстро исключать блоки, где точно нет нужных значений.
Продвинутые техники индексации, которые дают максимум эффекта
Частичные индексы (partial index)
Индексируете только подмножество строк через WHERE. Это уменьшает размер индекса и ускоряет запросы к «горячим» данным.
-- индексируем только активные записи
CREATE INDEX sessions_active_idx ON sessions (user_id)
WHERE revoked_at IS NULL;
-- индексируем только редкие статусы (если TODO = 90% строк)
CREATE INDEX tasks_not_todo_idx ON tasks (status)
WHERE status <> 'TODO';
Индексы на выражениях (expression indexes)
Когда в WHERE участвует функция или вычисляемое выражение, индекс по исходному столбцу часто бесполезен — тогда имеет смысл создать индекс именно на это выражение, чтобы планировщик мог его использовать.
-- запрос
SELECT * FROM customers WHERE lower(email) = 'john@doe.com';
-- индекс под запрос
CREATE INDEX customers_lower_email_idx ON customers (lower(email));
Покрывающие индексы (INCLUDE) и Index Only Scan
Когда запрос выбирает только те столбцы, которые уже присутствуют в индексе, PostgreSQL может обойтись одним индексом: выполнить Index Only Scan и не ходить в heap-таблицу за данными. Для этого удобно добавлять «покрывающие» колонки через INCLUDE: они хранятся в листовых страницах индекса, но не участвуют в порядке ключа.
-- ключи поиска: (a, b), а поле c нужно только для выдачи результата
CREATE INDEX bar_ab_cover_idx ON bar (a, b) INCLUDE (c);
-- запрос, который потенциально может стать index-only (зависит от видимости страниц)
SELECT c FROM bar WHERE a = 10 AND b = 20;
Bitmap-сканы и комбинация индексов
Postgres умеет объединять несколько индексов для одного запроса (Bitmap Index Scan + Bitmap Heap Scan), особенно когда условия независимы. Иногда это лучше, чем создавать «слишком широкий» составной индекс под каждую комбинацию условий.
Практические примеры: какие индексы ставить под типовые задачи
1) API и фильтры по tenant + времени
Часто в мультиарендных системах фильтр идёт по tenant_id и диапазону по времени. Составной B-tree обычно оправдан.
CREATE INDEX events_tenant_time_idx ON events (tenant_id, created_at);
SELECT * FROM events
WHERE tenant_id = 42
AND created_at >= now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 200;
2) “Только активные” записи
Partial index уменьшит размер индекса и стоимость записи в архив/историю.
CREATE INDEX users_active_idx ON users (last_seen_at)
WHERE deleted_at IS NULL;3) Поиск по JSONB
Если вы храните документы/настройки/метаданные, GIN по jsonb — базовый инструмент.
-- payload должен быть jsonb
CREATE INDEX documents_payload_gin_idx
ON documents
USING GIN (payload);
-- пример запроса по ключу/значению
SELECT * FROM documents
WHERE payload @> '{"role":"admin"}';
4) Поиск по подстроке (LIKE/ILIKE) без якоря слева
Это типичная задача для каталогов, тикетов, логов, поисковых строк. Решается pg_trgm + GIN.
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX products_title_trgm_idx
ON products
USING GIN (title gin_trgm_ops);
SELECT * FROM products
WHERE title ILIKE '%iphone%';
5) Огромные логи/события (append-only)
BRIN часто даёт выгодный компромисс: маленький индекс и заметный выигрыш при фильтрах по времени.
CREATE INDEX logs_ts_brin_idx ON logs USING BRIN (ts);
SELECT * FROM logs
WHERE ts >= '2026-02-01' AND ts < '2026-02-02';
Вывод
Индексы в PostgreSQL — это управляемая оптимизация: вы обмениваете часть ресурсов (диск, стоимость записи, обслуживание) на ускорение чтения. Универсальный выбор по умолчанию — B-tree, но для jsonb/массивов/полнотекста и “поиска внутри поля” чаще нужен GIN, для очень больших коррелированных таблиц — BRIN, а для геометрии/диапазонов и специальных операторов — GiST/SP-GiST.
Главная привычка, которая делает индексы «правильными»: не ставить их по ощущениям, а проверять планы выполнения и фактические затраты через EXPLAIN (ANALYZE, BUFFERS), держать статистику актуальной и выбирать минимальный индекс, который покрывает ваши ключевые запросы без избыточного урона по записи и поддержке.
Чек-лист: как понять, что индекс нужен (и какой)
- 1) Зафиксируйте “проблемный” запрос
Берите реальный запрос из приложения (ORM/SQL), а не упрощённый аналог. Важно, чтобы условия WHERE/ORDER BY/JOIN были такими же, как в проде. - 2) Проверьте план выполнения до изменений
Снимите план с фактами и буферами — это ваша точка “до”.
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...
- 3) Оцените селективность
Если запрос возвращает “почти всё” (условно десятки процентов таблицы и больше), индекс может не помочь — Seq Scan может быть логичнее.
Если запрос возвращает небольшую долю строк (особенно при LIMIT) — индекс почти всегда кандидат. - 4) Проверьте, есть ли сортировка + LIMIT
Частый кейс “лента/лог/тикеты”: ORDER BY + LIMIT. Индекс под сортировку часто даёт максимальный эффект.
-- пример: лента событий
SELECT * FROM events
WHERE tenant_id = 42
ORDER BY created_at DESC
LIMIT 100;
- 5) Убедитесь, что условие совпадает с тем, что вы индексируете
Если в WHERE есть функция/каст/выражение — индекс по “голой” колонке может не сработать. Тогда нужен индекс на выражение.
-- запрос
SELECT * FROM customers WHERE lower(email) = 'john@doe.com';-- индекс под запрос
CREATE INDEX customers_lower_email_idx ON customers (lower(email));
- 6) Выберите тип индекса по задаче
- B-tree — равенство, диапазоны, сортировка, JOIN (дефолт для большинства OLTP)
- GIN — jsonb/массивы/полнотекст/триграммы (поиск “внутри поля”)
- BRIN — огромные таблицы, данные “по порядку” (например, по времени вставки)
- GiST/SP-GiST — специфические типы и операторы (гео/диапазоны/и т.п.)
- 7) Подумайте про “точечные” индексы вместо “всего подряд”
Часто лучше сделать частичный или покрывающий индекс, чем раздувать общий.
-- частичный индекс (только активные строки)
CREATE INDEX sessions_active_idx ON sessions (user_id)
WHERE revoked_at IS NULL;-- покрывающий индекс (чтобы чаще получать Index Only Scan)
CREATE INDEX bar_ab_cover_idx ON bar (a, b) INCLUDE (c);
- 8) Создайте индекс безопасно для прод-нагрузки
Если база под активной записью, обычно используют CONCURRENTLY (создание дольше, но меньше блокировок на запись).
CREATE INDEX CONCURRENTLY events_tenant_time_idx
ON events (tenant_id, created_at);
- 9) Сравните “после” тем же способом
Снова снимите EXPLAIN (ANALYZE, BUFFERS) и сравните:- время выполнения
- фактические строки (actual rows)
- BUFFERS: shared hit/read (сколько читали из кеша и с диска)
- тип скана: Index / Bitmap / Index Only
- 10) Проверьте, что индекс реально используется со временем
Убедитесь, что индекс не “мертвый груз”. Статистику использования смотрят через pg_stat_*-- смотрим, какие индексы реально сканируются
SELECT relname AS table,
indexrelname AS index,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC; - 11) Следите за побочными эффектами
После добавления индекса проверьте:- не выросло ли заметно время INSERT/UPDATE
- не раздулся ли диск
- не появились ли “лишние” индексы, которые дублируют друг друга
- 12) Удаляйте/пересматривайте лишние индексы
Если индекс не используется и не нужен для ограничений/уникальности — это постоянная плата за запись и место.
Как выбрать порядок колонок в составном индексе
Порядок колонок в составном индексе важнее, чем кажется: PostgreSQL эффективнее всего использует индекс, когда условия в запросе “начинаются” с первых колонок индекса (принцип левого префикса). Поэтому первую позицию обычно отдавайте тому, что почти всегда присутствует в WHERE и лучше всего сужает выборку. Дальше ставят колонку, по которой идёт диапазон (например, дата/время), а затем — поля, которые помогают ORDER BY или делают выборку ещё точнее. Типовой рабочий пример для мультиарендных систем: (tenant_id, created_at) — сначала фиксируем арендатора, затем быстро выбираем период или последние события. При этом низкокардинальные поля вроде status часто не стоит ставить первыми: если у статуса всего несколько значений, такой “первый ключ” плохо фильтрует строки и индекс может использоваться хуже. Если же частый запрос выглядит как “WHERE status = ‘error’ AND created_at > …”, то разумнее сделать отдельный частичный индекс под редкий статус, чем тащить status в начало общего составного индекса.
Диагностика по EXPLAIN: как читать план на уровне “что искать глазами”
Первое правило: смотрите не на «красивый план», а на факты. Используйте EXPLAIN (ANALYZE, BUFFERS) и сравнивайте оценку планировщика с реальностью: сколько строк он ожидал (rows) и сколько вернуло фактически (actual rows). Если расхождение в разы, оптимизатор может выбирать не тот путь — обычно это сигнал, что статистика устарела или распределение данных нестандартное.
Дальше быстро пробегитесь глазами по типу доступа к данным. Seq Scan означает, что таблица читается целиком (иногда это нормально на больших выборках или при параллельном скане). Index Scan — точечный проход по индексу с обращением к таблице за строками. Bitmap Index Scan + Bitmap Heap Scan — компромисс: индекс собирает «карту» подходящих строк, а затем таблица читается пачками страниц, что выгоднее при выборке множества строк и снижает random I/O. Index Only Scan — лучший сценарий для чтения: данные берутся из индекса без походов в heap (если видимость строк позволяет).
Следующий ориентир — BUFFERS. shared hit показывает, сколько блоков нашлось в памяти, shared read — сколько пришлось читать с диска. Если shared read большой, проблема часто в I/O, и индекс может давать слабый эффект просто потому, что данные не помещаются в кеш или чтение слишком случайное. Параллельно смотрите на «время» в узлах плана: иногда индекс используется, но узкое место не в поиске строк, а в сортировке (Sort), агрегации (Aggregate) или джойне (Nested Loop / Hash Join). Тогда индекс «не лечит» причину — нужно менять запрос, порядок условий, добавлять индекс под ORDER BY/LIMIT, либо пересматривать стратегию join’ов.
Мини-шаблон для проверки перед и после индекса выглядит так:
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...После добавления индекса ваша цель — увидеть более подходящий тип скана (Index/Bitmap/Index Only), уменьшение фактического времени, и (в идеале) падение shared read при сопоставимом количестве returned rows.
FAQ
- 1) Почему PostgreSQL игнорирует индекс и делает Seq Scan?
Обычно из-за низкой селективности (возвращаете слишком много строк), из-за более выгодной стоимости последовательного чтения (иногда параллельного), из-за несовпадения выражения в WHERE с тем, что индексировано, или из-за устаревшей/неудачной статистики.
- 2) Как понять, используется ли индекс на самом деле?
Запускайте EXPLAIN (ANALYZE, BUFFERS): если в плане появляются Index Scan, Bitmap Index Scan или Index Only Scan, значит оптимизатор действительно задействовал индекс. Параметр BUFFERS дополнительно покажет, сколько блоков было прочитано из кеша и сколько пришлось взять с диска.
- 3) Что такое Index Only Scan и почему он иногда всё равно читает таблицу?Index-only scan возможен, когда PostgreSQL уверен в видимости строк (visibility map). Если страница не отмечена как all-visible, системе всё равно нужно проверить видимость в heap. Регулярный VACUUM повышает шанс получить index-only scan.
- 4) Когда использовать INCLUDE, а когда делать широкий составной индекс?
INCLUDE полезен, когда поле нужно только для выдачи результата (SELECT), но не должно участвовать в ключе индекса. Это помогает покрывать запросы, не раздувая ключ и не ухудшая поиск/сортировку по главным колонкам.
- 5) Что выбрать для JSON: B-tree или GIN?
Для поиска внутри jsonb обычно нужен GIN. B-tree не предназначен для ускорения операторов “содержит/включает” по структурам JSON.
- 6) Почему запрос с LOWER(col) не использует индекс по col?
Потому что индекс по col не эквивалентен выражению lower(col). Нужен expression index на lower(col), либо переписывание запросов/хранение нормализованного значения в отдельной колонке.
- 7) Когда BRIN лучше, чем партиционирование?
BRIN стоит попробовать на очень больших таблицах с хорошей корреляцией значений с физическим порядком строк (например, логи по времени). Он проще и дешевле в сопровождении, чем партиционирование, хотя решает более узкую задачу.
- 8) Hash-индекс действительно быстрее B-tree для равенства?
Иногда — да, но он поддерживает только “=” по одному столбцу и имеет ограничения. На практике чаще начинают с B-tree и переходят к hash только при подтверждённой выгоде на конкретной нагрузке.
Источник-референс: https://dlt.github.io/blog/posts/introduction-to-postgresql-indexes/
Тестируйте индексы PostgreSQL на практике
Если вы хотите быстро проверить, как индексы влияют на реальные запросы в PostgreSQL, удобнее всего делать это на отдельном стенде: поднять базу, прогнать EXPLAIN (ANALYZE, BUFFERS), сравнить планы до/после CREATE INDEX, собрать метрики и не трогать прод. В этом смысле serverspace.uz подходит как практичная площадка для экспериментов: за минуту разворачиваете облачный сервер или готовый PostgreSQL-инстанс, подключаете мониторинг, масштабируете ресурсы под нагрузочное тестирование и платите только за фактическое использование. Это особенно полезно, когда нужно подобрать правильный тип индекса (B-tree vs GIN/BRIN), обкатать частичные/покрывающие индексы (INCLUDE) и убедиться, что оптимизация реально ускоряет ваши запросы, а не просто “красиво выглядит” в теории.
