Индекс в базах данных Postgresql
Индекс в базах данных Postgresql: INDEX, REINDEX.
Индексы - это специальные структуры в базах данных, которые позволяют ускорить поиск и сортировку по определенному полю или набору полей.
Индексом называется объект базы данных, позволяющий значительно повысить скорость обращения к БД за счет ускоренной обработки команд, содержащих сравнительные критерии. Хранимая в индексе информация о размещении данных по одному или нескольким полям таблицы повышает эффективность поиска записей при условной выборке (например, с использованием секции 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
Технология HOT (Heap-only tuples). Изменения с использованием HOT происходят, когда команда UPDATE изменяет значения в никем не индексируемых полях. Если изменяется значение в индексируемом поле, HOT не используется, и задействуется больше ресурсов. Поэтому правильное построение индексов может увеличить производительность при изменениях и снизить потребность в обслуживании.
- Статистика использования HOT для операции UPDATE:
select n_tup_upd, n_tup_hot_upd, schemaname,relname from pg_stat_user_tables;
Обслуживание индексов: Indexes Concurrently
- 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;


