CREATE INDEX

Описание

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

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

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

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

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

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

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

Параметры

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

Замечания

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

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

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

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

Для большинства методов индексов скорость создания индекса зависит от значения maintenance_work_mem. При увеличении этого значения индекс будет создаваться быстрее, пока оно не превысит действительно доступный объём памяти, что приведёт к использованию подкачки. Для хеш-индексов значение effective_cache_size также влияет на время создания индекса: PostgreSQL будет использовать один из двух различных методов создания индекса, в зависимости от того, больше или меньше оцениваемый размер индекса, чем effective_cache_size. Для наилучших результатов этот параметр нужно устанавливать в зависимости от объёма свободной памяти, но так, чтобы сумма maintenance_work_mem и effective_cache_size была меньше объёма ОЗУ за вычетом объёма, необходимого другим программам.

Для удаления индекса применяется 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 не описаны.

См. также