Reviers, le 7 avril 2025
Au fil des versions de PostgreSQL, la commande EXPLAIN s’est vu ajouter de
nombreuses options : ANALYZE, VERBOSE, BUFFERS, etc. Lors du développement
de la version 18, Robert Haas a proposé une nouvelle option très intéressante.
Au moment où cet article est écrit, on dénombre pas moins de 12 options pour la commande EXPLAIN :
ANALYZE;VERBOSE;COSTS(apparue en version 9.0) ;SETTINGS(apparue en version 12) ;GENERIC_PLAN(apparue en version 16) ;BUFFERS(apparue en version 9.0) ;SERIALIZE(apparue en version 17) ;WAL(apparue en version 9.0) ;TIMING(apparue en version 9.2) ;SUMMARY(apparue en version 10) ;MEMORY(apparue en version 17) ;FORMAT(apparue en version 9.0).
Nous pourrions penser que c’est suffisant, mais il existe un bon nombre d’autres
informations qu’il serait intéressant d’ajouter, parfois par des extensions.
Pensez par exemple à l’extension de Citus qui ajoute des nœuds spécifiques à un
plan d’exécution. Il pourrait être intéressant que cette extension gère des
options spécifiques sur EXPLAIN pour activer ou non l’affichage d’informations
supplémentaires spécifiques à ses nœuds.
Robert Haas a donc intégré dans PostgreSQL la possibilité que des modules
chargeables dynamiquement gèrent leurs propres options pour la commande
EXPLAIN. Cela fait partie de plusieurs commits, mais les deux principaux
sont :
- Make it possible for loadable modules to add EXPLAIN options
- Add some new hooks so extensions can add details to EXPLAIN
Robert a intégré son propre module, nommé pg_overexplain, dans les modules contrib.
En lisant ceci, il m’est venu l’idée d’écrire mon propre module EXPLAIN pour
tester cette nouvelle fonctionnalité. Le module en question, appelé
pg_explaintips et disponible sur
GitHub, donne quelques astuces sur
certains plans. Pour cela, il faut que l’option TIPS soit ajoutée à la
commande EXPLAIN. Le module se charge soit avec la commande LOAD soit en
le configurant dans le paramètre shared_preload_libraries.
Par exemple, lorsqu’un plan contient un nœud Seq Scan avec beaucoup de lignes
ignorées par le filtre, une information est automatiquement affichée pour
préconiser l’ajout d’un index. Voici rapidement une démonstration.
Création de la table de test :
CREATE TABLE t1 (id integer);
INSERT INTO t1 SELECT generate_series(1, 10_000);
Exemple d’un filtre ramenant la majorité de la table (donc pas de conseil affiché malgré la présence de l’option) :
EXPLAIN (ANALYZE,COSTS OFF,TIPS) SELECT * FROM t1 WHERE id>2;
Seq Scan on t1 (actual time=0.042..0.337 rows=998.00 loops=1)
Filter: (id > 2)
Rows Removed by Filter: 2
Buffers: shared hit=5
Planning:
Buffers: shared hit=4
Planning Time: 0.079 ms
Execution Time: 0.479 ms
Exemple d’un filtre ramenant peu de lignes de la table (donc conseil affiché sur l’ajout d’un index) :
EXPLAIN (ANALYZE,COSTS OFF,TIPS) SELECT * FROM t1 WHERE id<2;
Seq Scan on t1 (actual time=0.014..0.113 rows=1.00 loops=1)
Filter: (id < 2)
Rows Removed by Filter: 9999
Buffers: shared hit=5
Tips: You should probably add an index!
Planning Time: 0.035 ms
Execution Time: 0.127 ms
Le module que j’ai développé n’est qu’un exemple de ce qu’il est possible de
faire, pas quelque chose de réellement utilisable en production (pour l’instant
en tout cas). Il est capable de détecter un cas où un index serait intéressant,
où la mise à jour de la VM pourrait améliorer les performances d’un nœud Index
Only Scan, ou d’un manque de mémoire pour les nœuds Sort et Hash. Il
dispose aussi de paramètres de configuration permettant de calibrer les seuils
minimums avant l’affichage d’une astuce.
En tout cas, cette nouvelle fonctionnalité intégrée par Robert Haas pourrait
permettre de voir l’apparition de nouveaux types d’extensions très intéressantes
pour les personnes qui jouent beaucoup avec EXPLAIN.
