Какие индексы использовать для ускорения SQL запроса с миллионами пользователей?

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

Вопрос общий, но от Java-разработчика ждут связки с ORM: вы должны индексировать тот SQL, который на самом деле генерирует Hibernate, а не модель у себя в голове. Следом обычно спрашивают, как вы нашли медленный запрос и как выкатили индекс на большую таблицу.

Как отвечать

Путь такой: включить логирование SQL (org.hibernate.SQL, для параметров org.hibernate.orm.jdbc.bind) или взять запрос из pg_stat_statements, затем EXPLAIN (ANALYZE, BUFFERS). Дальше выбор:

• B-tree по умолчанию: равенство, диапазон, сортировка. Селективные колонки вроде email или user_id как раз лучшие кандидаты;
• составной под конкретный фильтр: равенство впереди, диапазон и ORDER BY в конце;
• по выражению: derived-запрос findByEmailIgnoreCase в Spring Data генерирует upper(email) = upper(?), обычный индекс по email его не обслужит, нужен индекс по upper(email);
• частичный: WHERE status = 'ACTIVE' для горячей части таблицы;
• хэш: только равенство и на практике редко выигрывает у B-tree.

Отдельно про пагинацию: Pageable даёт OFFSET и дополнительный count(*), на миллионах строк оба дорогие. Помогает Slice без подсчёта и keyset по (created_at, id).

Индексы описывают в миграциях Flyway или Liquibase, а не в @Table(indexes = ...): аннотация работает только при генерации схемы. CREATE INDEX CONCURRENTLY нельзя выполнять в транзакции, поэтому в Liquibase у changeset ставят runInTransaction="false", во Flyway отключают транзакцию для этого скрипта.

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

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

Ищут в индексах причину, когда проблема в N+1: сотня быстрых запросов по индексу всё равно медленнее одного JOIN FETCH.

Строят обычный CREATE INDEX на живой таблице с миллионами строк, и запись в неё блокируется на всё время сборки.