Les index sont une méthode courante pour augmenter les performances d'une base de données. Un index permet au serveur de bases de données de retrouver une ligne spécifique bien plus rapidement que sans index. Mais les index ajoutent aussi une surcharge au système de base de données dans son ensemble, si bien qu'ils doivent être utilisés avec discernement.
Supposons que nous ayons une table comme celle-ci:
Constructions UNION, CASE et
constructions relatives
CREATE TABLE test1 (
id integer,
contenu varchar
);
et que l'application utilise beaucoup de requêtes de la forme
SELECT contenu FROM test1 WHERE id = constante;
Sans préparation supplémentaire, le système devrait lire la table test1 en entier, ligne par ligne, pour trouver toutes les lignes qui correspondent. S'il y a beaucoup de lignes dans test1, et que seulement quelques lignes correspondent à la requête (peut-être même zéro ou une seule), alors, clairement, la méthode n'est pas efficace. Mais si on a indiqué au système de maintenir un index sur la colonne id, alors il peut utiliser une manière beaucoup plus efficace pour trouver les lignes recherchées. Par exemple, il pourrait n'avoir à parcourir que quelques niveaux d'un arbre de recherche.
Une approche similaire est utilisée dans la plupart des livres autres que ceux de fiction : les termes et concepts qui sont fréquemment recherchés par les lecteurs sont listés par ordre alphabétique à la fin du livre. Le lecteur qui recherche un mot particulier peut facilement parcourir l'index, puis aller directement à la page ou aux pages indiquée(s). De la même façon que l'auteur doit anticiper les sujets que les lecteurs risquent de rechercher, il est de la responsabilité du programmeur de prévoir quels index sont avantageux.
La commande suivante permet de créer un index sur la colonne id dont nous parlons :
CREATE INDEX test1_id_index ON test1 (id);
Le nom test1_id_index peut être choisi librement mais il est conseillé de choisir un nom qui rappelle le but de l'index.
Pour supprimer l'index, utilisez la commande DROP INDEX. Les index peuvent être ajoutés et retirés des tables à tout moment.
Une fois un index créé, aucune intervention supplémentaire n'est nécessaire : le système met à jour l'index lorsque la table est modifiée et utilise l'index dans les requêtes lorsqu'il pense que c'est plus efficace qu'une lecture complète de la table. Il faut néanmoins lancer la commande ANALYZE régulièrement pour permettre à l'optimiseur de requêtes de prendre les bonnes décisions. Voir le Chapitre 13, Conseils sur les performances pour comprendre quand et pourquoi l'optimiseur décide d'utiliser ou de ne pas utiliser un index.
Les index peuvent aussi bénéficier aux commandes UPDATE et DELETE qui ont des conditions de recherche. De plus, les index peuvent être utilisés dans les jointures. Ainsi, un index défini sur une colonne qui fait partie d'une condition de jointure peut accélérer significativement les requêtes avec jointures.
Créer un index sur une grosse table peut prendre beaucoup de temps. Par défaut, PostgreSQL™ autorise la lecture (SELECT) sur la table pendant la création d'un index sur celle-ci mais interdit les écritures (INSERT, UPDATE, DELETE) qui sont bloquées jusqu'à la fin de la construction de l'index. Dans des environnements de production, ceci est souvent inacceptable. Il est possible d'autoriser les écritures en parallèle de la création d'un index mais quelques précautions sont à prendre en considération. Pour plus d'informations, voir Construire des index en parallèle.
Après la création d'un index, le système doit le maintenir synchronisé avec la table. Cela rend plus lourdes les opérations de manipulation de données. C'est pourquoi les index qui sont peu, voire jamais, utilisés doivent être supprimés.