Индекс в базах данных 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;