Индексы: ускорение чтения и его цена

Индекс — отдельная структура, которая помогает находить строки без чтения всей таблицы. Это похоже на указатель в книге: по термину можно перейти к нужной странице.

Индекс не бесплатен. Он занимает место и кеш, обновляется при записи, создаёт WAL и требует обслуживания. Поэтому индекс проектируют под конкретное условие, порядок и объём результата, а не просто «на столбец».

#Последовательное чтение таблицы

Seq Scan — последовательное чтение страниц таблицы. Оно выгодно, если таблица мала или запросу нужна большая доля строк:

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT *
FROM orders
WHERE status <> 'cancelled';

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

Не отключайте enable_seqscan как постоянное исправление. Параметр полезен для сравнительного опыта в текущем сеансе, но принудительный план ещё нужно доказать на настоящей нагрузке.

#Index Scan и Bitmap Scan

Index Scan идёт по индексу и для найденных записей читает строки таблицы. Он хорош для небольшого числа совпадений и запроса ORDER BY ... LIMIT, если индекс уже хранит нужный порядок.

Bitmap Index Scan сначала собирает карту адресов подходящих строк, а Bitmap Heap Scan группирует их по страницам таблицы. Это уменьшает случайные обращения, когда совпадений больше.

Если памяти для точной карты не хватает, она становится приблизительной (lossy), и условие приходится перепроверять для строк прочитанной страницы. Ни один вид чтения не лучше вне конкретного запроса и распределения данных.

#Index Only Scan

Если все нужные столбцы находятся в индексе, сервер иногда может получить ответ без чтения таблицы:

CREATE INDEX orders_org_created_idx
ON orders (org_id, created_at DESC, id DESC)
INCLUDE (status, total_amount);

Столбцы после INCLUDE хранятся в индексной записи, но не участвуют в порядке и поисковом ключе.

MVCC-видимость обычно хранится в таблице. Не обращаться к ней можно только для страниц, отмеченных в карте видимости как полностью видимые. Поэтому Index Only Scan всё равно может показать Heap Fetches.

В часто изменяемой таблице таких страниц мало. Добавленные столбцы увеличат индекс и цену записи, но ожидаемого чтения только по индексу может не быть.

#Как доказать пользу

Сохраните запрос, значения параметров, схему, настройки и форму данных.
Возьмите несколько типичных значений, включая редкий случай и пустой ответ.
Сохраните планы и серии измерений с холодным и прогретым кешем.
Найдите первый нижний узел с неверной оценкой строк.
Измените один фактор: запрос, статистику или индекс.
Сравните план, буферы, WAL, временные файлы, размер индекса и запись.

EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, FORMAT JSON)
SELECT id, created_at, status, total_amount
FROM orders
WHERE org_id = $1
  AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Один более быстрый запуск может объясняться кешем, контрольной точкой или соседней нагрузкой. Нужна серия.

#Цена для записи

Подходящие INSERT, UPDATE и DELETE должны изменить индекс и записать WAL. Изменение индексируемого столбца мешает оптимизации HOT. Перед добавлением оцените:

размер и время построения;
прирост WAL и отставание реплики;
задержку вставок и обновлений;
давление на кеш и место на диске;
работу VACUUM после изменений и отменённых транзакций.

Индекс для редкого отчёта может не оправдать замедление постоянной записи.

#Построение в рабочей базе

SET lock_timeout = '2s';
SET statement_timeout = '2h';

CREATE INDEX CONCURRENTLY orders_org_created_v2_idx
ON orders (org_id, created_at DESC, id DESC)
INCLUDE (status, total_amount);

CONCURRENTLY разрешает обычные изменения во время построения, но добавляет этапы, дольше работает и может ждать старые транзакции. Команду нельзя запускать внутри BEGIN ... COMMIT.

Наблюдать за процессом и результатом можно так:

SELECT * FROM pg_stat_progress_create_index;

SELECT i.indisready, i.indisvalid, pg_get_indexdef(i.indexrelid)
FROM pg_index AS i
WHERE i.indexrelid = 'orders_org_created_v2_idx'::regclass;

После ошибки индекс может остаться с indisvalid = false. Само имя в каталоге не доказывает готовность. Найдите причину, затем удалите объект или повторите построение по подготовленному плану.

#Как решить, можно ли удалить индекс

Счётчик idx_scan = 0 недостаточен. Статистика могла недавно сброситься, а индекс может обслуживать редкий отчёт, реплику, уникальность или удаление по внешнему ключу.

SELECT
    s.schemaname,
    s.relname,
    s.indexrelname,
    s.idx_scan,
    pg_relation_size(s.indexrelid) AS index_bytes,
    pg_get_indexdef(s.indexrelid) AS definition
FROM pg_stat_user_indexes AS s
ORDER BY index_bytes DESC;

Сравните порядок ключей, выражения, условие частичного индекса, класс операторов и INCLUDE. Одинаковый первый столбец не делает два индекса дубликатами. Удаление в рабочей системе тоже требует плана блокировок.

#Разрастание индекса

Из-за обновлений и удалений индекс может занять заметно больше места, чем нужно текущим данным. Это называют разрастанием (bloat).

REINDEX CONCURRENTLY строит замену с меньшим влиянием на работу, но не должен быть регулярным ритуалом. Сначала подтвердите проблему, наличие свободного места, допустимые блокировки и причину частых изменений. Иначе новый индекс быстро разрастётся снова.

#Практика

В лаборатории 22 вы создадите индексы под два запроса и соберёте список с размером, использованием и полным определением.

Подробнее: типы индексов, чтение только по индексу, CREATE INDEX.