PostgreSQL - Типы индексов

Опубликовано: 30 Марта, 2022

В этой статье мы рассмотрим типы индексов в PostgreSQL и способы их использования.

PostgreSQL имеет 6 основных типов индексов:

Индексы B-дерева
Хеш-индексы
Индексы GIN
BRIN
Индексы GiST
Индексы SP-GiST

Обсудим их кратко.

Индексы B-дерева

B-дерево - это самобалансирующееся дерево, которое поддерживает отсортированные данные и позволяет выполнять поиск, вставку, удаление и последовательный доступ за логарифмическое время.

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

<
<=
=
>=
BETWEEN
IN
IS NULL
IS NOT NULL

Кроме того, планировщик запросов может использовать индекс B-дерева для запросов, которые включают оператор сопоставления с образцом LIKE и ~, если образец является константой и является якорем в начале образца.

Пример:

 имя_столбца НРАВИТСЯ 'foo%' 
column_name НРАВИТСЯ 'bar%' 
имя_столбца ~ '^ foo'

Кроме того, планировщик запросов рассмотрит возможность использования индексов B-дерева для ILIKE и ~ *, если шаблон начинается с неалфавитного символа, т.е. символов, на которые не влияет преобразование верхнего / нижнего регистра.

Хеш-индексы

Индексы хеширования могут обрабатывать только простое сравнение равенства (=). Это означает, что всякий раз, когда индексированный столбец участвует в сравнении с использованием оператора равенства (=), планировщик запросов рассмотрит возможность использования хеш-индекса.

Чтобы создать хеш-индекс, вы используете оператор CREATE INDEX с типом HASH-индекса в предложении USING следующим образом:

 Синтаксис:
CREATE INDEX имя_индекса 
ON имя_таблицы ИСПОЛЬЗУЕТ ХЭШ (индексированный_столбец);

Индексы GIN

GIN расшифровывается как Generalized Inverted Indexes . Обычно его называют GIN.

Индексы GIN наиболее полезны, когда в одном столбце хранится несколько значений, например типы hstore, array, jsonb и range.

BRIN

BRIN расшифровывается как Block Range Indexes . BRIN намного меньше и дешевле в обслуживании по сравнению с индексом B-дерева.

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

Индексы GiST

GiST расшифровывается как Generalized Search Tree . Индексы GiST позволяют строить общие древовидные структуры. Индексы GiST полезны при индексировании геометрических типов данных и полнотекстовом поиске.

Индексы SP-GiST

SP-GiST означает GiST с пространственными разделами . SP-GiST поддерживает многораздельные деревья поиска, которые облегчают разработку широкого спектра различных несбалансированных структур данных. Индексы SP-GiST наиболее полезны для данных, которые имеют естественный элемент кластеризации, а также не являются одинаково сбалансированным деревом, например, для ГИС, мультимедиа, телефонной маршрутизации и IP-маршрутизации.

PostgreSQL