PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 12.18 » Langage SQL » Index » Combiner des index multiples

11.5. Combiner des index multiples

Un parcours unique d'index ne peut utiliser que les clauses de la requête qui utilisent les colonnes de l'index avec les opérateurs de sa classe d'opérateurs et qui sont jointes avec AND. Par exemple, étant donné un index sur (a, b), une condition de requête WHERE a = 5 AND b = 6 peut utiliser l'index, mais une requête WHERE a = 5 OR b = 6 ne peut pas l'utiliser directement.

Heureusement, PostgreSQL peut combiner plusieurs index (y compris plusieurs utilisations du même index) pour gérer les cas qui ne peuvent pas être résolus par des parcours d'index simples. Le système peut former des conditions AND et OR sur plusieurs parcours d'index. Par exemple, une requête comme WHERE x = 42 OR x = 47 OR x = 53 OR x = 99 peut être divisée en quatre parcours distincts d'un index sur x, chaque parcours utilisant une des clauses de la requête. Les résultats de ces parcours sont alors assemblés par OR pour produire le résultat. Autre exemple, s'il existe des index séparés sur x et y, une résolution possible d'une requête comme WHERE x = 5 AND y = 6 consiste à utiliser chaque index avec la clause de la requête appropriée et d'assembler les différents résultats avec un AND pour identifier les lignes résultantes.

Pour combiner plusieurs index, le système parcourt chaque index nécessaire et prépare un bitmap en mémoire qui donne l'emplacement des lignes de table qui correspondent aux conditions de l'index. Les bitmaps sont ensuite assemblés avec des opérateurs AND ou OR selon les besoins de la requête. Enfin, les lignes réelles de la table sont visitées et renvoyées. Elles sont visitées dans l'ordre physique parce c'est ainsi que le bitmap est créé ; cela signifie que l'ordre des index originaux est perdu et que, du coup, une étape de tri séparée est nécessaire si la requête comprend une clause ORDER BY. Pour cette raison, et parce que chaque parcours d'index supplémentaire ajoute un temps additionnel, le planificateur choisit quelques fois d'utiliser un parcours d'index simple même si des index supplémentaires sont disponibles et peuvent être utilisés.

Le nombre de combinaisons d'index possibles croît parallèlement à la complexité des applications. Il est alors de la responsabilité du développeur de la base de décider des index à fournir. Il est quelques fois préférable de créer des index multicolonnes, mais il est parfois préférable de créer des index séparés et de s'appuyer sur la fonctionnalité de combinaison des index.

Par exemple, si la charge inclut un mélange de requêtes qui impliquent parfois uniquement la colonne x, parfois uniquement la colonne y et quelques fois les deux colonnes, on peut choisir deux index séparés sur x et y et s'appuyer sur la combinaison d'index pour traiter les requêtes qui utilisent les deux colonnes. On peut aussi créer un index multicolonne sur (x, y). Cet index est typiquement plus efficace que la combinaison d'index pour les requêtes impliquant les deux colonnes, mais, comme discuté dans la Section 11.3, il est pratiquement inutile pour les requêtes n'impliquant que y. Il ne peut donc pas être le seul index. Une combinaison de l'index multicolonne et d'un index séparé sur y est une solution raisonnable. Pour les requêtes qui n'impliquent que x, l'index multicolonne peut être utilisé, bien qu'il soit plus large et donc plus lent qu'un index sur x seul. La dernière alternative consiste à créer les trois index, mais cette solution n'est raisonnable que si la table est lue bien plus fréquemment qu'elle n'est mise à jour et que les trois types de requêtes sont communs. Si un des types de requêtes est bien moins courant que les autres, il est préférable de ne créer que les deux index qui correspondent le mieux aux types communs.