Index partiels

Pour l'instant, nous avons seulement discuté des colonnes à ajouter dans un index. Avec les index partiels (PostgreSQL) ou filtrés (SQL Server), vous pouvez aussi spécifier les lignes à indexer.

Un index partiel est utile pour les conditions where fréquentes qui utilisent des valeurs constantes, comme un code de statut dans l'exemple suivant :

SELECT message
  FROM messages
 WHERE traite       = 'N'
   AND destinataire = ?

Les requêtes de cette forme sont très habituelles dans les systèmes gérant des queues. La requête récupère tous les messages non traités pour un destinataire particulier. Les messages déjà traités sont rarement intéressants. Dans le cas où il faut les récupérer, ils sont généralement accédés via un critère plus spécifique comme la clé primaire.

Nous pouvons optimiser cette requête avec un index à deux colonnes. En considérant cette requête seule, l'ordre des colonnes n'a pas d'importance car il n'y a pas de conditions à intervalle.

CREATE INDEX messages_atraiter
          ON messages (destinataire, traite)

L'index remplit son but mais il inclut des lignes qui ne seront jamais recherchées (tous les messages qui ont déjà été traités). En raison de la complexité logarithmique, l'index rend la requête très rapide même s'il fait perdre beaucoup d'espace disque.

Avec une indexation partielle, vous pouvez limiter l'index pour qu'il inclue seulement les messages non traités. Pour cela, la syntaxe est très simple : une clause where.

CREATE INDEX messages_atraiter
          ON messages (destinataire)
       WHERE traite = 'N'

L'index contient seulement les lignes qui satisfont la clause where. Dans ce cas particulier, nous pouvons même supprimer la colonne TRAITE car elle vaut toujours 'N'. Cela signifie que l'index est réduit en taille sur deux dimensions : horizontalement car il contient moins de lignes, et verticalement car une colonne a été supprimée.

Du coup, l'index est plus petit. Pour une queue, cela peut même vouloir dire que la taille de l'index reste inchangée bien que la table grossisse sans limite. L'index ne contient pas tous les messages, juste ceux qui n'ont pas été traités.

La clause where d'un index partiel peut devenir arbitrairement complexe. La seule limitation fondamentale est sur les fonctions : vous pouvez seulement utiliser des fonctions déterministes comme partout ailleurs dans la définition d'un index. Néanmoins, SQL Server a des règles plus restrictives et n'autorise ni les fonctions ni l'opérateur OR dans les prédicats d'un index.

Une base de données peut utiliser un index partiel à chaque fois qu'une clause where apparaît dans une requête.

SELECT message
  FROM messages
 WHERE traite = 'N';