В чем разница между составным индексом и индексом с включенными столбцами

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

Понимаете ли вы, как B-tree индекс хранит ключ и зачем ему «попутчики», которые не участвуют в поиске. Следом обычно спрашивают, что такое покрывающий индекс и почему в Java-проекте он может не сработать из-за ORM.

Как отвечать

Разведите две роли колонок в индексе:

• составной индекс: все колонки ключевые, по ним строится дерево и порядок. Работает правило левого префикса: индекс (last_name, first_name) помогает запросу по last_name и по паре, а запросу по одному first_name обычно нет. Зато он обслуживает и ORDER BY last_name, first_name;
• индекс с INCLUDE: ключ короткий, а дополнительные колонки лежат только в листьях как полезная нагрузка. Искать и сортировать по ним индекс не умеет, зато может отдать их без похода в таблицу. Синтаксис CREATE INDEX ... ON users (email) INCLUDE (name) есть в PostgreSQL начиная с 11 и в SQL Server, в MySQL его нет;
• покрывающий индекс: общее название ситуации, когда в индексе есть всё, что нужно запросу. В PostgreSQL это видно в EXPLAIN как Index Only Scan.

Зачем INCLUDE, если можно добавить колонку в ключ: ключ остаётся компактным, а у уникального индекса уникальность проверяется только по ключевым колонкам. UNIQUE (email) INCLUDE (name) не даст завести два одинаковых email, а одинаковые имена разрешит.

Для Java важная деталь: в JPA через @Index(columnList = "last_name, first_name") составной индекс описать можно, а INCLUDE нет, его пишут в миграции Flyway или Liquibase.

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

Ставят в INCLUDE колонку, по которой фильтруют в WHERE, и ждут быстрого поиска. Включённая колонка не участвует в навигации по дереву.

Путают порядок колонок в составном индексе: первой ставят ту, по которой условия почти никогда нет.

Строят покрывающий индекс и не видят эффекта, потому что Hibernate грузит сущность целиком, со всеми колонками. Выигрыш появляется только на проекциях: DTO через select new, интерфейсные проекции Spring Data. Ещё в PostgreSQL Index Only Scan зависит от карты видимости, и без свежего VACUUM база всё равно ходит в таблицу.