F.47. pgpro_gbtree — глобальные индексы для секционированных таблиц #
Расширение pgpro_gbtree позволяет создавать и использовать глобальные индексы для секционированных таблиц.
Глобальный индекс создаётся для одного или нескольких столбцов секционированной таблицы и не включает ключ секционирования. На физическом уровне глобальный индекс секционированной таблицы содержит данные, относящиеся к индексированным столбцам для всех секций, что означает, что глобальный индекс позволяет выполнять сканирование индекса без перебора всех секций.
Глобальные индексы обладают следующими преимуществами:
Глобальные индексы позволяют гораздо быстрее выполнять поиск строк секционированной таблицы по условию для индексированных столбцов, поскольку такие индексы не секционируются, и нет необходимости выполнять сканирование индексов отдельных секций.
Они снимают ограничение Postgres Pro, не позволяющее создавать уникальный индекс для столбцов, не содержащих ключ секционирования.
F.47.1. Работа глобальных индексов #
Ниже представлено краткое описание работы глобальных индексов:
Глобальный индекс создаётся для заданного пользователем набора столбцов, а столбцы первичного ключа секционированной таблицы автоматически добавляются в глобальный индекс как столбцы
INCLUDE.Сканирование глобального индекса выполняется в два этапа:
Поиск значения выполняется в глобальном индексе.
Значение первичного ключа в столбцах индекса
INCLUDEиспользуется для поиска секции и значения в ней.
Глобальные индексы не перестраиваются для операций удаления и
DETACH PARTITION. Поэтому вполне нормально, что в них могут оставаться устаревшие данные, которые можно очистить с помощью VACUUM.Глобальные индексы не секционируются. Поэтому для них действует ограничение на размер данных в 32 терабайта. Предполагается, что для глобального индекса хватит 32 ТБ, поскольку он содержит только часть данных секционированной таблицы (однако это предположение может оказаться неверным при некоторых обстоятельствах).
F.47.2. Ограничения #
Создание глобальных индексов имеет следующие ограничения:
У секционированной таблицы, для которой создаётся глобальный индекс, должен быть первичный ключ.
Как и для любого индекса секционированной таблицы, нельзя использовать параметр
CONCURRENTLY.Выражения, используемые вместо имён столбцов, и предикаты в предложении
WHEREв настоящее время не поддерживаются.Указание неключевых столбцов (в предложении
INCLUDE) не поддерживается. Если такие столбцы указаны, командаCREATE INDEXвыдаёт предупреждение и игнорирует их.
Использование глобальных индексов имеет следующие ограничения:
Глобальные индексы, как и стандартные индексы B-деревья, не подходят для столбцов, содержащих мало уникальных значений. Примером неподходящего столбца может служить столбец
полс двумя значениямиМиЖ. Помимо высоких издержек самого индекса, для глобального индекса при добавлении нового значения блокируется хеш-сумма значения. При небольшом количестве возможных значений изменяющие транзакции, скорее всего, будут блокировать друг друга, то есть зависать на этой блокировке.Поскольку в глобальных индексах не хранится информацию о версиях строк, необходимость в сборке мусора для этого индекса не так важна, как для обычных индексов. Поэтому autovacuum в настоящее время не поддерживается.
Внешние ссылки на глобальный индекс секционированной таблицы из других таблиц на данный момент не поддерживаются.
Запрещено добавлять секционированную таблицу с глобальным индексом как секцию другой секционированной таблицы.
Если у секционированной таблицы есть глобальный индекс, удалять её первичный ключ нельзя. Сначала удалите глобальный индекс.
Команда CLUSTER для глобальных индексов на данный момент не поддерживается.
Предложение
ON CONFLICTна данный момент нельзя использовать с уникальными глобальными индексами.
F.47.3. Установка #
Расширение pgpro_sfile входит в состав Postgres Pro Enterprise. Чтобы его задействовать, создайте расширение следующим запросом:
CREATE EXTENSION pgpro_gbtree;
F.47.4. Использование #
F.47.4.1. Создание и удаление глобальных индексов #
Чтобы создать глобальный индекс, выполните команду CREATE INDEX с указанием метода доступа gbtree из расширения pgpro_gbtree. Создать глобальный индекс для таблицы t_int можно с помощью следующей команды:
CREATE INDEX t_int_v ON t_int USING gbtree (v);
Чтобы удалить глобальный индекс, выполните команду DROP INDEX. Например:
DROP INDEX t_int_v;
F.47.4.2. Использование глобальных индексов в запросах #
Невозможно реализовать обычное поведение Postgres Pro для глобального индекса, так как в стандартной реализации вся секционированная таблица, для которой создаётся глобальный индекс, не рассматривается как узел при построении плана запроса. Планы связываются с секциями, а затем для каждой секции выбирается наилучший метод сканирования, например SeqScan, IndexOnlyScan, IndexScan и т. д. Для планировщика Postgres Pro секционированная таблица предоставляет только список секций и не считается объектом планирования. Глобальный индекс может использоваться, если секционированная таблица становится объектом планирования. Это происходит только при следующих условиях:
Запрос не содержит условий для ключа секционирования, исключающих хотя бы одну секцию. Предполагается, что поиск с условием для ключа секционирования в запросе эффективнее, чем глобальный индекс.
Запрос содержит условие для столбцов глобального индекса.
F.47.5. Пример #
В следующем примере проиллюстрировано использование глобального индекса.
-- Создание расширения pgpro_gbtree CREATE EXTENSION pgpro_gbtree; -- Создание секционированной таблицы с тремя секциями CREATE TABLE t_int (i int PRIMARY KEY, v int, x int) PARTITION BY RANGE (i); CREATE TABLE t_int_1 PARTITION OF t_int FOR VALUES FROM (0) TO (100); CREATE TABLE t_int_2 PARTITION OF t_int FOR VALUES FROM (100) TO (200); CREATE TABLE t_int_3 PARTITION OF t_int FOR VALUES FROM (200) TO (300); -- Создание глобального индекса для столбца v CREATE INDEX t_int_v ON t_int USING gbtree (v); -- Создание обычного индекса для столбца x CREATE INDEX t_int_x ON t_int (x); -- План запроса с условием для столбца с глобальным индексом EXPLAIN SELECT * FROM t_int WHERE v > 100; -- План запроса с условием для столбца с нормальным индексом EXPLAIN SELECT * FROM t_int WHERE x > 100;
Результат будет выглядеть так:
CREATE EXTENSION
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX
QUERY PLAN
---------------------------------------------------------------------
Index Scan using t_int_v on t_int (cost=0.12..8.14 rows=0 width=0)
Index Cond: (v > 100)
(2 rows)
QUERY PLAN
------------------------------------------------------------------------------------
Append (cost=9.42..93.97 rows=2040 width=12)
-> Bitmap Heap Scan on t_int_1 (cost=9.42..27.92 rows=680 width=12)
Recheck Cond: (x > 100)
-> Bitmap Index Scan on t_int_1_x_idx (cost=0.00..9.25 rows=680 width=0)
Index Cond: (x > 100)
-> Bitmap Heap Scan on t_int_2 (cost=9.42..27.92 rows=680 width=12)
Recheck Cond: (x > 100)
-> Bitmap Index Scan on t_int_2_x_idx (cost=0.00..9.25 rows=680 width=0)
Index Cond: (x > 100)
-> Bitmap Heap Scan on t_int_3 (cost=9.42..27.92 rows=680 width=12)
Recheck Cond: (x > 100)
-> Bitmap Index Scan on t_int_3_x_idx (cost=0.00..9.25 rows=680 width=0)
Index Cond: (x > 100)
(13 rows)
Результат показывает, что при использовании глобального индекса сканирование выполняется для всей секционированной таблицы, а при использовании обычного индекса — для каждой секции по отдельности, и этот поиск может выполняться медленно при большом количестве секций.