11.3. Les index multicolonnes

Un index peut porter sur plus d'une colonne. Par exemple, si vous avez une table de cette forme:

CREATE TABLE test2 (
  majeur int,
  mineur int,
  nom varchar
);

(par exemple, si vous gardez votre répertoire /dev dans une base de données...) et que vous faites fréquemment des requêtes comme:

SELECT nom FROM test2 WHERE majeur = constante AND mineur = constante;

alors il est sans doute souhaitable de définir un index sur les colonnes majeur et mineur ensemble, par exemple avec:,

CREATE INDEX test2_mm_idx ON test2 (majeur, mineur);

Actuellement, seuls les B-trees et les index GiST supportent les index multicolonnes. Jusqu'à 32 colonnes peuvent être indexées. Cette limite peut être modifiée à la compilation de PostgreSQL. Voyez le fichier pg_config_manual.h.

L'optimiseur de requêtes peut utiliser un index multicolonnes pour les requêtes qui utilisent la colonnes la plus à gauche de la définition de l'index, plus un nombre quelconque des colonnes listées à sa droite, sans trou. Par exemple, un index sur (a, b, c) peut être utilisé dans des requêtes utilisant a, b, et c, ou dans des requêtes utilisant à la fois a et b, ou dans des requêtes n'utilisant que a, mais pas dans une requête utilisant une autre combinaison. (Dans une requête utilisant a et c, l'optimiseur pourrait choisir d'utiliser l'index pour a, en traitant c comme une colonne ordinaire non indexée.) Bien sur, chaque colonne doit être utilisée avec les opérateurs appropriés pour le type d'index. Les clauses qui comprennent un autre opérateur ne seront pas prises en compte.

Les index multicolonnes ne peuvent être utilisés que si les clauses des colonnes indexées sont jointes avec AND. Par exemple,

SELECT nom FROM test2 WHERE majeur = constant OR mineur = constant;

ne peut utiliser l'index test2_mm_idx (défini précédemment) sur les deux colonnes. (Il peut néanmoins l'utiliser pour faire une recherche sur la colonne major.)

Les index multicolonnes doivent être utilisés avec parcimonie. La plupart du temps, un index sur une seule colonne est suffisant et économise du temps et de l'espace disque. Les index avec plus de trois colonnes sont rarement utiles, sauf cas très particuliers.