11.2. Типы индексов
Postgres Pro поддерживает несколько типов индексов: B-дерево, хеш, GiST, SP-GiST, GIN, BRIN и расширение bloom. Для разных типов индексов применяются разные алгоритмы, ориентированные на определённые типы индексируемых предложений. По умолчанию команда CREATE INDEX создаёт индексы-B-деревья, эффективные в большинстве случаев. Выбрать другой тип можно, написав название типа индекса после ключевого слова USING. Например, создать хеш-индекс можно так:
CREATE INDEXимяONтаблицаUSING HASH (столбец);
11.2.1. B-дерево
B-деревья могут работать в условиях на равенство и в проверках диапазонов с данными, которые можно отсортировать в некотором порядке. Точнее, планировщик запросов Postgres Pro может задействовать индекс-B-дерево, когда индексируемый столбец участвует в сравнении с одним из следующих операторов:
< <= = >= >
При обработке конструкций, представимых как сочетание этих операторов, например BETWEEN и IN, так же может выполняться поиск по индексу-B-дереву. Кроме того, такие индексы могут использоваться и в условиях IS NULL и IS NOT NULL по индексированным столбцам.
Также оптимизатор может использовать эти индексы в запросах с операторами сравнения по шаблону LIKE и ~, если этот шаблон определяется константой и он привязан к началу строки — например, col LIKE 'foo%' или col ~ '^foo', но не col LIKE '%bar'. Но если ваша база данных использует не локаль C, для поддержки индексирования запросов с шаблонами вам потребуется создать индекс со специальным классом операторов; см. Раздел 11.10. Индексы-B-деревья можно использовать и для ILIKE и ~*, но только если шаблон начинается не с алфавитных символов, то есть символов, не подверженных преобразованию регистра.
B-деревья могут также применяться для получения данных, отсортированных по порядку. Это не всегда быстрее простого сканирования и сортировки, но иногда бывает полезно.
Индексы B-дерево поддерживают также оптимизацию поиска «ближайших соседей». Например, запрос:
SELECT * FROM events ORDER BY event_date <-> date '2017-05-05' LIMIT 10;
находит десять событий, ближайших к заданной дате. Возможность этого поиска тоже может быть ограничена в зависимости от применяемого класса операторов.
11.2.2. Хеш
Хеш-индексы хранят 32-битный хеш-код, полученный из значения индексированного столбца, поэтому хеш-индексы работают только с простыми условиями равенства. Планировщик запросов может применить хеш-индекс, только если индексируемый столбец участвует в сравнении с оператором =. Создать такой индекс можно следующей командой:
CREATE INDEXимяONтаблицаUSING HASH (столбец);
11.2.3. GiST
GiST-индексы представляют собой не просто разновидность индексов, а инфраструктуру, позволяющую реализовать много разных стратегий индексирования. Как следствие, GiST-индексы могут применяться с разными операторами, в зависимости от стратегии индексирования (класса операторов). Например, стандартный дистрибутив Postgres Pro включает классы операторов GiST для нескольких двумерных типов геометрических данных, что позволяет применять индексы в запросах с операторами:
<< &< &> >> <<| &<| |&> |>> @> <@ ~= &&
(Эти операторы описаны в Разделе 9.11.) Классы операторов GiST, включённые в стандартный дистрибутив, описаны в Таблице 68.1. В коллекции contrib можно найти и другие классы операторов GiST, реализованные как отдельные проекты. За дополнительными сведениями обратитесь к Главе 68.
GiST-индексы также могут оптимизировать поиск «ближайшего соседа», например такой:
SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;
который возвращает десять расположений, ближайших к заданной точке. Возможность такого применения индекса опять же зависит от класса используемого оператора. Операторы, которые можно использовать таким образом, перечислены в Таблице 68.1, в столбце «Операторы сортировки».
11.2.4. SP-GiST
Индексы SP-GiST, как и GiST, предоставляют инфраструктуру, поддерживающую различные типы поиска. SP-GiST позволяет организовывать на диске самые разные несбалансированные структуры данных, такие как деревья квадрантов, k-мерные и префиксные деревья. Например, стандартный дистрибутив Postgres Pro включает классы операторов SP-GiST для точек в двумерном пространстве, что позволяет применять индексы в запросах с операторами:
<< >> ~= <@ <<| |>>
(Эти операторы описаны в Разделе 9.11.) Классы операторов SP-GiST, включённые в стандартный дистрибутив, описаны в Таблице 69.1. За дополнительными сведениями обратитесь к Главе 69.
Индексы SP-GiST, как и GiST, поддерживают поиск ближайших соседей. Для классов операторов SP-GiST, поддерживающих упорядочивание по расстоянию, соответствующий оператор указан в столбце «Операторы упорядочивания» в Таблице 69.1.
11.2.5. GIN
GIN-индексы представляют собой «инвертированные индексы», в которых могут содержаться значения с несколькими ключами, например массивы. Инвертированный индекс содержит отдельный элемент для значения каждого компонента, и может эффективно работать в запросах, проверяющих присутствие определённых значений компонентов.
Подобно GiST и SP-GiST, индексы GIN могут поддерживать различные определённые пользователем стратегии и в зависимости от них могут применяться с разными операторами. Например, стандартный дистрибутив Postgres Pro включает класс операторов GIN для массивов, что позволяет применять индексы в запросах с операторами:
<@ @> = &&
(Эти операторы описаны в Разделе 9.19.) Классы операторов GIN, включённые в стандартный дистрибутив, описаны в Таблице 70.1. В коллекции contrib и в отдельных проектах можно найти и много других классов операторов GIN. За дополнительными сведениями обратитесь к Главе 70.
11.2.6. BRIN
BRIN-индексы (сокращение от Block Range INdexes, Индексы зон блоков) хранят обобщённые сведения о значениях, находящихся в физически последовательно расположенных блоках таблицы. Поэтому такие индексы наиболее эффективны для столбцов, значения в которых хорошо коррелируют с физическим порядком столбцов таблицы. Подобно GiST, SP-GiST и GIN, индексы BRIN могут поддерживать определённые пользователем стратегии и в зависимости от них применяться с разными операторами. Для типов данных, имеющих линейный порядок сортировки, записям в индексе соответствуют минимальные и максимальные значения данных в столбце для каждой зоны блоков. Это позволяет поддерживать запросы со следующими операторами:
< <= = >= >
Классы операторов BRIN, включённые в стандартный дистрибутив, описаны в Таблице 71.1. За дополнительными сведениями обратитесь к Главе 71.
11.2. Index Types
Postgres Pro provides several index types: B-tree, Hash, GiST, SP-GiST, GIN, BRIN, and the extension bloom. Each index type uses a different algorithm that is best suited to different types of indexable clauses. By default, the CREATE INDEX command creates B-tree indexes, which fit the most common situations. The other index types are selected by writing the keyword USING followed by the index type name. For example, to create a Hash index:
CREATE INDEXnameONtableUSING HASH (column);
11.2.1. B-Tree
B-trees can handle equality and range queries on data that can be sorted into some ordering. In particular, the Postgres Pro query planner will consider using a B-tree index whenever an indexed column is involved in a comparison using one of these operators:
< <= = >= >
Constructs equivalent to combinations of these operators, such as BETWEEN and IN, can also be implemented with a B-tree index search. Also, an IS NULL or IS NOT NULL condition on an index column can be used with a B-tree index.
The optimizer can also use a B-tree index for queries involving the pattern matching operators LIKE and ~ if the pattern is a constant and is anchored to the beginning of the string — for example, col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar'. However, if your database does not use the C locale you will need to create the index with a special operator class to support indexing of pattern-matching queries; see Section 11.10 below. It is also possible to use B-tree indexes for ILIKE and ~*, but only if the pattern starts with non-alphabetic characters, i.e., characters that are not affected by upper/lower case conversion.
B-tree indexes can also be used to retrieve data in sorted order. This is not always faster than a simple scan and sort, but it is often helpful.
B-tree indexes are also capable of optimizing “nearest-neighbor” searches, such as
SELECT * FROM events ORDER BY event_date <-> date '2017-05-05' LIMIT 10;
which finds the ten events closest to a given target date. The ability to do this is again dependent on the particular operator class being used.
11.2.2. Hash
Hash indexes store a 32-bit hash code derived from the value of the indexed column. Hence, such indexes can only handle simple equality comparisons. The query planner will consider using a hash index whenever an indexed column is involved in a comparison using the equal operator:
=
11.2.3. GiST
GiST indexes are not a single kind of index, but rather an infrastructure within which many different indexing strategies can be implemented. Accordingly, the particular operators with which a GiST index can be used vary depending on the indexing strategy (the operator class). As an example, the standard distribution of Postgres Pro includes GiST operator classes for several two-dimensional geometric data types, which support indexed queries using these operators:
<< &< &> >> <<| &<| |&> |>> @> <@ ~= &&
(See Section 9.11 for the meaning of these operators.) The GiST operator classes included in the standard distribution are documented in Table 68.1. Many other GiST operator classes are available in the contrib collection or as separate projects. For more information see Chapter 68.
GiST indexes are also capable of optimizing “nearest-neighbor” searches, such as
SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;
which finds the ten places closest to a given target point. The ability to do this is again dependent on the particular operator class being used. In Table 68.1, operators that can be used in this way are listed in the column “Ordering Operators”.
11.2.4. SP-GiST
SP-GiST indexes, like GiST indexes, offer an infrastructure that supports various kinds of searches. SP-GiST permits implementation of a wide range of different non-balanced disk-based data structures, such as quadtrees, k-d trees, and radix trees (tries). As an example, the standard distribution of Postgres Pro includes SP-GiST operator classes for two-dimensional points, which support indexed queries using these operators:
<< >> ~= <@ <<| |>>
(See Section 9.11 for the meaning of these operators.) The SP-GiST operator classes included in the standard distribution are documented in Table 69.1. For more information see Chapter 69.
Like GiST, SP-GiST supports “nearest-neighbor” searches. For SP-GiST operator classes that support distance ordering, the corresponding operator is listed in the “Ordering Operators” column in Table 69.1.
11.2.5. GIN
GIN indexes are “inverted indexes” which are appropriate for data values that contain multiple component values, such as arrays. An inverted index contains a separate entry for each component value, and can efficiently handle queries that test for the presence of specific component values.
Like GiST and SP-GiST, GIN can support many different user-defined indexing strategies, and the particular operators with which a GIN index can be used vary depending on the indexing strategy. As an example, the standard distribution of Postgres Pro includes a GIN operator class for arrays, which supports indexed queries using these operators:
<@ @> = &&
(See Section 9.19 for the meaning of these operators.) The GIN operator classes included in the standard distribution are documented in Table 70.1. Many other GIN operator classes are available in the contrib collection or as separate projects. For more information see Chapter 70.
11.2.6. BRIN
BRIN indexes (a shorthand for Block Range INdexes) store summaries about the values stored in consecutive physical block ranges of a table. Thus, they are most effective for columns whose values are well-correlated with the physical order of the table rows. Like GiST, SP-GiST and GIN, BRIN can support many different indexing strategies, and the particular operators with which a BRIN index can be used vary depending on the indexing strategy. For data types that have a linear sort order, the indexed data corresponds to the minimum and maximum values of the values in the column for each block range. This supports indexed queries using these operators:
< <= = >= >
The BRIN operator classes included in the standard distribution are documented in Table 71.1. For more information see Chapter 71.
