Méthodes B-Tree, GIN, GIST, HASH, BLOOM, BRIN...PostgreSQL est incroyablement riche au niveau des possibilités dindexation. Nous avons lembarras du choix, y compris celui de ne PAS créer dindex du tout.
Un index peut aider mais représente aussi une charge importante et il faut toujours évaluer sil est plus nuisible quutile. Il ne faut PAS nécessairement tout indexer comme le montre cet article.
Il peut donc être intéressant, notamment sur les tables de taille importante, de ne pas créer réellement un index afin dévaluer son utilisation par le planner sur une ou plusieurs requêtes. On parle alors dindexation virtuelle ou hypothétique.
Oracle Database inclut cette possibilité avec le mot clé NOSEGMENT à ajouter à la syntaxe normale. PostgreSQL ne propose pas cette fonctionnalité nativement mais, heureusement, elle est accessible via une extension développée par Dalibo appelée hypopg.
Nous allons ici déterminer si un index, créé directement sur la colonne dtn, serait utile pour rechercher les géants dont la date de naissance nest pas connue (dtn is null).
Démonstration avec PostgreSQL 10 beta :
Simple et efficace, lextension hypopg que vous pouvez aussi installer via pgxn permet donc bien de créer virtuellement un index. Cela se passe via une fonction hypopg_create_index prenant comme argument lordre habituel de création dindex.
Dans lexemple, cela a permis de constater quun index sur dtn serait utilisé par loptimiseur (planner) pour une requête particulière et dans un contexte particulier. Petit détail, le nom geants_i1 donné à lindex nest pas pris en compte. Il faut noter celui donné par la fonction afin de le repérer dans les plans dexécution. Ce nest cependant pas une limitation importante.
Il existe dautres fonctions permettant notamment de détruire les index virtuels mais ce nest pas essentiel. En effet, ils ne survivront de toute façon pas à votre session.
Cette extension est une contribution très importante. Elle peut être utilisée directement par les développeurs et les administrateurs mais aussi être intégrée par les outils analysant la charge et suggérant des pistes doptimisation. Cest évidemment le cas de POWA, également développé par Dalibo.
Mise à jour : 17/09/2017