Un index peut porter sur plusieurs colonnes d'une table. Soit, par exemple, une table de la forme :
CREATE TABLE test2 ( majeur int, mineur int, nom varchar );
(cas d'un utilisateur gardant son répertoire
/dev
dans une base de données...)
et que des requêtes comme :
SELECT nom FROM test2 WHERE majeur =constante
AND mineur =constante
;
sont fréquemment exécutées. Il peut alors être souhaitable de définir un
index qui porte sur les deux colonnes
majeur
et
mineur
. Ainsi :
CREATE INDEX test2_mm_idx ON test2 (majeur, mineur);
Actuellement, seuls les types d'index B-tree, GiST, GIN et BRIN supportent
les index multicolonnes. Qu'il puisse y avoir plusieurs colonnes clés est
indépendant de si les colonnes INCLUDE
peuvent être
ajoutées à l'index. Les index peuvent avoir jusqu'à 32 colonnes en
incluant les colonnes INCLUDE
. Cette limite peut être
modifiée à la compilation de PostgreSQL. Voir
le fichier pg_config_manual.h
.
Un index B-tree multicolonne peut être utilisé avec des conditions de requêtes impliquant un sous-ensemble quelconque de colonnes de l'index. L'index est toutefois plus efficace lorsqu'il y a des contraintes sur les premières colonnes (celles de gauche). La règle exacte est la suivante : les contraintes d'égalité sur les premières colonnes, et toute contrainte d'inégalité sur la première colonne qui ne possède pas de contrainte d'égalité sont utilisées pour limiter la partie parcourue de l'index. Les contraintes sur les colonnes à droite de ces colonnes sont vérifiées dans l'index, et limitent ainsi les visites de la table, mais elles ne réduisent pas la partie de l'index à parcourir.
Par exemple, avec un index sur
(a, b, c)
et une condition de requête WHERE a =
5 AND b >= 42 AND c < 77
, l'index est parcouru à partir de
la première entrée pour laquelle a
= 5 et b
= 42 jusqu'à la
dernière entrée pour laquelle a
= 5. Les entrées de l'index avec
c
>= 77 sont sautées, mais elles sont toujours
parcourues. En principe, cet index peut être utilisé pour les requêtes
qui ont des contraintes sur b
et/ou c
sans contrainte
sur a
-- mais l'index entier doit être parcouru, donc, dans
la plupart des cas, le planificateur préfère un parcours séquentiel de la
table à l'utilisation de l'index.
Un index GiST multicolonne peut être utilisé avec des conditions de requête qui impliquent un sous-ensemble quelconque de colonnes de l'index. Les conditions sur des colonnes supplémentaires restreignent les entrées renvoyées par l'index, mais la condition sur la première colonne est la plus importante pour déterminer la part de l'index parcourue. Un index GiST est relativement inefficace si sa première colonne n'a que quelques valeurs distinctes, même s'il y a beaucoup de valeurs distinctes dans les colonnes supplémentaires.
Un index multicolonne GIN peut être utilisé avec des conditions de requête qui impliquent tout sous-ensemble des colonnes de l'index. Contrairement à B-tree ou GiST, la qualité de la recherche dans l'index est identique quelles que soient les colonnes de l'index que la requête utilise.
Un index BRIN multicolonne peut être utilisé avec des conditions dans la
requête qui impliquent tout sous-ensemble de colonnes dans l'index. Comme
GIN et contrairement à B-tree ou GiST, l'efficacité de la recherche par
l'index est la même quelles que soient les colonnes utilisées dans les conditions
de la requête. La seule raison d'avoir plusieurs index BRIN au lieu d'un
index BRIN multicolonne sur une table est d'avoir un paramétrage de
stockage pages_per_range
différent.
Chaque colonne doit évidemment être utilisée avec des opérateurs appropriés au type de l'index ; les clauses qui impliquent d'autres opérateurs ne sont pas prises en compte.
Il est préférable d'utiliser les index multicolonnes avec parcimonie. Dans la plupart des cas, un index sur une seule colonne est suffisant et préserve espace et temps. Les index de plus de trois colonnes risquent fort d'être inefficaces, sauf si l'utilisation de cette table est extrêmement stylisée. Voir aussi la Section 11.5 et Section 11.9 pour les discussions sur les mérites des différentes configurations d'index.