Aller au contenu

EXPLAIN

La commande EXPLAIN devant une requête permet de récupérer son plan d’exécution. Seul le plan choisi par l’optimiseur parmi tous ceux possibles est affiché, donc à priori celui avec le coût calculé le plus faible.

C’est un passage obligé pour comprendre puis optimiser les performances d’une requête.

EXPLAIN seul donne juste le plan calculé, sans les détails de l’exécution, avec des nombres de lignes estimés et des coûts calculés (unités arbitraires).

Exemple avec une jointure et un agrégat (sous psql) :

# EXPLAIN SELECT COUNT(*)
  FROM employes_big
  INNER JOIN services s USING (num_service) ;

                                                        QUERY PLAN                                                        
══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════
 Aggregate  (cost=11900.68..11900.69 rows=1 width=8)
   ->  Hash Join  (cost=1.51..10653.14 rows=499015 width=0)
         Hash Cond: (employes_big.num_service = s.num_service)
         ->  Index Only Scan using employes_big_num_service_idx on employes_big  (cost=0.42..7954.25 rows=499015 width=4)
         ->  Hash  (cost=1.04..1.04 rows=4 width=4)
               ->  Seq Scan on services s  (cost=0.00..1.04 rows=4 width=4)
(6 lignes)

track_io_timing

Il est conseillé d’activer le paramètre de configuration track_io_timing sur les machines récentes pour afficher les temps passés hors du cache de PostgreSQL (détails et précautions).

Principales options d’EXPLAIN

Info

Le plus intéressant est d’avoir un plan réellement exécuté, avec les volumétries réelles et tous les détails, donc ainsi : 

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, WAL)  SELECT …

  • ANALYZE : exécute la requête (ATTENTION s’il y a un DELETE/INSERT/UPDATE ou des fonctions qui font des écritures !!). Nécessaire pour récupérer les volumétries réelles ;
  • VERBOSE : donne plus de détails. Utile pour une exploitation graphique, souvent trop verbeux pour la lecture directe ;
  • BUFFERS : affiche les volumétries en blocs (en cache de PG ou hors cache). Très utile pour savoir d’où viennent les données ;
  • SETTINGS : affiche en bas du plan les valeurs en cours des paramètres pouvant influencer le choix plan ; utile lors de tests sur ces paramètres ou pour un intervenant extérieur ;
  • WAL : affiche la quantité d’écritures dans les journaux. Utile. (PostgreSQL > 12) ;
  • COSTS OFF : allège l’affichage des coûts calculés ;
  • FORMAT JSON : sortie en JSON (et non texte), un peu plus complète, destinée à être exploitée par des outils.

À titre d’exemple, un plan complet exécuté de la requête précédente est visible graphiquement sur explain.dalibo.com (version texte).

SET track_io_timing = ON;
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, WAL)
SELECT COUNT(*)
  FROM employes_big
  INNER JOIN services s
  USING (num_service) ;

Outils

  • Des outils comme pgAdmin4 (touche F7) ou DBeaver (Shift-Ctrl-E) permettent de demander le plan d’une requête. L’affichage n’est pas forcément plus clair qu’un plan brut dans psql ;
  • explain.dalibo.com offre un affichage graphique sur les différents nœuds et offre quelques indicateurs visuels pointant de potentiels problèmes ;
  • explain.depesz.com est un autre outil reconnu, plus proche du plan texte ;
  • Détails sur ces outils et d’autres : Voir module de formation J0.

Pour aller plus loin