Index composites et partiels

Dans les leçons précédentes, nous avons abordé l'utilisation d'index pour les requêtes SGBD linéaires (WHERE country = 'Italy'). Qu’en est-il lorsque nous cherchons sur deux fronts combinés ?

Par exemple, dans un véritable e-commerce, nous pourrions vouloir rechercher les commandes qui appartiennent à un identifiant client spécifique ET se situent dans une plage de temps donnée : WHERE customer_id = 90 AND placed_at >= '2025-01-01'.

Si vous n'avez qu'un index de base sur customer_id, Postgres trouvera les 50 commandes du client en un rien de temps à l'aide de l'arbre binaire, mais il devra ensuite les parcourir manuellement et séquentiellement, en filtrant paresseusement les dates à l'oeil nu.

Index composites

Nous pouvons demander au SGBD d'ordonner les fichiers du « dictionnaire analytique » non pas selon une seule dimension clé, mais selon plusieurs.

CREATE INDEX idx_orders_customer_date ON orders(customer_id, placed_at);

Un index « composite » traite l'ordre de ses paramètres comme très important : ici les nœuds du B-Tree regroupent et catégorisent rigidement customer_id dans un bloc (le plus discriminant pour la séparation) ; à l'intérieur d'eux vit le sous-arbre se ramifiant avec les différentes valeurs de temps placed_at dans un ordre parfaitement trié.

Cette structure annihile brutalement les clauses WHERE avec plusieurs prédicats imbriqués.

Index partiels (index filtrés)

Chaque index coûte de l'espace disque et du processeur sur les flux d'entrée utilisateur. Et si le e-commerce nécessitait de trier et de récupérer en permanence un filtre, mais seulement pour une minorité d’enregistrements ? (Par exemple : interroger rapidement la vérification des notifications non lues.) Créer un index "is_read" sur les 10 000 000 de notifications est incroyablement fastidieux si "Non lu" n'en représente que 5.

CREATE INDEX idx_unread_notifs ON notifications(user_id)
  WHERE is_read = false; -- Questa aggiuntiva è l'"Indice Parziale"

La base de données construira un B-Tree ultra-rapide qui stocke les clés d'accès rapide à la base de données UNIQUEMENT si l'enregistrement est non lu ! Le disque adorera un index de 5 lignes (3 octets) au lieu d'un index de 10 millions. Les Fast SELECT bénéficient pleinement :

SELECT * FROM notifications WHERE user_id = 2 AND is_read = false;

A ton tour

Exploration des index conditionnels (partiels)