Sysadminium

Мониторинг индексов в PostgreSQL очень важен, потому как индексы могут разрастаться или не использоваться.

Поиск неиспользуемых индексов

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

В представлении pg_stat_all_indexes в поле idx.scan вы можете увидеть количество обращений к индексу. Если там ноль, значит индекс не используется и возможно его следует удалить. Но следует точно понимать что вы делаете, и перепроверить все перед удалением таких индексов.

Посмотреть представление pg_stat_all_indexes для какой-нибудь таблицы можно так:

postgres@s-pg13:~$ psql -d data_lowlevel
Timing is on.
psql (13.3)
Type "help" for help.

postgres@data_lowlevel=# SELECT * FROM pg_stat_all_indexes WHERE relname='t';
 relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+---------+--------------+----------+--------------+---------------
 16497 |      16504 | public     | t       | t_pkey       |        0 |            0 |             0
(1 row)

Time: 10,941 ms

Перестроение индексов

Помимо лишних индексов существует другая проблема – разрастание индексов. Например у вас выросла таблица и индексы в ней тоже разрослись. Они перестали помещаться в одной странице и заняли ещё одну. Затем вы почистили табличку с помощью VACUUM, а страницы индексов всё равно останется две, просто в них образуются пустоты. Страницы могут только делиться, а склеиваться назад уже не могут (если не использовать VACUUM FULL).

Существует команда REINDEX которая занимается перестроением индексов. Можно перестроить как отдельный индекс, так и все индексы в таблице, в базе данных или во всей системе:

• REINDEX INDEX индекс;
• REINDEX TABLE таблица;
• REINDEX DATABASE база;
• REINDEX SYSTEM;

Команда VACUUM FULL перестраивает и таблицы и индексы, а команда REINDEX только индексы. REINDEX также устанавливает эксклюзивную блокировку на таблицу.

Пересоздание индексов

Можно пересоздать индексы без эксклюзивной блокировки с помощью ключевого слова CONCURRENTLY:

postgres=# CREATE INDEX новый ON ... CONCURRENTLY;
postgres=# DROP INDEX старый;

С помощью такого перестроения индексов, новый индекс будет строится дольше, но это не приведёт к блокировке таблицы. Также такое создание нового индекса может завершиться с ошибкой, тогда этот новый индекс нужно удалить и попробовать сделать новый индекс ещё раз.

Индекс может служить для поддержки ограничения целостности, например для первичного ключа. В этом случае меняем индексы таким способом:

postgres=# CREATE UNIQUE INDEX новый ON ... CONCURRENTLY;
postgres=# ALTER TABLE ...
   DROP CONSTRAINT старое,
   ADD CONSTRAINT новое [UNIQUE|PRIMARI KEY] USING INDEX новый;

---