Создаёт новый индекс.
CREATE [UNIQUE] INDEX [ CONCURRENTLY ] [name] ON table_name [USING method]
( {column_name | (expression)} [COLLATE parameter] [opclass] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ WITH ( storage_parameter = value [, ... ] ) ]
[ TABLESPACE tablespace ]
[ WHERE predicate ]Команда CREATE INDEX создаёт индекс по указанным столбцам указанной таблицы. Индексы используются в первую очередь для повышения производительности базы данных (хотя неправильное использование может привести к её снижению).
Ключевые поля индекса задаются как имена столбцов или как выражения, заключённые в скобки. Если метод индексирования поддерживает многоколоночные индексы, можно указать несколько полей.
Поля индекса могут быть выражениями, вычисляемыми на основе значений одного или нескольких столбцов строк таблицы. Эта возможность позволяет быстро получать доступ к данным на основе определённого преобразования исходных данных. Например, индекс, вычисленный по функции upper(col), позволит использовать индекс при выполнении условия WHERE upper(col) = ‘JIM’.
База данных YMatrix предоставляет следующие методы индексирования: B-дерево (B-tree), битовый (bitmap), GiST, SP-GiST и GIN. Пользователи также могут определять собственные методы индексирования, однако это достаточно сложная задача.
Частичные индексы создаются при наличии предложения WHERE. Частичный индекс — это индекс, содержащий только часть записей таблицы, обычно ту часть, которая является более полезной по сравнению с остальной частью таблицы. Например, если у вас есть таблица, содержащая как выставленные, так и невыставленные заказы, причём невыставленные заказы составляют лишь небольшую долю от общего объёма таблицы, но являются наиболее часто запрашиваемыми, вы можете повысить производительность, создав индекс только по этой части.
Выражение, используемое в предложении WHERE, может ссылаться только на столбцы из базовой таблицы, но оно может использовать все столбцы, а не только те, которые входят в индекс. Подзапросы и агрегатные выражения в предложении WHERE запрещены. Те же ограничения распространяются на поля индекса, используемые в виде выражений.
Все функции и операторы, используемые в определениях индексов, должны быть неизменяемыми (immutable). Их результаты должны зависеть исключительно от параметров и не зависеть от внешних факторов (например, содержимого другой таблицы или значений параметров). Это ограничение гарантирует корректное поведение индекса. При использовании пользовательских функций в выражениях индекса или в предложении WHERE необходимо помечать такие функции как IMMUTABLE при их создании.
UNIQUE
— Создаёт индекс и проверяет таблицу на наличие дублирующихся значений каждый раз при добавлении данных. Дублирующиеся записи вызывают ошибку. Уникальные индексы применимы только к индексам B-дерева. В базах данных YMatrix уникальные индексы разрешены только в том случае, если столбцы ключа индекса совпадают с ключами распределения YMatrix (или являются их надмножеством). В секционированных таблицах уникальные индексы поддерживаются только внутри одной секции — они не могут охватывать все секции.
CONCURRENTLY
— При создании индекса не блокируются операции вставки, обновления или удаления. Процесс создания стандартного индекса блокирует операции записи (при этом операции чтения не затрагиваются) до завершения создания. Однако при использовании опции CONCURRENTLY YMatrix не требует блокировки всех операций записи. Для временных таблиц команда CREATE INDEX обычно выполняется без параллельности, поскольку другие сеансы не могут получить к ним доступ. Процесс создания индекса без параллельности также имеет меньшие накладные расходы.
name
— Имя создаваемого индекса. Индексы всегда создаются в той же схеме, что и родительская таблица. Если имя опущено, база данных YMatrix выберет подходящее имя на основе имени родительской таблицы и имени столбца индекса.
table_name
— Имя таблицы, по которой создаётся индекс (может указываться с указанием схемы).
method
— Имя используемого метода индексирования. Допустимые значения: btree, bitmap, gist, spgist, gin. Метод по умолчанию — btree. В настоящее время только методы B-дерево, GiST и GIN поддерживают многоколоночные индексы. По умолчанию можно указать до 32 полей. В настоящее время только B-дерево поддерживает уникальные индексы.
column_name
— Имя столбца таблицы, по которому создаётся индекс. Только методы индексирования B-дерево, битовый, GiST и GIN поддерживают многоколоночные индексы.
expression
— Выражение, основанное на одном или нескольких столбцах таблицы. Обычно выражения должны быть заключены в скобки, как показано в синтаксисе. Однако, если выражение представляет собой вызов функции, скобки можно опустить.
collation
— Имя набора правил сортировки, используемого индексом. По умолчанию индекс использует правила сортировки, объявленные для индексируемого столбца, либо правила сортировки, полученные в результате выражения. Индексы с нестандартными правилами сортировки полезны для запросов, включающих выражения с нестандартными правилами сортировки.
opclass
— Имя класса операторов. Класс операторов определяет операторы, которые будут использоваться для индекса по данному столбцу. Например, индекс B-дерева по четырёхбайтовому целому числу будет использовать класс int4_ops (этот класс операторов содержит функции сравнения для четырёхбайтовых целых чисел). На практике класс операторов по умолчанию для типа данных столбца обычно достаточен. Смысл наличия классов операторов заключается в том, что для некоторых типов данных может существовать более одного осмысленного порядка сортировки. Например, комплексные числа можно сортировать по модулю или по действительной части. Для этого можно определить два класса операторов для данного типа данных и затем выбрать соответствующий класс при создании индекса.
ASC
— Указывает восходящий порядок (по умолчанию).
DESC
— Указывает нисходящий порядок.
NULLS FIRST
— Указывает, что NULL-значения должны располагаться перед не-NULL. Это значение по умолчанию при указании DESC.
NULLS LAST
— Указывает, что NULL-значения должны располагаться после не-NULL. Если DESC не указано, это значение используется по умолчанию.
storage_parameter
— Имя параметра хранения, специфичного для метода индексирования. Каждый метод индексирования имеет свой набор допустимых параметров хранения.
FILLFACTOR — методы индексирования B-дерево, битовый, GiST и SP-GiST принимают этот параметр. FILLFACTOR индекса — это процент, определяющий степень, до которой метод индексирования будет пытаться заполнить страницы индекса. Для B-деревьев листовые страницы заполняются до указанного процента при первоначальном создании индекса и при его расширении вправо (добавление новых максимальных ключевых значений). Если впоследствии страницы заполняются полностью, они разделяются, что приводит к постепенному снижению эффективности индекса. По умолчанию коэффициент заполнения B-дерева равен 90, но можно выбрать любое целое значение от 10 до 100. Если таблица статична, коэффициент заполнения 100 лучше всего минимизирует физический размер индекса. Однако для таблиц с большим количеством обновлений меньший коэффициент заполнения лучше, поскольку он уменьшает необходимость разделения страниц. Другие методы индексирования используют коэффициент заполнения аналогичным образом, но с различиями; значение по умолчанию зависит от метода.
BUFFERING — индексы GiST дополнительно принимают параметр BUFFERING. BUFFERING определяет, будет ли база данных YMatrix использовать технику построения буфера, описанную в разделе «GiST buffer-building» документации PostgreSQL, для создания индекса. Если установлено OFF, техника отключена; если ON — включена; при AUTO изначально отключена, но как только размер индекса достигает эффективного размера кэша, она немедленно включается. Значение по умолчанию — AUTO.
FASTUPDATE — метод индексирования GIN принимает параметр хранения FASTUPDATE. FASTUPDATE — это логический параметр, который включает или отключает технику быстрого обновления индекса GIN. Значение ON включает быстрые обновления (по умолчанию), значение OFF — отключает. Дополнительную информацию см. в разделе «GIN fast update technique» документации PostgreSQL.
tablespace_name
— Табличное пространство, в котором будет создан индекс. Если не указано, используется табличное пространство по умолчанию.
predicate
— Выражения ограничений для частичных индексов.
Для каждого столбца в индексе можно указать класс операторов. Класс операторов определяет операторы, которые будут использоваться для индекса по этому столбцу. Например, индекс B-дерева по четырёхбайтовому целому числу будет использовать класс int4_ops. Этот класс операторов содержит функции сравнения для четырёхбайтовых целых чисел. На практике класс операторов по умолчанию для типа данных столбца обычно достаточен. Главная идея наличия классов операторов заключается в том, что для некоторых типов данных может существовать более одного осмысленного порядка сортировки. Например, мы можем захотеть сортировать комплексные типы данных по абсолютной величине или по действительной части. Для этого можно определить два класса операторов для типа данных и затем выбрать соответствующий класс при создании индекса.
Для методов индексирования, поддерживающих упорядоченные сканирования (в настоящее время только B-дерево), можно указать дополнительные предложения, такие как ASC, DESC, NULLS FIRST и/или NULLS LAST, чтобы изменить порядок сортировки индекса. Поскольку упорядоченные индексы можно просматривать как вперёд, так и назад, создание одностолбцового индекса с DESC обычно избыточно — обычные индексы уже поддерживают нужный порядок сортировки. Ценность этих параметров проявляется при создании многоколоночных индексов, соответствующих порядку сортировки, запрашиваемому смешанными запросами сортировки, например SELECT ... ORDER BY x ASC, y DESC. Предложение NULLS полезно, если необходимо поддержать поведение «NULL-значения сортируются в начало» в запросах, использующих индексы для избежания шага сортировки, вместо поведения по умолчанию «NULL-значения сортируются в конец».
Для большинства методов индексирования скорость создания индекса зависит от параметра maintenance_work_mem. Более высокие значения уменьшают время, необходимое для создания индексов, при условии, что вы не превышаете объём реально доступной памяти, иначе компьютер начнёт использовать подкачку.
После создания индекса на секционированной таблице он будет распространяться на все дочерние таблицы, созданные базой данных Matrix. Индексы нельзя создавать на таблицах, созданных базой данных YMatrix для использования с секционированными таблицами.
Уникальные индексы разрешены только в том случае, если столбцы индекса совпадают со столбцами ключа распределения YMatrix (или являются их надмножеством).
Уникальные индексы запрещены для таблиц с оптимизацией под добавление (append-optimized tables).
Уникальные индексы можно создавать на секционированных таблицах. Однако уникальность обеспечивается только внутри каждой секции. Уникальность между секциями не контролируется. Например, для секционированной таблицы, где секции основаны на годах, а подсекции — на кварталах, уникальность обеспечивается только внутри каждой отдельной подсекции по кварталам. Уникальность между подсекциями по кварталам не обеспечивается.
По умолчанию индексы не используются с условиями IS NULL. В таких случаях лучший способ использовать индексы — создать частичные индексы с предикатами IS NULL.
Битовые индексы работают лучше всего для столбцов с 100–100 000 различных значений. Для столбцов с более чем 100 000 различных значений производительность и эффективность использования пространства битовых индексов снижаются. Размер битового индекса пропорционален количеству строк в таблице, умноженному на количество различных значений в индексируемом столбце.
Столбцы с менее чем 100 различными значениями обычно не получают значительной выгоды от любого типа индекса. Например, столбец gender с двумя возможными значениями (male и female) не является хорошим кандидатом для индексирования.
Предыдущие версии базы данных YMatrix также поддерживали метод индексирования R-tree. Этот метод был удалён, поскольку он не даёт явных преимуществ по сравнению с методом GiST. Если указано USING rtree, команда CREATE INDEX интерпретирует это как USING gist.
Использование хэш-индексов отключено в базе данных YMatrix.
Создать индекс B-дерева по столбцу title таблицы films:
CREATE UNIQUE INDEX title_idx ON films (title);Создать битовый индекс по столбцу gender таблицы employee:
CREATE INDEX gender_bmp_idx ON employee USING bitmap
(gender);Создать индекс по выражению lower(title), чтобы разрешить регистронезависимые поиски:
CREATE INDEX ON films ((lower(title)));(В этом примере мы решили опустить имя индекса, поэтому система выберет имя, обычно films_lower_idx.)
Создать индекс с использованием нестандартных правил сортировки:
CREATE INDEX title_idx_german ON films (title COLLATE "de_DE");Создать индекс с нестандартным коэффициентом заполнения:
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 по свойству point, чтобы эффективно использовать оператор 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 является расширением языка базы данных YMatrix. В стандарте SQL нет положений, касающихся индексов.