Analyse de coûts de requêtes (EXPLAIN)

DéfinitionPlan d'exécution

Le moteur SQL d'une BD peut afficher le plan qu'il prévoit d'exécuter pour une requête donnée, c'est à dire la liste des opération qui vont être exécutées, ainsi qu'une estimation du coup de ces opérations.

SyntaxeSyntaxe PostgreSQL

1
EXPLAIN <requête SQL>

Exemple

Soit la table "taero" des aérodromes de France.

  • SELECT count(*) FROM taero;

    retourne 421

  • SELECT * FROM taero LIMIT 7;

    retourne la table ci-après.

Sept premières lignes de la table "taero"
EXPLAIN SELECT code FROM taero

EXPLAIN SELECT code FROM taero retourne "Seq Scan on taero (cost=0.00..8.21 rows=421 width=16)"

  • L'opération exécute un sequential scan (parcours séquentiel de toute la table)

  • Le coût estimé de démarrage (coût pour récupérer le premier enregistrement) est de 0.00 et le coût estimé total (coût pour récupérer tous les tuples) est de 8.21.

    L'unité est relative aux pages disque accédées, mais elle peut être considérée arbitrairement pour faire des comparaisons.

  • Le nombre estimé de lignes rapportées est de 421, pour une taille de 16 octets chacune.

DéfinitionAnalyse d'exécution

Le moteur SQL d'une BD peut exécuter une requête et afficher le coût réellement observé d'exécution des opérations.

SyntaxeSyntaxe PostgreSQL

1
EXPLAIN ANALYSE <requête SQL>

Exemple

EXPLAIN ANALYSE SELECT code FROM taero

EXPLAIN ANALYSE SELECT code FROM taero retourne "Seq Scan on taero (cost=0.00..8.21 rows=421 width=16) (actual time=0.017..1.960 rows=421 loops=1) Total runtime: 3.668 ms"

  • Le coût réel de démarrage de l'opération est de 0.017ms et le coût réel total est de 1.960ms

  • Le nombre de lignes réellement rapporté est de 421

  • loops=1 indique l'opération de Seq Scan a été exécuté une seule fois

  • Le coût total de 3.668ms inclut le temps lié à l'environnement du moteur d'exécution (start and stop).

Attention

  1. EXPLAIN ne donne qu'une valeur estimée algorithmiquement du coût :

    • cette estimation est indépendante de contingences matériel, c'est un coût théorique

    • cette estimation est reproductible (chaque exécution donne des informations identiques)

    • l'instruction SQL n'est pas exécutée

  2. ANALYSE donne un temps de calcul mesuré réellement :

    • ce temps mesuré dépend de l'environnement matériel (état du réseau, charge du serveur, ...)

    • ce temps mesuré est variable à chaque exécution (en fonction des éléments précédents)

    • l'instruction SQL est exécutée

      (on peut insérer la requête à analyser dans une transaction et effectuer un ROLLBACK si l'on souhaite analyser l'exécution d'une requête sans l'exécuter)

Remarque

EXPLAIN n'existe pas dans le standard SQL.

ComplémentPour aller plus loin : PostgreSQL

SyntaxeSyntaxe sous Oracle

1
EXPLAIN PLAN FOR <requête SQL>

Il faut préalablement avoir créé ou avoir accès à une table particulière PLAN_TABLE qui sert à stocker les résultats du EXPLAIN.

ComplémentPour aller plus loin : Oracle