Создаёт новый индекс.
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-tree, bitmap, GiST, SP-GiST и GIN. Пользователи также могут определять собственные методы индексирования, хотя это довольно сложная задача.
Если указано условие WHERE, создается частичный индекс. Частичный индекс содержит записи только для подмножества таблицы, которое часто оказывается более полезным, чем остальная часть таблицы. Например, если у вас есть таблица, содержащая как выставленные, так и невыставленные заказы, причём невыставленные заказы составляют небольшую часть от общего количества, но часто запрашиваются, то создание индекса только по этому подмножеству может повысить производительность.
Выражения, используемые в условии WHERE, могут ссылаться только на столбцы базовой таблицы, однако они могут использовать все столбцы, а не только проиндексированные. Подзапросы и агрегатные выражения запрещены в WHERE. Те же ограничения применяются к полям индекса, являющимся выражениями.
Все функции и операторы, используемые в определении индекса, должны быть иммутабельными (immutable). Их результаты должны зависеть исключительно от параметров и не должны зависеть от внешних факторов, таких как содержимое другой таблицы или значения параметров. Это обеспечивает корректное поведение индекса. При использовании пользовательских функций в выражениях индекса или в условиях WHERE необходимо помечать эти функции как IMMUTABLE при их создании.
UNIQUE: Создаёт уникальный индекс и проверяет наличие дублирующихся значений при каждой вставке данных. Дублирующиеся записи вызовут ошибку. Уникальные индексы применимы только к B-tree. В YMatrix уникальный индекс разрешён только в том случае, если столбцы ключа индекса совпадают с распределительным ключом YMatrix (или являются его надмножеством). Для секционированных таблиц уникальные индексы поддерживаются только внутри отдельных секций и не могут охватывать все секции.
CONCURRENTLY: Создаёт индекс без блокировок, которые мешали бы операциям вставки, обновления и удаления. Обычное создание индекса блокирует таблицу для операций записи (хотя чтение остаётся возможным) до завершения. При указании CONCURRENTLY YMatrix избегает блокировки операций записи. Для временных таблиц CREATE INDEX обычно выполняется неконкурентно, поскольку другие сессии не могут получить к ним доступ. Неконкурентное создание индекса имеет меньшие накладные расходы.
name: Имя создаваемого индекса. Индексы всегда создаются в той же схеме, что и родительская таблица. Если имя опущено, YMatrix автоматически сгенерирует подходящее имя на основе имени таблицы и столбцов индекса.
table_name: Имя таблицы, для которой создаётся индекс (может включать имя схемы).
method: Имя метода индексирования. Допустимые значения: btree, bitmap, gist, spgist и gin. По умолчанию используется btree. В настоящее время только B-tree, GiST и GIN поддерживают многоколоночные индексы. По умолчанию можно указать до 32 полей. Только B-tree в настоящее время поддерживает уникальные индексы.
column_name: Имя столбца таблицы, по которому создаётся индекс. Методы B-tree, bitmap, GiST и GIN поддерживают многоколоночные индексы.
expression: Выражение, основанное на одном или нескольких столбцах таблицы. Обычно выражение должно быть заключено в скобки, как показано в синтаксисе. Однако скобки можно опустить, если выражение представляет собой вызов функции.
collation: Имя коллации, используемой индексом. По умолчанию индекс использует коллацию, объявленную для столбца, или результирующую коллацию выражения. Индексы с нестандартной коллацией полезны для запросов, включающих выражения с нестандартной коллацией.
opclass: Имя класса операторов. Класс операторов определяет операторы, которые будут использоваться индексом для данного столбца. Например, B-tree индекс по четырёхбайтовому целому числу будет использовать класс int4_ops. Как правило, класс операторов по умолчанию для типа данных столбца является достаточным. Классы операторов полезны, когда для типа данных существует несколько осмысленных способов упорядочивания. Например, комплексные числа можно упорядочивать по модулю или по действительной части, что требует разных классов операторов.
ASC: Указывает сортировку по возрастанию (по умолчанию).
DESC: Указывает сортировку по убыванию.
NULLS FIRST: Указывает, что значения NULL располагаются перед не-NULL значениями. Это значение по умолчанию, когда указано DESC.
NULLS LAST: Указывает, что значения NULL располагаются после не-NULL значений. Это значение по умолчанию, когда DESC не указано.
storage_parameter: Имя параметра хранения, специфичного для метода индексирования. Каждый метод индексирования имеет свой набор допустимых параметров хранения.
FILLFACTOR: Поддерживается методами B-tree, bitmap, GiST и SP-GiST. Определяет плотность заполнения страниц индекса. Для B-tree листовые страницы заполняются до указанного процента при первоначальном создании и при расширении вправо. Если страницы полностью заполняются, происходит разделение, что постепенно снижает эффективность индекса. По умолчанию для B-tree установлено значение 90, но можно выбрать любое значение от 10 до 100. Для статических таблиц fillfactor 100 минимизирует физический размер, а меньшие значения лучше подходят для сильно изменяемых таблиц. Другие методы индексирования используют fillfactor аналогичным образом, с метод-зависимыми значениями по умолчанию.
BUFFERING: Кроме FILLFACTOR, индексы GiST поддерживают параметр BUFFERING. Он определяет, будет ли использоваться технология буферизации при построении. Значение OFF отключает её; ON включает; AUTO изначально отключает, но включает, как только размер индекса достигнет эффективного размера кэша. По умолчанию используется AUTO.
FASTUPDATE: Метод индексирования GIN поддерживает параметр хранения FASTUPDATE. Этот булев параметр включает или отключает технологию быстрого обновления GIN. Значение ON включает её (по умолчанию), а OFF — отключает. Подробности см. в документации PostgreSQL.
tablespace_name: Табличное пространство, в котором будет создан индекс. Если не указано, используется табличное пространство по умолчанию.
predicate: Выражение-ограничение для частичного индекса.
Для каждого столбца индекса можно указать класс операторов. Класс операторов определяет операторы, которые будут использоваться индексом для этого столбца. Например, B-tree индекс по четырёхбайтовому целому числу будет использовать класс int4_ops. Этот класс содержит функции сравнения для четырёхбайтовых целых чисел. Как правило, класс операторов по умолчанию для типа данных столбца является достаточным. Классы операторов полезны, когда для типа данных существует несколько осмысленных способов упорядочивания. Например, комплексные числа можно упорядочивать по модулю или по действительной части, что требует разных классов операторов.
Для методов индексирования, поддерживающих упорядоченные сканирования (в настоящее время только B-tree), дополнительные параметры ASC, DESC, NULLS FIRST и/или NULLS LAST могут изменять порядок сортировки индекса. Поскольку упорядоченные индексы можно сканировать как вперёд, так и назад, создание одностолбцового индекса с DESC обычно избыточно, так как стандартный индекс может обеспечить нужный порядок сортировки. Ценность этих параметров проявляется при создании многоколоночных индексов, соответствующих порядку сортировки, запрашиваемому запросами со смешанной сортировкой, например SELECT ... ORDER BY x ASC, y DESC. Параметры NULLS полезны для поддержки поведения «NULL в начале» в запросах, использующих индексы для избежания шага сортировки, в противовес поведению по умолчанию «NULL в конце».
Для большинства методов индексирования скорость создания индекса зависит от maintenance_work_mem. Более высокие значения уменьшают время создания индекса, при условии, что они не превышают объём реально доступной памяти, иначе произойдёт свопинг.
После создания индекса на секционированной таблице он распространяется на все подтаблицы, созданные YMatrix. Создание индексов на таблицах, предназначенных для использования с секционированными таблицами, созданными YMatrix, не поддерживается.
Уникальный индекс (UNIQUE) разрешён только в том случае, если столбцы индекса совпадают со столбцами распределительного ключа YMatrix (или являются его надмножеством).
Уникальные индексы не допускаются на таблицах с режимом append-optimized.
Уникальные индексы можно создавать на секционированных таблицах. Однако уникальность гарантируется только внутри каждой секции. Уникальность между секциями не обеспечивается. Например, для секционированной таблицы с секциями по годам и подсекциями по кварталам уникальность гарантируется только внутри каждой квартальной подсекции, но не между ними.
По умолчанию индексы не используются для условий IS NULL. В таких случаях лучший способ использовать индекс — создать частичный индекс с предикатом IS NULL.
Bitmap-индексы работают лучше всего для столбцов с 100–100 000 различных значений. При количестве различных значений более 100 000 эффективность bitmap-индексов по производительности и занимаемому месту снижается. Размер bitmap-индекса пропорционален количеству строк в таблице, умноженному на количество различных значений в индексируемом столбце.
Столбцы с менее чем 100 различными значениями обычно плохо выигрывают от любого типа индекса. Например, столбец пола с двумя значениями (мужской и женский) не является хорошим кандидатом для индексирования.
В предыдущих версиях YMatrix также был метод индексирования R-tree. Этот метод был удалён, поскольку он не даёт существенных преимуществ по сравнению с методом GiST. Указание USING rtree интерпретируется как USING gist командой CREATE INDEX.
Использование hash-индексов отключено в YMatrix.
Создание B-tree индекса по столбцу title таблицы films:
CREATE UNIQUE INDEX title_idx ON films (title);Создание bitmap-индекса по столбцу 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 не предусмотрена директива для создания индексов.