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

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

Умеете ли вы идти от конкретного запроса к индексу, а не перечислять типы индексов. Миллионы строк для правильно проиндексированной таблицы немного: B-tree на десятки миллионов записей имеет всего три или четыре уровня. Следом дают запрос и просят прочитать EXPLAIN.

Как отвечать

Сначала попросите сам запрос: что в WHERE, JOIN, ORDER BY и есть ли LIMIT. Потом EXPLAIN (ANALYZE, BUFFERS): ищете Seq Scan, лишнюю сортировку и расхождение ожидаемых и фактических строк.

Каркас решений на примере PostgreSQL:

• составной индекс: равенство впереди, диапазон и сортировка следом. Для WHERE country = $1 AND created_at > $2 ORDER BY created_at DESC LIMIT 50 нужен (country, created_at DESC), и сортировка из плана уходит;
• покрывающий: INCLUDE (name, email) даёт Index Only Scan без похода в таблицу, если visibility map свежая;
• частичный: WHERE deleted_at IS NULL, когда запросы почти всегда про живых пользователей;
• по выражению: уникальный индекс на lower(email) для входа без учёта регистра;
• пагинация: OFFSET 100000 читает и выбрасывает сто тысяч строк при любом индексе, keyset по (created_at, id) от номера страницы не зависит.

Со стороны Go: индекс на живой таблице строят через CREATE INDEX CONCURRENTLY, а он не работает внутри транзакции. В goose такую миграцию помечают -- +goose NO TRANSACTION. Упавшая сборка оставляет невалидный индекс, его удаляют и строят заново.

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

Ставят колонки составного индекса в порядке объявления в таблице, а не по запросу, и индекс используется частично или не используется вовсе.

Забывают про подготовленные выражения. pgx по умолчанию кэширует statement, после нескольких выполнений PostgreSQL может перейти на generic-план, и если условие частичного индекса пришло параметром (status = $1), такой индекс не выберется.

Не проверяют запись. Каждый индекс замедляет INSERT и UPDATE, а лишние находят через pg_stat_user_indexes с idx_scan = 0.