PostgreSQLLa base de données la plus sophistiquée au monde.

11.2. Types d'index

PostgreSQL™ propose plusieurs types d'index : B-tree, R-tree, Hash et GiST. Chaque type d'index utilise un algorithme différent qui convient à un type particulier de requêtes. Par défaut, la commande CREATE INDEX créera un index B-tree, ce qui convient dans la plupart des situations. Les index B-tree savent traiter les égalités et les recherches sur des tranches de valeurs des données qui peuvent être triées. En particulier, l'optimiseur de requêtes de PostgreSQL™ essaie d'utiliser un index B-tree lorsque 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 (mais notez que IS NULL n'est pas équivalent à = et n'est pas indexable).

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'. Néanmoins, si votre serveur n'utilise pas la locale C, il vous faudra créer l'index avec une classe d'opérateur spéciale pour supporter l'indexage à correspondance de modèles. Voir la Section 11.8, « Classes d'opérateurs » ci-dessous. Il est aussi possible d'utiliser des index B-tree pour ILIKE et ~* mais seulement si le modèle commence avec des caractères non alphabétiques, c'est-à-dire des caractères non affectés par les conversions majuscules/minuscules.

Les index R-tree sont adaptés aux requêtes sur des données spatiales à deux dimensions. Pour créer un index R-tree, utilisez une commande de la forme :

CREATE INDEX nom ON table USING RTREE (colonne);

L'optimiseur de requêtes de PostgreSQL™ envisagera d'utiliser un index R-tree lorsqu'une colonne indexée fait partie d'une comparaison utilisant l'un de ces opérateurs :

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

Voir la Section 9.10, « Fonctions et opérateurs géométriques » pour connaître la signification de ces opérateurs.

Les index hachés ne supportent que les simples comparaisons d'égalité. L'optimiseur de requêtes envisagera d'utiliser un index haché lorsqu'une colonne indexée fait partie d'une comparaison utilisant l'opérateur =. La commande suivante est utilisée pour créer un index haché:

CREATE INDEX nom ON table USING HASH (colonne);

Les index GiST ne sont pas un seul genre d'index mais plutôt une infrastructure à l'intérieur de laquelle plusieurs stratégies d'indexage peuvent être implémentées. En accord, les opérateurs particuliers avec lesquels un index GiST peut être utilisé varient suivant sur la stratégie d'indexage (la classe d'opérateur). La distribution standard de PostgreSQL™ inclut des classes d'opérateur GiST équivalentes à celles de l'opérateur R-tree, et un grand nombre d'autres classes d'opérateurs GiST sont disponibles dans la collection contrib ou via des projets séparés. Pour plus d'informations, voir le Chapitre 49, Index GiST.

[Note]

Note

Les tests ont montré que les index de hachage de PostgreSQL™ ne sont pas plus performants que les index B-tree, alors que la taille de ces index et que leur temps de construction est bien pire. De plus, les opérations sur les index hachés ne sont pas encore tracées dans les WAL, donc les index de hachage pourraient avoir besoin d'être reconstruits avec REINDEX après un arrêt brutal de la base de données. Pour ces raisons, l'utilisation des index de hachage est actuellement déconseillée.

De façon similaire, les index R-tree ne semblent pas avoir un avantage en terme de performance comparé aux opérations équivalentes sur les index GiST. Comme les index de hachages, ils ne sont pas tracés sur les WAL et pourraient nécessiter un REINDEX après un arrêt brutal de la base de données.

Bien que les problèmes des index de hachage pourraient être corrigés ultérieurement, il est probable que le type d'index R-tree soit supprimé dans une prochaine version. Les utilisateurs sont encouragés à migrer les applications qui utilisent les index R-tree pour qu'elles utilisent des index GiST.