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

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

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

Как отвечать

Главное сразу: индексы выбирают не по таблице, а по запросам. Смотрите, что реально ходит в базу (pg_stat_statements, медленный лог MySQL), и разбирайте план через EXPLAIN (ANALYZE, BUFFERS).

• где стоит колонка в запросе: WHERE, условие JOIN, ORDER BY, GROUP BY. Колонка только из SELECT индекса не требует;
• селективность: чем больше уникальных значений, тем полезнее индекс. Индекс по status с тремя значениями обычно бесполезен: базе дешевле прочитать таблицу целиком;
• порядок в составном индексе: работает правило левого префикса. Сначала колонки со сравнением на равенство, потом одна с диапазоном, потом сортировка. Индекс (user_id, created_at) закрывает и фильтр, и сортировку, а (created_at, user_id) для такого запроса бесполезен;
• частичный индекс: WHERE is_active в PostgreSQL, если запросы ходят только за живыми строками. Индекс выходит в разы меньше;
• покрывающий индекс: INCLUDE даёт index-only scan, и запрос вообще не читает саму таблицу;
• функциональный индекс: если в запросе lower(email), обычный индекс по email не подойдёт, нужен индекс по выражению;
• цена записи: каждый индекс это лишняя работа на INSERT, UPDATE, DELETE и лишнее место. В PostgreSQL индекс по часто обновляемой колонке ломает HOT-обновления и разгоняет раздувание таблицы.

Не забудьте про уборку: неиспользуемые индексы видно по idx_scan в pg_stat_user_indexes, их удаление ускоряет запись.

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

Вешают индекс на каждую колонку из WHERE. Планировщик возьмёт один или два, а запись просядет из-за всех.

Заворачивают колонку в функцию или сравнивают с другим типом, после чего индекс перестаёт применяться, а причину ищут где угодно, кроме запроса.

Пытаются ускорить LIKE '%текст%' обычным B-tree. Для поиска по подстроке нужен триграммный GIN-индекс или полнотекстовый поиск, а не ещё один B-tree по той же колонке.