Зачем существуют разные типы индексов?

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

Понимаете ли вы, что универсальной структуры для поиска не существует: одна держит порядок, другая ищет вхождения внутри документа, третья работает с геометрией. Следом дают запрос, например по jsonb или с LIKE '%текст%', и просят выбрать индекс.

Как отвечать

Разным данным и операторам нужны разные структуры. Отвечайте на примере PostgreSQL, самой частой базы Python-бэкенда:

• B-tree: тип по умолчанию. Равенство, диапазоны, сортировка, LIKE 'префикс%' (если локаль базы не C, нужен класс операторов text_pattern_ops);
• Hash: только =. С PostgreSQL 10 пишется в WAL, но выигрыш перед B-tree обычно невелик;
• GIN: обратный индекс, «элемент → список строк». Нужен для jsonb (оператор @>), массивов, полнотекстового tsvector, а с расширением pg_trgm ускоряет LIKE '%текст%'. Читает быстро, но дорог на запись;
• GiST: обобщённое дерево для значений, которые пересекаются и вкладываются друг в друга. Геометрия PostGIS, диапазоны, поиск ближайших, ограничения EXCLUDE;
• BRIN: хранит минимум и максимум на диапазон страниц. Крошечный, работает на огромных таблицах, где значения идут в физическом порядке, как время вставки в логах;
• HNSW и IVFFlat из pgvector: приближённый поиск ближайших векторов для эмбеддингов.

Поверх типа есть модификаторы: составной, частичный (WHERE), по выражению, покрывающий (INCLUDE). В Django это Meta.indexes с GinIndex и BrinIndex из django.contrib.postgres.indexes и condition=Q(...), в SQLAlchemy параметры postgresql_using и postgresql_where. На живой таблице индекс строят через CREATE INDEX CONCURRENTLY, в Django это AddIndexConcurrently в неатомарной миграции.

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

Советуют кластерный индекс «для частых фильтров». В PostgreSQL поддерживаемого кластерного индекса нет, команда CLUSTER переупорядочивает таблицу один раз. Кластерный первичный ключ это особенность InnoDB в MySQL.

Ставят BRIN на колонку, значения которой разбросаны по таблице. Тогда почти каждый диапазон подходит под условие, и индекс ничего не отсекает.

Не проверяют выбор в EXPLAIN (ANALYZE, BUFFERS) и считают, что индекс используется, раз он создан.