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

Plan 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.

Syntaxe PostgreSQL

EXPLAIN <requête SQL>

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.

Analyse 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.

Syntaxe PostgreSQL

EXPLAIN ANALYSE <requête SQL>

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).

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
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)

Mode diaporama