Галерея
7757 8119 8300 8698 8817 9504 9722 9937
Интересные записи
Новое на сайте
Новое

PostgreSQL CREATE INDEX

Платформа PostgreSQL позволяет создавать индексы, отсортированные в восходящем или нисходящем порядке, а также индексы с признаком уникальности (UNIQUE). В реализацию также входит настройка производительности с помощью предложения USING. Синтаксис инструкции CREATE INDEX в PostgreSQL следующий.

CREATE [UNIQUE] INDEX имя_индекса ON имя.таблицы [USING {BTREE | RTREE | HASH}] (имя_функции | (имя_столбца [, …])} [WHERE условие)

Где:

USING [BTREE TREE | HASH | GIST]

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

BTREE

Для оптимизации индекса используются высокопараллельные структуры в виде В-деревьев Лемана-Яо (Lehman-Yao high-concurrency B-trees). Этот метод принимается по умолчанию, если не указан никакой другой. Индексы в виде В-деревьев по умолчанию могут вызываться в операциях сравнения с помощью операторов==. В-деревья поддерживают многостолбцовые индексы.

RTREE

Для оптимизации индекса используются структуры в виде R-деревьев алгоритма квадратичного разделения Гутмана (Guttman’s quadratic-split algorithm R-tree). Индексы в виде R-деревьев могут вызываться в операциях сравнения с помощью операторов «, &,», @, ~= и &&. Индексы в виде R-деревьев должны быть одностолбцовыми.

HASH

Для оптимизации индекса используется линейный алгоритм хеширования Литвина (Litwin’s linear hashing algorithm). Хеш-индексы можно вызывать в операциях сравнения с помощью оператора =. Хеш-индексы должны быть одностолбцовыми.

GIST

Для оптимизации индекса используются обобщенные поисковые деревья (Generalized Index Search Trees, GIST). Индексы GIST могут быть многостолбцовыми.

имя_функции

В качестве основы для значений индекса используется указанная функция, а не столбец базовой таблицы. Индекс на основе функции и обычный индекс на основе столбцов являются взаимоисключающими.

WHERE условие

Определяется конструкция WHERE для условного поиска, которая затем используется для создания частичного индекса (partial index). Частичный индекс содержит записи не для всех строк таблицы, а для выбранного набора строк. При использовании этого параметра вы можете получать интересные эффекты. Например, вы можете совмещать предложения UNIQUE и WHERE и обеспечивать уникальность не для всей таблицы, а только для ее части. Предложение WHERE должно:

  • ссылаться на столбцы базовой таблицы (хотя столбцы не обязательно должны быть столбцами самого индекса);
  • не использовать агрегатные функции;
  • не использовать подзапросы.

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

В следующем примере мы создадим индекс, используя индексный тип GIST. Также мы задействуем свойство уникальности только для издателей, находящихся за пределами США.

CREATE UNIQUE INDEX unq_pub_id ON publishers(pub_name, country)
USING GIST WHERE country ‘USA’;

Источник