Единственного оптимального индекса без СУБД, статистики и нагрузки нет. (sex, age, salary) помогает фильтровать диапазон, но обычно оставляет сортировку. (sex, age, created_at) даёт нужный порядок, однако salary проверяется отдельно. В PostgreSQL для фиксированного условия можно рассмотреть частичный индекс по created_at. Выбор подтверждают планом и измерениями.
Как выбрать индекс для employee с фильтрами sex, salary, age и сортировкой created_at?
Равенства, диапазон и ORDER BY конкурируют за порядок колонок индекса. Почему salary перед created_at не убирает сортировку автоматически.
Короткий ответ
Что ответить на собеседовании
Подробный разбор
Ответ с пояснениями
Условие: выбрать индекс для запроса:
SELECT * FROM employee
WHERE sex = 'm' AND salary > 300000 AND age = 20
ORDER BY created_at;
Уточните СУБД, объём таблицы, распределение значений и частоту этого запроса. Разберём B-tree в PostgreSQL.
Вариант для фильтрации:
CREATE INDEX employee_filter_idx
ON employee (sex, age, salary);
Равенства ограничивают начальные ключи, диапазон — salary. Добавление created_at после диапазона не создаёт глобальный порядок по дате: строки сначала упорядочены по зарплате. Поэтому утверждение «(sex, age, salary, created_at) всегда убирает Sort» неверно. См. составные индексы.
Вариант для порядка:
CREATE INDEX employee_order_idx
ON employee (sex, age, created_at);
При фиксированных sex и age индекс выдаёт даты в нужном порядке, но строки с неподходящей зарплатой нужно отфильтровать. Это может потребовать чтения большого числа записей. Отсутствие Sort само по себе не доказывает, что план быстрее; документация ORDER BY.
Для именно этого постоянного фильтра:
CREATE INDEX employee_target_idx
ON employee (created_at)
WHERE sex = 'm' AND age = 20 AND salary > 300000;
Частичный индекс содержит только подходящие строки и сохраняет порядок. Но он менее универсален, а планировщик должен доказать соответствие предикату; параметры и generic plan могут помешать. См. partial indexes.
Это альтернативы, а не указание создать все три индекса. Сравните планы и чтения на характерных данных, учитывая стоимость записи. SELECT * не делает индекс покрывающим автоматически; в запросе нет LIMIT, поэтому преимущества ранней остановки здесь не обещаются.