Какие виды индексов существуют

Что проверяют

Знаете ли вы индексы той базы, с которой работает ваш сервис, чаще всего PostgreSQL, и умеете ли проверить, что запрос из Go-кода их действительно использует. Следом обычно дают запрос и спрашивают, какой индекс под него нужен.

Как отвечать

Типы индексов в PostgreSQL и их задачи:

• B-tree: по умолчанию. Равенство, диапазоны, ORDER BY, префиксный LIKE 'abc%' (при локали не C нужен класс операторов text_pattern_ops);
• Hash: только равенство. С PostgreSQL 10 пишется в WAL и безопасен, но B-tree почти всегда не хуже;
• GIN: jsonb, массивы, полнотекстовый поиск, триграммы из pg_trgm для LIKE '%abc%';
• GiST: геоданные, диапазонные типы, ограничения исключения;
• BRIN: огромные таблицы, где значения коррелируют с физическим порядком, например логи по времени.

Модификаторы поверх типа: уникальный, составной, частичный (WHERE deleted_at IS NULL), по выражению (lower(email)) и покрывающий с INCLUDE, который даёт Index Only Scan.

Проверка одна: EXPLAIN (ANALYZE, BUFFERS) на реальном объёме данных. Нюанс для Go: pgx по умолчанию кеширует подготовленные выражения, а PostgreSQL после пяти выполнений может перейти на общий план, не учитывающий конкретное значение параметра. Такой план, например, не возьмёт частичный индекс под status = $1. Лечится настройкой plan_cache_mode или другим режимом выполнения запросов в pgx.

Индекс на живой таблице создают через CREATE INDEX CONCURRENTLY, чтобы не блокировать запись. Внутри транзакции он не выполняется, поэтому в goose такую миграцию помечают аннотацией +goose NO TRANSACTION.

Где спотыкаются

Называют bitmap-индекс типом PostgreSQL. Такого типа там нет, есть Bitmap Index Scan: способ объединить несколько обычных индексов в одном запросе. Хранимые bitmap-индексы есть в Oracle.

Уверены, что PostgreSQL сам создаёт индекс под внешний ключ. Не создаёт, и DELETE из родительской таблицы превращается в полный проход по дочерней.

Пишут WHERE created_at::date = $1 и удивляются, что индекс по created_at не работает. Условие переписывают в диапазон created_at >= $1 AND created_at < $2.