CREATE INDEX

CREATE INDEX — определить новый индекс

Синтаксис

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] имя ] ON [ ONLY ] имя_таблицы [ USING метод ]
    ( { имя_столбца | ( выражение ) } [ COLLATE правило_сортировки ] [ класс_операторов  [ ( параметр_класса_операторов = значение [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( имя_столбца [, ...] ) ]
    [ WITH ( параметр_хранения [= значение] [, ... ] ) ]
    [ TABLESPACE имя_табличного_пространства ]
    [ WHERE предикат ]

Описание

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

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

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

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

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

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

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

Параметры

UNIQUE

Указывает, что система должна проверять наличие повторяющихся значений в таблице при создании индекса (если данные уже существуют) и при каждом добавлении данных. Попытки добавить или обновить данные, которые вызовут дублирование записей, приведут к ошибке.

Дополнительные ограничения действуют при применении уникальных индексов к партиционированным таблицам; см. раздел CREATE TABLE.

CONCURRENTLY

Если используется этот параметр, QHB будет строить индекс без установления каких-либо блокировок, предотвращающих параллельные добавления, изменения или удаление записей в таблице, тогда как стандартная операция построения индекса блокирует запись (но не чтение) в таблице до своего завершения. Существует несколько особенностей, которые следует учитывать при использовании этого параметра — см. ниже Неблокирующее построение индексов.

IF NOT EXISTS

Не считать ошибкой, если индекс с таким именем уже существует. В этом случае будет выдано соответствующее уведомление. Обратите внимание, что нет никакой гарантии, что существующий индекс как-то соотносится с тем, который был бы создан. Если указано IF NOT EXISTS, то имя индекса является обязательным.

INCLUDE

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

В отношении добавления неключевых столбцов в индекс разумно проявлять консерватизм, особенно для широких столбцов. Если кортеж индекса превысит максимальный размер, разрешенный для типа индекса, добавление данных завершится ошибкой. В любом случае, неключевые столбцы дублируют данные из таблицы и раздувают размер индекса, что потенциально замедляет поиск. Более того, в индексе B-дерево, содержащем неключевой столбец, не будет работать исключение дубликатов

Столбцы, перечисленные в предложении INCLUDE, не нуждаются в соответствующих классах операторов; предложение может включать столбцы, типы данных которых не имеют классов операторов, определенных для данного метода доступа.

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

В настоящее время эту возможность поддерживают только методы доступа индексов B-дерево и GiST. В таких индексах значения столбцов, перечисленных в предложении INCLUDE, включаются в кортежи на уровне листьев, которые соответствуют кортежам кучи, но не включены в записи индекса верхнего уровня, используемые для навигации по деревьям.

имя

Имя создаваемого индекса. Имя схемы здесь не может быть включено: индекс всегда создается в той же схеме, что и его родительская таблица. Если имя опущено, QHB выбирает подходящее имя на основе имени родительской таблицы и индексированных имен столбцов.

ONLY

Указывает, что индексы не должны рекурсивно создаваться на партициях, если таблица партиционирована. По умолчанию используется рекурсивно.

имя_таблицы

Имя индексируемой таблицы (может быть дополнено схемой).

метод

Имя используемого индексного метода. Возможные варианты: btree, hash, gist, spgist, gin и brin. По умолчанию используется метод btree.

имя_столбца

Имя столбца таблицы.

выражение

Выражение, основанное на одном или нескольких столбцах таблицы. Обычно выражение должно быть записано в скобках, как показано в синтаксисе. Однако если выражение записано в виде вызова функции, круглые скобки могут быть опущены.

правило_сортировки

Имя правила сортировки, используемого для индекса. По умолчанию индекс использует правило сортировки, объявленное для подлежащего индексированию столбца, или результирующее правило подлежащего индексированию выражения. Индексы с нестандартными правилами сортировки могут быть полезны для запросов, содержащих выражения, использующие такие правила.

класс_операторов

Имя класса оператора. Дополнительную информацию см. ниже.

параметр_класса_операторов

Имя параметра класса операторов. Дополнительную информацию см. ниже.

ASC

Задает порядок сортировки по возрастанию (который является порядком сортировки по умолчанию).

DESC

Задает порядок сортировки по убыванию.

NULLS FIRST

Указывает, что значения NULL после сортировки оказываются перед остальными. Это значение по умолчанию, когда задано DESC.

NULLS LAST

Указывает, что значения NULL после сортировки оказываются после остальных. Это значение по умолчанию, когда задано ASC.

параметр_хранения

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

имя_табличного_пространства

Табличное пространство, в котором будет создан индекс. Если этот параметр не указан, выполняется обращение к default_tablespace или temp_tablespaces для индексов временных таблиц.

предикат

Выражение ограничения для частичного индекса.

Параметры хранения индекса

Необязательное предложение WITH задает параметры хранения для индекса. Каждый индексный метод имеет свой собственный набор допустимых параметров хранения. Индексные методы B-tree, hash, GiST и SP-GiST принимают этот параметр:

fillfactor (integer)

Коэффициент заполнения для индекса выражается в процентах и определяет, насколько плотно индексный метод будет пытаться заполнить страницы индекса. Для B-деревьев страницы уровня листьев заполняются до этого процента во время первоначального построения индекса, а также при расширении индекса вправо (добавление новых наибольших значений ключей). Если впоследствии страницы будут полностью заполнены, они будут разделены, что приведет к постепенному снижению эффективности индекса. По умолчанию B-деревья используют коэффициент заполнения 90, но можно выбрать любое целочисленное значение от 10 до 100. Если таблица статична, то коэффициент заполнения, равный 100, помогает уменьшить физический размер таблицы, но для интенсивно изменяемых таблиц лучше использовать меньшее значение коэффициента заполнения, чтобы минимизировать необходимость разбиения страниц. С другими индексными методами коэффициент заполнения действует по-другому, но примерно в том же ключе; значение коэффициента заполнения по умолчанию для разных методов разное.

Индексы B-дерево также принимают эти параметры:

deduplicate_items (boolean)

Контролирует использование техники исключения дубликатов, описанной в подразделе Дедупликация. Устанавливается на значения ON или OFF для включения или выключения соответственно. (Допустимые варианты написания ON и OFF описаны в разделе Настройка параметров.) Значение по умолчанию равно ON.

Примечание
После выключения deduplicate_items во время выполнения команды ALTER INDEX в будущем при операциях добавления элементов автоматическая дедупликация срабатывать не будет, но представление существующих кортежей со списком идентификаторов на стандартное не поменяется.

vacuum_cleanup_index_scale_factor (floating point)

Значение vacuum_cleanup_index_scale_factor для индекса.

Индексы GiST дополнительно принимают этот параметр:

buffering (enum)

Определяет, используется ли для построения индекса метод буферизации, описанный в разделе Индексы GiST. Реализация. Со значением OFF он отключен, с ON — включен, а с AUTO — вначале отключен, но включается на лету, как только размер индекса достигает effective_cache_size. Значение по умолчанию равно AUTO.

Индексы GIN принимают другие параметры:

fastupdate (boolean)

Этот параметр управляет использованием механизма быстрого обновления, описанного в разделе Быстрое обновление GIN. Это логический параметр: ON включает быстрое обновление, OFF отключает его. (Допустимые варианты написания ON и OFF описаны в разделе Настройка параметров.) Значение по умолчанию равно ON.

Примечание
Выключение fastupdate в ALTER INDEX предотвращает помещение добавляемых в дальнейшем строк в список записей, ожидающих индексации, но записи, добавленные в этот список ранее, в нем остаются. Чтобы очистить очередь операций, надо затем выполнить для этой таблицы VACUUM или вызвать функцию gin_clean_pending_list.

gin_pending_list_limit (integer)

Пользовательский параметр gin_pending_list_limit. Это значение указывается в килобайтах.

Индексы BRIN принимают другие параметры:

pages_per_range (integer)

Определяет количество блоков таблицы, которые составляют один диапазон блоков для каждой записи индекса BRIN (более подробную информацию см. в разделе Индексы BRIN). Значение по умолчанию равно 128.

autosummarize (boolean)

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

Неблокирующее построение индексов

Создание индекса может помешать обычной работе базы данных. Как правило, QHB блокирует для записи предназначенную к индексированию таблицу и полностью выполняет построение индекса за одно сканирование таблицы. Другие транзакции по-прежнему могут читать из таблицы, но при попытке добавления, изменения или удаления строки в таблице они будут блокироваться до завершения построения индекса. Это может иметь серьезные последствия, если система является производственной базой данных в реальном времени. Индексация очень больших таблиц может занять много часов, и даже у таблиц меньшего размера построение индекса может блокировать записи на время, неприемлемое для производственной системы.

QHB поддерживает построение индексов без блокировки записи. Этот метод вызывается путем указания CONCURRENTLY команды CREATE INDEX. Если используется этот параметр, QHB должен выполнить два сканирования таблицы и помимо этого дождаться завершения всех существующих транзакций, которые потенциально могут изменить или использовать индекс. Таким образом, данный метод требует проделать в сумме больше действий, чем стандартное построение индекса, и занимает значительно больше времени. Однако поскольку он позволяет продолжать обычную работу во время построения индекса, этот метод удобен для добавления новых индексов в производственной среде. Разумеется, дополнительная нагрузка на процессор и подсистему ввода/вывода, вызванная созданием индекса, может замедлить другие операции.

При неблокирующем построении индекса он действительно попадает в системный каталог в одной транзакции, а затем в еще двух транзакциях происходят два сканирования таблиц. Перед каждым сканированием операция построения индекса должна ожидать завершения существующих транзакций, которые модифицируют таблицу. После второго сканирования этой операции также необходимо дождаться завершения всех транзакций, которые получили снимок таблицы (см. главу Параллельный контроль) перед вторым сканированием. Затем индекс наконец может быть помечен как готовый к использованию, и команда CREATE INDEX завершается. Однако даже в этом случае индекс может не сразу стать пригодным для запросов: в худшем случае его нельзя применять до тех пор, пока существуют транзакции, запущенные до начала построения индекса.

Если при сканировании таблицы возникает проблема, например взаимоблокировка или нарушение уникальности в уникальном индексе, команда CREATE INDEX завершится ошибкой, но оставит «нерабочий» индекс. Этот индекс будет игнорироваться при запросах, поскольку он может быть неполным; однако он всё равно будет потреблять ресурсы при обновлениях. Команда qsql \d сообщит о таком индексе, пометив его как INVALID:

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

Рекомендуемый метод восстановления в таких случаях заключается в том, чтобы удалить индекс и попытаться снова выполнить CREATE INDEX CONCURRENTLY. (Другая возможность состоит в том, чтобы перестроить индекс с помощью команды REINDEX INDEX CONCURRENTLY.)

Еще одна сложность, с которой можно столкнуться при неблокирующем построении уникального индекса, в том, что когда начинается второе сканирование таблицы, ограничение уникальности уже применяется к другим транзакциям. Это означает, что нарушения ограничений могут быть зарегистрированы в других запросах раньше, чем индекс станет доступным для использования, или даже в тех случаях, когда построение индекса в конечном итоге завершится неудачей. Кроме того, если во время второго сканирования происходит сбой, то «нерабочий» индекс все равно продолжает после этого применять свое ограничение уникальности.

Метод неблокирующего построения поддерживает также индексы выражений и частичные индексы. Ошибки, возникающие при вычислении этих выражений, могут привести к поведению, описанному выше для случаев с нарушением ограничений уникальности.

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

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

Примечания

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

В настоящее время только методы B-tree, GiST, GIN и BRIN поддерживают составные индексы. По умолчанию можно указать до 32 полей. (Это ограничение можно изменить при пересборке QHB.) В настоящее время только B-tree поддерживает уникальные индексы.

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

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

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

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

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

QHB может создавать индексы, используя несколько процессоров, чтобы быстрее обрабатывать строки таблицы. Эта функция называется параллельным построением индекса. Для индексных методов, поддерживающих параллельное построение индексов (в настоящее время только В-дерево), maintenance_work_mem задает максимальный объем памяти, который может использовать каждая операция построения индекса в целом, независимо от количества запущенных рабочих процессов. Целесообразность использования параллельных процессов и их оптимальное количество обычно автоматически определяется моделью стоимости.

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

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

Совет
Имеет смысл сбросить настройки parallel_workers после выполнения построения индекса. Это позволит избежать нежелательных изменений планов запросов, так как parallel_workers влияет на все параллельные сканирования таблицы.

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

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

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

Предыдущие выпуски системы также поддерживали индексный метод R-дерево. Этот метод был удален, поскольку не имел существенных преимуществ по сравнению с методом GiST. Указание USING rtree в CREATE INDEX будет интерпретироваться как USING GiST, чтобы упростить преобразование старых баз данных в GiST.

Примеры

Создание уникального индекса 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 по координатам точек, позволяющего эффективно использовать операторы box с результатом функции преобразования:

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 является расширением QHB. В стандарте SQL нет положений для индексов.

См. также

ALTER INDEX, DROP INDEX, REINDEX