Описание

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

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

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

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

Если в команде присутствует предложение WHERE, она создаёт частичный индекс. Такой индекс содержит записи только для части таблицы, обычно более полезной для индексации, чем остальная таблица. Например, если таблица содержит информацию об оплаченных и неоплаченных счетах, при этом последних сравнительно немного, но именно эта часть таблицы наиболее востребована, то увеличить быстродействие можно, создав индекс только по этой части. Ещё одно возможное применение WHERE — добавив UNIQUE, обеспечить уникальность в подмножестве таблицы. Подробнее это рассматривается в Разделе 11.8.

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

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

Параметры

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

Примечания

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

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

Для каждого столбца индекса можно задать класс операторов и дополнительные параметры класса. Данный класс определяет, какие операторы будут использоваться индексом для этого столбца. Например, индекс-B-дерево по четырёхбайтовым целым будет использовать класс int4_ops; этот класс операторов включает функции сравнения для таких значений. На практике обычно достаточно использовать класс операторов по умолчанию для типа данных столбца. Существование классов операторов объясняется в первую очередь тем, что для некоторых типов данных можно предложить более одного осмысленного порядка сортировки. Например, может возникнуть желание отсортировать комплексные числа как по абсолютному значению, так и по вещественной части. Это можно сделать, определив два класса операторов для типа данных и выбрав подходящий класс при создании индекса. За дополнительными сведениями о классах операторов обратитесь к Разделу 11.10 и Разделу 40.16.

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

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

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

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

Postgres Pro может строить индексы, задействуя несколько процессоров для ускорения обработки строк таблицы. Это называется параллельным построением индексов. Для методов индексов, поддерживающих построение в параллельном режиме (в настоящее время это только B-дерево), параметр 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 не влияет на определение количества параллельных исполнителей. Если параметру parallel_workers в ALTER TABLE присваивается 0, параллельное построение индексов для этой таблицы полностью отключается.

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

Для удаления индекса применяется DROP INDEX.

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

В предыдущих выпусках Postgres Pro также поддерживался метод индекса 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 является языковым расширением Postgres Pro. Средства обеспечения индексов в стандарте SQL не описаны.

См. также