Indexation virtuelle

      Méthodes B-Tree, GIN, GIST, HASH, BLOOM, BRIN...PostgreSQL est incroyablement riche au niveau des possibilités d’indexation. Nous avons l’embarras du choix, y compris celui de ne PAS créer d’index du tout.
      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 d’indexation 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 n’est pas connue (dtn is null).
      Démonstration avec PostgreSQL 10 beta :

-- postgres select version(); version ------------------------------------------------------------------------------------------------------------------- PostgreSQL 10beta4 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit (1 ligne) create table geants( idg serial, dtn timestamp, dtb timestamp, devise varchar(128), berserk boolean, taille smallint ); CREATE TABLE with recursive serie(i, r) as (select 20000, random() UNION ALL select i - 1, random() from serie where i > 10001) insert into geants(dtn, dtb, devise, berserk, taille) select case when r < 0.96 then current_timestamp - (i || ' days')::interval + (trunc(r * 100 + 1) || ' days')::interval end, case when r >= 0.96 then current_timestamp - (i || ' days')::interval + (trunc(r * 100 + 1) || ' days')::interval else current_timestamp - (i || ' days')::interval + (trunc(r * 100 + 6) || ' days')::interval end, upper(md5(random()::text)), case when random() < 0.001 then true else false end, 200 + (trunc(random() * 200 + 1)) from serie; INSERT 0 10000 analyze geants; ANALYZE explain select avg(taille) from geants where dtn is null; QUERY PLAN ---------------------------------------------------------------- Aggregate (cost=214.08..214.09 rows=1 width=32) -> Seq Scan on geants (cost=0.00..213.00 rows=430 width=2) Filter: (dtn IS NULL) (3 lignes) -- root apt-get install postgresql-server-dev-10 apt-get install git cd /tmp git clone https://github.com/dalibo/hypopg cd /tmp/hypopg make make install -- postgres CREATE EXTENSION hypopg; CREATE EXTENSION SELECT * FROM hypopg_create_index('create index geants_i1 on geants(dtn)'); indexrelid | indexname ------------+------------------------- 25626 | <25626>btree_geants_dtn (1 ligne) explain select avg(taille) from geants where dtn is null; QUERY PLAN ----------------------------------------------------------------------------------------------- Aggregate (cost=24.68..24.69 rows=1 width=32) -> Index Scan using <25626>btree_geants_dtn on geants (cost=0.04..23.60 rows=430 width=2) Index Cond: (dtn IS NULL) (3 lignes)

      Un index peut aider mais représente aussi une charge importante et il faut toujours évaluer s’il est plus nuisible qu’utile. Il ne faut PAS nécessairement tout indexer comme le montre cet article.
      Simple et efficace, l’extension 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 l’ordre habituel de création d’index.
      Dans l’exemple, cela a permis de constater qu’un index sur dtn serait utilisé par l’optimiseur (planner) pour une requête particulière et dans un contexte particulier. Petit détail, le nom geants_i1 donné à l’index n’est pas pris en compte. Il faut noter celui donné par la fonction afin de le repérer dans les plans d’exécution. Ce n’est cependant pas une limitation importante.
      Il existe d’autres fonctions permettant notamment de détruire les index virtuels mais ce n’est 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 d’optimisation. C’est évidemment le cas de POWA, également développé par Dalibo.

Mise à jour : 17/09/2017