| Документация по PostgreSQL 9.4.1 | |||
|---|---|---|---|
| Пред. | Уровень выше | Глава 11. Индексы | След. |
11.2. Типы индексов
PostgreSQL поддерживает несколько типов индексов: B-дерево, хеш, GiST, SP-GiST и GIN. Для разных типов индексов применяются разные алгоритмы, ориентированные на определённые типы запросов. По умолчанию команда CREATE INDEX создаёт индексы типа B-дерево, эффективные в большинстве случаев.
B-деревья могут работать в условиях на равенство и в проверках диапазонов с данными, которые можно отсортировать в некотором порядке. Точнее, планировщик запросов PostgreSQL может задействовать индекс-B-дерево, когда индексируемая колонка участвует в сравнении с одним из следующих операторов:
| < |
| <= |
| = |
| >= |
| > |
При обработке конструкций, представимых как сочетание этих операторов, например BETWEEN и IN, так же может выполняться поиск по индексу-B-дереву. Кроме того, такие индексы могут использоваться и в условиях IS NULL и IS NOT NULL по индексированным колонкам.
Также оптимизатор может использовать эти индексы в запросах с операторами сравнения по шаблону LIKE и ~, если этот шаблон определяется константой и он привязан к началу строки — например, col LIKE 'foo%' или col ~ '^foo', но не col LIKE '%bar'. Но если ваша база данных использует не локаль C, для поддержки индексирования запросов с шаблонами вам потребуется создать индекс со специальным классом операторов; см. Раздел 11.9. Индексы-B-деревья можно использовать и для ILIKE и ~*, но только если шаблон начинается не с алфавитных символов, то есть символов, не подверженных преобразованию регистра.
B-деревья могут также применяться для получения данных, отсортированных по порядку. Это не всегда быстрее простого сканирования и сортировки, но иногда бывает полезно.
Хеш-индексы работают только с простыми условиями равенства. Планировщик запросов может применить хеш-индекс, только если индексируемая колонка участвует в сравнении с оператором =. Создать такой индекс можно следующей командой:
CREATE INDEX имя ON таблица USING hash (колонка);
| Предостережение |
Операции с хеш-индексами в настоящее время не проходят через WAL, так что после аварийной остановки базы данных может потребоваться перестроить хеш-индексы командой REINDEX. Кроме того, изменения в хеш-индексах после начальной копии не переносятся при потоковой или файловой репликации, так что в последующих запросах они будут давать неправильные ответы. По этим причинам настоятельно рекомендуется не использовать их. |
GiST-индексы представляют собой не просто разновидность индексов, а инфраструктуру, позволяющую реализовать много разных стратегий индексирования. Как следствие, GiST-индексы могут применяться с разными операторами, в зависимости от стратегии индексирования (класса операторов). Например, стандартный дистрибутив PostgreSQL включает классы операторов GiST для нескольких двумерных типов геометрических данных, что позволяет применять индексы в запросах с операторами:
| << |
| &< |
| &> |
| >> |
| <<| |
| &<| |
| |&> |
| |>> |
| @> |
| <@ |
| ~= |
| && |
(Эти операторы описаны в Разделе 9.11.) Классы операторов GiST, включённые в стандартный дистрибутив, описаны в Таблице 56-1. В коллекции contrib можно найти и другие классы операторов GiST, реализованные как отдельные проекты. За дополнительными сведениями обратитесь к Главе 56.
GiST-индексы также могут оптимизировать поиск "ближайшего соседа", например такой:
SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;
который возвращает десять расположений, ближайших к заданной точке. Возможность такого применения индекса опять же зависит от класса используемого оператора. Операторы, которые можно использовать таким образом, перечислены в Таблице 56-1, в колонке "Операторы сортировки".
Индексы SP-GiST, как и GiST, предоставляют инфраструктуру, поддерживающие различные типы поиска. SP-GiST позволяет организовывать на диске самые разные не сбалансированные структуры данных, такие как деревья квадрантов, к-мерные деревья и цифровые деревья. Например, стандартный дистрибутив PostgreSQL включает классы операторов SP-GiST для точек в двумерном пространстве, что позволяет применять индексы в запросах с операторами:
| << |
| >> |
| ~= |
| <@ |
| <^ |
| >^ |
(Эти операторы описаны в Разделе 9.11.) Классы операторов SP-GiST, включённые в стандартный дистрибутив, описаны в Таблице 57-1. За дополнительными сведениями обратитесь к Главе 57.
GIN-индексы представляют собой инвертированные индексы, в которых могут содержаться значения с несколькими ключами, например массивы. Подобно GiST и SP-GiST, индексы GIN могут поддерживать различные определённые пользователем стратегии и в зависимости от них могут применяться с разными операторами. Например, стандартный дистрибутив PostgreSQL включает классы операторов GIN для одномерных массивов, что позволяет применять индексы в запросах с операторами:
| <@ |
| @> |
| = |
| && |
(Эти операторы описаны в Разделе 9.18.) Классы операторов GIN, включённые в стандартный дистрибутив, описаны в Таблице 58-1. В коллекции contrib можно найти и другие классы операторов GIN, реализованные как отдельные проекты. За дополнительными сведениями обратитесь к Главе 58.
| Пред. | Начало | След. |
| Введение | Уровень выше | Составные индексы |
| PostgreSQL 9.4.1 Documentation | |||
|---|---|---|---|
| Prev | Up | Chapter 11. Indexes | Next |
11.2. Index Types
PostgreSQL provides several index types: B-tree, Hash, GiST, SP-GiST and GIN. Each index type uses a different algorithm that is best suited to different types of queries. By default, the CREATE INDEX command creates B-tree indexes, which fit the most common situations.
B-trees can handle equality and range queries on data that can be sorted into some ordering. In particular, the PostgreSQL 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.9 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.
Hash 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 = operator. The following command is used to create a hash index:
CREATE INDEX name ON table USING hash (column);
| Caution |
Hash index operations are not presently WAL-logged, so hash indexes might need to be rebuilt with REINDEX after a database crash if there were unwritten changes. Also, changes to hash indexes are not replicated over streaming or file-based replication after the initial base backup, so they give wrong answers to queries that subsequently use them. For these reasons, hash index use is presently discouraged. |
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 PostgreSQL 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 56-1. Many other GiST operator classes are available in the contrib collection or as separate projects. For more information see Chapter 56.
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 56-1, operators that can be used in this way are listed in the column "Ordering Operators".
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 PostgreSQL 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 57-1. For more information see Chapter 57.
GIN indexes are inverted indexes which can handle values that contain more than one key, arrays for example. 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 PostgreSQL includes GIN operator classes for one-dimensional arrays, which support indexed queries using these operators:
| <@ |
| @> |
| = |
| && |
(See Section 9.18 for the meaning of these operators.) The GIN operator classes included in the standard distribution are documented in Table 58-1. Many other GIN operator classes are available in the contrib collection or as separate projects. For more information see Chapter 58.