Почему изменение индексируемого поля отключает HOT update?

Что такое HOT update?

HOT (Heap Only Tuple) update — это оптимизация в PostgreSQL, которая позволяет обновлять строки без изменения индексов. Когда строка обновляется, PostgreSQL создает новую версию строки (tuple) в той же странице (page) кучи (heap), если есть свободное место. Если обновляемые поля не входят ни в один индекс, то индексы остаются неизменными, что экономит ресурсы.

Почему изменение индексируемого поля отключает HOT?

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

Пример

CREATE TABLE users (id INT PRIMARY KEY, name TEXT, email TEXT);
CREATE INDEX idx_email ON users(email);

-- HOT update возможен, так как name не индексирован
UPDATE users SET name = 'Alice' WHERE id = 1;

-- HOT update невозможен, так как email индексирован
UPDATE users SET email = 'alice@example.com' WHERE id = 1;

Вывод

HOT update — полезная оптимизация для снижения нагрузки на индексы при обновлении неиндексированных полей. Однако, при изменении индексируемых полей, необходимо выполнять полное обновление индекса, что увеличивает затраты на запись. Это важно учитывать при проектировании схемы базы данных и выборе индексов.