Описание

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

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

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

PostgreSQL предоставляет следующие методы индексов: 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 полей. (Этот предел можно изменить, пересобрав PostgreSQL.) Уникальные индексы поддерживает только B-дерево.

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

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

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

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

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

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

Примеры

Создание индекса-B-дерева по столбцу title в таблице films:

CREATE UNIQUE INDEX title_idx ON films (title);

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

См. также