Индекс в базах данных Postgresql

Индекс в базах данных Postgresql: INDEX, REINDEX.

Индексы - это специальные структуры в базах данных, которые позволяют ускорить поиск и сортировку по определенному полю или набору полей.

Индексом называется объект базы данных, позволяющий значительно повысить скорость обращения к БД за счет ускоренной обработки команд, содержащих сравнительные критерии. Хранимая в индексе информация о размещении данных по одному или нескольким полям таблицы повышает эффективность поиска записей при условной выборке (например, с использованием секции WHERE). После того, как индекс будет создан, система должна поддерживать ее синхронизированным с таблицей. Это добавляет дополнительные расходы на операции с данными. Поэтому индексы, которые редко или никогда не используются в запросах должны быть удалены.

Создание индекса в большой таблице может занять длительное время.

По умолчанию, Использование и настройка PostgreSQL позволяет читать (выбирать) из таблицы, параллельно с созданием индекса, но блокирует запись (вставку, обновление и удаление) до конца построения индекса.

Почему мои запросы работают медленно? Почему они не используют мои индексы? Индексы не используются для каждого запроса. Они используются только если таблица больше минимального размера и запрос выбирает только маленький процент строк в таблице. Так устроено, потому что доступ к диску с применением рандомизации при сканировании индексов может быть медленнее, чем простое чтение таблицы или ее последовательное сканирование.

Команда REINDEX используется для перестройки существующих индексов. Использовать её имеет смысл в случае:

порчи индекса;
постоянного увеличения его размера.

Второй случай требует пояснений. Индекс, как и таблица, содержит блоки со старыми версиями записей. PostgreSQL не всегда может заново использовать эти блоки, и поэтому файл с индексом постепенно увеличивается в размерах. Если данные в таблице часто меняются, то расти он может весьма быстро. Если вы заметили подобное поведение какого-то индекса, то стоит настроить для него периодическое выполнение команды REINDEX. Учтите: команда REINDEX, как и VACUUM FULL, полностью блокирует таблицу, поэтому выполнять её надо тогда, когда загрузка сервера минимальна.

Перестроить только индекс 'index_ratesheet_id'
```
REINDEX INDEX index_ratesheet_id;
```

Данный запрос показывает размер объектов в базе данных (например, таблиц и индексов), исключая pg_catalog information_schema.

SELECT C.oid,C.relfilenode, nspname, relname AS "relation",
relkind, pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC;

При помощи команды Отслеживание времени выполнения команд с помощью утилиты time, замерим время переиндексации индекса (в данном случае размер индекса составляет 12 GB)
```
time -p psql -U postgres mbillcz5054 -c "REINDEX INDEX i_cdr_pairs;"
REINDEX
real 920.49
user 0.00
sys 0.00
```
В поле real видно, что задача выполнялась более 15 минут.
reindexdb - эта утилита запущенная из операционной системы выполнит SQL команду REINDEX для каждой таблицы в заданной по умолчанию базе данных. Ключ -а заставит выполнить REINDEX для всех индексов во всех базах данных сервера.

Технология HOT

Технология HOT (Heap-only tuples). Изменения с использованием HOT происходят, когда команда UPDATE изменяет значения в никем не индексируемых полях. Если изменяется значение в индексируемом поле, HOT не используется, и задействуется больше ресурсов. Поэтому правильное построение индексов может увеличить производительность при изменениях и снизить потребность в обслуживании.

Статистика использования HOT для операции UPDATE:
```
select n_tup_upd, n_tup_hot_upd, schemaname,relname from pg_stat_user_tables;
```

Изменения записей, без использования HOT могут вызывать раздувание индексов. При автоматической сборки мусора (VACUUM) раздутые индексы не обнаруживаются и никакие индексы не перестраиваются.

Обслуживание индексов: Indexes Concurrently

Building Indexes Concurrently создание нового индекса без блокировки таблицы.

Использование и настройка PostgreSQL поддерживает команду CREATE INDEX CONCURRENTLY, которая создает индекс без полной блокировки таблицы, она позволяет добавлять, изменять и удалять записи во время создания индекса. Также PostgreSQL позволяет иметь два индекса с разными именами, но с одним и тем же определением.

CREATE INDEX CONCURRENTLY нельзя использовать с первичными индексами.

Идея в том, чтобы создать новый индекс, идентичный старому, удалить старый индекс и присвоить новому имя старого.

Пример использования CREATE INDEX CONCURRENTLY.

Старый индекс

oid  | relfilenode | nspname  |              relation              | relkind |    size    
-------+-------------+----------+------------------------------------+---------+------------
 35615 |       67875 | public   | i_cdr_out                          | i       | 1628 MB

Новый индекс

  oid  | relfilenode | nspname  |              relation              | relkind |    size    
-------+-------------+----------+------------------------------------+---------+------------
 68397 |       68397 | public   | i_cdr_out                          | i       | 1557 MB

Изменился внутренний идентификатор (oid) и номер текущего файла(relfilenode) - это подтверждает, что индекс был заменен успешно. Так же уменьшился размер индекса. Это было достигнуто нижеприведенными SQL командами. В которых создается составной индекс i_cdr_out_new, после его создания в транзакции происходит удаление старого индекса и переименование нового.

CREATE INDEX CONCURRENTLY i_cdr_out_new
  ON cdr
  USING btree (begin_time, dst_peer_id, cause_local);
BEGIN;
DROP INDEX i_cdr_out;
ALTER INDEX i_cdr_out_new RENAME TO i_cdr_out;
COMMIT;