Documentation PostgreSQL 14.22 » Langage SQL » Index » Types d'index

Précédent | Niveau supérieur | Suivant
11.1. Introduction | Sommaire | 11.3. Index multicolonnes

## 11.2. Types d'index

PostgreSQL propose plusieurs types d'index : B-tree, Hash, GiST, SP-GiST, GIN, BRIN et l'extension bloom. Chaque type d'index utilise un algorithme différent qui convient à un type particulier de clauses indexables. Par défaut, la commande `CREATE INDEX` crée un index B-tree, ce qui convient dans la plupart des situations. Les autres types d'index sont sélectionnés en écrivant le mot clé `USING` suivi du nom du type d'index. Par exemple, pour créer un index hash :

    CREATE INDEX name ON table USING HASH (column);

### 11.2.1. B-Tree

Les index B-tree savent traiter les requêtes d'égalité et par tranches sur des données qu'il est possible de trier. En particulier, l'optimiseur de requêtes de PostgreSQL considère l'utilisation d'un index B-tree lorsqu'une colonne indexée est utilisée dans une comparaison qui utilise un de ces opérateurs :

    <   <=   =   >=   >

Les constructions équivalentes à des combinaisons de ces opérateurs, comme `BETWEEN` et `IN`, peuvent aussi être implémentées avec une recherche par index B-tree. Une condition `IS NULL` ou `IS NOT NULL` sur une colonne indexée peut aussi être utilisée avec un index B-tree.

L'optimiseur peut aussi utiliser un index B-tree pour des requêtes qui utilisent les opérateurs de recherche de motif `LIKE` et `~` si le motif est une constante et se trouve au début de la chaîne à rechercher -- par exemple, `col LIKE 'foo%'` ou `col ~ '^foo'`, mais pas `col LIKE '%bar'`. Toutefois, si la base de données n'utilise pas la locale C, il est nécessaire de créer l'index avec une classe d'opérateurs spéciale pour supporter l'indexation à correspondance de modèles. Voir la Section 11.10 ci-dessous. Il est aussi possible d'utiliser des index B-tree pour `ILIKE` et `~*`, mais seulement si le modèle débute par des caractères non alphabétiques, c'est-à-dire des caractères non affectés par les conversions majuscules/minuscules.

Les index B-tree peuvent aussi être utilisés pour récupérer des données triées. Ce n'est pas toujours aussi rapide qu'un simple parcours séquentiel suivi d'un tri, mais c'est souvent utile.

### 11.2.2. Hash

Les index Hash enregistrent un code de hachage sur 32 bits dérivé de la valeur d'une colonne indexée. De ce fait, de tels index peuvent seulement gérer des comparaisons simples d'égalité. Le planificateur de requêtes considère l'utilisation d'un index hash quand une colonne indexée est impliquée dans une comparaison avec l'opérateur d'égalité :

    =

### 11.2.3. GiST

Les index GiST ne constituent pas un unique type d'index, mais plutôt une infrastructure à l'intérieur de laquelle plusieurs stratégies d'indexage peuvent être implémentées. De cette façon, les opérateurs particuliers avec lesquels un index GiST peut être utilisé varient en fonction de la stratégie d'indexage (la classe d'opérateur). Par exemple, la distribution standard de PostgreSQL inclut des classes d'opérateurs GiST pour plusieurs types de données géométriques à deux dimensions, qui supportent des requêtes indexées utilisant ces opérateurs :

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

Voir la Section 9.11 pour connaître la signification de ces opérateurs. Les classes d'opérateurs GiST incluses dans la distribution standard sont documentées dans Tableau 65.1. Beaucoup de classes d'opérateurs GiST sont disponibles dans l'ensemble des `contrib` ou comme projet séparé. Pour plus d'informations, voir Chapitre 65.

Les index GiST sont aussi capables d'optimiser des recherches du type « voisin-le-plus-proche » (nearest-neighbor), comme :

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

qui trouve les dix places les plus proches d'une cible donnée. Cette fonctionnalité dépend de nouveau de la classe d'opérateurs utilisée. Dans Tableau 65.1, les opérateurs pouvant être utilisés de cette façon sont listés dans la colonne « Opérateurs de tri ».

### 11.2.4. SP-GiST

Les index SP-GiST, tout comme les index GiST, offrent une infrastructure qui supporte différents types de recherches. SP-GiST permet l'implémentation d'une grande étendue de structures de données non balancées, stockées sur disque comme les quadtrees, les arbres k-d, et les arbres radix. PostgreSQL inclut les classes d'opérateurs SP-GiST pour les points à deux dimensions, qui supportent les requêtes indexées en utilisant les opérateurs suivants :

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

(Voir Section 9.11 pour la signification de ces opérateurs.) Les classes d'opérateurs SP-GiST incluses dans la distribution standard sont documentées dans Tableau 66.1. Pour plus d'informations, voir Chapitre 66.

Comme GiST, SP-GiST supporte les recherches de type « voisin-le-plus-proche ». Pour les classes d'opérateurs SP-GiST qui supportent le tri par distance, l'opérateur correspondant est spécifié dans la colonne « Opérateurs d'ordre » dans Tableau 66.1.

### 11.2.5. GIN

Les index GIN sont des « index inversés » qui sont appropriés quand les valeurs à index contiennent plusieurs valeurs composantes, comme les tableaux. Un index inversé contient une entrée séparée pour chaque valeur composante, et peut gérer efficacement les requêtes testant la présence de valeurs composantes spécifiques.

Comme GiST et SP-GiST, GIN supporte différentes stratégies d'indexation utilisateur. Les opérateurs particuliers avec lesquels un index GIN peut être utilisé varient selon la stratégie d'indexation. Par exemple, la distribution standard de PostgreSQL inclut une classe d'opérateurs GIN pour des tableaux à une dimension qui supportent les requêtes indexées utilisant ces opérateurs :

    <@   @>   =   &&

Voir Section 9.19 pour la signification de ces opérateurs. Les classes d'opérateurs GIN incluses dans la distribution standard sont documentées dans Tableau 67.1. Beaucoup d'autres classes d'opérateurs GIN sont disponibles dans les modules `contrib` ou dans des projets séparés. Pour plus d'informations, voir Chapitre 67.

### 11.2.6. BRIN

Les index BRIN (raccourci pour Block Range INdexes) stockent des résumés sur les valeurs enregistrées dans des blocs physiques successifs de la table. De ce fait, ils sont plus efficaces pour les colonnes dont les valeurs sont corrélées avec l'ordre physique des lignes dans la table. Comme GiST, SP-GiST et GIN, BRIN supporte plusieurs stratégies d'indexation, et les opérateurs compatibles avec un index BRIN varient suivant la stratégie d'indexation. Pour les types de données qui ont un ordre de tri linéaire, la donnée indexée correspond aux valeurs minimale et maximale des valeurs de la colonne pour chaque ensemble de blocs. Cela supporte les requêtes indexées utilisant ces opérateurs :

    <   <=   =   >=   >

Les classes d'opérateurs BRIN incluses dans la distribution standard sont documentées dans Tableau 68.1. Pour plus d'informations, voir Chapitre 68.