Soit une table définie ainsi :

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ée 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 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.

En général, les index PostgreSQL peuvent être utilisées pour optimiser les requêtes qui contiennent une ou plusieurs clauses WHERE ou JOIN de la forme

colonne-indexee operateur-indexable valeur-comparaison

Ici, la colonne-indexee est n'importe quel colonne ou expression de la définition de l'index. L'operateur-indexable est un opérateur qui est un membre de la classe d'opérateur de l'index pour la colonne indexée. (Plus de détails là-dessus ci-dessous.) et la valeur-comparaison peut être toute expression qui n'est pas volatile et ne fait pas référence à la table de l'index.

Dans certains case, l'optimiseur de requêtes peut extraire une clause indexable de cette forme à partir d'une autre construction SQL. Un exemple simple qui, si la clause originale est

valeur-comparaison operateur colonne-indexee

alors elle peut être inversée dans une forme indexable si l'operateur original a un opérateur de commutation qui est un membre de la classe d'opérateur de l'index.

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 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. Les index peuvent aussi empêcher la création d'enregistrements heap-only tuples. C'est pourquoi les index qui sont peu, voire jamais, utilisés doivent être supprimés.