Какие индексы разумно ставить на числовые и строковые поля в базе данных

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

Понимаете ли вы, какой оператор в запросе обслуживает какой индекс, и что ORM делает с индексами сама. Для Python-разработчика это обычно PostgreSQL и Django или SQLAlchemy. Следом часто спрашивают, почему icontains тормозит, хотя индекс есть.

Как отвечать

Числовые поля почти всегда закрываются B-tree: он работает на =, диапазонах и ORDER BY. Кандидаты: внешние ключи, created_at, суммы в фильтрах. Сам PostgreSQL внешние ключи не индексирует, Django для ForeignKey создаёт индекс по умолчанию, в SQLAlchemy нужно явно указать index=True.

Со строками вариантов больше:

• равенство и уникальность: B-tree, для email или логина уникальный. unique=True уже создаёт индекс, db_index=True рядом не нужен;
• без учёта регистра: индекс по выражению, в Django models.Index(Lower("email"), name="email_lower"), и в запросе тот же Lower;
• префикс LIKE 'abc%': B-tree помогает только с классом varchar_pattern_ops или коллацией C. Django на PostgreSQL для CharField с db_index=True сам создаёт второй индекс с суффиксом _like;
• подстрока '%abc%': только триграммы, расширение pg_trgm и GIN с классом gin_trgm_ops, в Django через GinIndex и OpClass;
• поиск по словам: SearchVector и GIN по tsvector;
• флаги и статусы: частичный индекс models.Index(fields=["status"], condition=Q(status="new"), name="status_new").

Хэш-индекс в PostgreSQL надёжен с 10-й версии, но выигрыш над B-tree обычно мал, а диапазонов и сортировки он не умеет. На большой таблице индекс добавляют через AddIndexConcurrently в миграции с atomic = False.

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

Ставят db_index=True на BooleanField. При двух значениях планировщик всё равно читает таблицу, а запись дорожает.

Строят триграммный индекс по самой колонке и ждут, что его подхватит icontains. Django оборачивает колонку в UPPER(...), поэтому индекс нужен по тому же выражению: OpClass(Upper("name"), name="gin_trgm_ops").

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