Описание

Команда CREATE INDEX строит индекс по указанному столбцу(столбцам) указанного отношения, которым может быть таблица или материализованное представление. Индексы в основном используются для повышения производительности базы данных (хотя неуместное использование может привести к замедлению).

Ключевые поля индекса указываются в виде имён столбцов или, альтернативно, в виде выражений в круглых скобках. Несколько полей могут быть указаны, если метод индекса поддерживает многоколоночные индексы.

Поле индекса может быть выражением, вычисленным на основе значений одного или нескольких столбцов строки таблицы. Эта возможность может использоваться для быстрого доступа к данным на основе некоторого преобразования базовых данных. Например, индекс, вычисленный по upper(col), позволил бы предложению WHERE upper(col) = 'JIM' использовать индекс.

Digital Q.DataBase предоставляет методы индексов B-дерева, хэша, GiST, SP-GiST, GIN и BRIN. Пользователи также могут определять собственные методы индексов, но это довольно сложно.

Когда присутствует предложение WHERE, создаётся частичный индекс. Частичный индекс — это индекс, который содержит записи только для части таблицы, обычно для той части, которая более полезна для индексирования, чем остальная часть таблицы. Например, если у вас есть таблица, содержащая как выставленные, так и не выставленные счета, причём невыставленные счета составляют небольшую часть общей таблицы, но это часто используемый раздел, вы можете повысить производительность, создав индекс только для этой части. Ещё одно возможное применение — использование WHERE вместе с UNIQUE для обеспечения уникальности подмножества таблицы. Более подробное обсуждение см. в разделе Раздел 2.8.8.

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

Все функции и операторы, используемые в определении индекса, должны быть «неизменяемыми», то есть их результаты должны зависеть только от их аргументов и никогда от внешних влияний (таких как содержимое другой таблицы или текущее время). Это ограничение гарантирует, что поведение индекса хорошо определено. Чтобы использовать пользовательскую функцию в выражении индекса или в предложении WHERE, не забудьте пометить функцию как неизменяемую при её создании.

Параметры

postgres=# \d tab
       Table "public.tab"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 col    | integer |           |          |
Indexes:
    "idx" btree (col) INVALID

Примечания

Информацию о том, когда индексы могут использоваться, когда они не используются и в каких конкретных ситуациях они могут быть полезны, см. в разделе Глава 2.8.

В настоящее время только методы индексов B-дерева, GiST, GIN и BRIN поддерживают индексы с несколькими ключевыми столбцами. Возможность наличия нескольких ключевых столбцов не зависит от того, можно ли добавлять столбцы INCLUDE в индекс. Индексы могут содержать до 32 столбцов, включая столбцы INCLUDE. (Это ограничение может быть изменено при сборке Digital Q.DataBase.) Только B-дерево в настоящее время поддерживает уникальные индексы.

Класс операторов с необязательными параметрами может быть указан для каждого столбца индекса. Класс операторов идентифицирует операторы, которые будут использоваться индексом для этого столбца. Например, индекс B-дерева по четырёхбайтовым целым числам будет использовать класс int4_ops; этот класс операторов включает функции сравнения для четырёхбайтовых целых чисел. На практике стандартного класса операторов для типа данных столбца обычно достаточно. Основная цель наличия классов операторов заключается в том, что для некоторых типов данных может существовать более одного значимого порядка. Например, мы можем захотеть отсортировать тип данных комплексного числа либо по абсолютному значению, либо по действительной части. Мы могли бы сделать это, определив два класса операторов для типа данных, а затем выбрав подходящий класс при создании индекса. Дополнительная информация о классах операторов содержится в разделах Раздел 2.8.10 и Раздел 5.1.16.

Когда CREATE INDEX вызывается для секционированной таблицы, поведение по умолчанию — рекурсивно обработать все разделы, чтобы гарантировать, что все они имеют соответствующие индексы. Сначала проверяется каждый раздел, чтобы определить, существует ли уже эквивалентный индекс, и если да, то этот индекс будет присоединён как индекс раздела к создаваемому индексу, который станет его родительским индексом. Если соответствующий индекс не существует, будет создан новый индекс и автоматически присоединён; имя нового индекса в каждом разделе будет определяться так, как если бы имя индекса не было указано в команде. Если указана опция ONLY, рекурсия не выполняется, и индекс помечается как недействительный. (ALTER INDEX ... ATTACH PARTITION помечает индекс действительным, как только все разделы получат соответствующие индексы.) Однако обратите внимание, что любой раздел, созданный в будущем с помощью CREATE TABLE ... PARTITION OF, будет автоматически иметь соответствующий индекс, независимо от того, указана ли опция ONLY.

Для методов индекса, поддерживающих упорядоченное сканирование (в настоящее время только B-дерево), необязательные предложения ASC, DESC, NULLS FIRST и/или NULLS LAST могут быть указаны для изменения порядка сортировки индекса. Поскольку упорядоченный индекс может сканироваться как в прямом, так и в обратном направлении, обычно нет необходимости создавать одноколоночный индекс DESC — такой порядок сортировки уже доступен с обычным индексом. Ценность этих опций заключается в том, что можно создавать многоколоночные индексы, соответствующие порядку сортировки, запрошенному запросом со смешанным порядком, таким как SELECT ... ORDER BY x ASC, y DESC. Опции NULLS полезны, если вам нужно поддерживать поведение «NULL сортируются низко», а не стандартное «NULL сортируются высоко», в запросах, которые зависят от индексов, чтобы избежать шагов сортировки.

Система регулярно собирает статистику по всем столбцам таблицы. Вновь созданные индексы, не основанные на выражениях, могут немедленно использовать эту статистику для определения полезности индекса. Для новых индексов по выражениям необходимо выполнить ANALYZE или дождаться, пока фоновый процесс автоочистки проанализирует таблицу, чтобы сгенерировать статистику для этих индексов.

Во время выполнения CREATE INDEX временно меняется search_path на pg_catalog, pg_temp.

Для большинства методов индекса скорость создания индекса зависит от настройки maintenance_work_mem. Большие значения сократят время, необходимое для создания индекса, при условии, что вы не сделаете его больше, чем реально доступный объём памяти, что приведёт к свопингу машины.

Digital Q.DataBase может строить индексы, используя несколько ЦП для более быстрой обработки строк таблицы. Эта возможность известна как параллельное построение индекса. Для методов индекса, поддерживающих построение индексов параллельно (в настоящее время B-дерево и BRIN), maintenance_work_mem указывает максимальный объём памяти, который может использоваться каждой операцией построения индекса в целом, независимо от того, сколько рабочих процессов было запущено. Как правило, модель стоимости автоматически определяет, сколько рабочих процессов следует запросить, если таковые вообще требуются.

Параллельное построение индексов может выиграть от увеличения maintenance_work_mem там, где эквивалентное последовательное построение индекса увидит небольшую пользу или не увидит её вовсе. Обратите внимание, что maintenance_work_mem может влиять на количество запрашиваемых рабочих процессов, поскольку параллельные рабочие должны иметь как минимум 32MB долю общего бюджета maintenance_work_mem. Также должна оставаться доля 32MB для ведущего процесса. Увеличение max_parallel_maintenance_workers может позволить использовать больше рабочих процессов, что сократит время, необходимое для создания индекса, при условии, что построение индекса не ограничивается вводом-выводом. Конечно, также должна быть достаточная мощность ЦП, которая в противном случае простаивала бы.

Установка значения для parallel_workers с помощью ALTER TABLE напрямую управляет количеством параллельных рабочих процессов, которые будут запрошены командой CREATE INDEX для таблицы. Это полностью обходит модель стоимости и предотвращает влияние maintenance_work_mem на количество запрашиваемых параллельных рабочих процессов. Установка parallel_workers в 0 с помощью ALTER TABLE отключит параллельное построение индексов на таблице во всех случаях.

В то время как CREATE INDEX с опцией CONCURRENTLY поддерживает параллельное построение без специальных ограничений, только первое сканирование таблицы фактически выполняется параллельно.

Используйте DROP INDEX для удаления индекса.

Как и любая длительная транзакция, CREATE INDEX для таблицы может влиять на то, какие кортежи могут быть удалены параллельным VACUUM на любой другой таблице.

В предыдущих выпусках Digital Q.DataBase также был метод индекса R-дерева. Этот метод был удалён, поскольку он не имел значительных преимуществ перед методом GiST. Если указано USING rtree, команда CREATE INDEX интерпретирует это как USING gist, чтобы упростить преобразование старых баз данных в GiST.

Каждый фоновый процесс, выполняющий CREATE INDEX, будет сообщать о своём прогрессе в представлении pg_stat_progress_create_index. Подробности см. в разделе Раздел 3.12.4.4.

Примеры

Создать уникальный индекс B-дерева по столбцу title в таблице films:

CREATE UNIQUE INDEX title_idx ON films (title);

Создать уникальный индекс B-дерева по столбцу title с включёнными столбцами director и rating в таблице films:

CREATE UNIQUE INDEX title_idx ON films (title) INCLUDE (director, rating);

Создать индекс B-дерева с отключённой дедупликацией:

CREATE INDEX title_idx ON films (title) WITH (deduplicate_items = off);

Создать индекс по выражению lower(title), позволяющий эффективно выполнять регистронезависимый поиск:

CREATE INDEX ON films ((lower(title)));

(В этом примере мы решили опустить имя индекса, поэтому система выберет имя, обычно films_lower_idx.)

Создать индекс с нестандартной сортировкой:

CREATE INDEX title_idx_german ON films (title COLLATE "de_DE");

Создать индекс с нестандартным порядком сортировки значений NULL:

CREATE INDEX title_idx_nulls_low ON films (title NULLS FIRST);

Создать индекс с нестандартным коэффициентом заполнения:

CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70);

Создать индекс GIN с отключённым быстрым обновлением:

CREATE INDEX gin_idx ON documents_table USING GIN (locations) WITH (fastupdate = off);

Создать индекс по столбцу code в таблице films и разместить индекс в табличном пространстве indexspace:

CREATE INDEX code_idx ON films (code) TABLESPACE indexspace;

Создать индекс GiST по атрибуту точка, чтобы можно было эффективно использовать операторы с прямоугольником на результате функции преобразования:

CREATE INDEX pointloc
    ON points USING gist (box(location,location));
SELECT * FROM points
    WHERE box(location,location) && '(0,0),(1,1)'::box;

Создать индекс без блокировки операций записи в таблицу:

CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);

Совместимость

CREATE INDEX является расширением языка Digital Q.DataBase. В стандарте SQL нет положений об индексах.

Смотрите также