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

• Переводы/Перевод главы "Индексы" мануала
• REINDEX -- rebuild indexes

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

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

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

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

• Статистика использования HOT для операции UPDATE:

  select n_tup_upd, n_tup_hot_upd, schemaname,relname from pg_stat_user_tables;

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

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

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

Пример использования 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;