Индексы PostgreSQL

JuniorMiddleSeniorФундамент ~55 мин

Границы темы

Scope: индексы PostgreSQL 18, прежде всего B-tree: физическая модель, planner, MVCC, диагностика и эксплуатационная цена.

Не рассматриваем подробно: алгоритм конкурентного split страницы, WAL records каждого access method и расширения вроде pgvector.

Результаты обучения

После статьи читатель сможет:

1. объяснить, почему индекс ускоряет не «SELECT вообще», а конкретный access pattern;
2. проследить путь predicate → planner → index page → TID → heap tuple;
3. выбрать порядок колонок и тип специализированного индекса;
4. доказать пользу через план и реальные buffer/time metrics;
5. оценить write amplification, bloat, locking и migration risk.

Основная модель

Индекс — отдельная структура доступа, оптимизированная под конкретный

Повторяющийся способ чтения и изменения данных: фильтры, сортировка, объём и частота.. PostgreSQL может пройти по ней

вместо чтения всех heap pages, если Часть СУБД, оценивающая возможные планы выполнения и выбирающая план с наименьшей ожидаемой стоимостью. оценивает такой путь дешевле. Выигрыш зависит от predicate, operator class,

Доля строк, которую условие запроса оставляет после фильтрации., correlation, visibility и требуемых

колонок.

Диаграмма

Главная модель

Индекс не заменяет таблицу. Обычный Index Scan сначала находит TID в индексе, затем читает heap и проверяет видимость версии строки по MVCC.

Основные термины

Термин	Смысл
Access method	B-tree, Hash, GiST, SP-GiST, GIN или BRIN
Operator class	Связь типа/операторов с правилами конкретного access method
Selectivity	Ожидаемая доля строк, прошедших условие
TID	Физическая ссылка на tuple: page/block и offset
Covering index	Индекс с данными, достаточными для запроса
Index-only scan	План, способный вернуть данные из индекса при выполнении visibility условий
Partial index	Индекс только для строк, удовлетворяющих predicate
Expression index	Индекс по вычисленному выражению

End-to-end сценарий

Запрос:

SELECT id, created_at
FROM orders
WHERE tenant_id = 42
  AND status = 'pending'
ORDER BY created_at
LIMIT 50;

Возможный индекс:

CREATE INDEX CONCURRENTLY orders_pending_tenant_created_idx
ON orders (tenant_id, created_at)
INCLUDE (id)
WHERE status = 'pending';

Диаграмма

Индекс одновременно:

• исключает строки других tenants;
• хранит только pending;
• отдаёт результаты в порядке created_at;
• содержит id как payload для потенциального index-only scan.

Но он увеличивает стоимость изменения tenant_id, created_at, id и переходов строк в/из состояния pending.

Что происходит под капотом

Упрощённая структура B-tree

                         root page
                    [k20 | k40 | k70]
                    /      |      \
             internal   internal   internal
                |           |          |
           leaf pages linked in key order
        [k01 k02 ...] <-> [k20 k21 ...] <-> [...]
              entry = key + heap TID (+ INCLUDE payload)

Высокий branching factor позволяет большинству B-tree иметь небольшую глубину. Для range scan система находит начальную leaf page, затем проходит соседние entries.

Почему planner может отказаться от индекса

Planner сравнивает оценочную стоимость доступных paths. Ошибка в

Количество различных значений или оценка количества строк на этапе плана запроса. способна изменить выбор плана.

Sequential scan часто разумен, если запрос возвращает большую часть таблицы: последовательное чтение heap дешевле многих случайных переходов index → heap.

На выбор влияют:

• statistics и оценка количества строк;
• размер таблицы и индекса;
• correlation физического порядка heap с ключом;
• random_page_cost, seq_page_cost, CPU costs;
• необходимость sort;
• наличие нужных columns;
• parametrized values и generic/custom plan.

SET enable_seqscan = off полезен только как диагностический эксперимент. Это не исправление плохого индекса или statistics.

MVCC и index-only scan

Индексная запись сама по себе обычно не говорит, видима ли heap tuple текущему snapshot. Структура PostgreSQL, отмечающая heap-страницы, где tuples видимы всем transactions. отмечает heap pages, где все tuples видимы всем текущим и будущим transactions. Поэтому index-only scan особенно эффективен на малоизменяемых, хорошо vacuumed данных.

HOT update

Если update не меняет индексируемые columns и на heap page есть место, PostgreSQL может создать HOT chain без новых entries во всех indexes. Лишний индексируемый payload способен лишить update этой оптимизации.

Выбор access method

Access method	Сильная сторона	Типичные операции
B-tree	Equality, ranges, ordering	=, <, BETWEEN, ORDER BY
Hash	Equality	=
GIN	Множество keys внутри value	arrays, full text, некоторые JSONB operations
GiST	Расширяемые search trees	geometry, ranges, nearest-neighbor
SP-GiST	Partitioned search spaces	tries, quadtrees, некоторые spatial types
BRIN	Компактные summaries диапазонов blocks	Очень большие физически коррелированные таблицы

Тип определяется не названием column, а операторами запросов, distribution и operator class.

Multicolumn B-tree

Для (a, b, c) equality constraints на leading columns и первое inequality обычно сильнее всего сужают читаемый диапазон. PostgreSQL 18 также документирует skip scan, который может помочь некоторым условиям без equality по первой column, но его выбор зависит от количества distinct values и cost estimate.

Predicate	Индекс (tenant_id, created_at)
tenant_id = ?	Хорошо использует leading column
tenant_id = ? AND created_at > ?	Хорошо ограничивает range
created_at > ?	Может быть невыгоден или использовать skip scan
ORDER BY tenant_id, created_at	Может отдать требуемый порядок
ORDER BY created_at для всех tenants	Обычно не соответствует общему порядку

Правило «самая селективная колонка всегда первая» недостаточно. Порядок выбирают по реальным predicates, equality/range, ordering и группировке access patterns.

Минимальный воспроизводимый пример

Подготовка

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id bigint NOT NULL,
  status text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO orders (tenant_id, status, created_at)
SELECT
  1 + (n % 100),
  CASE WHEN n % 20 = 0 THEN 'pending' ELSE 'done' END,
  now() - make_interval(secs => n)
FROM generate_series(1, 200000) AS s(n);

ANALYZE orders;

Baseline и индекс

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM orders
WHERE tenant_id = 42
  AND status = 'pending'
ORDER BY created_at
LIMIT 50;

CREATE INDEX orders_pending_tenant_created_idx
ON orders (tenant_id, created_at)
INCLUDE (id)
WHERE status = 'pending';

ANALYZE orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM orders
WHERE tenant_id = 42
  AND status = 'pending'
ORDER BY created_at
LIMIT 50;

Что сравнивать

Не копируйте ожидаемые числа: они зависят от hardware, cache и данных. Сравните:

• Planning Time и Execution Time;
• estimated rows с actual rows;
• Buffers: shared hit/read;
• наличие отдельного Sort;
• Heap Fetches у index-only scan;
• количество loops.

Запустите запрос несколько раз и помните о warmed cache. Один faster run не является доказательством стабильного выигрыша.

Специализированные индексы

Expression

CREATE INDEX users_email_lower_idx ON users (lower(email));

Predicate должен соответствовать индексированному expression:

SELECT id FROM users WHERE lower(email) = lower($1);

Partial

CREATE INDEX jobs_ready_idx
ON jobs (scheduled_at)
WHERE state = 'ready';

Planner должен уметь доказать, что query predicate влечёт predicate индекса. Parameterized или иначе сформулированное условие не всегда позволяет это сделать.

Unique

CREATE UNIQUE INDEX users_email_unique_idx ON users (lower(email));

Unique index обеспечивает invariant конкурентно; предварительная проверка SELECT-ом не заменяет constraint.

Ограничения и trade-offs

Выигрыш	Цена
Меньше читаемых pages	Дополнительное место
Быстрый lookup/range	Работа на INSERT/UPDATE/DELETE
Порядок без Sort	Более широкий и дорогой index
Index-only scan	INCLUDE payload, bloat и возможная потеря HOT
Partial index	Query predicate должен соответствовать index predicate
CREATE INDEX CONCURRENTLY	Дольше, больше работы и особые failure states

Failure modes и типичные ошибки

Ошибка	Симптом	Проверка
Индекс «на всякий случай»	Write latency и storage растут	Usage statistics + workload
Неверный порядок columns	Читается широкий диапазон	Predicates и plan conditions
Stale statistics	Estimate сильно отличается от actual	ANALYZE, statistics target
Function/cast mismatch	Predicate не использует expression index	EXPLAIN, types, expression
Слишком широкий INCLUDE	Bloat и дорогие writes	Размер index и update workload
Ожидание index-only scan	Большое Heap Fetches	Visibility map, vacuum, churn
Benchmark на пустой таблице	Нереалистичный plan	Реальные distribution и volume
Создание blocking index в production	Долгая блокировка writes	Migration plan и CONCURRENTLY

Безопасность и производительность

Индекс не исправляет утечку данных: tenant predicate и authorization остаются обязательными. Expression/partial predicate должны использовать immutable semantics, а migration — учитывать locks, disk headroom, WAL/replication lag и rollback.

Проверять нужно не только latency одного SELECT, но и:

• p95/p99 write latency;
• WAL volume;
• replica lag;
• autovacuum duration;
• index size и cache residency;
• CPU и I/O всей системы.

Практическое задание

Создайте таблицу с неравномерным распределением tenants и statuses. Спроектируйте:

1. индекс для последних 50 pending jobs одного tenant;
2. индекс для поиска email без учёта регистра;
3. BRIN-кандидат для append-only событий по времени.

Критерии готовности:

• для каждого индекса записан access pattern;
• есть baseline и post-index EXPLAIN (ANALYZE, BUFFERS);
• estimates сопоставлены с actual rows;
• измерена цена INSERT/UPDATE;
• описаны безопасный rollout, мониторинг и rollback.

Подсказка

Начинайте не с CREATE INDEX, а с таблицы query → predicate → order → projection → frequency → latency budget. После этого выбирайте минимальную структуру.

Проверка знаний

ИНТЕРАКТИВНАЯ ПРОВЕРКА

PostgreSQL indexes: выбор и доказательство

0 / 4

01Почему planner может выбрать Sequential Scan при существующем индексе?

Любой Sequential Scan означает ошибку PostgreSQLЧтение большой доли heap последовательно оценено дешевлеPostgreSQL использует индекс только после restart

02Что нужно сравнить до и после добавления индекса?

Buffers hit/readEstimated и actual rowsСтоимость INSERT/UPDATE и WALТолько один случайный Execution Time

03Index-only scan гарантирует полное отсутствие обращений к heap.

ВерноНеверно

04Восстановите инженерный порядок работы с индексом.

1. Создать минимальный индекс
2. Зафиксировать access pattern
3. Сравнить plan, buffers и write cost
4. Получить baseline

Проверено: 0 из 4

Самопроверка

1. Почему индекс с низкой selectivity иногда всё равно полезен?
2. Почему planner может выбрать sequential scan при существующем индексе?
3. Чем INCLUDE отличается от key column?
4. Почему index-only scan иногда делает heap fetches?
5. Как лишний индекс влияет на HOT update и WAL?

Ответы и критерии

1. Он может обеспечивать порядок, покрывать запрос, быть partial или сочетаться с другими условиями; одна selectivity не определяет ценность.
2. Чтение значительной части heap последовательно может быть дешевле случайных переходов через индекс.
3. Key участвует в навигации и ordering, INCLUDE хранится как payload для возврата данных.
4. MVCC visibility обычно проверяется в heap, если page не отмечена all-visible.
5. Добавляет index maintenance и WAL; изменение индексируемого значения может лишить update возможности остаться HOT.

Краткое резюме

Индекс проектируют от workload, а доказывают планом и измерениями. Фундаментальная модель — predicate → planner → index entries → TIDs → MVCC-visible heap tuples. Каждое ускорение чтения имеет цену записи, памяти, WAL и эксплуатации.

Следующие шаги

• storage pages и buffer manager;
• query planner, statistics и cardinality;
• MVCC, vacuum и bloat;
• locks и online schema migrations;
• GIN/GiST/BRIN на профильных workloads.

Источники

• PostgreSQL 18: Indexes — access methods, multicolumn, expression, partial и covering indexes.
• PostgreSQL 18: Multicolumn Indexes — leading columns и skip scan.
• PostgreSQL 18: Index-Only Scans — covering indexes, visibility map и heap fetches.
• PostgreSQL 18: Using EXPLAIN — чтение estimates, actual execution и planner decisions.
• PostgreSQL 18: Routine Vacuuming — MVCC cleanup, visibility map и влияние vacuum.
• PostgreSQL 18: Building Indexes Concurrently — ограничения и operational behavior CREATE INDEX CONCURRENTLY.