Yggdrasil peut-il suffire ?
Chaque SGBD dispose dun optimiseur ou planner lui permettant de déterminer comme exécuter votre requête. Ce nest pas parce quun index peut être utilisé quil le sera. Le choix est basé sur une estimation de coût et le but est bien sûr daméliorer les temps de réponse.
Les optimiseurs dOracle et PostgreSQL savent au moment de faire le choix de passer par un index si un critère élimine beaucoup de lignes ou non. Mais ces SGBD ne se limitent pas à la sélectivité. Ils utilisent en plus ce quOracle définit comme le clustering factor et PostgreSQL comme la corrélation. Cela leur permet de déterminer plus précisément le coût de lecture lors de lexécution si un index est utilisé.
Lors de mes tests sommaires avec SQL Server jai constaté quavec ce SGBD seule la sélectivité compte lorsquil sagit de déterminer si un index doit être utilisé. Loptimiseur est donc logiquement bien plus conservateur lorsque la sélectivité nest pas excellente.
Il existe cependant un cas où il est plus évident que lindex peut être bénéfique même si la sélectivité est médiocre. Si toutes les données à retourner sont dans lindex alors il peut permettre de ne pas lire du tout les données de la table. SQL Server a particulièrement besoin de cette caractéristique. Avec ce SGBD vous pouvez ainsi stocker dans les index les données de colonnes ne participant pas à lindex au lieu davoir un simple pointeur vers la table, lindex devient alors "couvrant". Je ne suis pas spécialiste SQL Server mais jimagine quil ne faut tout de même pas en abuser. Le risque pourrait être de recopier N fois la table au niveau des N index avec une pénalité au niveau des écritures, sans compter le fait que la RAM nest pas extensible à linfini et quil faut bien monter ces pages dindex en cache lorsquelles sont lues.
Le terme dindex couvrant nest pas employé avec Oracle et PostgreSQL. Mais ces SGBD sont capables de ne pas accéder à la table lorsque ce nest pas nécessaire. Etre couvrant nest pas une propriété liée à lindex mais une capacité à lexécution. Le terme employé par PostgreSQL est Index Only Scan. Les Index Only Scan ont été introduits avec la version 9.2 ce qui signifie que TOUTES les versions supportées de PostgreSQL en sont capables en 2017. Démonstration avec PostgreSQL 9.6 :
Ouille pas très convaincant ! Lexécution de la requête dure ici toujours autour de 1s. LIndex Only Scan ne servirait à rien ?!? En fait cette situation est liée au fonctionnement de PostgreSQL (MVCC) et à la nature artificielle de mon test. Dans la réalité la table aurait vécu, reçu des AUTOVACUUM etc. Ici ce nest pas le cas et le nombre de vérifications auprès de la table (Heap Fetches) est exactement le même que celui du nombre de lignes à considérer après filtrage, 540630. Nous allons procéder à un VACUUUM (attention pas un VACUUM FULL !), ce que je vous conseille après un chargement massif décisionnel par exemple, et relancer la requête :
Plus de "heap fetches" nécessaires, les temps de réponse ont ici été divisés par 5. PostgreSQL est bien capable déviter complètement de lire les données de la table lorsque les données requises sont présentes dans un index et le gain peut se révéler très spectaculaire.