CREATE INDEX
Определяет новый индекс.
Синтаксис
CREATE [UNIQUE] INDEX [<name>] ON <table_name> [USING <method>]
( {<column_name> | (<expression>)} [COLLATE <collation>] [<opclass>] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ WITH ( <storage_parameter> = <value> [, ... ] ) ]
[ TABLESPACE <tablespace_name> ]
[ WHERE <predicate> ]Описание
Команда CREATE INDEX создает индекс для указанного столбца (или столбцов) указанной таблицы или материализованного представления.
Индексы используются в основном для повышения производительности базы данных (при этом их ненадлежащее использование может наоборот привести к снижению производительности).
Ключевые поля индекса задаются именами столбцов или выражениями, записанными в скобках. Если метод индексирования поддерживает многостолбцовые индексы, можно указать несколько полей.
Поле индекса может быть выражением, вычисляемым на основе значений одного или нескольких столбцов строки таблицы.
Этот механизм можно использовать для получения быстрого доступа к данным на основе некоторого преобразования базовых данных.
Например, индекс, вычисленный для upper(col), позволит использовать индекс в выражении WHERE upper(col) = 'JIM'.
Greengage DB предоставляет методы индексирования B-tree, bitmap, GiST, SP-GiST и GIN. Пользователи также могут определять свои собственные методы индексирования, но это довольно сложно.
При наличии выражения WHERE создается частичный индекс.
Частичный индекс — это индекс, содержащий записи только для части таблицы, обычно той части, которая более полезна для индексирования, чем остальная часть таблицы.
Например, если у вас есть таблица, содержащая оплаченные и неоплаченные заказы, при этом неоплаченные заказы составляют небольшую долю от общей таблицы и их запрашивают чаще всего, вы можете повысить производительность, создав индекс только для этой части.
Выражение WHERE может относиться только к таблице, на основе которой создается индекс, при этом можно использовать все столбцы таблицы, а не только включаемые в индекс.
Нельзя использовать подзапросы и агрегатные выражения в WHERE.
Те же ограничения применяются к полям индекса, которые являются выражениями.
Все функции и операторы, используемые в определении индекса, должны быть неизменяемыми (immutable).
Их результат должен зависеть исключительно от аргументов, а не от внешних факторов (например, содержимого другой таблицы или значений параметров).
Это ограничение гарантирует, что поведение индекса четко определено.
Чтобы использовать пользовательскую функцию в выражении индекса или выражении WHERE, необходимо при ее создании пометить функцию как IMMUTABLE.
Параметры
| Параметр | Описание |
|---|---|
UNIQUE |
Проверяет наличие дублирующихся значений в таблице при создании индекса и при каждом добавлении данных. Дублирующиеся записи вызовут ошибку. Уникальные индексы применяются только к индексам B-tree. В Greengage DB уникальные индексы разрешены только в том случае, если столбцы ключа индекса совпадают с ключом распределения (или являются его надмножеством). В партиционированных таблицах уникальный индекс поддерживается только в пределах отдельной партиции, но не между всеми партициями |
name |
Имя создаваемого индекса. Индекс всегда создается в той же схеме, что и его родительская таблица. Если имя не указано, Greengage DB выбирает подходящее имя на основе имени родительской таблицы и имен индексируемых столбцов |
table_name |
Имя (опционально указанное со схемой) индексируемой таблицы |
method |
Имя используемого метода индексирования.
Доступные варианты: В настоящее время только методы индексирования B-tree, bitmap, GiST и GIN поддерживают многостолбцовые индексы. По умолчанию можно указать до 32 полей. Только B-tree в настоящее время поддерживает уникальные индексы. GPORCA поддерживает только индексы B-tree, bitmap, GiST и GIN. GPORCA игнорирует индексы, созданные с использованием неподдерживаемых методов индексирования |
column_name |
Имя столбца таблицы, для которого создается индекс |
expression |
Выражение на основе одного или нескольких столбцов таблицы. Выражение обычно должно записываться в окружающих скобках, как показано в синтаксисе. Однако скобки можно опустить, если выражение имеет форму вызова функции |
collation |
Имя правила сортировки (collation), используемого для индекса. По умолчанию индекс использует правило сортировки, объявленное для индексируемого столбца, или правило сортировки результата индексируемого выражения. Индексы с недефолтными правилами сортировки могут быть полезны для запросов, включающих выражения с такими правилами |
opclass |
Имя класса операторов.
Класс операторов определяет операторы, которые будут использоваться индексом для этого столбца.
Например, индекс B-tree для четырехбайтовых целых чисел будет использовать класс |
ASC |
Задает порядок сортировки по возрастанию (используется по умолчанию) |
DESC |
Задает порядок сортировки по убыванию |
NULLS FIRST |
Указывает, что значения null сортируются перед значениями не-null. Это поведение по умолчанию, когда указано |
NULLS LAST |
Указывает, что значения null сортируются после значений не-null. Это поведение по умолчанию, когда |
storage_parameter |
Имя параметра хранения, специфичного для метода индексирования. Каждый метод индексирования имеет свой собственный набор допустимых параметров хранения
ПРИМЕЧАНИЕ
Отключение |
tablespace_name |
Табличное пространство, в котором создается индекс.
Если не указано, используется табличное пространство по умолчанию или |
predicate |
Выражение ограничения для частичного индекса |
Примечания
Для методов индексирования, поддерживающих упорядоченное сканирование (в настоящее время только B-tree), могут быть указаны необязательные выражения ASC, DESC, NULLS FIRST и/или NULLS LAST для изменения порядка сортировки индекса.
Поскольку упорядоченный индекс можно сканировать как в прямом, так и в обратном направлении, создание одностолбцового индекса DESC обычно нецелесообразно — такой порядок сортировки уже доступен при использовании обычного индекса.
Ценность этих опций заключается в том, что можно создавать многостолбцовые индексы, соответствующие порядку сортировки, запрашиваемому запросом со смешанным порядком, таким как SELECT … ORDER BY x ASC, y DESC.
Опции NULLS полезны, если вам нужно поддержать поведение "nulls sort low" вместо поведения по умолчанию "nulls sort high" в запросах, которые зависят от индексов для исключения шагов сортировки.
Для большинства методов индексирования скорость создания индекса зависит от значения параметра maintenance_work_mem.
Более высокие значения сократят время, необходимое для создания индекса, при условии, что вы не сделаете его больше объема реально доступной памяти, что приведет к переходу системы в режим подкачки (swapping).
При создании индекса для партиционированной таблицы индекс распространяется на все дочерние таблицы, созданные в Greengage DB. Создание индекса для таблицы, созданной Greengage DB для использования партиционированной таблицей, не поддерживается.
Уникальные индексы (UNIQUE) разрешены только в том случае, если столбцы индекса совпадают со столбцами ключа распределения Greengage DB (или являются их надмножеством).
Уникальные индексы (UNIQUE) не разрешены для append-optimized таблиц.
Уникальный индекс (UNIQUE) может быть создан для партиционированной таблицы.
Однако уникальность обеспечивается только внутри партиции; уникальность не обеспечивается между партициями.
Например, для партиционированной таблицы с партициями по году и под-партициями по кварталу уникальность обеспечивается только в каждой отдельной под-партициями квартала.
Уникальность не обеспечивается между под-партициями кварталов.
По умолчанию индексы не используются для выражений IS NULL.
Лучший способ использовать индексы в таких случаях — создать частичный индекс с использованием предиката IS NULL.
Индексы bitmap работают лучше всего для столбцов, имеющих от 100 до 100000 уникальных значений.
Для столбца с более чем 100000 уникальных значений производительность и эффективность использования пространства индексом bitmap снижаются.
Размер индекса bitmap пропорционален количеству строк в таблице, умноженному на количество уникальных значений в индексируемом столбце.
Столбцы с менее чем 100 уникальными значениями обычно не получают большой выгоды от любого типа индекса. Например, столбец пола с двумя уникальными значениями для мужского и женского пола — кандидатом для индекса.
В предыдущих версиях Greengage DB также был метод индексирования R-tree.
Этот метод был удален, так как он не имел существенных преимуществ перед методом GiST.
Если указано USING rtree, CREATE INDEX будет интерпретировать это как USING gist.
Для получения дополнительной информации о типе индекса GiST обратитесь к документации PostgreSQL.
Использование хеш-индексов (hash indexes) в Greengage DB отключено.
Примеры
Создать индекс с методом 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");Создать индекс с коэффициентом заполнения (fill factor) не по умолчанию:
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 является расширением языка Greengage DB.
В стандарте SQL не предусмотрены средства для работы с индексами.
Greengage DB не поддерживает одновременное создание индексов (ключевое слово CONCURRENTLY не поддерживается).