PostgreSQL – короткий динамический индекс — Пушистиков де ла Вега на vc.ru

Как сделать короткий (частичный) динамический (покрывающий плавающее/скользящее окно данных) индекс для большой таблицы.

Использование короткого индекса может ускорить запросы. Индекс создается параллельно основному. Коротким он называется потому, что в него попадает ограниченный набор данных (в примере ниже это записи не старше 7 дней).

Пример …

CREATE TABLE test ( created_at timestamptz ); CREATE FUNCTION your_index_low_value() RETURNS timestamptz AS $$ SELECT CURRENT_DATE - 7 $$ -- не старше 7 дней LANGUAGE SQL IMMUTABLE; CREATE INDEX your_index ON test(created_at) WHERE created_at >= your_index_low_value();

Теперь самое главное – как сделать индекс динамическим, для этого необходимо сделать job, который будет раз в сутки запускать вот такую команду …

REINDEX INDEX CONCURRENTLY your_index;

Готово. Индекс перестраивается достаточно быстро, к тому же его перестроение не блокирует транзакции. Даже если с ним что-то случится – не страшно, основной индекс всегда подстрахует. Все запросы к таблице test будут отдавать предпочтение этому индексу (проверить надо обязательно), если он подходит под условия запроса. Функция обязательно должна быть IMMUTABLE и возвращать тот же тип данных, что и поле, с которым она сравнивается.

Небольшое неудобство состоит в том, что в условии запроса, для сравнения с полем, на которое поставлено ограничение, можно использовать только константу или IMMUTABLE функцию.

Обратите внимание, что короткие индексы не всегда дадут солидный профит, уточняйте эффект в практическом тесте.