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

февраля 08, 2022

PostgreSQL предоставляет несколько типов индексов: B-tree, Hash, GiST, SP-GiST, GIN и BRIN. Каждый тип индекса использует свой алгоритм, который лучше всего подходит для разных типов запросов. По умолчанию команда CREATE INDEX создает индексы B-tree, которые подходят для наиболее распространенных ситуаций. Другие типы индексов выбираются путем написания ключевого слова USING, за которым следует имя типа индекса. Например, чтобы создать хэш-индекс:

CREATE INDEX name ON table USING HASH (column);

B-Tree (B-дерево)

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

<   <=   =   >=   >

Конструкции, эквивалентные комбинациям этих операторов, такие как BETWEEN и IN, также могут быть реализованы с поиском по индексу B-дерева. Кроме того, условие IS NULL или IS NOT NULL для столбца индекса можно использовать с индексом B-дерева.

Оптимизатор также может использовать индекс B-дерева для запросов, включающих операторы сопоставления с шаблоном LIKE и ~, если шаблон является константой и привязан к началу строки — например, col LIKE 'foo%' или col ~ '^foo', но не col LIKE '%bar'. Однако, если ваша база данных не использует локаль C, вам потребуется создать индекс со специальным классом операторов для поддержки индексации запросов на сопоставление с образцом. Также можно использовать индексы B-дерева для ILIKE и ~*, но только если шаблон начинается с неалфавитных символов, то есть символов, на которые не влияет преобразование верхнего/нижнего регистра.

Индексы B-дерева также можно использовать для извлечения данных в отсортированном порядке. Это не всегда быстрее, чем простое сканирование и сортировка, но часто бывает полезно.

Hash

Хэш-индексы хранят 32-битный хэш-код, полученный из значения индексированного столбца. Следовательно, такие индексы могут обрабатывать только простые сравнения на равенство. Планировщик запросов рассмотрит возможность использования хеш-индекса всякий раз, когда индексированный столбец участвует в сравнении с использованием оператора равенства:

GiST

Индексы GiST — это не отдельный вид индексов, а скорее инфраструктура, в рамках которой можно реализовать множество различных стратегий индексирования. Соответственно, конкретные операторы, с которыми можно использовать индекс GiST, различаются в зависимости от стратегии индексирования (класса операторов). Например, стандартный дистрибутив PostgreSQL включает классы операторов GiST для нескольких двумерных геометрических типов данных, которые поддерживают индексированные запросы с использованием следующих операторов:

<<   &<   &>   >>   <<|   &<|   |&>   |>>   @>   <@   ~=   &&

Индексы GiST также способны оптимизировать поиск "ближайшего соседа", например

SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;

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

SP-GiST

Индексы SP-GiST, как и индексы GiST, предлагают инфраструктуру, поддерживающую различные виды поиска. SP-GiST позволяет реализовать широкий спектр различных несбалансированных структур данных на основе дисков, таких как деревья квадрантов, k-d-деревья и поразрядные деревья (radix trees). Например, стандартный дистрибутив PostgreSQL включает классы операторов SP-GiST для двумерных точек, которые поддерживают индексированные запросы с использованием следующих операторов:

<<   >>   ~=   <@   <<|   |>>

Как и GiST, SP-GiST поддерживает поиск по методу ближайшего соседа для классов операторов SP-GiST, которые поддерживают дистанционное упорядочение.

GIN

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

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

<@   @>   =   &&

BRIN

Индексы BRIN (сокращение от Block Range INdexes) хранят сводки о значениях, хранящихся в последовательных диапазонах физических блоков таблицы. Таким образом, они наиболее эффективны для столбцов, значения которых хорошо коррелируют с физическим порядком строк таблицы. Подобно GiST, SP-GiST и GIN, BRIN может поддерживать множество различных стратегий индексирования, и конкретные операторы, с которыми может использоваться индекс BRIN, различаются в зависимости от стратегии индексирования. Для типов данных с линейным порядком сортировки индексированные данные соответствуют минимальному и максимальному значениям значений в столбце для каждого диапазона блоков. Это поддерживает индексированные запросы с использованием следующих операторов:

<   <=   =   >=   >

Поиск по этому блогу

Alex tools