L'utilisation d'index est une façon habituelle d'améliorer 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. 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.
Soit une table définie ainsi :
Constructions UNION, CASE et
constructions relatives
CREATE TABLE test1 (
id integer,
contenu varchar
);
et une application qui utilise beaucoup de requêtes de la forme :
SELECT contenu FROM test1 WHERE id = constante;
Sans préparation, le système doit lire la table test1 dans son intégralité, ligne par ligne, pour trouver toutes les lignes qui correspondent. S'il y a beaucoup de lignes dans test1, et que seules 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 le système doit maintenir un index sur la colonne id, alors il peut utiliser une manière beaucoup plus efficace pour trouver les lignes recherchées. Il se peut qu'il n'ait ainsi qu'à parcourir 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 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 les index qui sont utiles.
La commande suivante peut être utilisé pour créer un index sur la colonne id :
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, on utilise 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 14, 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 à 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 aussi 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). Elles sont bloquées jusqu'à la fin de la construction de l'index. Dans des environnements de production, c'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. Pour plus d'informations, voir la section intitulée « 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.