Как создать индекс с фильтром для индексации пользователей старше 18 лет

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

Знаете ли вы частичные индексы и умеете ли довести их до работающего состояния в Java-стеке, где запросы генерирует Hibernate. Следом спрашивают, почему индекс создан, а в плане запроса его нет.

Как отвечать

В PostgreSQL это CREATE INDEX idx_users_adult ON users (age) WHERE age >= 18. В индекс попадают только строки под условием, поэтому он компактнее и дешевле обслуживается при записи. В SQL Server то же самое называется filtered index, в MySQL такой возможности нет.

Затем пройдите по практике:

• где описывать: JPA-аннотация @Index знает только имя, колонки и уникальность, условия WHERE в ней нет. Индекс пишется в миграции Flyway или Liquibase, на проде через CREATE INDEX CONCURRENTLY вне транзакции;
• модель данных: колонка age устаревает каждый день. Правильно хранить birth_date, но условие индекса на now() сделать нельзя, там допустимы только IMMUTABLE-выражения. Порог считается в Java через LocalDate.now().minusYears(18) и уходит в запрос по обычному индексу;
• использование индекса: планировщик возьмёт частичный индекс, только если докажет, что условие запроса попадает в условие индекса. Метод Spring Data findByAgeGreaterThanEqual(int age) отправит age >= ?. PgJDBC после пяти выполнений (prepareThreshold) переходит на серверный prepared statement, и PostgreSQL может выбрать обобщённый план, где параметр ничего не доказывает;
• решение: зафиксировать константу в @Query("select u from User u where u.age >= 18"), тогда условие совпадёт с индексом при любом плане.

Смысл частичный индекс имеет, когда отбирает малую часть таблицы. Если взрослых большинство, выгоднее обычный индекс или частичный индекс по другому признаку.

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

Путают строгое и нестрогое неравенство. «Старше 18» в требованиях почти всегда означает совершеннолетие, то есть >= 18, а > 18 отрезает восемнадцатилетних.

Полагаются на spring.jpa.hibernate.ddl-auto=update. Частичный индекс он не создаст, а на проде этот режим вообще опасен.

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