INCLUDE в PostgreSQL: почему ваш индекс всё ещё не работает (и как это починить)
`INCLUDE` — пожалуй самый недооцененный инструмент в арсенале DBA.
Что это такое?
Обычный B-tree индекс хранит только те колонки, по которым вы ищете и сортируете. Если запросу нужны другие поля, Postgres находит указатель в индексе, а потом идет в саму таблицу (heap) за данными. Это две операции ввода-вывода.
`INCLUDE` добавляет дополнительные колонки прямо в «листья» индекса. База находит строку по ключу и сразу забирает остальные данные оттуда же. Поход в таблицу отменяется.
Миф: INCLUDE ускоряет запись
Часто говорят, что `INCLUDE` нужен, чтобы не обновлять дерево индекса при изменении доп. колонок. Это не совсем так. При `INSERT` или `UPDATE` индекс всё равно переписывается.
Реальная магия `INCLUDE` кроется в другом:
1. Обход ограничений типов данных
Вы не можете положить геометрический тип `box` или некоторые пользовательские типы в ключ B-tree индекса — для них не определены операторы сравнения «больше-меньше».
Но их можно добавить через `INCLUDE`. Индекс будет искать по `id`, а `box` просто лежать рядом в листе.
2. Уникальность без потери покрытия
Вам нужно гарантировать уникальность по `email`, но запросы всегда тянут `user_id`.
Если добавить `user_id` в ключ индекса, база начнет сортировать данные еще и по нему. Это лишняя работа.
Уникальность проверяется только по `email`, но запрос `SELECT user_id FROM users WHERE email = '...'` ответит прямо из индекса.
3. Спасение от «неправильной» сортировки
Если добавить колонку в ключ (например, `CREATE INDEX ON logs (user_id, created_at)`), база будет поддерживать порядок и по `user_id`, и по `created_at`.
Если вам не нужно искать диапазон дат, а нужно просто подтянуть `created_at` для конкретного `user_id` — вы заставляете дерево индекса делать лишнюю работу по поддержке порядка.
`INCLUDE (created_at)` говорит базе: «Храни это здесь, но не трать ресурсы на пересортировку индекса по этой дате».
Когда это выстрелит?
Только когда планировщик сможет использовать *Index Only Scan*. Для этого страница таблицы должна быть «чистой» для вашей транзакции.
Если в таблице много `UPDATE` и `DELETE`, регулярный `VACUUM` критически важен — именно он обновляет Visibility Map, который говорит индексу: «Этим данным в куче можно верить, в таблицу не ходи». Если карта видимости грязная, `INCLUDE` не спасет — база всё равно пойдет в heap проверять видимость строк.
`INCLUDE` — пожалуй самый недооцененный инструмент в арсенале DBA.
Что это такое?
Обычный B-tree индекс хранит только те колонки, по которым вы ищете и сортируете. Если запросу нужны другие поля, Postgres находит указатель в индексе, а потом идет в саму таблицу (heap) за данными. Это две операции ввода-вывода.
`INCLUDE` добавляет дополнительные колонки прямо в «листья» индекса. База находит строку по ключу и сразу забирает остальные данные оттуда же. Поход в таблицу отменяется.
Миф: INCLUDE ускоряет запись
Часто говорят, что `INCLUDE` нужен, чтобы не обновлять дерево индекса при изменении доп. колонок. Это не совсем так. При `INSERT` или `UPDATE` индекс всё равно переписывается.
Реальная магия `INCLUDE` кроется в другом:
1. Обход ограничений типов данных
Вы не можете положить геометрический тип `box` или некоторые пользовательские типы в ключ B-tree индекса — для них не определены операторы сравнения «больше-меньше».
Но их можно добавить через `INCLUDE`. Индекс будет искать по `id`, а `box` просто лежать рядом в листе.
2. Уникальность без потери покрытия
Вам нужно гарантировать уникальность по `email`, но запросы всегда тянут `user_id`.
Если добавить `user_id` в ключ индекса, база начнет сортировать данные еще и по нему. Это лишняя работа.
CREATE UNIQUE INDEX uq_users_email
ON users (email) INCLUDE (user_id);
Уникальность проверяется только по `email`, но запрос `SELECT user_id FROM users WHERE email = '...'` ответит прямо из индекса.
3. Спасение от «неправильной» сортировки
Если добавить колонку в ключ (например, `CREATE INDEX ON logs (user_id, created_at)`), база будет поддерживать порядок и по `user_id`, и по `created_at`.
Если вам не нужно искать диапазон дат, а нужно просто подтянуть `created_at` для конкретного `user_id` — вы заставляете дерево индекса делать лишнюю работу по поддержке порядка.
`INCLUDE (created_at)` говорит базе: «Храни это здесь, но не трать ресурсы на пересортировку индекса по этой дате».
Когда это выстрелит?
Только когда планировщик сможет использовать *Index Only Scan*. Для этого страница таблицы должна быть «чистой» для вашей транзакции.
Если в таблице много `UPDATE` и `DELETE`, регулярный `VACUUM` критически важен — именно он обновляет Visibility Map, который говорит индексу: «Этим данным в куче можно верить, в таблицу не ходи». Если карта видимости грязная, `INCLUDE` не спасет — база всё равно пойдет в heap проверять видимость строк.