Оцените индекс на колонку пол для запроса по полу и возрасту

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

Понимаете ли вы селективность и то, как планировщик выбирает способ доступа. Вопрос с подвохом: правильный ответ здесь звучит как «отдельный индекс на такую колонку почти наверняка не нужен», и проверяют, хватит ли смелости это сказать вместо рассказа про ускорение любых фильтров.

Как отвечать

Начните с оценки, а не с команды создания.

• у колонки пола два-три значения, то есть условие отсекает примерно половину таблицы. Планировщик посчитает, что случайный доступ к такому количеству строк дороже последовательного чтения, и индекс просто не возьмёт, а цену за его поддержку при каждой вставке вы платить будете;
• для условия «пол равен, возраст в диапазоне» полезен составной индекс, где первой идёт колонка с равенством: create index idx_users_gender_age on users (gender, age);. По префиксу отбирается нужное значение пола, дальше идёт диапазонный проход по возрасту;
• обратный порядок (age, gender) заставит проходить диапазон возраста и проверять пол на каждой строке, это заметно хуже;
• если запрос берёт пару колонок, а не всю строку, покрывающий индекс позволит обойтись сканированием только по индексу;
• когда постоянно нужна выборка по одному значению, дешевле частичный индекс: create index idx_users_age_f on users (age) where gender = 'f';. Он меньше и не растёт от строк с другим значением.

Итог всегда проверяется планом: explain (analyze, buffers) до и после, со сравнением оценки планировщика и фактического числа строк.

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

Судят по частоте фильтра, а не по доле отсекаемых строк. «По полу фильтруют в каждом запросе» не аргумент за индекс: важно, сколько строк остаётся после условия и что запрос делает с ними дальше.

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

Третье: не считают цену. Каждый индекс это дополнительная работа при вставке и обновлении, место на диске и время восстановления, а на большой таблице ещё и долгое построение, поэтому в бою его добавляют без блокировки записи, через create index concurrently.