Индексы | Backend Typescript

Зачем нужны индексы

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

Ключевая идея: индексы улучшают скорость чтения, но увеличивают стоимость вставок, обновлений и удалений. Цель — найти баланс между скоростью запросов и издержками на обслуживание.

Типы индексов и их особенности

B-Tree — индекс по умолчанию; подходит для операторов =, <, <=, >, >=, BETWEEN, ORDER BY. Эффективен для селективных условий, хорошо поддерживает сортировку.
Hash — для равенства (=). Узкая специализация; сегодня надёжен, но редко выигрывает у B-Tree.
GIN — инвертированный индекс для множественных значений внутри поля (массива, jsonb, tsvector). Идеален для jsonb и полнотекстового поиска.
GiST — обобщённый сбалансированный индекс для «похожести» и геоданных (поиск ближайших, пересечения областей). Используется PostGIS.
BRIN — компактный индекс по диапазонам страниц. Отлично работает на очень больших, «почти отсортированных» по времени таблицах (журналы событий).
SP-GiST — для разреженных данных и специальных разбиений пространства (квадродеревья и т. п.).

Как планировщик выбирает индекс

Планировщик оценивает кардинальность и селективность условий, используя статистику из ANALYZE. Если ожидания выигрыша превышают стоимость доступа к индексу и последующего чтения строк, будет выбран Index Scan или Bitmap Index Scan вместо Seq Scan.

 EXPLAIN ANALYZE
        SELECT *
        FROM orders
        WHERE customer_id = 42;

Index Scan using orders_customer_id_idx on orders (cost=... rows=... width=...)

Базовые рецепты: создать, проверить, удалить

-- Создание простого индекса
    CREATE INDEX orders_customer_id_idx ON public.orders (customer_id);

-- Проверка использования (через объяснение плана)
    EXPLAIN ANALYZE
    SELECT *
    FROM public.orders
    WHERE customer_id = 42;

-- Удаление индекса (если не нужен)
    DROP INDEX IF EXISTS public.orders_customer_id_idx;

Сложные и полезные приёмы

Составные индексы и порядок столбцов

Порядок столбцов важен: условие по левым префиксам использует индекс лучше. Например, индекс (customer_id, created_at) отлично подходит для WHERE customer_id = ? AND created_at >= ? и для сортировки по created_at в рамках одного customer_id.

 CREATE INDEX idx_orders_customer_created ON public.orders (customer_id, created_at DESC); 

Частичные индексы (partial)

Позволяют индексировать только нужное подмножество строк, уменьшая размер и ускоряя операции записи. Хорошо подходят для «активных» данных.

CREATE INDEX idx_orders_active
    ON public.orders (customer_id)
    WHERE status = 'active';

Индексы по выражению

Индексирует результат функции/выражения. Пригодно для кейс-инсенситив поиска и предобработки значений.

CREATE INDEX idx_users_lower_email ON public.users (lower(email));
SELECT *
FROM public.users
WHERE lower(email) = 'foo@bar.com
';

Покрывающие индексы (INCLUDE)

Позволяют добавить «включённые» столбцы, которые не участвуют в упорядочивании, но доступны для Index Only Scan.

CREATE INDEX idx_orders_customer_created_inc
    ON public.orders (customer_id, created_at) INCLUDE (amount, status);

GIN для jsonb и массивов

Для jsonb и массивов индекс GIN значительно ускоряет проверки «содержит», поиск по ключам и полнотекст.

CREATE INDEX idx_docs_jsonb_gin ON public.docs USING GIN (data jsonb_path_ops);

SELECT *
FROM public.docs
WHERE data @> '{"user":{"id":42}}';

Полнотекстовый поиск (tsvector + GIN)

ALTER TABLE public.articles
    ADD COLUMN fts tsvector;

UPDATE public.articles
    SET fts = to_tsvector('russian', title || ' ' || body);

CREATE INDEX idx_articles_fts ON public.articles USING GIN (fts);

SELECT id
FROM public.articles
WHERE fts @@ plainto_tsquery('russian', 'индексы постгрес');

BRIN для огромных таблиц логов

Если таблица растёт по времени, а запросы фильтруют по диапазонам дат, BRIN даст значительный выигрыш при минимальном размере индекса.

CREATE INDEX idx_events_time_brin ON public.events
    USING BRIN (event_time) WITH (pages_per_range = 64);

Сканирования и как их читать в EXPLAIN

Seq Scan — последовательное сканирование всей таблицы. Нормально для маленьких таблиц или низкой селективности.
Index Scan — прямой доступ по индексу к строкам. Хорошо при высокой селективности.
Bitmap Index Scan + Bitmap Heap Scan — эффективно при средней селективности и объединении нескольких индексов.
Index Only Scan — чтение только индекса без обращения к таблице при наличии актуальной карты видимости и покрывающих столбцов.

EXPLAIN (ANALYZE, BUFFERS)
    SELECT customer_id, amount
    FROM public.orders
    WHERE customer_id = 42
    ORDER BY created_at DESC
    LIMIT 10;

Index Only Scan using idx_orders_customer_created_inc on orders ...

Стоимость индексов при записи и как её снизить

Каждый INSERT/DELETE/UPDATE обновляет все релевантные индексы. Это увеличивает задержку и конкуренцию за ресурсы.

Удаляйте неиспользуемые индексы (pg_stat_user_indexes).
Используйте частичные индексы для «активной» части данных.
Снижайте bloat регулярным обслуживанием.
Тонко настраивайте fillfactor и целевые параметры autovacuum.

Обслуживание: VACUUM, ANALYZE, REINDEX

VACUUM — помечает удалённые версии строк как свободные, поддерживает карту видимости для Index Only Scan.
ANALYZE — обновляет статистику распределений для планировщика.
REINDEX — перестроение индекса при сильном bloat или ошибках структуры.

        VACUUM (VERBOSE) public.orders;

        ANALYZE public.orders;

        REINDEX INDEX CONCURRENTLY public.idx_orders_customer_created_inc;

Конкурентное создание и блокировки

CREATE INDEX без опции CONCURRENTLY ставит блокировки, мешающие записи. В нагруженных системах используйте конкурентное создание.

 CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_payments_user_created ON public.payments (user_id, created_at); 

Практические шаблоны (рецепты)

Последние N записей пользователя

        CREATE INDEX idx_orders_user_created_desc ON public.orders (user_id, created_at DESC);

        SELECT order_id, created_at
        FROM public.orders
        WHERE user_id = $1
        ORDER BY created_at DESC
        LIMIT 20;

Фильтр по статусу и дате

                CREATE INDEX idx_orders_active_created
                    ON public.orders (created_at)
                    WHERE status = 'active';

                SELECT *
                FROM public.orders
                WHERE status = 'active'
                  AND created_at >= now() - interval '7 days';
            

Поиск по email без учёта регистра

CREATE INDEX idx_users_lower_email ON public.users (lower(email));

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

jsonb: есть ли ключ со значением

CREATE INDEX idx_profiles_data_gin ON public.profiles USING GIN (data);

SELECT id
FROM public.profiles
WHERE data @> '{"role":"admin"}';

Полнотекст: найти статьи по фразе

                CREATE INDEX idx_articles_fts ON public.articles USING GIN (to_tsvector('russian', title || ' ' || body));

                SELECT id
                FROM public.articles
                WHERE to_tsvector('russian', title || ' ' || body)
                      @@ plainto_tsquery('russian', 'индексы постгрес');
            

BRIN для временных данных

CREATE INDEX idx_logs_ts_brin ON public.logs USING BRIN (ts);
SELECT *
FROM public.logs
WHERE ts BETWEEN $1 AND $2;

Диагностика и мониторинг

EXPLAIN (ANALYZE, BUFFERS) — измеряйте фактическое время и чтение страниц.
pg_stat_user_indexes — счётчики обращений к индексам. Ненужные индексы видны по нулевым/низким hit.
pg_stat_all_tables — мониторинг вакуума/автовакуума и др.
pg_indexes — список индексов и их определения.

                 SELECT relname AS table, indexrelname AS index, idx_scan
                 FROM pg_stat_user_indexes
                 ORDER BY idx_scan ASC NULLS FIRST
                 LIMIT 20;
            

Частые ошибки и анти-паттерны

Создание индекса «на всякий случай» на каждый столбец.
Дублирующие индексы с одинаковыми наборами столбцов.
Ожидание, что индекс ускорит запрос с низкой селективностью (например, status IN (...) на таблице, где почти все строки имеют эти статусы).
Невнимание к порядку столбцов в составном индексе.
Несовпадение выражения в запросе и в индексе по выражению.

Чек-лист перед созданием индекса

Запрос стабильный и часто исполняется?
Есть ли достаточная селективность условия?
Помогает ли индекс убрать сортировку/LIMIT?
Не существует ли уже подходящего индекса?
Готовы ли вы к издержкам на запись и обслуживанию?
Нужен ли частичный/выражение/INCLUDE для покрытия?
Создавать ли CONCURRENTLY в продакшене?