CREATE INDEX

примечание

Эта страница переведена при помощи нейросети GigaChat.

Предупреждение!

Изменено поведение оригинальной команды. Изменения описаны в рамках подраздела «Доработки Pangolin».

CREATE INDEX — создание нового индекса.

Синтаксис

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( column_name [, ...] ) ]
    [ NULLS [ NOT ] DISTINCT ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

Описание

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

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

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

PostgreSQL поддерживает методы индексации: B-tree, hash, GiST, SP-GiST, GIN и BRIN. Пользователь также может определить собственный метод, но это достаточно сложная задача.

Если указано условие WHERE, создается частичный индекс — он включает только те строки таблицы, которые удовлетворяют условию. Это может быть полезно, если интерес представляют лишь определенные данные. Например, можно создать индекс только по необработанным заказам, если они составляют малую часть таблицы, но часто участвуют в запросах. Также WHERE можно применять вместе с UNIQUE, чтобы ограничить уникальность в подмножестве таблицы.

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

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

Доработки Pangolin

Начиная с версии 6.1.0 в СУБД Pangolin добавлен новый параметр:

GLOBAL – параметр для создания глобального индекса поверх механизма B-Tree индексов. Применим только к партиционированным таблицам. Для других видов отношений использование GLOBAL недопустимо и приведет к ошибке. Уникальность глобального индекса задается аналогично неглобальному индексу через параметр UNIQUE.

Подробнее о глобальных индексах описано в разделе «Глобальные индексы и глобальные ограничения на партиционированные таблицы».

Параметры

UNIQUE

Обеспечивает уникальность значений: при создании индекса (если данные уже есть) и при каждом добавлении новых записей проверяется, нет ли дубликатов. Попытка вставить или обновить данные с уже существующим значением вызовет ошибку.

При использовании уникальных индексов к партиционированным таблицам действуют дополнительные ограничения — смотрите раздел CREATE TABLE.

CONCURRENTLY

Позволяет создавать индекс без блокировки операций записи (вставка, обновление, удаление) в таблице. Обычное создание индекса блокирует изменения (но не чтение) в таблице до завершения процесса.

Имеются особенности и ограничения при использовании — смотрите ниже раздел «Неблокирующее построение индексов».

Для временных таблиц CREATE INDEX всегда выполняется более простым, неблокирующим способом, так как они не могут использоваться никакими другими сеансами.

IF NOT EXISTS

Подавляет ошибку, если индекс с указанным именем уже существует, вместо этого будет выдано уведомление. Однако совпадение имени не гарантирует совпадение структуры с тем, который создавался бы. Имя индекса при указании IF NOT EXISTS обязательно.

INCLUDE

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

Следует с осторожностью добавлять в индекс неключевые столбцы — они увеличивают размер индекса, что может замедлить работу и даже привести к ошибке при вставке, если размер кортежа превышает допустимый предел для данного типа индексов. Кроме того, если неключевой столбец содержится в индексе B-tree, в таком индексе не будет работать исключение дубликатов.

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

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

Поддержка INCLUDE доступна для методов индексации: B-tree, GiST и SP-GiST. При этом значения столбцов, указанных в выражении INCLUDE, включаются только в в листовых страницах индекса (соответствующих строкам таблицы), но не включаются в элементы верхних уровней, используемые для навигации.

name

Указывает имя создаваемого индекса. Имя схемы указывать нельзя — индекс будет создан в той же схеме, что и таблица. Имя должно быть уникальным в рамках схемы. Если имя не указано — имя будет выбрано автоматически, на основе имени таблицы и столбцов.

ONLY

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

table_name

Указывает имя таблицы, при необходимости дополненное схемой, для которой создается индекс.

method

Указывает метод индексации, который будет использоваться. Возможные варианты: btree (по умолчанию), hash, gist, spgist, gin, brin и пользовательские, например bloom.

column_name

Указывает имя столбца таблицы, по которому строится индекс.

expression

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

collation

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

opclass

Указывает класс операторов. Подробнее об этом ниже.

opclass_parameter

Указывает имя параметра для используемого класса операторов. Подробнее об этом ниже.

ASC

Задает порядок сортировки значений в индексе по возрастанию. Значение по умолчанию.

DESC

Задают порядок сортировки значений в индексе по убыванию.

NULLS FIRST

Указывает, что значения NULL в индексе идут первыми (по умолчанию при DESC).

NULLS LAST

Указывает, что значения NULL в индексе идут последними (по умолчанию при ASC).

NULLS DISTINCT
NULLS NOT DISTINCT

Определяет, считаются ли значения NULL различными в уникальном индексе. По умолчанию NULL считаются различными, что позволяет включать несколько NULL в один и тот же столбец уникального индекса.

storage_parameter

Задает параметр хранения, специфичный для метода индексации. Смотрите ниже «Параметры хранения индекса» для получения подробной информации.

tablespace_name

Задает табличное пространство, в котором будет создан индекс. Если не указан, используется default_tablespace, либо temp_tablespaces для временных таблиц.

predicate

Задает выражение ограничения для частичного индекса.

Параметры хранения индекса

Опциональная выражение WITH позволяет указать параметры хранения для индекса. Поддерживаемые параметры зависят от метода индексирования. Общий параметр для методов индексирования B-tree, hash, GiST и SP-GiST:

fillfactor (integer)

Определяет коэффициент заполнения, то есть насколько плотно будут заполняться страницы индекса (в процентах).

Для индексов типа B-tree это означает, что при первоначальной постройке и при добавлении новых наибольших значений ключей листовые страницы будут заполняться только до заданного процента. Если страницы со временем переполняются, они делятся, что приводит к фрагментации индекса на диске. По умолчанию для B-tree установлен fillfactor равный 90, но допускаются значения от 10 до 100.

При создании индексов для таблиц, в которых предполагается значительное количество вставок или обновлений, имеет смысл использовать пониженный коэффициент заполнения (например, от 50 до 90). Это помогает уменьшить частоту разбиений страниц на раннем этапе жизни индекса, а в некоторых случаях даже снизить их общее число (хотя эффект зависит от характера нагрузки). Также стоит учитывать, что механизм удаления индекса снизу вверх в B-tree требует свободного места для хранения промежуточных версий кортежей, поэтому фактор заполнения может оказать влияние на его работу — пусть и несущественное.

Иногда бывает полезно установить fillfactor равным 100, чтобы максимально эффективно использовать дисковое пространство. Однако это оправдано только в случае, если таблица не будет изменяться после создания индекса. В противном случае даже редкие изменения могут привести к интенсивному разбиению страниц и снижению производительности.

Другие типы индексов используют коэффициент заполнения по-разному, но с аналогичной целью — контролировать плотность записи. Значения по умолчанию при этом варьируются в зависимости от метода.

Дополнительный параметр для B-tree индексов:

deduplicate_items (boolean)

Определяет, будет ли применяться метод устранения дубликатов в B-tree индексах, описанный в разделе «Исключение дубликатов». Для включения или отключения этой оптимизации можно задать

Примечание

Если отключить deduplicate_items с помощью ALTER INDEX, дубликаты исключаться не будут при будущих вставках. Однако уже существующие кортежи, использующие списки значений, не будут автоматически переведены в обычное представление.

Дополнительный параметр для GiST индексов:

buffering (enum)

Управляет применением буферной стратегии построения индекса, описанной в разделе «Методы построения индекса GiST». Значения:

• OFF — буферизация отключена;
• ON — буферизация включена;
• AUTO — буферизация начально отключена, но включается автоматически, когда размер индекса достигает значения effective_cache_size.

По умолчанию установлено AUTO. Если возможна сортированная сборка индекса, она будет использоваться вместо буферной, за исключением случая, когда явно указано buffering=ON.

Дополнительные параметры для GIN индексов:

fastupdate (boolean)

Управляет использованием механизма быстрой записи, описанного в разделе «Техника быстрого обновления GIN». Это логический параметр: значение

Примечание

Отключение параметра fastupdate через ALTER INDEX предотвращает добавление новых данных в список ожидания, но не очищает уже накопленные записи. Чтобы очистить их, выполните VACUUM таблицы или вызовите функцию gin_clean_pending_list.

gin_pending_list_limit (integer)

Позволяет задать предельный размер списка ожидания в килобайтах через параметр gin_pending_list_limit.

Дополнительные параметры для BRIN индексов:

pages_per_range (integer)

Определяет, сколько блоков таблицы составляет один диапазон для записей в индексе BRIN. Подробности описаны в разделе «Обслуживание индекса». Значение по умолчанию — 128.

autosummarize (boolean)

Инициирует автоматическое обобщение предыдущего диапазона после каждой вставки в новый диапазон если установлено значение

Неблокирующее построение индексов

Создание индекса может мешать нормальной работе базы данных. Обычно PostgreSQL блокирует таблицу от изменений, пока создает индекс: выполняет всю операцию построения индекса за одно сканирование таблицы и любые попытки вставки, обновления или удаления строк в это время будут приостановлены до завершения создания индекса. Это может оказать серьезное влияние на работу производственной базы данных. Для очень больших таблиц построение индекса может занять часы, а даже при меньших объемах создание индекса может заблокировать запись на периоды, которые неприемлемы для производственной системы.

Чтобы избежать блокировки операций записи, PostgreSQL поддерживает создание индексов в фоновом режиме с помощью параметра CONCURRENTLY в команде CREATE INDEX. При использовании этого параметра PostgreSQL должен дважды просканировать таблицу и дождаться завершения всех транзакций, которые могли бы использовать или модифицировать данные, относящиеся к индексу. Такой подход требует больше времени и ресурсов по сравнению с обычным построением индекса, но он позволяет обычным операциям продолжаться во время создания индекса, поэтому этот метод полезен для добавления новых индексов в производственной среде. Конечно, дополнительная нагрузка на процессор и подсистему ввода/вывода, создаваемая при построении индекса, может привести к замедлению других операций.

Во время неблокирующего создания индекс сначала добавляется в системный каталог как «недействительный» в одной транзакции, затем следуют два сканирования таблицы в отдельных транзакциях. Перед каждым сканированием таблицы PostgreSQL ждет завершения всех активных транзакций, которые вносили изменения в таблицу. После второго сканирования система также должна дождаться окончания всех транзакций, получивших снимок перед вторым сканированием, включая транзакции, задействованные на любом этапе неблокирующего построения индексов для других таблиц (это касается частичных индексов и индексов, построенных не просто по столбцам). Только после этого индекс может быть помечен как «действительный» и готов к использованию, после чего команда CREATE INDEX CONCURRENTLY завершается. Однако даже тогда индекс может быть не готов немедленно к применению в запросах: в худшем случае он не будет использоваться, пока существуют транзакции, начатые до начала построения индекса.

Если в процессе построения индекса возникает ошибка (например, взаимоблокировка или нарушение уникальности при создании уникального индекса), команда CREATE INDEX завершится с ошибкой, но в системе останется «недействительный» индекс. Он не будет использоваться в запросах, так как может содержать неполные данные, однако будет по-прежнему добавлять накладные расходы на обновления. Команда \d в psql отобразит такой индекс как INVALID:

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

Если создание индекса завершилось с ошибкой (например, из-за взаимоблокировки или нарушения ограничений уникальности), рекомендуется удалить индекс и повторить попытку с CREATE INDEX CONCURRENTLY. Альтернативный вариант — использовать команду REINDEX INDEX CONCURRENTLY для перестроения индекса.

Следует учитывать, что при неблокирующем создании уникального индекса ограничение уникальности начинает применяться ко всем другим транзакциям уже во время второго сканирования таблицы. Это означает, что ошибки нарушения уникальности могут возникнуть в других запросах еще до того, как индекс станет «действительным», или даже в случае, если создание индекса в итоге завершится неудачей. Более того, если ошибка все же произойдет на втором сканировании, «недействительный» индекс будет продолжать проверять уникальность, несмотря на свой статус.

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

Обычные создания индексов позволяют запускать другие аналогичные процессы параллельно для одной и той же таблицы, тогда как неблокирующее построение индекса может быть только одно в конкретный момент времени для одной таблицы. В любом случае модификация схемы таблицы не допускается во время построения индекса. Еще одно отличие заключается в том, что обычную команду CREATE INDEX можно выполнять внутри транзакционного блока (BEGIN ... COMMIT), тогда как CREATE INDEX CONCURRENTLY нельзя.

Метод неблокирующего построения для индексов партиционированных таблиц в настоящее время не поддерживается. Однако можно создать индексы на каждой отдельной партиции с использованием CONCURRENTLY, а затем построить общий партиционированный индекс уже без параметра CONCURRENTLY. Поскольку итоговая операция создания партиционированного индекса носит чисто метаданные характер (без сканирования данных), это позволяет минимизировать время блокировки на запись в родительскую таблицу.

Примечания

В настоящее время только методы индексации B-tree, GiST, GIN и BRIN поддерживают индексы с несколькими ключевыми столбцами. Возможность использования нескольких ключевых столбцов не зависит от наличия неключевых столбцов INCLUDE. Максимум — 32 столбца, включая как ключевые, так и INCLUDE. Этот предел может быть изменен при построении PostgreSQL. В настоящее время только B-tree поддерживает уникальные индексы.

Для каждого столбца индекса можно указать класс операторов с опциональными параметрами. Он определяет, какие операторы будут использоваться для обработки значений в индексе. К примеру, для индекса B-tree по четырехбайтовым целым числам применяется класс int4_ops, включающий функции сравнения, соответствующие этому типу. Как правило, достаточно использовать операторный класс, предлагаемый по умолчанию для типа данных столбца. Основная причина существования различных классов операторов — возможность определения альтернативных логик сортировки для одного и того же типа. Так, комплексные числа можно упорядочивать либо по модулю, либо по вещественной части, что реализуется с помощью разных операторных классов, выбираемых при создании индекса. Дополнительная информация о классах операторов описана в разделе «Интерфейсы расширений для индексов».

При выполнении CREATE INDEX для партиционированной таблицы PostgreSQL по умолчанию распространяет операцию на все партиции. Система сначала проверяет каждую партицию на наличие соответствующего индекса: если такой найден, он прикрепляется как дочерний к создаваемому родительскому индексу. Если подходящий индекс отсутствует, создатется новый, который автоматически прикрепляется к родительскому. Имена таких индексов в партициях формируются так, как если бы имя не указывалось явно.

Если используется параметр ONLY, рекурсивное создание индексов отключается, и создаваемый индекс помечается как неактивный. Позже его можно активировать через ALTER INDEX ... ATTACH PARTITION, после того как будут созданы соответствующие индексы во всех партициях. Важно отметить, что будущие партиции, созданные через CREATE TABLE ... PARTITION OF, автоматически получат нужный индекс, независимо от того, был ли использован параметр ONLY.

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

PostgreSQL регулярно собирает статистику по всем столбцам таблицы. Обычные (без выражений) индексы могут использовать ее сразу после создания для определения полезности индекса. Для индексов выражений требуется выполнить ANALYZE или дождаться, пока это сделает autovacuum, чтобы проанализировать таблицу и сгенерировать статистику для этих индексов.

На время выполнения команды CREATE INDEX, search_path временно меняется на pg_catalog, pg_temp.

Для большинства методов индексирования скорость создания индекса зависит от параметра maintenance_work_mem. Увеличение этого значения может ускорить процесс построения индекса, при условии, что объем не превышает реально доступную оперативную память — иначе система может начать использовать файл подкачки, что существенно замедлит работу.

PostgreSQL может создавать индексы, используя несколько CPU для ускорения обработки строк таблицы. Такой подход называется параллельным построением индексов. На данный момент параллельный режим поддерживает метод B-tree и BRIN. При этом параметр maintenance_work_mem определяет максимальный объем памяти, доступный для всей операции создания индекса — независимо от количества задействованных рабочих процессов. Обычно необходимость в параллельной обработке и число задействуемых процессов определяются автоматически на основе встроенной модели оценки затрат.

Параллельное построение индекса особенно выигрывает от увеличения maintenance_work_mem в тех случаях, когда последовательное построение либо не ускоряется, либо ускоряется незначительно. Важно помнить, что каждый параллельный процесс должен получить не менее 32 МБ из общего объема maintenance_work_mem, и еще как минимум 32 МБ должны остаться ведущему процессу. Повышение значения max_parallel_maintenance_workers может позволить задействовать больше рабочих процессов и тем самым сократить общее время построения индекса — при условии, что оно не ограничено скоростью дисковой подсистемы. Также необходимо наличие достаточного количества свободных процессорных ядер.

Если задать параметр parallel_workers через ALTER TABLE, это явно укажет, сколько параллельных процессов будет использоваться при выполнении CREATE INDEX для данной таблицы. В этом случае механизм оценки затрат не используется, и параметр maintenance_work_mem уже не влияет на выбор числа исполнителей. Присвоение значению parallel_workers нуля полностью отключает возможность параллельного построения индексов для этой таблицы.

Совет

Сбросьте parallel_workers после его настройки в рамках оптимизации построения индекса. Это позволит избежать непреднамеренных изменений в планах запросов, поскольку parallel_workers влияет на все параллельные сканирования таблиц.

При использовании CREATE INDEX CONCURRENTLY разрешено параллельное построение без специальных ограничений, но только первое сканирование таблицы выполняется параллельно.

Используйте DROP INDEX для удаления индекса.

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

Ранее PostgreSQL поддерживал метод индексации R-tree, но он был удален как избыточный по отношению к GiST. Если указать USING rtree, CREATE INDEX интерпретирует это как USING gist, чтобы упростить миграцию старых баз данных в GiST.

Каждый процесс, выполняющий CREATE INDEX, отображает информацию о прогрессе в представлении pg_stat_progress_create_index. Подробнее описано в разделе «Отчет о ходе выполнения CREATE INDEX».

Примеры

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

CREATE UNIQUE INDEX title_idx ON films (title);

Создание уникального индекса B-tree по столбцу title с неключевыми столбцами director и rating в таблице films:

CREATE UNIQUE INDEX title_idx ON films (title) INCLUDE (director, rating);

Создание индекса B-Tree без исключения дубликатов:

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

Смотрите также

ALTER INDEX, DROP INDEX, REINDEX, «Отчет о ходе выполнения CREATE INDEX»